Tuning PgBouncer di VPS Sederhana untuk Beban Bursty

Saat trafik POS Gatsu mulai meningkat, gejala pertama bukan CPU yang tinggi, melainkan koneksi yang menggantung. PostgreSQL kami sebenarnya idle, tapi klien tidak bisa mendapatkan slot. Solusinya: pasang PgBouncer dengan baik.

Mode pooling: transaction hampir selalu

Untuk web app yang stateless seperti POS, transaction pooling memberikan rasio multiplexing terbaik. Yang perlu disesuaikan:

• Hindari SET SESSION di kode aplikasi — gunakan SET LOCAL di dalam transaksi.
• Cursor server-side (DECLARE CURSOR) tidak akan bekerja; pakai cursor client-side atau pagination biasa.
• Prepared statement: gunakan server_reset_query_always = 1 dan pastikan ORM kamu tidak mengandalkan plan cache lintas transaksi.

Angka yang saya pakai untuk VPS 2 vCPU / 4 GB

[databases]
gatsu = host=127.0.0.1 dbname=gatsu_prod

[pgbouncer]
pool_mode = transaction
max_client_conn = 600
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 1200
server_idle_timeout = 60
query_wait_timeout = 30
ignore_startup_parameters = extra_float_digits

Beberapa catatan:

• default_pool_size = 25 berarti maksimal 25 koneksi nyata ke PostgreSQL per database. Sesuaikan dengan max_connections di Postgres dan jumlah pool yang kamu punya. Rumus kasar: max_connections ≥ Σ pool_size + superuser_reserve + 10.
• max_client_conn = 600 berarti hingga 600 klien bisa "merasa" terkoneksi sekaligus. Cocok dengan keep-alive HTTP yang panjang.
• reserve_pool_size kecil tapi krusial saat trafik bursty: ia memberi nafas saat antrian sudah penuh.

Memantau dengan benar

Saya selalu pasang dashboard sederhana untuk metrik berikut:

• SHOW POOLS — apakah cl_waiting sering > 0? Itu sinyal pool size kurang.
• SHOW STATS — avg_query_time per database. Lompatan tiba-tiba di sini biasanya bukan masalah PgBouncer, tapi query yang tidak ber-index.
• p95 latency dari sisi aplikasi. PgBouncer dapat menyembunyikan masalah Postgres jika hanya melihat dari satu sisi.

Hal yang ingin saya ketahui lebih awal

PgBouncer adalah komponen kritikal yang sering diperlakukan sebagai konfigurasi sekali pasang. Kenyataannya, angka-angka di atas akan berubah seiring pertumbuhan trafik. Buat alarm proaktif untuk cl_waiting sebelum pengguna melapor.