Stabilkan Query Lambat dengan Keyset Pagination dan Index yang Tepat

Jika query daftar data terasa cepat di awal tetapi melambat drastis saat tabel terus tumbuh, penyebabnya sering bukan sekadar “database kurang kuat”, melainkan pola akses yang boros. OFFSET/LIMIT memaksa database membaca, menyortir, lalu membuang banyak baris sebelum mengembalikan halaman yang diminta. Pada tabel besar, biaya ini terus naik.

Salah satu perbaikan paling efektif adalah keyset pagination: alih-alih berkata “lewati 10.000 baris”, kita berkata “ambil baris setelah cursor terakhir yang sudah saya lihat”. Pendekatan ini lebih hemat kerja, sejalan dengan pola pikir optimasi resource: hindari alokasi atau pekerjaan yang tidak perlu, kurangi scan berulang, dan pastikan index mendukung urutan akses yang benar.

Inspirasi yang mirip juga muncul di dunia optimasi memori: performa sering membaik bukan karena trik besar, tetapi karena kita berhenti melakukan kerja yang tidak perlu. Di SQL, kerja yang tidak perlu itu biasanya berupa sort mahal, scan panjang, dan pembuangan baris akibat OFFSET.

Mengapa OFFSET/LIMIT Menjadi Masalah saat Data Tumbuh?

Query seperti ini terlihat sederhana dan umum dipakai:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

Masalahnya, database tidak bisa “melompat” begitu saja ke baris ke-10001 tanpa biaya. Agar hasil tetap urut dan konsisten, engine sering tetap harus:

mencari kandidat baris yang sesuai filter,
menyusun urutan hasil,
melewati 10.000 baris pertama,
baru mengembalikan 20 baris berikutnya.

Pada volume data kecil, biaya ini mungkin masih tidak terasa. Namun pada tabel yang terus bertambah, efeknya muncul sebagai:

latensi halaman akhir yang jauh lebih lambat daripada halaman awal,
CPU naik karena sort dan scan berulang,
I/O meningkat karena banyak baris dibaca tetapi tidak dipakai,
performa tidak stabil saat traffic tinggi.

Ini mirip dengan alokasi memori yang boros: masalah utamanya bukan operasi final yang kecil, melainkan pekerjaan tambahan di belakang layar yang terus menumpuk.

Gejala Query Lambat yang Perlu Diwaspadai

Sebelum mengganti strategi pagination, identifikasi gejalanya dengan jelas. Beberapa tanda umum:

1. Halaman awal cepat, halaman jauh lambat

Jika page 1 atau offset 0 cepat, tetapi page 500 lambat, hampir pasti ada biaya skip yang besar.

2. EXPLAIN menunjukkan sort atau scan besar

Perhatikan apakah query melakukan:

filesort atau sort eksplisit,
scan banyak row dibanding jumlah hasil akhir,
penggunaan index yang hanya membantu filter tetapi tidak membantu urutan.

3. Index ada, tetapi tetap lambat

Ini sering terjadi karena index yang tersedia tidak sesuai dengan kombinasi WHERE + ORDER BY. Index yang “ada” belum tentu index yang “tepat”.

4. Data hasil kadang lompat atau duplikat saat data berubah

Pada sistem yang aktif menulis data, OFFSET juga bisa memunculkan inkonsistensi antarhalaman. Baris yang baru masuk di halaman awal dapat menggeser hasil di halaman berikutnya.

Keyset Pagination: Prinsip Kerja dan Alasan Lebih Stabil

Keyset pagination menggunakan nilai kolom terakhir dari halaman sebelumnya sebagai cursor. Bukan lagi “ambil halaman ke-500”, melainkan “ambil 20 baris setelah (created_at, id) tertentu”.

Misalnya urutan daftar adalah:

ORDER BY created_at DESC, id DESC

Maka halaman pertama:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Anggap baris terakhir yang diterima client memiliki nilai:

created_at = '2025-01-10 08:30:00'
id = 48123

Untuk halaman berikutnya, gunakan cursor:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
  AND (
    created_at < '2025-01-10 08:30:00'
    OR (created_at = '2025-01-10 08:30:00' AND id < 48123)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Mengapa ini lebih cepat?

Database tidak perlu membuang ribuan baris akibat OFFSET.
Dengan index yang cocok, engine bisa melanjutkan pencarian dari posisi cursor.
Biaya per halaman cenderung lebih stabil, bahkan saat data membesar.

Kolom id dipakai sebagai tie-breaker agar urutan deterministik. Jangan hanya mengandalkan created_at jika nilainya bisa sama pada banyak baris.

Contoh Query Buruk vs Perbaikan

Contoh buruk: filter ada, urutan ada, tapi OFFSET besar

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20000;

Masalah pada query ini:

skip 20.000 baris tetap mahal,
jika index tidak sesuai urutan kolom filter dan sort, engine bisa melakukan scan besar,
hasil makin lambat seiring pertumbuhan data.

Perbaikan dengan keyset pagination

Halaman pertama:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Halaman berikutnya:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
  AND (
    created_at < '2025-01-10 08:30:00'
    OR (created_at = '2025-01-10 08:30:00' AND id < 48123)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Secara logika, kita menyempitkan ruang pencarian ke “baris setelah cursor”, bukan memaksa engine membaca banyak hasil yang nantinya dibuang.

Jika database mendukung perbandingan tuple

Pada beberapa kondisi, bentuk ini lebih ringkas:

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
  AND (created_at, id) < ('2025-01-10 08:30:00', 48123)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Namun untuk portabilitas dan kemudahan debugging, bentuk eksplisit dengan OR sering lebih aman dijelaskan dan diverifikasi lintas engine.

Index Komposit yang Tepat: Bukan Sekadar “Sudah Ada Index”

Keyset pagination hanya benar-benar efektif jika didukung index yang sejalan dengan pola query. Untuk query sebelumnya, index yang sering masuk akal adalah:

-- PostgreSQL / MySQL (konsep umum)
CREATE INDEX idx_posts_tenant_status_created_id
ON posts (tenant_id, status, created_at, id);

Tujuannya bukan hanya mempercepat filter, tetapi juga membantu urutan akses sesuai ORDER BY created_at DESC, id DESC pada subset data tenant_id dan status yang relevan.

Mengapa urutan kolom index penting?

Urutan kolom di index menentukan seberapa baik engine bisa menggunakannya untuk:

menyaring baris dengan cepat,
mempertahankan urutan tanpa sort tambahan,
melanjutkan scan dari cursor.

Untuk query:

WHERE tenant_id = 42 AND status = 'published'
ORDER BY created_at DESC, id DESC

Urutan index yang biasanya lebih tepat adalah:

(tenant_id, status, created_at, id)

Bukan:

(created_at, id, tenant_id, status)

Alasannya: kolom filter dengan selektivitas yang dipakai selalu pada query sering lebih efektif diletakkan lebih depan agar engine cepat masuk ke rentang data yang benar, lalu membaca dalam urutan yang diinginkan.

Kesalahan umum dalam desain index

Hanya index di kolom filter, misalnya (tenant_id, status), tetapi tidak mencakup kolom urut. Akibatnya, database masih perlu sort.
Hanya index di kolom sort, misalnya (created_at, id), tetapi filter utama tidak terbantu sehingga scan tetap besar.
Urutan kolom terbalik, sehingga index tidak optimal untuk query dominan.
Tidak ada tie-breaker unik, sehingga pagination tidak stabil ketika banyak nilai sama.

Tentang ASC/DESC pada index

PostgreSQL dan MySQL memiliki perilaku implementasi yang berbeda dalam detail penggunaan arah index, tetapi prinsip umumnya tetap: samakan pola index dengan pola query yang paling sering dipakai, lalu verifikasi dengan EXPLAIN. Jangan mengandalkan asumsi tanpa melihat execution plan aktual.

EXPLAIN dan EXPLAIN ANALYZE: Cara Membaca Apakah Perbaikan Benar-Benar Bekerja

Jangan berhenti di teori. Jalankan EXPLAIN, dan jika aman di lingkungan yang sesuai, gunakan EXPLAIN ANALYZE untuk melihat eksekusi nyata.

Apa yang ingin Anda lihat?

Jumlah row yang dibaca jauh lebih kecil.
Sort besar berkurang atau hilang.
Index scan/range scan sesuai index komposit yang dirancang.
Biaya antarhalaman lebih konsisten.

Contoh pemeriksaan

EXPLAIN
SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20000;

Lalu bandingkan dengan:

EXPLAIN
SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'published'
  AND (
    created_at < '2025-01-10 08:30:00'
    OR (created_at = '2025-01-10 08:30:00' AND id < 48123)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Indikator praktis yang patut dicari

Nama node dan istilah berbeda antarengine, tetapi secara umum cari hal berikut:

apakah query menggunakan index yang Anda harapkan,
apakah ada operasi sort terpisah,
apakah estimasi dan realisasi jumlah row sangat jauh berbeda,
apakah filter dievaluasi setelah scan besar, bukan sejak awal akses index.

Jika estimasi row meleset jauh, statistik tabel mungkin perlu diperbarui. Pada sistem produksi, statistik yang buruk dapat membuat optimizer memilih rencana yang tidak efisien meski index sudah benar.

Sort Mahal, Index Tidak Selektif, dan Scan Berulang: Bottleneck Nyata di SQL

Tiga masalah ini sering muncul bersamaan.

1. Sort mahal

Jika hasil harus diurutkan setelah banyak baris dikumpulkan, query akan menghabiskan CPU dan memori lebih besar. Pada volume tinggi, ini bisa memicu spill ke disk atau memperlambat query lain.

2. Index tidak selektif

Kolom seperti status saja sering kurang selektif. Jika hampir semua baris bernilai published, index tunggal pada status tidak banyak membantu. Di sini index komposit dengan kolom pembatas lain seperti tenant_id atau timestamp menjadi lebih berguna.

3. Scan berulang

OFFSET besar membuat halaman berikutnya mengulang pekerjaan sebelumnya. Database terus membaca baris awal berulang-ulang hanya untuk mencapai posisi yang diminta. Keyset pagination menghindari pola ini dengan melanjutkan dari titik terakhir.

Trade-off dan Keterbatasan Keyset Pagination

Meski kuat, keyset pagination bukan solusi universal.

1. Tidak cocok untuk “lompat ke halaman 500” secara langsung

Jika UI mutlak membutuhkan nomor halaman arbitrer, keyset pagination kurang cocok. Pendekatan ini lebih pas untuk next/previous, infinite scroll, feed, dan daftar administratif yang bergerak maju-mundur lewat cursor.

2. Perlu urutan yang stabil dan deterministik

Anda harus punya kombinasi kolom yang membuat urutan konsisten, misalnya created_at, id. Jika urutan tidak unik, pengguna bisa melihat data duplikat atau terlewat.

3. Filter dinamis membuat jumlah index bisa meledak

Ini tantangan paling sering di sistem nyata. Misalnya API mendukung filter:

tenant_id
status
category_id
author_id
rentang tanggal

Anda tidak mungkin membuat index untuk semua kombinasi. Strateginya:

identifikasi query yang paling dominan berdasarkan log produksi,
optimalkan 1-3 pola akses utama,
hindari over-indexing yang memperlambat insert/update,
pisahkan endpoint atau mode pencarian jika kebutuhan sangat berbeda.

4. Cursor perlu dikelola dengan benar di API

Biasanya cursor dikirim ke client sebagai nilai kolom terakhir, sering kali dienkode agar tidak rawan salah parse. Namun isi cursor tetap harus merepresentasikan urutan query secara lengkap, misalnya created_at dan id, bukan hanya salah satunya.

Pola Implementasi API yang Praktis

Dalam API, bentuk respons umum untuk keyset pagination:

{
  "items": [
    { "id": 48140, "created_at": "2025-01-10T08:35:00Z", "title": "..." },
    { "id": 48123, "created_at": "2025-01-10T08:30:00Z", "title": "..." }
  ],
  "next_cursor": "2025-01-10T08:30:00Z|48123"
}

Saat request berikutnya datang, server mem-parse cursor lalu membangun kondisi WHERE yang sesuai. Praktik yang baik:

sertakan semua kolom sort dalam cursor,
validasi format cursor,
pastikan query berikutnya memakai filter yang sama seperti halaman pertama,
jangan mencampur cursor dari satu mode sort dengan mode sort lain.

Checklist Migrasi Aman dari OFFSET/LIMIT ke Keyset Pagination

Perubahan ini menyentuh query, API, dan perilaku UI. Lakukan bertahap.

Identifikasi endpoint paling lambat
Cari query paginasi dengan OFFSET besar atau latensi tinggi yang meningkat seiring pertumbuhan data.
Tentukan urutan yang stabil
Gunakan kolom utama sort ditambah tie-breaker unik, misalnya created_at DESC, id DESC.
Rancang index komposit
Sesuaikan dengan pola WHERE + ORDER BY yang paling dominan.
Verifikasi dengan EXPLAIN
Pastikan index dipakai, sort berkurang, dan row yang dibaca menurun.
Tambahkan dukungan cursor di API
Jangan langsung menghapus mode lama jika masih dipakai client lama.
Uji konsistensi data antarhalaman
Perhatikan kasus nilai timestamp sama, insert baru di tengah navigasi, dan filter yang berubah.
Monitor dampak write
Index tambahan mempercepat baca, tetapi bisa menambah biaya insert/update/delete.
Rollout bertahap
Aktifkan per endpoint atau per client, lalu bandingkan metrik latensi dan load database.

Debugging Tips saat Hasil Masih Lambat

1. Query sudah keyset, tetapi tetap scan besar

Cek apakah index komposit sesuai urutan filter dan sort. Jika tidak, keyset pagination tidak akan memberi hasil maksimal.

2. Masih ada sort di execution plan

Berarti index belum bisa memenuhi urutan yang diminta. Tinjau ulang susunan kolom index dan konsistensi arah ORDER BY.

3. Hasil duplikat atau hilang

Biasanya karena cursor tidak menyertakan tie-breaker unik, atau kondisi </> tidak cocok dengan arah sorting.

4. Query tertentu cepat, query lain lambat

Jangan menggeneralisasi satu index untuk semua filter dinamis. Fokus pada pola akses yang paling sering dan paling mahal.

5. Optimizer memilih index yang tidak diharapkan

Periksa statistik tabel, distribusi data, dan apakah query sebenarnya cukup selektif. Terkadang masalahnya bukan pada adanya index, tetapi pada estimasi optimizer yang salah.

Kapan Tetap Memakai OFFSET/LIMIT?

OFFSET/LIMIT masih masuk akal untuk beberapa kasus:

tabel kecil,
halaman yang dangkal,
alat internal dengan volume rendah,
kebutuhan kuat untuk lompat ke nomor halaman tertentu.

Namun untuk feed, log, transaksi, event, atau tabel multi-tenant yang terus tumbuh, keyset pagination hampir selalu lebih stabil karena ia mengurangi pekerjaan yang tidak perlu sejak level akses data.

Penutup

Menstabilkan query lambat pada tabel besar bukan hanya soal menambah server atau memasang index secara acak. Kuncinya adalah mengubah pola kerja query agar lebih hemat resource: hindari sort mahal, hindari scan berulang akibat OFFSET, dan pastikan index mengikuti pola WHERE + ORDER BY.

Keyset pagination adalah pendekatan praktis untuk itu. Dengan cursor yang tepat, urutan yang deterministik, dan index komposit yang dirancang berdasarkan query nyata, Anda bisa menjaga latensi tetap lebih konsisten saat data terus bertambah. Mulailah dari endpoint paling lambat, ukur dengan EXPLAIN/EXPLAIN ANALYZE, lalu migrasikan bertahap dengan checklist yang aman.

Quick Search

shopping_cart Keranjang