Masalah Performa OFFSET/LIMIT pada Dataset Besar

Pola pagination standar menggunakan klausa LIMIT dan OFFSET memiliki kompleksitas waktu O(N). Ketika mengeksekusi query seperti SELECT * FROM transactions ORDER BY created_at DESC LIMIT 20 OFFSET 1000000, database engine tidak langsung melompat ke baris ke-1.000.001. Mesin database harus membaca 1.000.020 baris data dari disk/buffer pool, mengurutkannya, lalu membuang 1.000.000 baris pertama untuk mengembalikan 20 baris yang diminta.

Operasi ini menyebabkan pembacaan I/O disk yang masif, pemborosan memori (buffer pool churn), dan latensi respons yang berbanding lurus dengan kedalaman halaman (deep paging). Solusinya adalah keyset pagination (cursor pagination), di mana client mengirimkan penanda data terakhir (anchor) yang dilihat, dan query berikutnya menyaring baris menggunakan perbandingan kondisi WHERE.

Rancang Bangun Indeks Komposit B-Tree

Keyset pagination bergantung pada klausa WHERE yang memanfaatkan kolom pengurutan sebagai penanda. Kolom tunggal seperti created_at umumnya tidak unik—bisa terdapat beberapa data dengan timestamp identik dalam satu milidetik. Jika hanya menggunakan created_at, pagination akan melewatkan baris atau menampilkan duplikasi data.

Untuk menjamin determinisme, kolom unik sekunder seperti id (Primary Key) wajib digunakan sebagai tiebreaker. Indeks komposit B-Tree harus disusun selaras dengan urutan sort query.

-- Skema Indeks Komposit pada PostgreSQL / MySQL
CREATE INDEX idx_transactions_created_at_id 
ON transactions (created_at DESC, id DESC);

Aturan B-Tree: Urutan kolom pada definisi indeks harus sama persis dengan urutan kolom pada klausa ORDER BY. Jika query mengurutkan created_at DESC, id DESC, maka indeks harus mencakup created_at terlebih dahulu, diikuti oleh id.

Implementasi Backend: Keyset Pagination Laravel

Framework modern seperti Laravel menyediakan implementasi native keyset pagination melalui cursorPaginate(). Metode ini mengkodekan nilai penanda kolom sort ke dalam string base64URL.

namespace App\Http\Controllers;

use App\Models\Transaction;
use Illuminate\Http\Request;
use Inertia\Inertia;
use Inertia\Response;

class TransactionController extends Controller
{
    public function index(Request $request): Response
    {
        $transactions = Transaction::query()
            ->when($request->input('status'), function ($query, $status) {
                $query->where('status', $status);
            })
            ->orderBy('created_at', 'desc')
            ->orderBy('id', 'desc')
            ->cursorPaginate(20)
            ->withQueryString();

        return Inertia::render('Transactions/Index', [
            'transactions' => $transactions,
            'filters' => $request->only(['status']),
        ]);
    }
}

Secara internal, Laravel menyusun query SQL keyset seperti berikut:

SELECT * FROM transactions 
WHERE (created_at < '2026-03-29 10:00:00') 
   OR (created_at = '2026-03-29 10:00:00' AND id < 542010)
ORDER BY created_at DESC, id DESC 
LIMIT 21;

Analisis Query Plan: EXPLAIN ANALYZE

Perbandingan eksekusi query pada tabel berisi 2.500.000 baris data:

1. Pendekatan OFFSET (Sebelum Optimasi)

EXPLAIN ANALYZE 
SELECT * FROM transactions 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 500000;

Hasil Query Plan:

Limit  (cost=42150.12..42151.81 rows=20 width=128) (actual time=248.512..248.517 rows=20 loops=1)
  ->  Index Scan Backward using idx_transactions_created_at on transactions  
      (cost=0.43..210748.50 rows=2500000 width=128) (actual time=0.062..214.331 rows=500020 loops=1)
Execution Time: 249.120 ms

Database terpaksa membaca 500.020 baris data dari indeks dan heap table sebelum menyaring 20 baris target.

2. Keyset Pagination + Indeks Komposit (Setelah Optimasi)

EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE (created_at < '2026-03-25 14:10:02') 
   OR (created_at = '2026-03-25 14:10:02' AND id < 1845120)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Hasil Query Plan:

Limit  (cost=0.43..2.11 rows=20 width=128) (actual time=0.041..0.052 rows=20 loops=1)
  ->  Index Scan using idx_transactions_created_at_id on transactions  
      (cost=0.43..208912.10 rows=2480000 width=128) (actual time=0.039..0.049 rows=20 loops=1)
      Index Cond: ((created_at < '2026-03-25 14:10:02'::timestamp) OR ((created_at = '2026-03-25 14:10:02'::timestamp) AND (id < 1845120)))
Execution Time: 0.078 ms

Index seek langsung melompat ke titik koordinat pointer B-Tree tanpa memindai baris sebelumnya. Waktu eksekusi turun drastis dari 249 ms menjadi 0.078 ms.

Integrasi Frontend: Continuous Loading di Inertia.js

Frontend bertugas merender data awal, memantau posisi scroll, dan memicu partial reload menggunakan Inertia router untuk mengambil set data berikutnya tanpa me-reset scroll window.

Edge Cases: Dynamic Filtering dan Index Prefix Rule

Ketika aplikasi menerapkan filter dinamis (misalnya WHERE status = 'PAID'), indeks (created_at, id) tidak lagi optimal. Mesin database harus melakukan filter post-scan terhadap baris yang diambil dari indeks.

Aturan Leftmost Prefix

Kolom pencarian dengan selektivitas tinggi yang menggunakan perbandingan kesetaraan (=) harus diposisikan di urutan terdepan dalam indeks komposit sebelum kolom rentang (range/sorting):

-- Jika sering memfilter berdasarkan status:
CREATE INDEX idx_transactions_status_created_id 
ON transactions (status, created_at DESC, id DESC);

Jika filter bersifat dinamis dengan banyak kombinasi kolom (misal: user_id, status, payment_method), pembuatan indeks tunggal untuk setiap kombinasi tidak realistis. Pendekatan standard engineering:

  • Prioritaskan indeks komposit pada kolom tenant/relasi wajib (misal: tenant_id, created_at DESC, id DESC).
  • Bila filter dinamis menghasilkan sub-dataset kecil (cardinality rendah), biarkan engine memfilter in-memory setelah menyaring tenant.
  • Hindari cursor pagination jika sistem mewajibkan navigasi acak langsung ke nomor halaman tertentu (misalnya melompat dari halaman 1 langsung ke halaman 50), karena keyset membutuhkan referensi baris sebelumnya. Gunakan cursor pagination untuk infinite scroll, log viewer, timeline feeds, dan export data.