Mencegah Query Feed Lambat: Index dan Pagination untuk Tabel Besar

Mencegah query feed lambat bukan soal menambah server lebih dulu, tetapi memastikan database tidak dipaksa membaca terlalu banyak baris untuk mengambil sedikit hasil. Pada tabel yang terus membesar, kombinasi offset pagination, ORDER BY yang tidak didukung index, dan count(*) yang mahal sering menjadi penyebab utama feed atau listing terasa makin lambat.

Analogi yang mudah: ketika perhatian publik melonjak karena sebuah topik tiba-tiba ramai, trafik baca juga bisa ikut naik mendadak. Referensi konteks seperti kampanye yang ramai dibicarakan di media dapat membantu membayangkan lonjakan ini, tetapi masalah intinya tetap teknis: query yang tadinya cukup cepat bisa runtuh ketika beban baca naik bersamaan dengan ukuran tabel. Solusinya biasanya ada pada desain index, bentuk query, dan strategi pagination.

Gejala nyata saat feed mulai melambat

Pada tahap awal, query listing sering terlihat baik-baik saja karena tabel masih kecil. Masalah muncul ketika data sudah ratusan ribu atau jutaan baris, lalu endpoint feed tetap menggunakan pola lama.

Tanda-tanda yang sering muncul

Halaman 1 cepat, tetapi halaman 100 atau 1000 jauh lebih lambat.
CPU database naik saat trafik baca meningkat, meski query terlihat sederhana.
Slow query log menunjukkan banyak query SELECT ... ORDER BY ... LIMIT ... OFFSET ....
Waktu respons API tidak stabil karena query perlu menyortir atau memindai banyak baris.
Endpoint feed ikut melambat karena aplikasi selalu menjalankan count(*) untuk total halaman.

Masalah ini umum pada feed artikel, produk, komentar, notifikasi, aktivitas pengguna, dan daftar transaksi.

Kenapa offset pagination makin mahal

Offset pagination mudah dipakai dan nyaman untuk UI yang menampilkan nomor halaman. Namun biayanya meningkat seiring offset membesar, karena database tetap harus melewati sejumlah baris sebelum sampai ke hasil yang diminta.

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

Secara logika, query di atas hanya meminta 20 baris. Tetapi untuk sampai ke sana, database mungkin perlu membaca, membandingkan, atau melompati ribuan baris terlebih dahulu. Jika index tidak mendukung filter dan urutan, biaya ini bertambah karena ada proses sort tambahan atau bahkan scan besar.

Mengapa ini memburuk saat tabel besar

OFFSET tidak gratis: semakin besar offset, semakin banyak baris yang harus dilewati.
ORDER BY mahal tanpa index yang tepat: database bisa membuat sort sementara di memori atau disk.
Konsistensi data menurun: pada data yang terus berubah, halaman dengan offset dapat duplikat atau melewatkan item ketika ada insert baru di depan.

Karena itu, offset pagination cocok untuk dataset kecil, halaman administrasi terbatas, atau kebutuhan nomor halaman yang eksplisit. Untuk feed besar dan trafik tinggi, sering kali ini bukan pilihan terbaik.

Index yang benar: sesuaikan dengan WHERE + ORDER BY

Kesalahan paling umum adalah menambahkan index terpisah untuk setiap kolom, lalu berharap database otomatis menggabungkannya secara optimal. Pada query feed, yang biasanya lebih efektif adalah composite index yang mengikuti pola filter dan urutan hasil.

Contoh query yang umum

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND tenant_id = 42
ORDER BY published_at DESC, id DESC
LIMIT 20;

Pada query ini, database perlu:

Memfilter status dan tenant_id.
Mengembalikan hasil dalam urutan published_at DESC, id DESC.
Mengambil sedikit baris pertama secepat mungkin.

Index yang sering lebih sesuai adalah:

CREATE INDEX idx_posts_feed
ON posts (tenant_id, status, published_at DESC, id DESC);

Mengapa urutannya seperti itu?

tenant_id dan status membantu menyempitkan ruang pencarian sejak awal.
published_at dan id membantu memenuhi ORDER BY tanpa sort tambahan.
id sebagai tie-breaker penting jika banyak baris memiliki published_at yang sama.

Contoh sebelum dan sesudah

Sebelum, index terpisah sering tidak cukup:

CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at);

Pola ini bisa tetap membuat database membaca banyak kandidat lalu menyortir hasilnya.

Sesudah, composite index biasanya lebih cocok:

CREATE INDEX idx_posts_feed
ON posts (tenant_id, status, published_at DESC, id DESC);

Prinsip praktisnya: rancang index berdasarkan query penting yang benar-benar berjalan di produksi, bukan berdasarkan daftar kolom populer.

Trade-off selectivity yang perlu dipahami

Tidak semua kolom filter bagus diletakkan di depan index. Kolom dengan selektivitas sangat rendah, misalnya status jika mayoritas data bernilai published, bisa kurang membantu jika berdiri sendiri. Tetapi kolom ini tetap bisa berguna sebagai bagian dari composite index jika memang selalu muncul bersama filter yang lebih selektif seperti tenant_id, author_id, atau category_id.

Beberapa pertimbangan:

Kolom sangat selektif bagus untuk mempersempit pencarian.
Kolom urutan harus selaras dengan ORDER BY jika ingin menghindari sort mahal.
Terlalu banyak index memperlambat insert/update/delete dan menambah ukuran storage.

Jadi, pilih index berdasarkan kompromi antara performa baca dan biaya tulis.

Cara membaca EXPLAIN untuk query feed

Jangan menebak. Gunakan EXPLAIN untuk melihat bagaimana database berencana menjalankan query.

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

Apa yang perlu dicari

Apakah index yang diharapkan dipakai? Jika bukan, cek urutan kolom index dan bentuk query.
Jumlah baris yang diperkirakan dibaca terlalu besar atau tidak.
Apakah ada sort tambahan, temporary table, atau indikasi scan yang terlalu luas.
Apakah filter diterapkan di index atau baru setelah banyak baris diambil.

Tanda index tidak terpakai atau kurang efektif

Planner memilih full scan padahal tabel besar.
Jumlah baris yang diperiksa jauh lebih besar daripada jumlah baris yang dikembalikan.
ORDER BY memicu sort eksplisit meski Anda sudah punya index di kolom urutan.
Query baru cepat jika dipaksa tanpa offset kecil, tetapi memburuk drastis di offset besar.

Jika hasil EXPLAIN menunjukkan database tetap harus membaca banyak baris, masalahnya bukan hanya pada LIMIT. Biasanya akar masalah ada di pola akses data: filter kurang selektif, urutan index tidak cocok, atau offset terlalu besar.

Kapan beralih dari offset ke keyset/cursor pagination

Jika feed dibaca berurutan dari item terbaru ke lama, dan Anda tidak benar-benar membutuhkan nomor halaman acak seperti halaman 387, maka keyset pagination biasanya lebih efisien dan lebih stabil.

Offset pagination

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

Keyset pagination

Ambil halaman pertama:

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND tenant_id = 42
ORDER BY published_at DESC, id DESC
LIMIT 20;

Lalu untuk halaman berikutnya, kirim penanda item terakhir dari halaman sebelumnya:

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND tenant_id = 42
  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 cepat?

Database tidak perlu melompati ribuan baris.
Query langsung melanjutkan dari posisi terakhir pada index.
Lebih tahan terhadap data baru yang masuk di bagian depan feed.

Kapan keyset pagination cocok

Feed berita, timeline, aktivitas, komentar, log, notifikasi.
Urutan data jelas dan stabil, misalnya berdasarkan waktu dan id.
Pengguna lebih sering menekan tombol “lanjut” daripada lompat ke halaman acak.

Kapan offset masih masuk akal

Admin table kecil sampai menengah.
Perlu nomor halaman eksplisit.
Pengguna sering melompat ke halaman tertentu.

Keterbatasan keyset/cursor pagination

Lebih sulit diintegrasikan dengan UI yang mewajibkan nomor halaman absolut.
Perlu pengurutan deterministik; jangan hanya mengandalkan timestamp yang bisa duplikat.
Cursor harus dirancang hati-hati agar aman dan tidak mudah dimanipulasi jika dipublikasikan ke klien.

Masalah count(*) pada tabel besar

Banyak implementasi pagination selalu menampilkan total halaman, sehingga setiap request melakukan count(*). Pada tabel besar atau filter kompleks, ini bisa mahal.

SELECT COUNT(*)
FROM posts
WHERE status = 'published'
  AND tenant_id = 42;

Meskipun terlihat sederhana, query ini tetap dapat memaksa database memindai banyak entri index atau baris yang relevan. Jika endpoint feed dipanggil sangat sering, biaya totalnya bisa signifikan.

Pendekatan yang bisa dipilih

Hindari total count real-time jika UI tidak benar-benar memerlukannya.
Gunakan “has next page” dengan mengambil satu item ekstra.
Simpan agregat terpisah jika total penting dan bisa dihitung secara asynchronous.
Cache count untuk filter yang umum, dengan memahami risiko data sedikit stale.

Untuk banyak feed publik, informasi “masih ada halaman berikutnya atau tidak” jauh lebih bernilai daripada angka total yang presisi setiap saat.

Contoh audit query: dari pola lambat ke pola lebih sehat

Pola awal yang sering bermasalah

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

Masalah pada query ini:

Tidak ada tie-breaker stabil selain published_at.
Offset besar.
Bisa tetap menyortir banyak data jika index tidak tepat.

Perbaikan 1: tambahkan urutan deterministik dan index yang sesuai

CREATE INDEX idx_posts_status_published_id
ON posts (status, published_at DESC, id DESC);

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

Perbaikan 2: ganti ke keyset pagination

SELECT id, title, 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;

Hasil yang dicari bukan sekadar query “berhasil”, tetapi query yang tetap stabil saat jumlah data dan trafik naik.

Kesalahan umum yang membuat index tidak membantu

Urutan kolom index tidak mengikuti pola query. Composite index sensitif terhadap urutan.
ORDER BY tidak lengkap atau tidak deterministik. Jika banyak nilai sama, pagination bisa tidak konsisten.
Memilih terlalu banyak kolom pada SELECT. Semakin banyak data yang perlu diambil, semakin besar biaya I/O.
Menambahkan fungsi pada kolom filter atau sort. Misalnya membungkus kolom dengan fungsi tertentu bisa membuat index sulit dipakai.
Mengandalkan index tunggal untuk query kompleks. Feed sering butuh composite index yang spesifik.
Terlalu banyak index mirip. Ini menaikkan biaya write tanpa memberi manfaat nyata.

Checklist audit query feed yang lambat

Identifikasi endpoint paling sering dipanggil dan paling lambat.
Ambil query aktual dari log, APM, atau slow query log.
Catat pola WHERE, ORDER BY, LIMIT, dan apakah ada OFFSET besar.
Jalankan EXPLAIN untuk melihat rencana eksekusi.
Cek apakah index mendukung kombinasi filter dan urutan.
Pastikan urutan hasil deterministik, misalnya created_at DESC, id DESC.
Evaluasi apakah count(*) benar-benar dibutuhkan di setiap request.
Uji keyset pagination untuk endpoint feed yang dominan sequential.
Bandingkan hasil sebelum/sesudah dengan data dan trafik yang representatif.
Pantau dampaknya pada latensi baca dan biaya write.

Langkah rollout aman di produksi

Optimasi query feed sebaiknya dirilis bertahap. Perubahan index dan pagination bisa memengaruhi performa baca, tulis, dan perilaku API.

Praktik rollout yang aman

Tambahkan index secara terkontrol. Jadwalkan pada jam yang tepat dan pahami dampaknya pada beban tulis.
Uji query dengan data realistis. Dataset kecil di lokal sering menipu.
Rilis di belakang feature flag. Misalnya aktifkan keyset pagination hanya untuk sebagian trafik.
Pantau metrik utama. Latency p95/p99, query time database, rows examined, CPU, dan error rate.
Sediakan fallback. Jika implementasi cursor baru menimbulkan bug UI atau duplikasi data, Anda perlu jalur rollback cepat.

Hal yang perlu diuji setelah rollout

Apakah item hilang atau duplikat saat ada insert baru selama pengguna melakukan pagination.
Apakah urutan hasil tetap konsisten untuk nilai timestamp yang sama.
Apakah endpoint lain ikut terpengaruh oleh penambahan index baru pada beban write.
Apakah cache atau CDN masih relevan untuk pola akses feed Anda.

Kesimpulan

Mencegah query feed lambat pada tabel besar biasanya membutuhkan tiga keputusan utama: gunakan index yang mengikuti pola WHERE + ORDER BY, kurangi ketergantungan pada offset pagination saat data membesar, dan hindari count(*) real-time jika tidak benar-benar diperlukan. Jika sebuah topik mendadak ramai lalu trafik baca melonjak, struktur query yang benar akan jauh lebih membantu daripada sekadar menaikkan kapasitas secara reaktif.

Mulailah dari query nyata yang paling mahal, baca EXPLAIN, cocokkan composite index dengan pola akses, lalu pertimbangkan keyset pagination untuk feed yang dibaca berurutan. Pendekatan ini biasanya memberi hasil yang lebih stabil, lebih hemat, dan lebih tahan terhadap pertumbuhan data.

Quick Search

shopping_cart Keranjang