Offset Pagination yang Membusuk saat Data Tumbuh: Audit, EXPLAIN, dan Fix Bertahap

Offset pagination yang membusuk saat data tumbuh biasanya bukan bug mendadak, melainkan utang performa yang baru terlihat ketika jumlah baris, pola sort, dan kebutuhan hitung total mulai membesar. Query yang dulu terasa normal seperti LIMIT 20 OFFSET 10000 bisa berubah mahal karena database tetap harus melewati banyak baris sebelum mengembalikan halaman yang diminta.

Masalah ini sering muncul di feed, admin listing, halaman pencarian, atau endpoint API yang terlihat sederhana. Tanda-tandanya khas: halaman awal cepat, halaman belakang lambat; CPU database naik; query count total ikut mahal; dan sorting tertentu tiba-tiba membuat request timeout. Solusinya bukan sekadar menaikkan spesifikasi server, tetapi mengaudit bentuk query, urutan sort, indeks, serta mengganti offset pagination di titik yang tepat dengan pendekatan yang lebih stabil.

Mengapa offset pagination awalnya terasa aman lalu membusuk

Pada dataset kecil, biaya OFFSET sering tidak terasa. Database masih bisa membaca beberapa ratus atau ribu baris dengan cepat. Masalahnya muncul ketika:

jumlah data bertambah menjadi ratusan ribu atau jutaan baris,
pengurutan dilakukan pada kolom yang tidak didukung indeks yang sesuai,
query memakai filter tambahan yang mengubah rencana eksekusi,
setiap request juga meminta COUNT(*) untuk total halaman.

Secara konsep, OFFSET 10000 LIMIT 20 tidak berarti database “lompat” langsung ke baris ke-10001. Pada banyak kasus, engine tetap perlu menemukan dan membuang 10000 baris terlebih dahulu, lalu baru mengambil 20 berikutnya. Jika pengurutannya membutuhkan sorting eksplisit, biaya ini menjadi lebih mahal lagi.

Pola yang sering menipu: halaman 1 sampai 5 cepat saat pengujian lokal, lalu halaman 300 di production menjadi lambat karena data, distribusi nilai, dan konkurensi sudah berbeda jauh.

Gejala yang perlu dicurigai di production

1. Halaman awal cepat, halaman akhir lambat

Ini gejala paling klasik. Request dengan OFFSET kecil terlihat sehat, tetapi latency naik seiring nomor halaman. Jika metrik hanya mengamati halaman awal, masalah ini mudah lolos.

2. Sorting tertentu jauh lebih lambat daripada sorting lain

Contohnya listing default diurutkan berdasarkan created_at DESC masih cukup cepat, tetapi ketika pengguna memilih urut berdasarkan updated_at atau score, respons tiba-tiba buruk. Biasanya ini berarti indeks yang ada hanya cocok untuk sebagian sort.

3. Endpoint terasa lambat padahal hasil yang dikembalikan sedikit

Developer sering berasumsi “kan cuma ambil 20 baris”. Yang mahal bukan jumlah hasil akhirnya, melainkan pekerjaan untuk menemukan 20 baris itu.

4. Query hitung total lebih mahal dari query datanya

Di banyak admin panel atau API, respons pagination mencakup total item, total halaman, dan current page. Jika COUNT(*) harus memindai banyak baris dengan filter kompleks, biaya total request bisa didominasi oleh query hitung ini.

5. Lonjakan CPU, I/O, atau temporary sort

Pada level database, Anda bisa melihat peningkatan penggunaan CPU, disk read, atau operasi sort yang menulis ke memori sementara/disk. Ini petunjuk bahwa query tidak memanfaatkan indeks dengan baik untuk filtering dan ordering.

Contoh query yang tampak wajar tetapi rentan

Misalkan ada tabel posts untuk feed atau admin listing:

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

Query ini terlihat normal. Namun, tanpa indeks yang sesuai, database bisa melakukan langkah mahal seperti:

memindai banyak baris dengan status = 'published',
mengurutkan hasil berdasarkan published_at,
melewati 10000 baris pertama,
baru mengembalikan 20 baris.

Masalah membesar bila ada tie pada published_at. Untuk hasil yang stabil antar halaman, sebaiknya selalu ada tie-breaker yang deterministik, misalnya id DESC:

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

Namun perubahan ini juga berarti indeks harus mendukung urutan tersebut. Menambah tie-breaker bagus untuk konsistensi hasil, tetapi bisa memperburuk performa bila indeksnya belum tepat.

Cara membaca EXPLAIN tanpa tenggelam di detail engine

Saat audit performa, EXPLAIN adalah titik mulai yang paling berguna. Nama kolom output berbeda antar database, tetapi prinsip bacanya mirip. Fokus pada pertanyaan berikut:

1. Apakah query memakai index scan atau full scan?

Jika database membaca hampir seluruh tabel untuk query listing biasa, itu sinyal buruk. Full scan kadang masuk akal untuk tabel kecil, tetapi menjadi mahal saat data tumbuh.

2. Apakah sorting dilakukan lewat indeks atau sort terpisah?

Jika engine harus melakukan sort eksplisit setelah membaca banyak baris, berarti urutan ORDER BY belum ditopang indeks yang cocok. Ini sangat umum pada offset pagination lambat.

3. Berapa estimasi rows yang harus dibaca?

Jangan hanya melihat hasil akhir 20 baris. Lihat perkiraan baris yang diproses untuk sampai ke hasil itu. Jika yang dibaca puluhan ribu atau jutaan, problemnya ada di sana.

4. Apakah filter dan sort menggunakan indeks yang sama?

Indeks terbaik untuk pagination biasanya mendukung kombinasi filter + order, bukan hanya salah satunya. Indeks pada status saja atau published_at saja sering belum cukup.

5. Apakah query count memiliki rencana eksekusi yang berbeda dan lebih mahal?

Banyak tim hanya memeriksa query data, padahal query total count justru yang lebih boros. Audit keduanya secara terpisah.

Contoh pemeriksaan:

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

Jika hasil EXPLAIN menunjukkan banyak baris dibaca lalu disort, Anda perlu meninjau indeks dan bentuk paginasi. Jika count juga dipakai:

EXPLAIN
SELECT COUNT(*)
FROM posts
WHERE status = 'published';

Untuk filter yang lebih kompleks, biaya count bisa meningkat cukup tajam, terutama jika ada join, kondisi rentang waktu, atau pencarian teks.

Indeks komposit: fix yang sering benar, tetapi harus presisi

Kesalahan umum adalah menambah indeks satu per kolom lalu berharap optimizer akan selalu merangkainya secara ideal. Untuk query pagination, sering kali yang dibutuhkan adalah indeks komposit yang mengikuti pola filter dan sort.

Untuk query berikut:

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

Indeks yang lebih masuk akal biasanya mengikuti urutan akses tersebut, misalnya:

CREATE INDEX idx_posts_status_published_at_id
ON posts (status, published_at, id);

Mengapa bentuk ini membantu?

status dipakai untuk menyaring subset data,
published_at dipakai untuk urutan utama,
id dipakai sebagai tie-breaker agar urutan stabil.

Dengan indeks seperti ini, database punya peluang lebih besar untuk membaca data dalam urutan yang sudah sesuai, sehingga mengurangi kebutuhan sort terpisah.

Catatan penting tentang urutan kolom indeks

Urutan kolom dalam indeks bukan kosmetik. Indeks (published_at, status, id) dan (status, published_at, id) bisa menghasilkan perilaku sangat berbeda. Jika filter utama Anda adalah status = 'published', menaruh status di depan sering lebih cocok. Namun keputusan akhir tetap perlu divalidasi dengan EXPLAIN pada query nyata.

Indeks bukan solusi universal

Ada beberapa batasan penting:

Terlalu banyak indeks memperlambat operasi tulis.
Indeks hanya membantu jika selaras dengan pola query yang benar-benar dipakai.
Jika offset sangat besar, indeks yang baik tetap tidak menghilangkan biaya “melewati” banyak baris.

Artinya, indeks komposit sering menjadi langkah audit pertama, tetapi untuk feed atau daftar yang dikonsumsi dalam pagination dalam-dalam, Anda biasanya tetap perlu mempertimbangkan keyset pagination.

Offset vs keyset pagination: kapan harus pindah

Offset pagination

Kelebihan:

Mudah dipahami dan diimplementasikan.
Cocok untuk UI yang benar-benar membutuhkan lompat ke halaman tertentu, misalnya halaman 7.
Sering sudah menjadi kontrak API lama.

Kekurangan:

Semakin mahal untuk halaman besar.
Rentan inkonsistensi saat data berubah di tengah navigasi.
Sering mendorong kebutuhan count total yang mahal.

Keyset pagination

Keyset pagination, kadang disebut cursor-based pagination, mengambil halaman berikutnya berdasarkan nilai terakhir yang sudah dilihat, bukan berdasarkan nomor halaman.

Contoh untuk urutan published_at DESC, id DESC:

SELECT id, title, status, published_at
FROM posts
WHERE status = 'published'
  AND (
    published_at < :last_published_at
    OR (published_at = :last_published_at AND id < :last_id)
  )
ORDER BY published_at DESC, id DESC
LIMIT 20;

Mengapa ini lebih stabil?

Database tidak perlu membuang ribuan baris berdasarkan offset.
Query mengikuti posisi logis terakhir yang dilihat pengguna.
Dengan indeks yang tepat, biaya antar halaman cenderung lebih konsisten.

Kelebihan:

Lebih efisien untuk feed yang terus bertambah.
Lebih tahan terhadap pertumbuhan data.
Lebih stabil untuk infinite scroll atau next/previous navigation.

Kekurangan:

Tidak ideal jika UI wajib mendukung lompat ke halaman arbitrer.
Kontrak API berubah: dari page menjadi cursor.
Implementasi perlu tie-breaker yang konsisten dan encoding cursor yang aman.

Aturan praktis: jika use case Anda adalah feed, timeline, activity log, atau daftar yang umumnya dibaca berurutan, keyset pagination biasanya pilihan yang lebih tepat. Jika use case adalah admin table yang butuh lompat ke halaman tertentu, offset masih bisa dipakai, tetapi harus diaudit dan dibatasi dengan disiplin.

Masalah lain yang sering tersembunyi: COUNT total yang mahal

Banyak implementasi pagination dianggap selesai jika sudah ada data dan metadata seperti:

{
  "items": [...],
  "page": 12,
  "pageSize": 20,
  "total": 987654,
  "totalPages": 49383
}

Masalahnya, angka total sering membutuhkan query tambahan yang mahal. Pada dataset besar, biaya count bisa sama mahal atau lebih mahal daripada query data, apalagi jika ada:

join ke tabel lain,
filter dinamis dari banyak parameter,
kondisi rentang tanggal,
status atau permission berbasis user.

Strategi yang lebih realistis

Hilangkan total jika tidak benar-benar dibutuhkan. Banyak UI cukup dengan hasNextPage.
Hitung total secara terpisah atau async. Jangan jadikan total sebagai syarat wajib setiap request.
Cache hasil count untuk filter yang umum. Cocok untuk dashboard atau admin yang query-nya relatif berulang.
Gunakan estimasi bila akurasi penuh tidak kritis. Ini keputusan produk, bukan sekadar teknis.

Untuk banyak feed modern, pengguna tidak peduli “ada 49383 halaman”. Mereka hanya butuh daftar terus bisa memuat berikutnya. Dalam kasus seperti ini, menghapus count total sering memberi dampak besar dengan perubahan minimal pada UX.

Strategi migrasi bertahap tanpa memutus API

Sering kali problemnya bukan “cara membuat query cepat”, tetapi “cara memperbaikinya tanpa merusak klien yang sudah bergantung pada page dan limit”. Pendekatan bertahap biasanya lebih aman.

1. Audit endpoint mana yang benar-benar bermasalah

Jangan migrasikan semua listing sekaligus. Kelompokkan:

Feed / timeline / log: kandidat kuat untuk keyset.
Admin table kecil: mungkin cukup dengan indeks yang benar.
Halaman pencarian berat: butuh audit query dan count secara khusus.

2. Tambahkan tie-breaker yang deterministik

Sebelum migrasi ke keyset, pastikan urutan hasil benar-benar stabil. Jika saat ini hanya ORDER BY created_at DESC, tambahkan id DESC sebagai tie-breaker dan sesuaikan indeks.

3. Perkenalkan cursor sebagai mode baru

Alih-alih menghapus page, tambahkan parameter baru misalnya cursor. Klien lama tetap memakai offset, klien baru bisa memilih mode cursor.

GET /api/posts?limit=20&page=5
GET /api/posts?limit=20&cursor=eyJwdWJsaXNoZWRfYXQiOiIyMDI0LTAxLTAxVDEwOjAwOjAwWiIsImlkIjoxMjM0fQ==

Cursor sebaiknya berisi nilai sort terakhir, biasanya di-encode agar tidak bergantung pada format internal API secara langsung.

4. Ubah bentuk respons secara kompatibel

Contoh respons yang masih ramah migrasi:

{
  "items": [...],
  "pagination": {
    "limit": 20,
    "nextCursor": "...",
    "hasNextPage": true,
    "page": 5,
    "total": null
  }
}

Untuk mode offset lama, page masih ada. Untuk mode baru, klien menggunakan nextCursor. Anda tidak perlu memutus API dalam satu langkah besar.

5. Terapkan feature flag atau rollout bertahap

Aktifkan cursor pagination hanya untuk endpoint tertentu, user internal, atau persentase traffic kecil. Pantau latency, error rate, dan hasil pagination ganda untuk memastikan urutan data tetap konsisten.

6. Kurangi ketergantungan pada total count

Jika klien lama mengharuskan total, pertimbangkan untuk:

menyediakan total hanya pada halaman pertama,
mengembalikan total secara tertunda,
menandai total sebagai opsional di versi API berikutnya.

Contoh audit nyata: dari query aman ke query yang memburuk

Bayangkan endpoint admin listing artikel:

SELECT id, title, author_id, status, created_at
FROM articles
WHERE status IN ('draft', 'published')
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Awalnya tabel hanya berisi puluhan ribu baris dan performa masih dapat diterima. Lalu data tumbuh, admin makin sering memfilter, dan tim menambah total count:

SELECT COUNT(*)
FROM articles
WHERE status IN ('draft', 'published');

Gejala yang muncul:

halaman 1 cepat, halaman 100 lambat,
ketika filter status berubah, latency melonjak,
CPU database tinggi pada jam kerja admin,
count total ternyata sering lebih berat daripada query data.

Perbaikannya biasanya bertahap:

pastikan order stabil: ORDER BY created_at DESC, id DESC,
tambahkan indeks komposit yang sesuai,
uji ulang dengan EXPLAIN,
putuskan apakah admin benar-benar perlu lompat ke halaman 100,
jika tidak, sediakan mode cursor untuk navigasi next/previous,
hapus atau longgarkan kebutuhan total count.

Checklist audit untuk feed dan admin listing yang mulai lambat

Apakah query memakai LIMIT/OFFSET dengan offset besar?
Apakah ORDER BY memiliki tie-breaker deterministik seperti id?
Apakah ada indeks komposit yang cocok dengan WHERE + ORDER BY?
Apakah EXPLAIN menunjukkan full scan atau sort mahal?
Apakah query count diperiksa terpisah dari query data?
Apakah UI benar-benar membutuhkan nomor halaman, atau cukup next/previous?
Apakah sorting alternatif didukung indeks yang berbeda?
Apakah perubahan data di tengah pagination menyebabkan duplikasi atau item terlewat?
Apakah endpoint mengembalikan terlalu banyak kolom sehingga membaca data jadi lebih berat?
Apakah ada batas maksimal page/offset untuk mencegah abuse?

Kesalahan umum saat memperbaiki offset pagination

1. Menambah indeks tanpa memeriksa query nyata

Indeks harus mengikuti pola akses aktual. Menebak-nebak indeks sering hanya menambah biaya write tanpa menyelesaikan bottleneck.

2. Menganggap keyset selalu pengganti total

Keyset menyelesaikan masalah traversal halaman berurutan, tetapi tidak otomatis menggantikan kebutuhan bisnis akan total item. Dua masalah ini terkait, tetapi berbeda.

3. Tidak menstabilkan urutan hasil

Jika banyak baris memiliki nilai sort yang sama dan tidak ada tie-breaker, pengguna bisa melihat duplikasi atau item hilang antar halaman.

4. Mengukur hanya di halaman pertama

Uji pagination pada offset besar atau dengan cursor setelah beberapa halaman. Banyak masalah hanya muncul jauh dari halaman awal.

5. Mengabaikan konkurensi dan perubahan data

Pagination pada data yang terus berubah memang rumit. Bahkan query cepat pun bisa menghasilkan pengalaman buruk jika urutan tidak stabil atau kontrak API tidak jelas.

Penutup

Offset pagination yang membusuk saat data tumbuh adalah masalah yang sangat umum karena gejalanya sering terlambat muncul. Query terlihat masuk akal, lolos saat development, lalu production membuktikan sebaliknya begitu data dan traffic meningkat.

Urutan perbaikannya biasanya jelas: audit gejala nyata, baca EXPLAIN untuk melihat apakah full scan atau sort mahal terjadi, tambahkan indeks komposit yang benar, evaluasi biaya COUNT(*), lalu migrasikan endpoint yang cocok ke keyset pagination secara bertahap. Jika dilakukan disiplin, Anda bisa memangkas bottleneck besar tanpa memutus API yang sudah dipakai klien.

Quick Search

shopping_cart Keranjang