Pagination Tanpa COUNT Mahal: Indexing untuk Tabel yang Terus Tumbuh

Pada banyak aplikasi, halaman daftar data melambat bukan karena mengambil 20 baris yang ditampilkan, tetapi karena dua hal yang terlihat sepele: COUNT(*) untuk menghitung total dan OFFSET yang makin jauh. Saat tabel terus tumbuh dan filter bersifat dinamis, keduanya bisa menjadi biaya utama. Solusinya bukan sekadar menambah RAM atau cache, tetapi mengubah desain pagination dan index agar database membaca lebih sedikit data.

Pelajaran pentingnya mirip dengan tema kebijakan noise dan differential privacy: jangan menganggap angka total selalu presisi dan gratis. Di banyak sistem produk, total yang tepat sampai digit terakhir tidak selalu sebanding dengan biaya query-nya. Kadang lebih sehat untuk performa jika total diestimasi, ditunda, atau bahkan dihilangkan dari UI. Jika listing Anda mulai lambat saat data tumbuh, fokuslah pada tiga hal: hindari COUNT mahal, kurangi ketergantungan pada OFFSET, dan pastikan index cocok dengan pola WHERE + ORDER BY.

Masalah Utama: COUNT(*) dan OFFSET/LIMIT Tidak Skalabel

Kenapa COUNT(*) terasa mahal pada filter dinamis

COUNT(*) terdengar sederhana, tetapi pada query nyata ia sering harus memproses banyak baris yang cocok dengan filter. Jika pengguna bisa memfilter berdasarkan status, tenant, rentang waktu, kata kunci, atau kombinasi lain, maka database mungkin tetap harus menelusuri banyak entry index atau bahkan menyentuh heap/table untuk memastikan kecocokan.

Contoh pola umum:

SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 20000;

SELECT COUNT(*)
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01';

Masalahnya:

Query listing harus melompati banyak baris karena OFFSET besar.
Query COUNT harus menghitung semua baris yang cocok, bukan hanya 20 baris untuk halaman saat ini.
Jika index tidak cocok dengan pola filter, keduanya menjadi lebih mahal seiring pertumbuhan tabel.

Kenapa OFFSET makin mahal di halaman belakang

OFFSET tidak gratis. Untuk mengembalikan baris ke-20001 sampai 20020, database umumnya tetap harus menemukan dan membuang 20000 baris sebelumnya sesuai urutan. Walaupun ada index, pekerjaan ini bertambah linear terhadap kedalaman halaman. Akibatnya, halaman awal terasa cepat, tetapi halaman belakang mulai lambat dan tidak stabil.

Ada masalah lain yang sering luput: pada data yang terus berubah, OFFSET juga rawan menghasilkan duplikasi atau baris terlewat. Jika ada row baru masuk di depan hasil, isi halaman 2 yang diambil sesaat setelah halaman 1 bisa bergeser.

Desain yang Lebih Sehat: Jangan Selalu Mengejar Total Presisi

Dalam banyak produk, kebutuhan sebenarnya bukan “berapa total presisi sampai satuan”, melainkan:

bisakah pengguna lanjut ke halaman berikutnya,
bisakah pengguna memfilter dan menemukan data dengan cepat,
apakah UI tetap responsif saat data tumbuh.

Ini penting sebagai pelajaran desain. Seperti pada diskusi differential privacy, kita belajar bahwa angka yang terlihat rapi dan presisi belum tentu murah atau bahkan sehat untuk sistem. Dalam konteks pagination, total yang presisi bisa menjadi fitur mahal. Alternatif yang sering lebih baik:

Tidak menampilkan total sama sekali, hanya tombol Next/Previous.
Menampilkan “lebih dari N hasil” alih-alih angka pasti.
Menghitung total secara asynchronous untuk filter tertentu.
Menggunakan estimasi bila akurasi absolut bukan kebutuhan utama.

Jika UX mewajibkan nomor halaman 1, 2, 3, ... dan total halaman akurat, Anda sedang memilih trade-off performa. Pastikan keputusan itu sadar, bukan default yang diwariskan dari komponen tabel.

Ganti OFFSET dengan Keyset Pagination

Apa itu keyset pagination

Keyset pagination mengambil halaman berikutnya berdasarkan nilai baris terakhir yang sudah dilihat, bukan berdasarkan nomor halaman dan OFFSET. Karena database cukup melanjutkan scan dari titik tertentu di index, biayanya cenderung stabil meskipun pengguna menelusuri jauh.

Contoh, jika urutan data adalah ORDER BY created_at DESC, id DESC, maka cursor bisa berisi pasangan (created_at, id) dari baris terakhir halaman sebelumnya.

Sebelum: OFFSET/LIMIT

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 OFFSET 20000;

Sesudah: keyset pagination

-- halaman pertama
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;

-- halaman berikutnya, gunakan row terakhir dari halaman sebelumnya
SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < '2025-06-20 10:15:00'
    OR (created_at = '2025-06-20 10:15:00' AND id < 987654)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Kenapa bentuk kondisi ini bekerja:

created_at adalah kunci urutan utama.
id dipakai sebagai tie-breaker agar urutan deterministik saat banyak row punya timestamp sama.
Database bisa melanjutkan pencarian dari posisi cursor di index, bukan menghitung dan membuang ribuan baris.

Syarat penting keyset pagination

ORDER BY harus stabil dan deterministik. Jangan mengurutkan hanya dengan kolom yang tidak unik jika bisa ada nilai kembar.
Gunakan tie-breaker unik, biasanya id.
Cursor harus menyimpan semua kolom yang dipakai untuk urutan.
Arah index harus sesuai dengan pola query atau setidaknya masih dapat dimanfaatkan optimizer.

Kelebihan dan keterbatasan

Kelebihan:

Performa lebih stabil pada tabel besar.
Mengurangi biaya scan dan sort.
Lebih tahan terhadap insert baru dibanding OFFSET.

Keterbatasan:

Tidak cocok jika UX mewajibkan lompat langsung ke halaman 937.
Cursor lebih kompleks dibanding page=2.
Harus hati-hati saat urutan atau filter berubah di tengah navigasi.

Indexing untuk Tabel yang Terus Tumbuh

Prinsip dasar: index harus mengikuti WHERE + ORDER BY

Kesalahan paling umum adalah punya banyak index tunggal, misalnya index pada tenant_id, status, dan created_at secara terpisah, lalu berharap query gabungan otomatis cepat. Pada banyak kasus, database tetap harus melakukan pekerjaan tambahan yang mahal karena pola akses sebenarnya adalah kombinasi filter dan urutan.

Untuk query ini:

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 berbentuk komposit:

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

Kenapa index ini membantu:

tenant_id dan status menyaring ruang pencarian lebih dulu.
created_at, id menyediakan urutan yang dibutuhkan query.
Database berpeluang mengambil 20 baris pertama yang cocok langsung dari jalur index tanpa sort besar.

Urutan kolom dalam composite index

Tidak ada satu rumus yang selalu benar, tetapi pola praktisnya:

Kolom filter dengan pencocokan tepat sering diletakkan di depan.
Kolom untuk urutan diletakkan setelah kolom filter utama.
Tambahkan tie-breaker unik di akhir urutan.

Yang harus dihindari: membuat index hanya berdasarkan intuisi tanpa mengecek query nyata. Query dengan filter dinamis bisa membutuhkan beberapa index komposit berbeda, tetapi jangan berlebihan karena setiap index menambah biaya write, storage, vacuum/maintenance, dan planning complexity.

Covering index untuk mengurangi akses ke table/heap

Jika database Anda mendukung konsep covering index atau include columns, Anda bisa mengurangi kebutuhan membaca row utama ketika semua kolom yang dibutuhkan listing tersedia di index. Ini sangat berguna untuk endpoint list yang sering dipanggil dan hanya menampilkan sedikit kolom.

Contoh konsepnya:

-- Bentuk umum, detail sintaks bergantung pada database
CREATE INDEX idx_orders_listing
ON orders (tenant_id, status, created_at DESC, id DESC)
INCLUDE (total_amount);

Dengan ini, query listing yang hanya butuh id, created_at, status, dan total_amount berpeluang diselesaikan dari index saja atau dengan akses heap yang lebih sedikit. Hasilnya bisa signifikan terutama saat row lebar atau banyak kolom jarang dipakai.

Jangan mengejar covering index untuk semua query. Fokus pada endpoint yang paling panas dan benar-benar sensitif latency.

Partial index bila filter sangat umum dan stabil

Jika sebagian besar query selalu menyasar subset data tertentu, partial index bisa lebih kecil dan lebih efisien. Misalnya listing hampir selalu untuk order aktif atau status tertentu:

CREATE INDEX idx_orders_paid_recent
ON orders (tenant_id, created_at DESC, id DESC)
WHERE status = 'paid';

Ini relevan bila:

filter seperti status = 'paid' sangat sering dipakai,
subset datanya jauh lebih kecil daripada seluruh tabel,
Anda ingin mengurangi ukuran index dan biaya scan.

Namun partial index hanya membantu jika kondisi query selaras dengan predikat index. Untuk filter dinamis yang sangat bervariasi, manfaatnya lebih terbatas.

Kapan COUNT Masih Layak, dan Kapan Pakai Estimasi

Gunakan COUNT presisi jika memang dibutuhkan

Ada kasus di mana total presisi memang penting, misalnya laporan resmi, billing tertentu, atau ekspor yang harus sinkron dengan hasil final. Tetapi untuk halaman listing operasional sehari-hari, sering kali total presisi bukan kebutuhan utama.

Pola yang lebih ringan daripada COUNT sinkron di setiap request

Fetch one extra row: ambil LIMIT 21 untuk halaman ukuran 20. Jika row ke-21 ada, tampilkan tombol Next tanpa perlu COUNT.
Lazy count: tampilkan data dulu, hitung total di background jika pengguna benar-benar membutuhkannya.
Cached/denormalized counters: cocok untuk hitungan yang definisinya stabil, tetapi lebih sulit untuk filter arbitrer.
Estimasi: gunakan pendekatan perkiraan jika UI hanya butuh gambaran kasar.

Contoh pola fetch one extra row

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

Jika hasil 21 baris, kirim 20 baris pertama ke klien dan set has_next=true. Pendekatan ini sangat praktis untuk API dan menghilangkan kebutuhan COUNT pada flow utama.

Kapan estimasi count masuk akal

Estimasi cocok bila:

UI hanya menampilkan “sekitar 12 ribu hasil”.
Filter digunakan untuk eksplorasi, bukan audit.
Biaya COUNT presisi tinggi dan sering memicu load.

Estimasi tidak cocok bila:

angka dipakai untuk keputusan finansial atau compliance,
pengguna akan membandingkan hasil dengan sumber resmi lain,
akurasi per request adalah persyaratan produk.

Intinya, presisi adalah fitur. Kalau fitur itu mahal, pastikan benar-benar diperlukan.

Membaca EXPLAIN: Apa yang Harus Diperiksa

Setelah menulis query dan index, jangan berhenti di teori. Jalankan EXPLAIN atau EXPLAIN ANALYZE sesuai lingkungan yang aman untuk pengujian, lalu periksa beberapa hal berikut:

1. Apakah query memakai index yang Anda harapkan

Jika planner memilih sequential scan atau index yang tidak relevan, index Anda mungkin tidak cocok, statistik usang, atau filter terlalu tidak selektif.

2. Apakah ada sort mahal

Untuk pagination yang sehat, Anda ingin urutan datang langsung dari index sebanyak mungkin. Jika EXPLAIN menunjukkan sort besar sebelum LIMIT, itu tanda index belum mengikuti ORDER BY.

3. Berapa banyak row yang discan vs dikembalikan

Jika query mengembalikan 20 row tetapi harus membaca puluhan ribu row, ada masalah akses path. Keyset pagination dan composite index yang tepat biasanya menurunkan angka ini.

4. Apakah COUNT membaca terlalu banyak data

Pada COUNT dengan filter dinamis, cek apakah planner bisa memakai index secara efisien atau malah menyapu ruang data yang luas. Jika ya, pertimbangkan menghapus COUNT dari request utama.

5. Heap/table fetch yang berlebihan

Jika index scan tetap diikuti banyak akses ke heap/table untuk mengambil kolom tambahan, pertimbangkan covering index pada endpoint yang benar-benar panas.

Jangan hanya melihat jenis scan. Lihat juga jumlah row, langkah sort, dan apakah path akses sesuai intuisi Anda terhadap query.

Contoh Perbaikan End-to-End

Sebelum

Gejala:

halaman 1 cepat, halaman 500 lambat,
setiap request memanggil SELECT data + COUNT(*),
index hanya terpisah pada tenant_id dan created_at.

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

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

Sesudah

Perubahan:

ganti OFFSET dengan keyset pagination,
gunakan urutan deterministik created_at DESC, id DESC,
buat composite index sesuai filter dan urutan,
hapus COUNT sinkron dari jalur utama, ganti dengan has_next.

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

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

-- halaman berikutnya
SELECT id, created_at, status, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND (
    created_at < :cursor_created_at
    OR (created_at = :cursor_created_at AND id < :cursor_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 21;

Hasil yang diharapkan:

latency antar halaman lebih konsisten,
beban database turun karena tidak ada COUNT per request,
query lebih mudah dioptimalkan karena pola akses jelas.

Trade-off UX dan Produk

Perubahan teknis ini sering gagal bukan karena SQL-nya sulit, tetapi karena asumsi UX sudah telanjur memakai paradigma halaman bernomor lengkap dengan total pasti. Sebelum mengubah backend, sinkronkan dengan tim produk:

Apakah pengguna benar-benar perlu tahu total pasti setiap saat?
Apakah tombol Next/Previous cukup?
Apakah “menampilkan 20 hasil berikutnya” lebih berguna daripada “halaman 438 dari 912”?
Apakah total bisa dimuat belakangan atau hanya saat diminta?

Jika jawaban untuk pertanyaan-pertanyaan itu adalah ya, maka Anda punya ruang besar untuk menghilangkan bottleneck struktural.

Kesalahan Umum

Mengurutkan tanpa tie-breaker unik. Ini memicu hasil pagination yang tidak stabil.
Mengandalkan OFFSET pada tabel yang terus bertambah. Gejalanya muncul terlambat, lalu terasa tiba-tiba memburuk.
Membuat banyak single-column index padahal query butuh composite index.
Selalu menjalankan COUNT(*) karena komponen frontend menganggap total wajib ada.
Menambah index tanpa memantau biaya write. Insert/update/delete juga bisa melambat.
Tidak memeriksa EXPLAIN. Optimisasi tanpa plan nyata mudah meleset.

Checklist Implementasi

Identifikasi endpoint listing yang lambat dan pisahkan waktu untuk SELECT data vs COUNT.
Catat query nyata beserta filter paling sering digunakan.
Pastikan ORDER BY deterministik, misalnya created_at DESC, id DESC.
Ganti OFFSET/LIMIT dengan keyset pagination untuk alur next/previous.
Buat composite index yang mengikuti pola WHERE + ORDER BY.
Pertimbangkan covering index untuk endpoint yang hanya menampilkan sedikit kolom dan sering dipanggil.
Gunakan partial index jika ada filter stabil yang sangat umum.
Hilangkan COUNT sinkron dari request utama bila tidak wajib.
Jika perlu total, tentukan apakah harus presisi, bisa di-cache, atau cukup estimasi.
Verifikasi dengan EXPLAIN bahwa row yang dibaca turun dan sort mahal hilang.
Uji konsistensi pagination saat ada insert baru di antara dua request.
Monitor dampak ke write performance setelah penambahan index.

Penutup

Pagination pada tabel yang terus tumbuh bukan sekadar masalah menambahkan LIMIT. Jika Anda masih mengandalkan COUNT(*) pada setiap request dan OFFSET besar untuk halaman belakang, performa akan memburuk seiring waktu. Solusi yang lebih tahan skala adalah keyset pagination, composite index yang sesuai dengan WHERE + ORDER BY, dan keberanian untuk tidak selalu menampilkan total presisi.

Itulah pelajaran desain yang berguna: angka total bukan hanya soal kebenaran, tetapi juga biaya. Dalam banyak sistem produk, estimasi atau penghilangan total justru membuat sistem lebih sehat, pengalaman pengguna tetap baik, dan database tidak dipaksa melakukan pekerjaan yang nilainya kecil dibanding biayanya.

Quick Search

shopping_cart Keranjang