Query Lambat Saat Data Tumbuh: Keyset Pagination dan Index yang Tepat

Masalah paling umum pada endpoint daftar data bukan karena query-nya salah secara sintaks, tetapi karena query yang tampak aman saat data masih sedikit menjadi mahal saat tabel membesar. Query lambat saat data tumbuh sering muncul pada endpoint yang memakai OFFSET/LIMIT, sorting yang tidak didukung index, atau kombinasi filter dan urutan hasil yang tidak cocok dengan susunan kolom pada index.

Di praktik sehari-hari, banyak developer terbantu oleh ORM, autocomplete SQL, atau saran AI untuk menghasilkan query yang benar dan cepat dibuat. Itu berguna, tetapi ada risiko: bottleneck sebenarnya tersembunyi sampai data masuk ke ratusan ribu atau jutaan baris. Artikel ini fokus pada diagnosis dan perbaikan yang konkret: perbedaan offset vs keyset pagination, pemilihan composite index, urutan kolom index, filter + sort, dan cara membaca EXPLAIN secara dasar.

Mengapa query list yang awalnya cepat tiba-tiba melambat?

Gejalanya biasanya muncul di produksi lebih dulu daripada di lokal:

Halaman awal cepat, tetapi halaman ke-100 atau ke-1000 jauh lebih lambat.
CPU database naik saat traffic endpoint list meningkat.
Latency p95/p99 memburuk, sementara query yang sama terlihat sederhana.
Sorting pada kolom tertentu terasa berat meskipun filter tampak selektif.
Timeout atau koneksi pool habis saat user sering menelusuri halaman data lama.

Penyebabnya sering bukan satu hal. Beberapa pola yang umum:

OFFSET besar: database tetap harus melewati banyak baris sebelum mengambil hasil yang diminta.
Index tidak sesuai pola akses: ada index, tetapi urutan kolomnya tidak membantu filter dan sorting yang dipakai query.
Sort tidak ter-cover: database perlu melakukan sort tambahan karena urutan hasil tidak bisa diambil langsung dari index.
Query dari ORM terlihat rapi, tetapi tidak efisien: join, count, eager loading, atau default ordering menambah biaya tanpa disadari.

Intinya, query yang “jalan” belum tentu query yang “skala”.

OFFSET/LIMIT: mudah dipakai, tetapi mahal saat data besar

Contoh query yang umum

SELECT id, created_at, status, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Query ini terlihat wajar. Masalahnya, untuk mengambil 20 baris setelah offset 10000, database biasanya tetap harus menemukan, membaca, atau melewati banyak baris lebih dulu. Semakin besar offset, semakin besar kerja yang dibuang.

Mengapa OFFSET makin lambat?

OFFSET tidak berarti database bisa “lompat” ke baris ke-10001 secara gratis. Database tetap harus berjalan mengikuti urutan hasil. Jika urutan itu tidak didukung index yang tepat, biayanya makin besar karena ada scan dan sort tambahan.

Ini sangat terasa pada endpoint seperti:

Riwayat transaksi
Daftar order admin
Feed aktivitas
Log audit
Inbox atau notifikasi

Pada dataset kecil, masalah ini sering tidak terlihat. Saat data tumbuh, barulah latensi naik secara tidak proporsional.

Kapan OFFSET masih masuk akal?

OFFSET/LIMIT masih layak dipakai jika:

Data relatif kecil.
User benar-benar butuh lompat ke halaman acak, misalnya halaman 17.
Endpoint bukan jalur panas (hot path).
Sorting sederhana dan didukung index dengan baik.

Namun untuk endpoint list yang aktif dipakai dan tabelnya terus membesar, ini biasanya hanya solusi sementara.

Keyset pagination: ambil data berdasarkan posisi terakhir, bukan nomor halaman

Keyset pagination mengganti konsep “halaman ke-N” menjadi “lanjut setelah item terakhir yang sudah saya lihat”. Ini biasanya lebih efisien karena database tidak perlu membuang ribuan baris akibat offset.

Contoh dasar keyset pagination

Misalnya daftar order diurutkan dari yang terbaru:

SELECT id, created_at, status, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Untuk halaman berikutnya, gunakan nilai baris terakhir sebagai cursor:

SELECT id, created_at, status, total_amount
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < ('2025-01-15 10:30:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Kenapa ada id juga? Karena created_at sering tidak unik. Tanpa tie-breaker yang stabil, hasil pagination bisa lompat, duplikat, atau ada data yang terlewat. Pasangan (created_at, id) memberi urutan yang deterministik.

Mengapa keyset lebih efisien?

Database bisa memanfaatkan index untuk langsung mencari posisi awal yang relevan, lalu membaca beberapa baris berikutnya sesuai urutan. Kerjanya mendekati “mulai dari titik ini”, bukan “lewati 10000 dulu”.

Kapan keyset pagination cocok?

Feed atau timeline
Daftar transaksi terbaru
Audit trail
Event log
Semua endpoint “next page” yang berorientasi urutan waktu atau ID

Trade-off keyset pagination

Tidak ideal untuk lompat ke halaman acak: sulit mendukung “langsung ke halaman 245”.
Perlu urutan yang stabil: biasanya butuh kolom unik tambahan sebagai tie-breaker.
Cursor handling lebih kompleks: API perlu mengirim dan memvalidasi cursor.
UI perlu menyesuaikan: lebih cocok untuk “load more” atau next/previous daripada pagination angka klasik.

Jika kebutuhan produk utamanya adalah menelusuri data terbaru atau melanjutkan dari posisi terakhir, keyset biasanya lebih tepat daripada OFFSET/LIMIT.

Composite index: bukan sekadar “ada index”, tetapi cocok dengan query

Kesalahan umum adalah menganggap satu index per kolom sudah cukup. Padahal query list hampir selalu menggabungkan filter + sort + pagination. Karena itu, sering kali yang dibutuhkan adalah composite index dengan urutan kolom yang selaras dengan pola query.

Contoh kasus

Query:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Index yang lebih relevan biasanya mendekati pola ini:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);

Kenapa urutannya seperti itu?

Kolom filter dengan pencocokan exact seperti tenant_id dan status diletakkan lebih depan.
Kolom sorting seperti created_at dan id diletakkan setelahnya.
Dengan susunan ini, database punya peluang lebih besar untuk memfilter dan sekaligus mengembalikan hasil sesuai urutan tanpa sort tambahan.

Urutan kolom index sangat penting

Index pada (created_at, status) tidak setara dengan (status, created_at). Jika query Anda memfilter status = 'paid' lalu mengurutkan created_at, urutan kolom bisa menentukan apakah index benar-benar membantu atau hanya sebagian.

Secara umum:

Letakkan kolom yang dipakai untuk filter exact di depan.
Letakkan kolom sort setelah filter exact.
Gunakan tie-breaker unik seperti id bila sort utama tidak unik.

Namun jangan menelan aturan ini sebagai dogma tunggal. Distribusi data, selektivitas filter, dan optimizer database tetap berpengaruh. Karena itu validasi dengan EXPLAIN tetap wajib.

Kesalahan yang sering terjadi

Membuat banyak single-column index, tetapi tidak ada composite index untuk query utama.
Menaruh kolom sort di depan padahal query lebih dulu menyaring berdasarkan tenant atau status.
Mengurutkan berdasarkan kolom yang tidak stabil tanpa tie-breaker.
Menganggap index yang bagus untuk satu query akan otomatis bagus untuk semua query.

Filter + sort harus dipikirkan bersama

Banyak bottleneck muncul karena filter dan sorting dirancang terpisah. Contohnya, developer menambahkan filter status karena fitur bisnis membutuhkan itu, lalu menambahkan sorting terbaru karena UI memintanya. Query tetap benar, tetapi index lama tidak lagi cocok.

Contoh yang tampak ringan, tetapi mahal

SELECT id, created_at, priority, status
FROM tickets
WHERE project_id = 99
  AND status IN ('open', 'pending')
ORDER BY priority DESC, created_at DESC
LIMIT 50;

Jika hanya ada index pada project_id atau status, database mungkin tetap harus mengumpulkan banyak kandidat lalu menyortirnya. Untuk endpoint yang sering dipanggil, pola ini cepat menjadi mahal.

Pertanyaan yang perlu diajukan saat mendesain index:

Filter exact apa yang hampir selalu ada?
Urutan hasil default apa yang dipakai UI/API?
Apakah ada multi-tenant scope yang seharusnya selalu paling depan di index?
Apakah kolom sort unik? Jika tidak, apa tie-breakernya?

Membaca EXPLAIN secara dasar untuk menemukan bottleneck nyata

Anda tidak perlu menjadi pakar query planner untuk mulai mendiagnosis masalah. Yang penting adalah bisa melihat apakah database memakai index yang benar, membaca terlalu banyak baris, atau melakukan sort tambahan.

Langkah praktis

Ambil query SQL final yang benar-benar dikirim aplikasi, bukan pseudo query dari kode.
Jalankan EXPLAIN pada query tersebut di environment yang representatif.
Perhatikan apakah index yang dipakai sesuai harapan.
Bandingkan jumlah baris yang diperkirakan/dibaca dengan jumlah hasil yang diambil.
Lihat apakah ada indikasi sort tambahan atau scan yang terlalu lebar.

Apa yang dicari dari EXPLAIN?

Jenis akses: apakah database menggunakan index, atau justru melakukan scan luas.
Index yang dipakai: apakah optimizer memilih composite index yang Anda siapkan.
Perkiraan jumlah baris: jika jauh lebih besar dari LIMIT, ada sinyal inefisiensi.
Operasi sort: jika urutan hasil tidak bisa dipenuhi dari index, database biasanya perlu kerja tambahan.

Nama field pada output EXPLAIN berbeda antar database, jadi fokuslah pada konsepnya: apakah query ini langsung menemukan baris yang relevan dengan index, atau harus menyapu dan menyortir terlalu banyak data?

Contoh interpretasi sederhana

Jika query LIMIT 20 OFFSET 10000 menunjukkan pembacaan ribuan atau puluhan ribu baris untuk menghasilkan 20 baris, itu tanda kuat bahwa offset menjadi biaya utama. Jika query keyset dengan index yang tepat hanya membaca sedikit lebih banyak dari jumlah hasil, biasanya itu arah yang benar.

Jangan hanya melihat “query pakai index” lalu menganggap masalah selesai. Query bisa tetap lambat meski memakai index yang salah atau hanya memanfaatkan sebagian kecil dari kebutuhan filter + sort.

Contoh migrasi dari OFFSET ke keyset pada endpoint produksi

Versi awal

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 20000;

Gejala di produksi:

Halaman pertama cepat, halaman dalam lambat.
Lonjakan penggunaan CPU database saat admin sering menelusuri histori.
Respons API tidak stabil pada jam sibuk.

Versi yang lebih baik

Tambahkan urutan yang stabil:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

Halaman berikutnya:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-15 10:30:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Index yang mendukung:

CREATE INDEX idx_orders_tenant_created_id
ON orders (tenant_id, created_at DESC, id DESC);

Jika nanti ditambah filter status yang hampir selalu dipakai:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);

Anda tidak harus membuat semua variasi index sekaligus. Mulailah dari query paling panas dan paling mahal.

Pengaruh ORM, autocomplete SQL, dan saran AI dalam konteks ini

Masalah performa list endpoint sering lolos karena alat bantu modern membuat query terasa terlalu mudah. ORM mempermudah pagination, autocomplete mempercepat penulisan SQL, dan saran AI dapat menghasilkan query yang valid dalam hitungan detik. Semuanya berguna, tetapi biasanya tidak memahami konteks produksi Anda secara penuh: distribusi data, cardinality, pola akses user, multi-tenant filtering, atau index yang sudah ada.

Risiko utamanya bukan alat bantu itu sendiri, melainkan kebiasaan menerima query default tanpa memeriksa:

Apakah ORDER BY punya tie-breaker yang stabil?
Apakah pagination masih berbasis offset meski data terus tumbuh?
Apakah index mengikuti pola filter + sort nyata?
Apakah SQL final dari ORM sama dengan yang Anda bayangkan?
Apakah EXPLAIN menunjukkan scan atau sort yang mahal?

Jadikan alat bantu sebagai akselerator penulisan, bukan pengganti verifikasi performa.

Checklist kapan harus migrasi dari OFFSET/LIMIT

Gunakan checklist ini untuk memutuskan kapan endpoint sebaiknya beralih ke keyset pagination:

Latency meningkat jelas pada halaman yang lebih dalam.
Tabel tumbuh dari ribuan ke ratusan ribu atau jutaan baris.
Endpoint list menjadi jalur panas dengan traffic tinggi.
User lebih sering menelusuri data terbaru daripada lompat ke halaman acak.
Query memiliki urutan alami yang stabil, misalnya created_at + id atau id saja.
EXPLAIN menunjukkan pembacaan baris jauh lebih banyak daripada hasil yang dikembalikan.
Sorting dan filtering sudah cukup stabil sehingga layak dioptimalkan dengan composite index.

Jika sebagian besar jawaban adalah ya, migrasi ke keyset biasanya layak diprioritaskan.

Tips diagnosis dan debugging yang sering membantu

Log query final: terutama untuk query yang dibangun ORM secara dinamis.
Uji dengan data realistis: bottleneck sering tidak muncul di dataset kecil.
Bandingkan halaman awal vs halaman dalam: ini cepat mengungkap biaya offset.
Pastikan urutan stabil: tanpa tie-breaker, pagination bisa tidak konsisten.
Evaluasi index satu per satu: terlalu banyak index juga menambah biaya write.
Periksa count query: kadang bottleneck bukan hanya query list, tetapi juga total count untuk pagination angka.

Trade-off yang perlu diingat

Keyset pagination unggul untuk performa dan konsistensi urutan, tetapi kurang cocok untuk navigasi halaman acak.
Composite index membantu query baca, tetapi menambah biaya insert/update/delete dan konsumsi storage.
Menambah index tanpa analisis bisa membuat write path melambat atau optimizer bingung memilih index.
Query yang optimal untuk satu endpoint belum tentu optimal untuk endpoint lain.

Penutup

Ketika tabel tumbuh, bottleneck pada endpoint list biasanya datang dari pola yang berulang: OFFSET/LIMIT yang makin mahal, sorting tanpa urutan stabil, dan index yang tidak mengikuti kombinasi filter + sort. Solusi praktisnya bukan sekadar “tambah index”, tetapi memahami bagaimana query dijalankan.

Untuk mencegah query lambat saat data tumbuh, mulai dari endpoint yang paling sering dipakai: lihat SQL final, jalankan EXPLAIN, ukur perbedaan halaman awal dan halaman dalam, lalu pertimbangkan migrasi ke keyset pagination dengan composite index yang tepat. Dengan langkah ini, Anda bisa menjaga endpoint list tetap responsif saat data berubah dari ribuan menjadi jutaan baris.

Quick Search

shopping_cart Keranjang