Anatomi Masalah: Plan Regression Pasca-Deploy

Regresi query plan terjadi ketika PostgreSQL cost-based optimizer (CBO) beralih dari execution plan yang efisien ke plan yang buruk pasca-deploy migrasi, perubahan schema, atau pergeseran distribusi data statistik. Kasus umum meliputi optimizer memilih Seq Scan dibanding Index Scan, atau memilih Nested Loop pada relasi berukuran besar karena estimasi kardinalitas (n_distinct) meleset pasca perintah ANALYZE otomatis tertunda.

Hasil akhirnya adalah lonjakan CPU, koneksi pool penuh, dan latensi API meningkat drastis. Artikel ini membahas pola mitigasi insiden menggunakan ekstensi pg_hint_plan sebagai stopgap darurat sebelum solusi permanen diterapkan.

Deteksi Latensi Cepat via pg_stat_statements

Saat insiden terjadi, temukan query yang mengalami anomali latensi menggunakan ekstensi pg_stat_statements. Query berikut mengidentifikasi kueri dengan total waktu eksekusi tertinggi dan mean time yang menyimpang:

SELECT 
    queryid,
    substring(query, 1, 80) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Dapatkan execution plan aktual kueri target tanpa membebani sistem secara berlebih menggunakan EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS, COSTS) 
SELECT o.id, o.total, c.email 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'PENDING' AND o.created_at >= NOW() - INTERVAL '1 day';

Jika terlihat lonjakan drastis pada shared read atau optimizer memilih Hash Join padahal indeks tersedia, regresi plan telah terkonfirmasi.

pg_hint_plan: Emergency Stopgap vs Optimasi Permanen

PostgreSQL core team secara filosofis menolak built-in query hints demi menjaga optimizer independen (walaupun diskusi query planning directives kembali mencuat pada siklus PostgreSQL era modern). Ekstensi pg_hint_plan mengintervensi planner PostgreSQL menggunakan komentar terstruktur di blok SQL tanpa mengubah source code core engine.

Peringatan Arsitektur: Query hints adalah utang teknis operasional. Gunakan strictly sebagai hotfix saat outage produksi untuk memulihkan Service Level Agreement (SLA). Jangan gunakan hint sebagai pengganti indexing strategy yang tepat.

Instalasi dan Konfigurasi pg_hint_plan

Muat library melalui parameter konfigurasi PostgreSQL di postgresql.conf:

shared_preload_libraries = 'pg_stat_statements,pg_hint_plan'
pg_hint_plan.enable_hint = on

Reload engine: SELECT pg_reload_conf();

Penerapan Hint di Kode Aplikasi

Jika optimizer salah memilih sequential scan padahal indeks idx_orders_status_created tersedia, paksa path index dan join strategy menggunakan hint block:

/*+ 
    IndexScan(o idx_orders_status_created)
    Leading((o c))
    NestLoop(o c)
*/
SELECT o.id, o.total, c.email 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'PENDING' AND o.created_at >= NOW() - INTERVAL '1 day';

Bypass optimizer selesai dalam hitungan detik setelah deployment hotfix kode aplikasi atau via middleware query injection (seperti PgBouncer rewrite engine jika menggunakan wrapper).

Observabilitas: Memantau Lock Contention

Saat menerapkan hotfix atau indeks darurat, database rentan terhadap lock contention. Validasi status lock secara real-time untuk memastikan perbaikan tidak menyebabkan cascade deadlock:

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Jika index baru dibuat secara darurat di database production, gunakan selalu opsi CREATE INDEX CONCURRENTLY agar tidak mengunci relasi dengan AccessExclusiveLock.

Alur Postmortem: Pencabutan Hint Menuju Solusi Permanen

Setelah metrik saturasi CPU normal dan error rate mereda, jalankan langkah perbaikan permanen sebelum hint dihapus:

  1. Update Statistik Planner: Jalankan target sampling yang lebih dalam pada kolom predikat: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; ANALYZE orders;
  2. Buat Indeks Komposit yang Presisi: Jika indeks lama memiliki korelasi rendah, bangun indeks komposit baru yang meng-cover predikat filter dan sorting.
  3. Validasi Ulang Execution Plan Alami: Hapus blok /*+ ... */ di staging environment yang memiliki volume data setara production. Jalankan EXPLAIN (ANALYZE) untuk memastikan optimizer secara mandiri memilih jalur optimal.
  4. Deploy Pembersihan Hint: Hapus hint dari codebase. Hint yang tertinggal dalam jangka panjang berisiko menyebabkan sub-optimal plan ketika skema dan data volume bertumbuh.