Langsung Mengatasi Query Lambat

Portal peluang AI sering mengalami query lambat karena tabel peluang tumbuh cepat, filter kompleks, dan sorting bergantung pada tanggal atau skor. Fokus utama untuk menjaga pengalaman pencarian tetap responsif adalah: (1) identifikasi penyebab dengan EXPLAIN, (2) pasang indeks yang sesuai, dan (3) gunakan pola pagination yang skala untuk dataset besar. Tanpa pendekatan tersebut, sistem menjadi tidak responsif saat traffic atau data meningkat.

Dalam artikel ini, kita akan mengevaluasi query nyata, merancang indeks yang menghemat I/O, serta memilih metode pagination yang tidak tergantung pada offset besar sekaligus memberikan strategi monitoring agar optimasi tetap efektif seiring pertumbuhan data.

Diagnosa Query Lambat dengan EXPLAIN

Langkah pertama adalah memahami bagaimana database mengeksekusi query pencarian. Gunakan EXPLAIN ANALYZE (atau hanya EXPLAIN jika ANALYZE tidak diizinkan) untuk melihat urutan join, estimasi baris, dan apakah indeks digunakan.

Contoh query: mencari peluang berdasarkan kategori dan skor terbaru.

EXPLAIN ANALYZE SELECT id, judul, skor, updated_at
FROM peluang_ai
WHERE kategori = 'Cloud'
ORDER BY skor DESC, updated_at DESC
LIMIT 25;

Cari baris “Seq Scan” yang menunjukkan tabel dipindai penuh. Perhatikan juga “rows” vs “loops” yang menunjukkan estimasi meleset. Jika filter menggunakan WHERE dan ORDER BY berbeda kolom, pastikan query mengeksekusi index scan secara nyata.

Strategi Indexing untuk Filter dan Urutan

Indeks Komposit

Indeks komposit berguna saat query memfilter dan mengurut berdasarkan beberapa kolom yang sama setiap kali. Contoh: WHERE kategori dan ORDER BY skor, updated_at.

CREATE INDEX idx_peluang_kategori_skor_update
ON peluang_ai (kategori, skor DESC, updated_at DESC);

Indeks ini memungkinkan database memenuhi filter dan urutan dari satu struktur, menghindari sort tambahan. Pastikan urutan kolom menyesuaikan dengan pola WHERE pertama kali, lalu ORDER BY. Gunakan direction DESC di indeks jika sering mengurut menurun.

Indeks Partial

Jika sebagian besar query mencari peluang aktif, indeks partial menggantikan indeks penuh dengan hanya kondisi yang relevan.

CREATE INDEX idx_peluang_aktif_skor
ON peluang_ai (kategori, skor DESC)
WHERE status = 'active';

Partial index mengurangi ukuran dan mempercepat pemindaian bila hampir semua query menambahkan status = 'active'. Pastikan clause WHERE query benar-benar cocok dengan definisi partial index.

Covering Index

Terkadang query hanya membaca beberapa kolom. Menambahkan kolom yang sering dipilih ke indeks membuatnya menjadi covering index sehingga tak perlu mengakses baris tabel lagi.

CREATE INDEX idx_peluang_cover
ON peluang_ai (kategori, skor DESC)
INCLUDE (judul, updated_at);

Setelah indexing ini, database bisa menjawab query SELECT judul, updated_at langsung dari indeks tanpa heap fetch. Gunakan INCLUDE atau add_columns tergantung database.

Pagination Efisien untuk Dataset Besar

Offset yang tinggi tidak skalabel karena database masih memindai semua baris hingga offset tersebut. Pilih pola pagination berdasarkan kebutuhan.

Seek Pagination / Keyset Pagination

Seek pagination melewatkan offset dengan memanfaatkan nilai terakhir dari halaman sebelumnya untuk filter berikutnya.

SELECT id, judul, skor, updated_at
FROM peluang_ai
WHERE kategori = 'Cloud'
  AND (skor, updated_at) < (:last_skor, :last_updated)
ORDER BY skor DESC, updated_at DESC
LIMIT 25;

Keuntungan: kinerja konsisten, tidak perlu menghitung offset. Kerugian: sulit melompat langsung ke halaman tertentu tanpa nilai kursor. Pastikan kolom di ORDER BY memiliki nilai unik gabungan agar urutan deterministik.

Cursor Pagination (API)

Untuk API GraphQL/REST, berikan token cursor yang berisi nilai kombinasi kolom terakhir. Server menentukan WHERE berlanjut dan mengembalikan token baru. Trade-off: perlu menjaga format cursor (misalnya base64) agar tidak bocor detail internal, dan validasi jika data berubah cepat.

Monitoring dan Refactor saat Data Tumbuh

Optimasi bukan sekali jadi. Pasang monitoring untuk memantau latency query utama dan persentase cache miss. Tools seperti slow query log, Prometheus exporter, atau fitur native DB membantu.

  • Slow query log: otomatiskan eksport query lama (> threshold) lalu identifikasi kandidat indeks.
  • Histori EXPLAIN: simpan rencana query sebelum dan sesudah perubahan indeks untuk membandingkan.
  • Alert volume data: ketika tabel tumbuh 2-3x, ulangi analisis cardinality dan pertimbangkan indeks ulang atau partisi.

Refactor berarti menghapus indeks yang jarang dipakai karena setiap INSERT/UPDATE/DELETE harus memperbarui indeks. Gunakan pg_stat_user_indexes (PostgreSQL) atau query serupa untuk melihat penggunaan real-time.

Ketika data tumbuh, pertimbangkan arsitektur tambahan seperti materialized view untuk agregasi populer, atau pipeline asynchronous agar query user-facing tetap ringan.

Kesimpulan

Optimasi query portal peluang AI memerlukan pendekatan berlapis: diagnosa dengan EXPLAIN, indeks yang sesuai (komposit, partial, covering), pagination efisien melalui seek/cursor, serta monitoring berkelanjutan. Setiap strategi membawa trade-off—misalnya ukuran indeks vs kecepatan insert, atau kemudahan offset vs kinerja seek—jadi ukur dampaknya sebelum produksi. Dengan pola ini, portal tetap responsif meski volume peluang terus bertambah.