Strategi Index Berlapis dan Pagination untuk Query Responsif di Data Besar

Ketika kueri mulai melambat walaupun volume data terus bertambah, solusi pertama adalah mengevaluasi perencanaan indeks dan strategi pagination. Strategi Index Berlapis dan Pagination untuk Query Responsif di Data Besar menjawab kebutuhan itu dengan kombinasi indeks yang berjenjang dan pagination adaptif agar respons tetap konsisten saat data tumbuh.

Pada dasarnya, kita ingin indeks membantu penggunaan kolom filter, sort, dan select secara bersamaan tanpa memaksa full table scan. Dalam paragraf berikut kita langsung membandingkan hasil EXPLAIN sebelum dan sesudah penambahan indeks berlapis agar terlihat perbaikannya.

Diagnosa Query Lambat dengan EXPLAIN

Gejala umum: query dengan klausa WHERE dan ORDER BY yang dulu lancar jadi terlambat, latensi meningkat seiring bertambahnya baris. Catatan pertama datang dari slow log dan pemeriksaan skalabilitas: ketika query dijalankan, planner memilih seq scan karena tidak ada indeks yang memenuhi kombinasi kolom filter dan sort.

Contoh kueri:

SELECT id, customer_id, total_amount
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

Berikut cuplikan EXPLAIN sebelum indeks tambahan:

Seq Scan on orders  (cost=0.00..1250.00 rows=200 width=48)

Seq scan menunjukkan PostgreSQL membaca seluruh tabel. Kita butuh indeks yang menghentikan planner pada kolom status dan created_at, serta memenuhi kolom yang ditampilkan.

Strategi Index Berlapis

Indeks berlapis menyatukan beberapa pendekatan agar query tetap responsif meski tabel terus tumbuh.

1. Indeks Komposit (Composite) untuk Filter+Sort

Indeks komposit yang mencakup (status, created_at) memungkinkan planner mengeksekusi filter sekaligus sort tanpa memindahkan data ke memori. Dalam PostgreSQL, kita membuat:

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

Perhatikan: urutan kolom penting. Kolom pertama status digunakan untuk filter, lalu created_at untuk sort. Index ini menurunkan cost karena planner bisa langsung melakukan index scan yang terurut.

2. Indeks Penutup (Covering Index) dengan INCLUDE

Jika query hanya membaca beberapa kolom, tambahkan kolom tersebut dalam klausa INCLUDE sehingga noothed scan tidak perlu mengunjungi tabel:

CREATE INDEX idx_orders_covering ON orders (status, created_at DESC) INCLUDE (total_amount, customer_id);

Planner tetap menggunakan indeks untuk membaca total_amount dan customer_id tanpa lookup tambahan. Ini mengurangi IO dan membuat respons lebih cepat.

3. Indeks Filtered (Partial Index) untuk Data Filtering Berat

Jika hanya sebagian kecil row yang sering diquery (misalnya status = 'PAID'), indeks partial menjadi lapisan paling ringan:

CREATE INDEX idx_orders_paid ON orders (created_at DESC)
WHERE status = 'PAID';

Dengan demikian, struktur indeks hanya menyimpan subset data sehingga lebih kecil dan lebih cepat dibanding indeks komplit, terutama ketika data tersebar ke banyak status.

Biaya Pemeliharaan Indeks

Trade-off utama: semakin banyak indeks, semakin tinggi biaya insert/update/delete. Prioritaskan indeks yang sering digunakan (lihat monitoring nanti) dan hapus indeks jarang pakai.

Pagination Adaptif: Offset vs Cursor

Pagination tradisional dengan OFFSET menjadi lambat karena database harus menghitung dan melewati banyak baris sebelum mulai menampilkan halaman berikutnya.

Pendekatan offset bagus untuk halaman awal tetapi buruk saat OFFSET besar, karena peningkatan cost linearly, walaupun indeks sudah optimal.

Cursor-based pagination (atau keyset pagination) menggunakan nilai terakhir yang terbaca untuk melanjutkan ke data selanjutnya. Contoh:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'PAID'
  AND created_at < '2024-10-01T12:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

Parameter created_at terakhir menjadi cursor. Dengan indeks berlapis yang sudah ada, database langsung mengakses baris yang relevan tanpa menghitung offset.

Gunakan pagination adaptif: offset untuk halaman samping/kecil, cursor saat pengguna menggulir terus atau API yang butuh konsistensi tanpa mengulangi data.

Monitoring dan Checklist

Tanpa monitoring, indeks yang dibangun bisa berubah tidak relevan seiring pola query. Sebuah checklist membantu:

• Slow log: identifikasi query yang melewati threshold latency. Fokus pada query yang mengeksekusi lebih dari 1% total waktu.
• Indeks tidak terpakai: periksa statistik (PostgreSQL pg_stat_user_indexes) agar indeks yang jarang dipakai bisa di-drop atau diganti.
• Ukuran indeks: indeks besar menandakan fragmentasi. Pertimbangkan REINDEX jika bloat tinggi dan kapasitas masih tersedia.
• Hit ratio: pastikan indeks sering dibaca, bukan hanya ditulis. Hit ratio rendah bisa berarti query berubah atau statistik out-of-date.

Rekam hasil monitoring secara mingguan untuk evaluasi apakah indeks masih memberikan nilai. Tools seperti pg_stat_statements, slow query log, atau query profiler di database lain sangat membantu.

Audit Berkala untuk Menjaga Relevansi Indeks

Indeks tidak dibuat sekali dan dilupakan. Buat check-in berkala:

1. Review query teratas (per-24 jam) untuk melihat perubahan pola. Query baru mungkin butuh indeks tambahan.
2. Update statistik (ANALYZE) dan pastikan planner memilih indeks yang benar.
3. Evaluasi indeks lama: apakah masih dipakai? Jika tidak, hapus atau ganti partial index.
4. Uji kueri dengan EXPLAIN (ANALYZE) setelah perubahan indeks untuk memastikan perbaikan nyata.

Pertahankan dokumentasi struktur indeks dan alasan pembuatan. Ini mempercepat diskusi saat tim memutuskan penghapusan/penambahan indeks.

Dengan strategi indeks berlapis, pagination adaptif, monitoring rutin, dan audit berkala, kita menjaga responsivitas query tanpa mengorbankan write throughput—karena setiap lapisan indeks dirancang untuk kebutuhan spesifik query.