Optimasi Deep Pagination SQL saat Data Tumbuh di Produksi

Optimasi deep pagination SQL menjadi penting ketika tabel produksi terus membesar dan endpoint daftar mulai melambat di halaman-halaman tinggi. Gejalanya biasanya jelas: query dengan LIMIT/OFFSET yang awalnya terasa ringan menjadi makin mahal, CPU dan I/O naik, operasi sort membengkak, dan pengalaman pengguna memburuk karena halaman akhir memerlukan waktu jauh lebih lama dibanding halaman awal.

Masalah utamanya bukan sekadar ukuran data, tetapi cara database mengeksekusi pagination berbasis offset. Untuk mengambil halaman ke-1000, database umumnya tetap harus menemukan, mengurutkan, lalu melewati baris-baris sebelumnya sebelum mengembalikan sedikit hasil. Karena itu, solusi optimasi bukan hanya menambah hardware, tetapi memilih pola query, index, dan desain API yang sesuai.

Kenapa deep pagination memburuk saat data tumbuh

Masalah dasar pada LIMIT/OFFSET

Query seperti ini terlihat sederhana:

SELECT id, created_at, status, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Secara logis, Anda meminta 50 baris mulai dari posisi ke-50001. Tetapi secara fisik, database sering tetap harus:

mencari semua kandidat yang cocok dengan WHERE,
mengurutkannya sesuai ORDER BY,
melewati 50.000 baris pertama,
baru mengembalikan 50 baris berikutnya.

Semakin besar nilai OFFSET, semakin besar pekerjaan yang dibuang. Database tetap bekerja keras untuk baris yang tidak pernah dikirim ke aplikasi.

Gejala nyata di produksi

Pada sistem yang trafiknya tinggi, deep pagination biasanya muncul dalam bentuk:

Latency meningkat di halaman tinggi: halaman 1 cepat, halaman 500 sangat lambat.
CPU naik: terutama jika query memicu sort besar atau scan yang luas.
I/O meningkat: karena lebih banyak halaman data/index yang harus dibaca.
Memori bertambah untuk sort: jika hasil harus diurutkan sebelum dipotong oleh LIMIT.
UX menurun: pengguna melihat loading lebih lama, timeout, atau hasil yang terasa tidak konsisten.

Masalah ini sering baru terlihat saat data produksi tumbuh beberapa kali lipat, meskipun query yang sama tampak baik-baik saja di lingkungan lokal atau staging dengan dataset kecil.

Kenapa ORDER BY dan index sangat menentukan

Sort mahal jika database tidak bisa mengikuti urutan index

Pagination hampir selalu bergantung pada ORDER BY. Jika urutan yang diminta tidak didukung index yang tepat, database mungkin perlu melakukan sort eksplisit terhadap banyak baris. Ini mahal, terutama bila digabung dengan OFFSET besar.

Contoh query:

SELECT id, created_at, status
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

Query di atas akan jauh lebih mudah dioptimalkan jika ada index yang sejalan dengan pola filter dan urutan, misalnya index komposit pada kolom yang dipakai oleh WHERE dan ORDER BY.

Prinsip umum pemilihan index

Tanpa masuk ke detail vendor, prinsip umumnya adalah:

Kolom filter di WHERE harus membantu mempersempit kandidat lebih awal.
Kolom sort di ORDER BY harus sebisa mungkin selaras dengan index.
Jika urutan tidak unik, tambahkan tie-breaker unik seperti id agar hasil stabil.

Contoh urutan yang lebih aman untuk pagination:

ORDER BY created_at DESC, id DESC

Tanpa tie-breaker unik, dua baris dengan created_at yang sama bisa berpindah posisi antar request, menghasilkan duplikasi atau data terlewat.

Catatan: Index tidak otomatis menyelesaikan semua masalah deep pagination. Jika Anda tetap memakai OFFSET 500000, database masih mungkin harus melewati banyak entri index sebelum sampai ke posisi yang diminta.

Cara membaca EXPLAIN dan EXPLAIN ANALYZE secara umum

Saat query daftar mulai melambat, jangan menebak. Gunakan EXPLAIN atau EXPLAIN ANALYZE untuk melihat bagaimana database mengeksekusi query.

Apa yang perlu diperhatikan

Apakah terjadi scan luas: lihat apakah database membaca jauh lebih banyak baris daripada yang dikembalikan.
Apakah ada sort eksplisit: ini tanda bahwa urutan hasil tidak bisa langsung diambil dari index.
Estimasi vs realita: bila menggunakan EXPLAIN ANALYZE, perhatikan apakah jumlah baris aktual jauh berbeda dari estimasi.
Rows examined vs rows returned: jika selisihnya sangat besar, pagination Anda kemungkinan boros.
Urutan operasi: cek apakah filter diterapkan lebih awal atau justru setelah pembacaan besar.

Pola temuan yang sering muncul

Pada query offset yang buruk, Anda biasanya akan melihat tanda-tanda seperti:

database membaca puluhan ribu atau ratusan ribu baris untuk mengembalikan 50 baris,
sort dilakukan pada himpunan data besar,
index yang dipakai hanya membantu sebagian, tetapi tidak cukup untuk menghindari offset traversal.

Secara praktis, pertanyaan yang perlu dijawab dari EXPLAIN adalah: apakah database dapat langsung melanjutkan dari posisi terakhir, atau harus menghitung ulang dari awal lalu membuang sebagian besar hasil?

Offset pagination vs keyset pagination

Offset pagination: mudah, tetapi memburuk di halaman tinggi

Pendekatan offset umum dipakai karena sederhana dan cocok untuk UI berbasis nomor halaman:

SELECT id, created_at, status, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000;

Kelebihan:

mudah dipahami,
mudah dipetakan ke page number,
cocok untuk dataset kecil sampai menengah.

Kekurangan:

semakin mahal saat offset membesar,
rentan inkonsistensi saat data baru masuk atau data lama berubah,
bisa memicu sort dan scan besar.

Keyset/seek pagination: lebih stabil untuk data besar

Keyset pagination tidak meminta database melompat ke posisi ke-N, tetapi melanjutkan dari nilai terakhir yang sudah dilihat.

Contoh halaman pertama:

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

Misalkan baris terakhir pada hasil memiliki:

created_at = '2025-01-10 09:15:00'
id = 84219

Halaman berikutnya diambil dengan kondisi seek:

SELECT id, created_at, status, total
FROM orders
WHERE status = 'paid'
  AND (
    created_at < '2025-01-10 09:15:00'
    OR (created_at = '2025-01-10 09:15:00' AND id < 84219)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Alih-alih membuang 50.000 baris pertama, database melanjutkan dari titik terakhir. Pada dataset besar, ini biasanya jauh lebih efisien dan lebih stabil.

Syarat keyset pagination agar benar

Kolom sort harus stabil: nilainya tidak sering berubah setelah data dibuat, atau Anda menerima konsekuensi perubahan posisi.
Harus ada urutan total: jika kolom sort tidak unik, tambahkan tie-breaker unik seperti id.
Urutan query harus konsisten: kondisi seek harus mengikuti logika yang sama dengan ORDER BY.

Jika Anda mengurutkan dengan ORDER BY created_at DESC, id DESC, maka cursor juga harus menyimpan created_at dan id.

Kapan keyset lebih tepat

Pilih keyset/seek pagination jika:

tabel sangat besar,
trafik baca tinggi,
pengguna lebih sering menekan next atau load more daripada lompat ke halaman acak,
latency halaman tinggi sudah menjadi masalah produksi.

Desain query dan index yang lebih aman

Pilih urutan yang deterministik

Hindari:

ORDER BY created_at DESC

Lebih aman:

ORDER BY created_at DESC, id DESC

Dengan ini, setiap baris punya posisi yang jelas walaupun banyak data memiliki waktu yang sama.

Sesuaikan index dengan pola akses

Untuk query seperti:

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

Pertimbangkan index komposit yang mengikuti pola filter dan urutan tersebut. Bentuk pastinya tergantung engine dan distribusi data, tetapi prinsipnya adalah membantu database:

menemukan subset status = 'paid',
membaca hasil dalam urutan created_at, id,
mengurangi kebutuhan sort tambahan.

Jika query Anda punya banyak kombinasi filter yang berbeda-beda, tidak semua bisa diatasi dengan satu index. Di sinilah observasi query nyata di produksi penting untuk memilih index yang paling bernilai.

Jangan SELECT kolom berlebihan

Untuk endpoint list, ambil hanya kolom yang benar-benar dibutuhkan. Semakin lebar baris yang dibaca, semakin besar I/O dan memori yang terlibat.

Kurang efisien:

SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

Lebih baik:

SELECT id, created_at, customer_id, total, status
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Strategi migrasi endpoint tanpa merusak API

Banyak tim ingin berpindah dari offset ke keyset, tetapi khawatir merusak kontrak API yang sudah dipakai klien. Migrasi bisa dilakukan bertahap.

Strategi yang umum dipakai

Pertahankan endpoint lama untuk kompatibilitas, misalnya ?page=10&per_page=50.
Tambahkan mode cursor pada endpoint yang sama atau endpoint baru, misalnya ?limit=50&cursor=....
Kembalikan token cursor pada respons untuk halaman berikutnya.
Edukasi klien bahwa daftar besar sebaiknya memakai cursor, terutama untuk infinite scroll atau admin list aktif.
Gunakan fallback offset hanya untuk kasus tertentu seperti lompat ke halaman acak.

Contoh bentuk respons API

{
  "data": [
    { "id": 84250, "created_at": "2025-01-10T09:16:02Z", "status": "paid" },
    { "id": 84249, "created_at": "2025-01-10T09:15:58Z", "status": "paid" }
  ],
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xMFQwOToxNTo1OFoiLCJpZCI6ODQyNDl9",
  "has_more": true
}

Cursor biasanya berisi nilai kolom sort terakhir, lalu dienkode agar tidak membebani klien dengan detail internal. Tidak wajib terenkripsi, tetapi sebaiknya tervalidasi dan tidak mudah dimanipulasi tanpa kontrol.

Menjaga kompatibilitas dengan UI lama

Jika UI masih membutuhkan nomor halaman, Anda punya beberapa opsi:

biarkan halaman awal tetap memakai offset untuk dataset kecil,
gunakan cursor untuk navigasi next/previous,
batasi akses ke halaman yang sangat dalam,
sediakan filter yang lebih sempit agar pengguna tidak perlu menelusuri ribuan halaman.

Dalam banyak aplikasi, kebutuhan bisnis sebenarnya bukan “lompat ke halaman 873”, tetapi “temukan data yang relevan secepat mungkin”. Menambah filter dan pencarian sering lebih bernilai daripada mempertahankan pagination numerik penuh.

Trade-off dan keterbatasan yang perlu dipahami

Kelebihan offset

sederhana diimplementasikan,
mudah untuk page number,
cukup baik untuk tabel kecil atau halaman dangkal.

Kekurangan offset

memburuk seiring offset membesar,
hasil bisa bergeser jika ada insert/delete di tengah,
kurang efisien untuk trafik tinggi.

Kelebihan keyset

lebih efisien pada dataset besar,
lebih stabil untuk navigasi berurutan,
mengurangi biaya scan dan discard.

Kekurangan keyset

tidak alami untuk lompat ke halaman acak,
perlu desain cursor dan urutan sort yang disiplin,
lebih rumit jika kombinasi filter/sort sangat dinamis.

Kapan offset masih layak dipakai

LIMIT/OFFSET tidak selalu salah. Pendekatan ini masih layak jika:

dataset relatif kecil,
pengguna jarang mengakses halaman dalam,
hasil sudah dipersempit oleh filter yang selektif,
kebutuhan utama adalah page number tradisional,
profil performa menunjukkan query masih sehat.

Masalahnya bukan keberadaan offset itu sendiri, tetapi penggunaan offset besar pada tabel besar tanpa index dan pola akses yang sesuai.

Checklist debugging saat query pagination melambat

Apakah query memakai ORDER BY yang deterministik, misalnya ditambah id sebagai tie-breaker?
Apakah ada index yang mendukung WHERE dan ORDER BY?
Apakah EXPLAIN menunjukkan scan atau sort besar?
Berapa banyak baris yang dibaca dibanding yang dikembalikan?
Apakah Anda memakai SELECT * tanpa perlu?
Apakah halaman lambat hanya terjadi pada offset besar?
Apakah data sering berubah sehingga hasil offset tidak stabil?
Apakah kebutuhan UI sebenarnya bisa diubah menjadi next/previous atau load more?
Apakah filter bisa dipersempit agar pengguna tidak masuk ke deep pagination?
Apakah endpoint yang paling panas sudah diprioritaskan untuk migrasi ke cursor?

Kesalahan umum yang sering terjadi

Mengandalkan offset besar sebagai default tanpa menguji dengan data produksi realistis.
Mengurutkan dengan kolom yang tidak stabil, sehingga hasil pagination berubah-ubah.
Tidak menambahkan tie-breaker unik, menyebabkan duplikasi atau item hilang antar halaman.
Menambah index secara membabi buta tanpa melihat pola query nyata dan biaya write.
Mengukur dari halaman pertama saja, padahal masalah muncul di halaman dalam.

Penutup

Optimasi deep pagination SQL pada dasarnya adalah soal mengurangi pekerjaan yang tidak perlu. LIMIT/OFFSET mudah dipakai, tetapi pada dataset besar database sering tetap harus membaca, mengurutkan, dan membuang banyak baris sebelum menghasilkan sedikit data. Di situlah CPU, I/O, dan latency mulai melonjak.

Langkah yang biasanya paling efektif adalah memastikan ORDER BY deterministik, menyiapkan index yang sesuai dengan pola WHERE dan urutan sort, lalu beralih ke keyset/seek pagination untuk endpoint yang aktif dan tabel yang terus tumbuh. Offset tetap berguna untuk kasus tertentu, tetapi untuk produksi berskala besar, cursor-based pagination sering menjadi pilihan yang lebih sehat dan lebih stabil.

Quick Search

shopping_cart Keranjang