Analisis Query Lambat Postgres dimulai dengan menanyakan: mengapa sebuah query tetap lama meski data tumbuh? Jawabannya langsung terlihat di lapisan pemantauan dan eksekusi. Beberapa deteksi awal adalah dengan pg_stat_activity untuk mendeteksi locking, EXPLAIN ANALYZE untuk pola akses, dan statistik index untuk hotspot yang menahan throughput.

1. Mengidentifikasi Lock dan Transaction Contention

Lock yang tidak terkelola adalah alasan utama query yang tiba-tiba melambat. Jalankan kueri berikut untuk melihat sesi yang menunggu lock:

SELECT pid, usename, state, wait_event, query_start, query
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL AND state <> 'idle';

Perhatikan wait_event seperti LWLock atau BufferPin. Jika ada banyak sesi menunggu relation lock pada tabel tertentu, itu menunjukkan locking yang disebabkan oleh transaksi panjang atau DDL. Catat juga waktu start query untuk mengetahui long-running transaction.

Gunakan pg_locks untuk melihat apakah lock tersebut bersifat exclusive dan apa yang menghalangi:

SELECT l.pid, l.locktype, l.mode, l.granted, c.relname
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.locktype = 'relation' AND c.relname = 'nama_tabel';

Ketika blocking disebabkan oleh transaksi SELECT besar yang membaca seluruh tabel, pertimbangkan menambah statement_timeout pada sesi itu atau memecah query menjadi batch kecil agar tidak menunggu lock schema.

Tips debugging

  • Gunakan SELECT pg_stat_get_backend_idset() untuk snapshot sesi.
  • Perhatikan transaksi yang menulis banyak sekaligus (INSERT/UPDATE besar) karena mereka memicu ShareLock.
  • Pastikan client library tidak meninggalkan transaksi terbuka tanpa commit.

2. Melihat Rencana Eksekusi: EXPLAIN ANALYZE

EXPLAIN ANALYZE adalah jantung diagnosis query lambat. Jalankan contoh berikut untuk query yang bermasalah:

EXPLAIN ANALYZE
SELECT id, status, total
FROM order_summary
WHERE customer_id = $1
AND created_at > now() - interval '7 days';

Perhatikan dua hal: apakah planner memilih sequential scan, dan apakah estimasi row mendekati actual row. Jika terjadi Seq Scan dan data sudah ratusan juta baris, ini hotspot index. Catat waktu total; contoh sebelum optimasi 4s.

Jika rencana menunjukkan Bitmap Heap Scan dengan banyak Recheck Cond, bisa jadi index tidak selektif. Periksa pg_stat_user_indexes untuk melihat idx_scan sebelum dan sesudah upgrade.

3. Mengidentifikasi Hotspot Index dan Penggunaan Index

Gunakan statistik index seperti:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'order_summary';

Jika idx_scan rendah sedangkan tabel sering di-query pada kolom tertentu, planner mungkin memilih seq scan karena cardinality rendah atau statistik tidak up-to-date. Jalankan ANALYZE order_summary; untuk menyamakan statistik.

Hotspot sering terjadi ketika semua query bergantung pada satu index yang menampung seluruh filter. Solusi: tambahkan partial index untuk nilai yang sering difilter:

CREATE INDEX idx_order_summary_active
ON order_summary (customer_id, created_at)
WHERE status = 'active';

Partial index menghemat ruang dan mempercepat query dengan kondisi tetap. Gunakan pg_stat_all_indexes untuk memastikan index digunakan.

4. Strategi Perbaikan Bottleneck

Reorganisasi dan Covering Index

Index buncit menyebabkan banyak page fetch. Reorganisasi dengan REINDEX atau CLUSTER ketika fragmen tinggi.

Covering index memuat kolom yang dibutuhkan query tanpa membaca tabel:

CREATE INDEX idx_order_summary_covering
ON order_summary (customer_id, created_at)
INCLUDE (status, total);

Dengan covering index, planner bisa memuai data langsung dari index. Pastikan kolom INCLUDE jarang diperbarui karena update dapat memicu read-modify-write tambahan.

Partial Index dan Pembatasan Transaksi Panjang

Ketika query menelusuri subset tertentu (misal order berstatus 'active'), partial index mengurangi index scan. Sebaliknya, transaksi yang berjalan lama (long-running transactions) menahan snapshot lama, menyebabkan vacuum tidak selesai dan memperlambat timbulnya bloat.

Batasi durasi transaksi dengan:

  • Set idle_in_transaction_session_timeout agar rollback otomatis jika client lupa commit.
  • Pisahkan operasi read-only besar dengan snapshot isolasi REPEATABLE READ dalam batch kecil, terutama untuk pagination.

Pagination Responsif Saat Data Tumbuh

Pada table besar, offset-based pagination bisa lambat karena harus scan semua baris sebelum offset. Ganti dengan pagination berbasis cursor (keyset) untuk menjaga responsif. Contoh query:

SELECT id, created_at, status
FROM order_summary
WHERE (created_at, id) > ($cursor_ts, $cursor_id)
ORDER BY created_at, id
LIMIT 50;

Cursor menghindari skip yang membebani, dan memanfaatkan index kombinasional. Pastikan index mencakup kolom urutan.

5. Menilai Dampak dan Monitoring

Setelah perubahan, dokumentasikan metrik sebelum dan sesudah. Contoh:

  • Sebelum: SELECT ... WHERE customer_id = ? membutuhkan 4.2 detik, menggunakan seq scan.
  • Setelah: menambahkan partial + covering index, query turun menjadi ~300 ms, sequential scan hilang dan idx_scan meningkat.

Pantau pg_stat_activity untuk memastikan jumlah transaksi menunggu turun, dan pg_stat_user_tables.n_tup_ins vs n_live_tup untuk melihat autovacuum berjalan efisien.

Kesimpulannya, Analisis Query Lambat Postgres adalah kombinasi pemantauan lock & contention, pemeriksaan rencana eksekusi, dan optimasi index yang tepat. Dengan pengukuran yang konsisten dan perbaikan bertahap, query bisa disederhanakan dan bottleneck bisa dihilangkan tanpa menambahkan kompleksitas berlebihan.