LIMIT/OFFSET terasa sederhana, tetapi performanya cenderung turun saat data membesar. Masalah utamanya bukan hanya query menjadi lebih lama, melainkan SQL engine harus membaca, menghitung, atau melompati semakin banyak row untuk mencapai offset yang diminta.

Untuk daftar transaksi, event, atau log yang terus bertambah, pendekatan yang lebih stabil biasanya adalah keyset pagination. Alih-alih berkata “ambil 50 data mulai dari baris ke-100000”, keyset berkata “ambil 50 data setelah nilai kunci terakhir yang sudah saya lihat”. Hasilnya biasanya lebih konsisten dan lebih hemat kerja di sisi database.

Mengapa LIMIT/OFFSET Melambat Saat Data Tumbuh

Query OFFSET umum terlihat seperti ini:

SELECT id, created_at, amount, status
FROM transactions
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

Secara logika, database tetap harus menemukan urutan hasil berdasarkan ORDER BY, lalu melewati 100000 row pertama sebelum mengembalikan 50 row berikutnya. Walaupun ada index, OFFSET besar tetap berarti pekerjaan besar.

Akar masalah di level SQL engine

  • Index scan tetap berjalan maju: engine menelusuri index sesuai urutan sort, tetapi row sebelum offset tetap harus dilewati satu per satu.
  • Semakin besar offset, semakin banyak row yang dibaca: biaya query tumbuh seiring nomor halaman.
  • Lookup tambahan bisa terjadi: jika index tidak menutup semua kolom yang dibutuhkan, engine mungkin perlu mengambil row dari tabel utama untuk banyak kandidat row.
  • Sort bisa mahal: bila index tidak cocok dengan ORDER BY, database mungkin harus melakukan sort eksplisit sebelum menerapkan LIMIT/OFFSET.

Akibat praktisnya adalah latency halaman awal mungkin baik, tetapi halaman jauh di belakang menjadi lambat. Ini umum terjadi pada dashboard admin, halaman audit log, histori transaksi, atau API list yang sering diakses dengan page number tinggi.

Dampaknya ke latency dan beban sistem

Saat OFFSET membesar, beban tidak hanya muncul pada satu query. Dalam sistem nyata, efeknya bisa berantai:

  • Waktu respon API meningkat pada halaman besar.
  • CPU dan I/O database naik karena lebih banyak row dipindai.
  • Cache query sulit membantu jika kombinasi halaman sangat banyak.
  • Traffic paralel membuat query OFFSET besar saling berebut resource.

Untuk tabel log atau transaksi yang terus bertambah, pola ini memburuk seiring waktu walaupun kode aplikasi tidak berubah.

Masalah Konsistensi: OFFSET Bisa Melewatkan atau Menggandakan Data

Selain performa, OFFSET juga punya masalah konsistensi hasil saat ada data baru masuk di antara dua request.

Misalnya halaman 1 diambil dengan query:

SELECT id, created_at, message
FROM logs
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 0;

Lalu sebelum pengguna membuka halaman 2, ada beberapa log baru yang masuk. Query halaman 2:

SELECT id, created_at, message
FROM logs
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50;

Karena posisi data sudah bergeser akibat insert baru di bagian atas, hasil halaman 2 bisa:

  • melewatkan row tertentu, atau
  • menampilkan row yang sebelumnya sudah muncul.

Ini terjadi karena OFFSET bergantung pada posisi dalam himpunan hasil, bukan pada identitas baris terakhir yang sudah dibaca.

Keyset Pagination: Ambil Data Berdasarkan Posisi Logis, Bukan Nomor Baris

Keyset pagination menggunakan nilai kolom pengurutan terakhir sebagai cursor. Untuk daftar yang diurutkan menurun berdasarkan waktu dan id, query awal bisa seperti ini:

SELECT id, created_at, amount, status
FROM transactions
ORDER BY created_at DESC, id DESC
LIMIT 50;

Setelah aplikasi menerima baris terakhir, misalnya:

  • created_at = '2025-04-01 10:15:00'
  • id = 987654

Maka halaman berikutnya diambil dengan:

SELECT id, created_at, amount, status
FROM transactions
WHERE (created_at < '2025-04-01 10:15:00')
   OR (created_at = '2025-04-01 10:15:00' AND id < 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dengan pendekatan ini, database tidak perlu menghitung offset besar. Engine cukup melanjutkan scan dari posisi kunci terakhir di index yang sesuai.

Mengapa keyset lebih cepat

  • Engine bisa langsung mencari titik awal berdasarkan nilai key cursor.
  • Jumlah row yang diproses cenderung dekat dengan ukuran halaman, bukan dengan nomor halaman.
  • Biaya query lebih stabil walaupun pengguna menavigasi jauh ke bawah.

Untuk tabel besar, ini biasanya jauh lebih ramah terhadap index scan dibanding OFFSET tinggi.

Contoh Kasus Nyata: Daftar Transaksi atau Log

Sebelum: OFFSET pada daftar transaksi

SELECT id, created_at, user_id, amount, status
FROM transactions
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100 OFFSET 20000;

Masalah query di atas:

  • Offset 20000 tetap harus dilewati.
  • Jika banyak transaksi baru masuk, hasil antarhalaman bisa bergeser.
  • Jika index tidak sesuai filter dan urutan, database bisa melakukan kerja tambahan yang mahal.

Sesudah: keyset pagination

Halaman pertama:

SELECT id, created_at, user_id, amount, status
FROM transactions
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100;

Halaman berikutnya memakai cursor dari row terakhir sebelumnya:

SELECT id, created_at, user_id, amount, status
FROM transactions
WHERE status = 'paid'
  AND (
    (created_at < '2025-04-01 10:15:00')
    OR (created_at = '2025-04-01 10:15:00' AND id < 987654)
  )
ORDER BY created_at DESC, id DESC
LIMIT 100;

Pola yang sama cocok untuk:

  • audit log
  • event stream
  • inbox pesan
  • histori invoice
  • data observability atau activity feed

Desain Index yang Cocok untuk Keyset Pagination

Keyset pagination hanya efektif bila didukung index yang sesuai dengan WHERE dan ORDER BY. Untuk contoh transaksi berstatus paid yang diurutkan dengan created_at DESC, id DESC, index komposit biasanya diperlukan.

CREATE INDEX idx_transactions_status_created_id
ON transactions (status, created_at DESC, id DESC);

Prinsip desain index:

  • Kolom filter yang sering dipakai biasanya ditempatkan lebih awal, misalnya status.
  • Kolom pengurutan keyset diletakkan setelahnya, misalnya created_at lalu id.
  • Urutan kolom di index perlu cocok dengan pola query yang dominan.

Beberapa database dapat memanfaatkan index meski arah ASC/DESC tidak selalu harus ditulis persis sama seperti query, tetapi jangan mengandalkan asumsi tanpa memeriksa EXPLAIN pada sistem Anda.

Syarat kolom pengurutan yang stabil

Keyset pagination membutuhkan urutan yang stabil dan deterministik. Ini berarti:

  • Kolom sort utama sebaiknya tidak sering berubah.
  • Jika kolom sort utama tidak unik, tambahkan tie-breaker unik, biasanya id.
  • Jangan memakai kolom yang nilainya bisa berubah-ubah di tengah navigasi jika Anda mengharapkan urutan konsisten.

Contoh buruk:

  • ORDER BY updated_at DESC pada tabel yang sering di-update. Row bisa berpindah posisi di antara request.

Contoh lebih aman:

  • ORDER BY created_at DESC, id DESC bila created_at mencerminkan urutan masuk data dan id unik.

Cursor Berbasis Kolom Unik

Di level API, cursor biasanya tidak dikirim sebagai offset, tetapi sebagai gabungan nilai kolom urutan terakhir. Contoh respons API:

{
  "data": [
    { "id": 987700, "created_at": "2025-04-01T10:16:10Z", "amount": 125000 },
    { "id": 987654, "created_at": "2025-04-01T10:15:00Z", "amount": 99000 }
  ],
  "next_cursor": "2025-04-01T10:15:00Z|987654"
}

Di implementasi nyata, cursor biasanya:

  • di-encode agar tidak raw, misalnya base64,
  • berisi semua komponen sort yang dibutuhkan,
  • divalidasi agar tidak rusak atau dimanipulasi sembarangan.

Contoh alur:

  1. Ambil halaman pertama tanpa cursor.
  2. Simpan nilai created_at dan id dari row terakhir.
  3. Kirim sebagai next_cursor.
  4. Request berikutnya memakai cursor itu untuk membentuk kondisi WHERE.

Jika hanya menggunakan created_at tanpa id, Anda berisiko kehilangan atau menduplikasi row yang memiliki timestamp sama.

Cara Membaca EXPLAIN secara Ringkas

Sebelum migrasi, periksa EXPLAIN pada query OFFSET dan keyset. Nama kolom hasil EXPLAIN berbeda antar database, tetapi fokus utamanya mirip.

Apa yang perlu dicari

  • Apakah index yang tepat digunakan? Pastikan query tidak jatuh ke full table scan jika seharusnya bisa memakai index.
  • Apakah ada sort tambahan? Jika database harus sort setelah membaca banyak row, biaya biasanya naik.
  • Berapa banyak row yang diperkirakan dibaca? OFFSET besar sering terlihat dari estimasi scan yang tinggi.
  • Apakah filter dan urutan cocok dengan index? Jika tidak cocok, keyset pun tidak akan optimal.

Interpretasi praktis

Untuk query OFFSET besar, Anda biasanya akan melihat tanda bahwa engine harus membaca banyak row sebelum mengembalikan sejumlah kecil hasil. Untuk keyset yang sehat, idealnya rencana eksekusi menunjukkan scan/index range yang dimulai dekat posisi cursor dan segera berhenti setelah memenuhi LIMIT.

Jangan hanya melihat bahwa “index dipakai”. Dua query sama-sama memakai index, tetapi jumlah row yang harus discan bisa sangat berbeda.

Trade-off Keyset Pagination

Keyset pagination bukan pengganti mutlak untuk semua kasus. Ada beberapa konsekuensi yang perlu diterima.

Kelebihan

  • Lebih stabil untuk data besar.
  • Latency lebih konsisten pada halaman lanjut.
  • Lebih tahan terhadap pergeseran hasil akibat insert baru.
  • Sangat cocok untuk feed, log, dan histori transaksi.

Kekurangan

  • Tidak nyaman untuk lompat ke halaman acak, misalnya langsung ke halaman 237.
  • Implementasi API lebih kompleks karena perlu cursor.
  • Query harus memakai urutan yang stabil; tidak semua kombinasi sort mudah didukung.
  • UI berbasis nomor halaman klasik perlu disesuaikan menjadi next/previous atau infinite scroll.

Kapan previous page lebih rumit

Maju ke halaman berikutnya biasanya mudah. Kembali ke halaman sebelumnya bisa memerlukan:

  • cursor terpisah,
  • menyimpan histori cursor di klien, atau
  • membalik operator dan urutan query lalu membalik hasil di aplikasi.

Ini bukan masalah besar untuk feed atau log, tetapi perlu dipikirkan jika UX Anda sangat bergantung pada navigasi bolak-balik yang presisi.

Checklist Migrasi dari OFFSET ke Keyset

  1. Identifikasi endpoint yang paling terdampak
    Cari query list dengan offset besar, latency tinggi, atau data yang sering bertambah.
  2. Tentukan urutan yang stabil
    Pilih kolom sort utama dan tie-breaker unik, misalnya created_at DESC, id DESC.
  3. Buat atau sesuaikan index komposit
    Pastikan index mendukung filter dan urutan query.
  4. Ubah kontrak API
    Ganti page atau offset dengan cursor untuk endpoint yang kritis.
  5. Encode dan validasi cursor
    Jangan memperlakukan cursor mentah sebagai input yang selalu aman.
  6. Uji konsistensi saat ada insert baru
    Simulasikan data masuk di antara request halaman.
  7. Bandingkan EXPLAIN sebelum dan sesudah
    Pastikan jumlah row yang dipindai menurun dan sort tambahan berkurang.
  8. Perbarui UI jika perlu
    Untuk keyset, pola next/previous biasanya lebih natural daripada nomor halaman absolut.

Kesalahan Umum Saat Menerapkan Keyset Pagination

  • Hanya memakai satu kolom non-unik sebagai cursor
    Contoh: hanya created_at. Jika banyak row punya timestamp sama, hasil bisa tidak lengkap.
  • ORDER BY tidak sesuai dengan kondisi WHERE
    Operator perbandingan harus konsisten dengan arah sort.
  • Tidak menambahkan tie-breaker unik
    Urutan menjadi tidak deterministik.
  • Index tidak cocok dengan query
    Akhirnya keyset tetap lambat karena engine tidak bisa melakukan range scan yang baik.
  • Menggunakan kolom yang sering berubah sebagai dasar sort
    Row dapat meloncat posisi di antara request.
  • Menganggap keyset cocok untuk semua kebutuhan UX
    Jika pengguna harus melompat ke halaman tertentu berdasarkan nomor, OFFSET mungkin masih lebih praktis.

Kapan OFFSET Masih Layak Dipakai

Meski punya keterbatasan, OFFSET belum tentu harus dihapus dari semua tempat. Pendekatan ini masih masuk akal bila:

  • ukuran data kecil atau menengah,
  • nomor halaman rendah dan jarang jauh,
  • pengguna memang butuh lompat ke halaman tertentu,
  • halaman administratif tidak sensitif terhadap sedikit pergeseran hasil,
  • query sudah cukup cepat dan tidak menjadi bottleneck nyata.

Contoh yang masih cocok:

  • halaman katalog internal dengan ribuan data, bukan puluhan juta,
  • laporan yang jarang diakses,
  • fitur backoffice dengan navigasi page number tradisional.

Intinya, jangan migrasi hanya karena tren. Migrasi masuk akal bila query list Anda sudah menunjukkan gejala: halaman lanjut lambat, beban database tinggi, atau hasil antarhalaman tidak konsisten saat data terus bertambah.

Kesimpulan

LIMIT/OFFSET melambat pada data besar karena database harus melewati semakin banyak row untuk mencapai posisi yang diminta. Ini berdampak langsung pada latency, jumlah row yang discan, dan konsistensi hasil saat ada insert baru.

Keyset pagination mengatasi masalah itu dengan memakai cursor berbasis kolom urutan yang stabil dan unik, sehingga database dapat melanjutkan pembacaan dari posisi logis terakhir, bukan menghitung ulang dari awal. Untuk daftar transaksi atau log berukuran besar, ini biasanya merupakan pilihan yang lebih efisien dan lebih konsisten, asalkan urutan data, index, dan desain API disusun dengan benar.