Desain Pagination dan Index untuk Tabel yang Terus Membesar

Desain pagination dan index untuk tabel yang terus membesar tidak bisa diperlakukan sebagai detail kecil. Pada data yang masih ratusan atau ribuan baris, hampir semua query terlihat cepat. Masalah baru terasa ketika tabel menjadi jutaan baris, endpoint list dipanggil terus-menerus, dan query yang dulu aman mulai menghasilkan latensi tidak stabil.

Prinsipnya mirip menata ultimate setup untuk sistem yang siap berkembang: bukan memilih komponen yang terlihat paling mewah, tetapi menyusun fondasi yang tetap rapi ketika beban naik. Dalam konteks database, fondasi itu adalah pola pagination yang sesuai, index yang mendukung filter dan urutan, serta desain endpoint yang tidak memaksa database melakukan kerja yang tidak perlu.

Mengapa query list sering runtuh lebih dulu

Pada banyak aplikasi backend, endpoint daftar data adalah sumber trafik terbesar: halaman admin, feed aktivitas, riwayat transaksi, audit log, job history, notifikasi, dan sejenisnya. Query seperti ini terlihat sederhana, tetapi diam-diam mahal karena biasanya menggabungkan tiga hal sekaligus:

Filter dengan WHERE
Pengurutan dengan ORDER BY
Pembatasan hasil dengan LIMIT dan sering juga OFFSET

Ketika index tidak selaras dengan pola akses, database harus memindai jauh lebih banyak baris daripada yang akhirnya dikembalikan. Ditambah lagi, banyak API masih menghitung total data dengan COUNT(*) pada setiap request. Kombinasi ini membuat endpoint terlihat normal di awal, lalu melambat drastis begitu tabel membesar.

Gejala bottleneck yang umum

Halaman awal cepat, tetapi halaman yang lebih dalam jauh lebih lambat.
Query list mendominasi slow query log.
CPU database naik saat trafik baca meningkat, walau write tidak berubah banyak.
Query plan menunjukkan scan besar, sort mahal, atau rows examined jauh lebih besar dari rows returned.
Timeout hanya terjadi pada endpoint pencarian atau list tertentu.

Offset pagination: mudah dipakai, tetapi biaya naik seiring kedalaman halaman

Offset pagination biasanya berbentuk LIMIT ... OFFSET .... Ini cocok untuk implementasi awal karena sederhana dan mudah dihubungkan dengan UI berbasis nomor halaman.

SELECT id, created_at, status, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 1000;

Masalahnya, database tetap perlu melewati banyak baris sebelum sampai ke data yang diminta. Pada offset yang makin besar, biaya kerja ikut naik. Walaupun ada index, offset dalam sering tetap mahal karena engine harus menemukan lalu melewati baris-baris sebelumnya.

Kapan offset pagination masih layak

Ukuran tabel masih kecil atau menengah.
Dataset yang dilihat pengguna terbatas, misalnya admin internal dengan volume rendah.
Pengguna benar-benar butuh lompat ke halaman tertentu, misalnya halaman 7 atau 12.
Query tidak berada di jalur trafik tinggi.

Kelemahan offset pada data yang terus berubah

Selain soal performa, offset pagination juga kurang stabil pada data yang aktif ditulis. Jika ada insert atau delete di antara dua request, pengguna bisa melihat data yang terlewat atau muncul dua kali antar halaman. Ini sering terjadi pada feed, event log, atau transaksi real-time.

Keyset pagination: lebih stabil untuk tabel besar

Keyset pagination memakai nilai baris terakhir sebagai penanda halaman berikutnya, bukan nomor halaman. Istilah lain yang sering dipakai adalah cursor-based pagination. Pendekatan ini biasanya jauh lebih stabil untuk tabel besar karena database tidak perlu menghitung dan melewati offset yang dalam.

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

Di sini, pasangan (created_at, id) dipakai sebagai cursor. Mengapa dua kolom? Karena created_at saja belum tentu unik. Jika beberapa baris memiliki timestamp yang sama, penambahan id menjaga urutan tetap deterministik.

Mengapa keyset pagination bekerja lebih baik

Database dapat langsung melanjutkan dari posisi terakhir di index.
Biaya tidak bertambah drastis walau pengguna terus membuka halaman berikutnya.
Lebih tahan terhadap insert baru di bagian atas hasil.
Lebih cocok untuk API dan aplikasi dengan aliran data berurutan.

Trade-off keyset pagination

Tidak alami untuk fitur “lompat ke halaman 37”.
Perlu cursor yang konsisten dan bisa di-encode di API.
Urutan harus stabil; ORDER BY tidak boleh ambigu.
Implementasi front-end dan kontrak API sedikit lebih kompleks dibanding offset.

Contoh pola endpoint

Daripada:

GET /orders?page=21&per_page=50

Untuk dataset besar, pertimbangkan:

GET /orders?limit=50&after=2025-01-15T10:30:00Z_987654

Cursor after dapat berisi gabungan kolom urutan, misalnya timestamp dan id. Secara implementasi, cursor biasanya di-encode agar tidak mudah dimanipulasi secara manual, tetapi yang penting adalah maknanya: lanjutkan setelah baris terakhir yang tadi diterima.

Index harus mendukung WHERE dan ORDER BY sekaligus

Kesalahan yang sangat umum adalah membuat index hanya pada kolom filter, lalu mengira urusan selesai. Padahal query list hampir selalu butuh WHERE dan ORDER BY. Jika index tidak mendukung keduanya, database bisa tetap melakukan sort besar setelah filtering.

Contoh query yang realistis

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

Untuk query seperti ini, index yang lebih masuk akal biasanya mengikuti pola filter lalu urutan, misalnya:

CREATE INDEX idx_orders_customer_status_created_id
ON orders (customer_id, status, created_at DESC, id DESC);

Intinya bukan menghafal urutan universal, tetapi memahami akses data. Jika query selalu memfilter customer_id dan status, lalu mengurutkan berdasarkan created_at dan id, index komposit seperti itu memberi database jalur yang jauh lebih efisien.

Aturan praktis saat merancang index komposit

Mulai dari kolom yang paling konsisten dipakai pada WHERE.
Masukkan kolom ORDER BY setelah kolom filter yang relevan.
Gunakan kolom tie-breaker unik seperti id jika urutan utama tidak unik.
Jangan membuat terlalu banyak index mirip; setiap index menambah biaya write, storage, dan maintenance.

Pola query yang sering melambat karena index tidak cocok

SELECT id, created_at, action, actor_id
FROM audit_logs
WHERE tenant_id = 10
ORDER BY created_at DESC
LIMIT 100 OFFSET 50000;

Pada awalnya query ini terlihat biasa. Namun jika hanya ada index pada tenant_id atau hanya pada created_at, database mungkin tetap harus membaca banyak data dan melakukan sort tambahan. Jika tabel audit terus membesar, query seperti ini hampir pasti masuk daftar lambat.

Hati-hati dengan filter yang tidak selektif

Index bukan jaminan mutlak. Jika filter sangat tidak selektif, misalnya hampir semua baris memiliki status = 'active', index pada kolom itu saja bisa kurang berguna. Dalam situasi seperti ini, gabungan dengan kolom lain yang lebih sempit atau perbaikan desain query sering lebih efektif.

Dampak COUNT(*) yang sering diremehkan

Banyak implementasi pagination mengembalikan total halaman dan total data pada setiap response. Itu berarti backend menjalankan query data plus query hitung total.

SELECT COUNT(*)
FROM orders
WHERE customer_id = 42
  AND status = 'paid';

Pada dataset besar, COUNT(*) dengan filter dapat menjadi mahal, terutama jika dipanggil di hampir setiap request. Masalahnya bukan hanya durasi query tunggal, tetapi akumulasi beban ketika endpoint ramai.

Kapan COUNT(*) masih masuk akal

Laporan backoffice yang tidak terlalu sering diakses.
Filter sederhana dengan index yang sangat mendukung.
Kebutuhan bisnis benar-benar memerlukan total akurat saat itu juga.

Alternatif yang lebih murah

Jangan kirim total pada endpoint list besar; cukup kirim has_next_page.
Hitung terpisah hanya saat UI benar-benar membutuhkannya.
Gunakan hitungan aproksimasi jika akurasi penuh tidak penting.
Pre-aggregate atau simpan ringkasan bila pola hitung sangat sering dan kriterianya stabil.

Untuk keyset pagination, sering kali metadata sederhana lebih cukup:

{
  "items": [...],
  "next_cursor": "2025-01-15T10:30:00Z_987654",
  "has_more": true
}

Desain seperti ini mengurangi tekanan untuk selalu menghitung total seluruh dataset.

Pola implementasi yang lebih aman untuk production

1. Gunakan urutan yang deterministik

Jangan hanya menulis:

ORDER BY created_at DESC

Jika created_at tidak unik, hasil antar halaman bisa tidak konsisten. Lebih aman:

ORDER BY created_at DESC, id DESC

Lalu jadikan pasangan itu sebagai dasar index dan cursor.

2. Ambil satu baris ekstra untuk mendeteksi halaman berikutnya

Daripada melakukan COUNT(*), ambil LIMIT 51 untuk permintaan 50 item. Jika hasil lebih dari 50, berarti masih ada halaman berikutnya. Kembalikan 50 item pertama dan jadikan item ke-50 sebagai cursor berikutnya.

3. Hindari SELECT *

Jangan mengambil kolom besar yang tidak dibutuhkan, terutama kolom teks panjang, JSON besar, atau blob. Query list sebaiknya hanya memuat kolom untuk tampilan ringkas. Detail lengkap bisa diambil lewat endpoint detail terpisah.

4. Pisahkan endpoint list dan export

Kesalahan umum adalah memaksa satu endpoint melayani browsing interaktif sekaligus export ribuan atau jutaan baris. Untuk production, lebih aman memisahkan:

Endpoint list untuk interaksi cepat, dengan limit ketat dan pagination efisien.
Endpoint/job export berbasis proses asinkron, file hasil, atau batch processing.

5. Batasi kombinasi sort yang didukung

Jika API membolehkan pengguna mengurutkan pada banyak kolom secara bebas, jumlah index yang dibutuhkan bisa meledak. Lebih realistis menentukan 1-3 pola sort resmi yang benar-benar didukung dan dioptimalkan.

Kapan harus mengubah desain endpoint

Pada fase awal, offset pagination sering cukup. Namun ada titik ketika menambal query tidak lagi cukup, dan desain endpoint perlu diubah.

Tanda-tandanya

Halaman dalam hampir tidak pernah dipakai, tetapi tetap membebani database.
Mayoritas pengguna hanya menekan “load more” atau scroll ke bawah.
Slow query tetap muncul meski index utama sudah diperbaiki.
Biaya COUNT(*) lebih besar daripada nilai bisnis metadata total.
Data terus berubah sehingga offset menghasilkan duplikasi atau data terlewat.

Perubahan desain yang layak dipertimbangkan

Ubah dari page-based ke cursor-based.
Hilangkan total_count dari response default.
Batasi horizon data, misalnya hanya 90 hari terakhir pada endpoint operasional.
Pecah endpoint berdasarkan use case, misalnya /recent-orders dan /order-history.
Arsipkan atau partisi data lama jika memang kebutuhan aksesnya berbeda.

Jika pengguna sebenarnya hanya butuh data terbaru, desain endpoint yang memaksa database mendukung navigasi halaman sangat dalam sering merupakan keputusan produk yang mahal dari sisi infrastruktur.

Contoh evolusi query: dari yang sederhana ke yang siap tumbuh

Versi awal yang sering dipakai

SELECT *
FROM transactions
WHERE account_id = 123
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalah pada query ini:

SELECT * mengambil kolom yang mungkin tidak perlu.
OFFSET 5000 makin mahal saat halaman makin dalam.
ORDER BY created_at bisa tidak stabil jika timestamp sama.
Belum jelas index yang mendukung filter dan urutan bersama.

Versi yang lebih siap untuk beban besar

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

Didukung oleh index komposit yang sesuai:

CREATE INDEX idx_transactions_account_created_id
ON transactions (account_id, created_at DESC, id DESC);

Pola ini lebih dekat dengan kebutuhan production: urutan stabil, hasil lebih konsisten, dan database punya jalur akses yang jelas.

Checklist audit performa untuk pagination dan index

Identifikasi endpoint list terpanas: endpoint mana yang paling sering dipanggil dan paling sering masuk slow log.
Periksa query aktual: jangan mengandalkan asumsi ORM; lihat SQL yang benar-benar dieksekusi.
Cek ORDER BY: apakah urutannya deterministik, atau masih ada potensi hasil lompat/duplikat.
Evaluasi offset: apakah ada halaman dalam yang mahal tetapi jarang berguna.
Pastikan index selaras: apakah index mendukung gabungan WHERE dan ORDER BY, bukan salah satu saja.
Bandingkan rows examined vs rows returned: jika jaraknya terlalu jauh, query kemungkinan boros.
Audit COUNT(*): apakah dipanggil di setiap request tanpa kebutuhan kuat.
Kurangi payload: hindari SELECT * pada query list.
Uji data realistis: jangan menilai performa hanya dari lokal dengan data sedikit.
Validasi pola akses produk: apakah user benar-benar butuh nomor halaman, atau cukup next/previous cursor.

Kesalahan umum yang sering terjadi

Menganggap index tunggal pada kolom filter otomatis cukup untuk query sort.
Menggunakan offset pagination untuk feed atau log yang terus berubah.
Tidak menambahkan tie-breaker unik pada ORDER BY.
Selalu mengembalikan total_count walau UI tidak benar-benar memakainya.
Mengoptimalkan query tanpa melihat execution plan dan slow query log.
Membiarkan API mendukung terlalu banyak opsi sort/filter tanpa strategi index yang jelas.

Penutup

Desain pagination dan index untuk tabel yang terus membesar adalah keputusan arsitektur, bukan sekadar detail query. Jika data diperkirakan terus tumbuh, jangan hanya memastikan query “cepat hari ini”. Pastikan pola aksesnya tetap stabil saat ukuran tabel, frekuensi request, dan kebutuhan produk bertambah.

Untuk backend production, aturan praktisnya sederhana: gunakan offset pagination hanya jika kebutuhan dan skala memang cocok; beralih ke keyset pagination saat data besar, aktif berubah, atau halaman dalam mulai mahal; rancang index komposit berdasarkan gabungan WHERE dan ORDER BY; dan pertanyakan apakah COUNT(*) benar-benar perlu di setiap request. Fondasi yang rapi sejak awal akan jauh lebih murah daripada mengejar bottleneck setelah tabel sudah terlalu besar.

Quick Search

shopping_cart Keranjang