Ratusan Koneksi, Sedikit Proses: Pasang PgBouncer untuk PostgreSQL di VPS

Berapa koneksi database yang sebenarnya dibuka aplikasi kita saat trafik sedang ramai? Jawabannya hampir selalu lebih banyak dari yang kita bayangkan. Setiap worker PHP-FPM memegang satu koneksi sendiri, dan begitu jumlah worker naik, PostgreSQL di VPS ikut menanggung puluhan sampai ratusan proses yang sebagian besar waktunya cuma menganggur menunggu query berikutnya.

Di titik itu pgbouncer untuk postgresql bukan lagi kemewahan, melainkan cara paling murah menahan beban tanpa menambah RAM. PgBouncer adalah connection pooler ringan yang berdiri di depan PostgreSQL: aplikasi tetap merasa bicara ke database biasa, tapi yang benar-benar menembak ke server hanya sedikit koneksi yang dipakai bergantian.

Oke, lanjuutt.. Kita pasang dari paket Ubuntu 26.04, atur autentikasi SCRAM, lalu uji dengan SHOW POOLS supaya angkanya kelihatan, bukan cuma dipercaya.

Kenapa Connection Pooler Penting di VPS Kecil

PostgreSQL membuka satu proses sistem operasi untuk setiap koneksi. Proses itu punya overhead memori sendiri, dan biaya membuka koneksi baru juga tidak gratis: TCP handshake, autentikasi, sampai inisialisasi sesi. Kalau aplikasi kita punya 40 worker PHP-FPM dan tiap worker memegang koneksi permanen, PostgreSQL melihat 40 proses hanya untuk melayani beban yang sebenarnya bisa ditangani 5-10 proses saja.

PgBouncer memutus rantai itu. Aplikasi boleh membuka 500 koneksi ke PgBouncer, sementara ke PostgreSQL hanya dibuka 20 koneksi yang dipakai bergantian. Di dokumentasinya, PgBouncer menyebut tujuannya memang satu: mengurangi dampak performa dari membuka koneksi baru.

Ada tiga mode pooling, dan bedanya penting:

session (default): satu koneksi server dipegang klien sampai klien itu disconnect. Paling aman, tapi hampir tidak membantu PHP-FPM yang pegang koneksi sepanjang umur proses.
transaction: koneksi server dikembalikan ke pool begitu transaksi selesai. Ini mode yang benar-benar memangkas jumlah proses di PostgreSQL, dan mode yang kita pakai hari ini.
statement: koneksi dilepas setelah satu query. Paling agresif, tapi transaksi multi-statement jadi tidak boleh.

Pasang PgBouncer di Ubuntu 26.04

Ubuntu 26.04 LTS (Resolute Raccoon) menyediakan paket pgbouncer versi 1.25.1-1 di komponen universe. Versi terbaru di hulu sebenarnya sudah 1.26.1, rilis 14 Oktober 2026, dan rilis itu membawa tiga perbaikan keamanan sekaligus: CVE-2026-107350, CVE-2026-107349, dan CVE-2026-107348. Artinya, kalau nanti paket Ubuntu naik versi, jangan ditunda.


sudo apt update
sudo apt install -y pgbouncer

Paketnya membawa beberapa berkas yang perlu kita tahu:

/etc/pgbouncer/pgbouncer.ini — konfigurasi utama
/etc/pgbouncer/userlist.txt — daftar user dan password/secret
/usr/share/pgbouncer/mkauth.py — skrip bantu membuat userlist dari pg_authid
unit pgbouncer.service dan pgbouncer.socket di systemd

Karena ada unit socket, cukup satu perintah untuk mengaktifkan layanannya:


sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer --no-pager

Port defaultnya 6432. Kalau systemctl status menunjukkan layanan menunggu di socket, itu normal: socket activation baru menjalankan proses saat ada klien pertama yang connect.

Menyiapkan userlist.txt dengan SCRAM

PgBouncer butuh tahu password user, tapi ia tidak boleh menyimpan password polos. Untuk PostgreSQL modern, pakai auth_type = scram-sha-256 dan simpan SCRAM secret di userlist.txt. Formatnya:


"appuser" "SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>"

Cara paling gampang mendapatkan secret itu ada di dokumentasi PgBouncer sendiri: jalankan psql dengan --echo-hidden, lalu ketik \password appuser. Perintah ALTER ROLE ... PASSWORD 'SCRAM-SHA-256$...' yang tercetak di layar sudah berisi secret lengkap; salin bagian dalam tanda kutip ke userlist.txt.

Alternatif yang lebih rapi untuk banyak user adalah auth_query, sehingga PgBouncer membaca hash langsung dari database. Kuncinya: jangan biarkan user aplikasi punya akses baca ke pg_authid. Dokumentasi menyarankan fungsi SECURITY DEFINER dengan search_path yang dikunci, dan fungsi itu harus dipasang di setiap database yang dilayani, karena auth_query dijalankan di dalam database tujuan.


CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END
    FROM pg_authid
    WHERE rolname = i_username AND rolcanlogin
    INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = pg_catalog, pg_temp;

Perhatikan SET search_path di badan fungsi. Contoh di dokumentasi PgBouncer memang menambahkan baris itu justru karena pernah ada celah search_path yang bisa dieksploitasi lewat StartupMessage. Jangan dihapus.

Konfigurasi pgbouncer.ini untuk Mode Transaction

Ini contoh konfigurasi yang bisa kita pakai sebagai titik awal, dengan satu database aplikasi:


[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
max_prepared_statements = 200
query_wait_timeout = 30
admin_users = admin_stat

Beberapa angka di atas perlu penjelasan, karena semuanya punya default yang tidak selalu cocok:

listen_addr = 127.0.0.1 membatasi PgBouncer hanya untuk aplikasi di mesin yang sama. Kalau aplikasi ada di VPS lain, buka ke IP privat/Tailscale, jangan ke 0.0.0.0.
max_client_conn = 500 (default 100) adalah jumlah klien yang boleh masuk ke PgBouncer. Ini yang biasanya perlu dinaikkan, bukan default_pool_size.
default_pool_size = 20 adalah koneksi server per pasangan user/database, dan defaultnya memang 20. Untuk VPS 2 GB, angka ini sudah lebih dari cukup untuk sebagian besar aplikasi web.
query_wait_timeout = 30 memotong klien yang menunggu koneksi lebih dari 30 detik. Defaultnya 120 detik; menunggu dua menit lalu timeout terasa seperti aplikasi hang, padahal poolnya penuh.
max_prepared_statements = 200 menyalakan pelacakan prepared statement di mode transaction. Bawaan PgBouncer 1.25.x adalah 0 — mati. Tetapi 1.26.0 mengubah bawaannya menjadi 200, jadi di versi baru baris ini disetel untuk kejelasan, bukan karena wajib.

Setelah diubah, jangan restart. Cukup reload:


sudo systemctl reload pgbouncer

Di konsol admin, RELOAD; melakukan hal yang sama. Ini penting untuk produksi: koneksi klien yang sedang jalan tidak ikut terputus.

Uji Koneksi dan Baca Statistiknya

Pertama, pastikan PgBouncer benar-benar menerima koneksi dari aplikasi:


psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser"

Kalau masuk tanpa error, langkah berikutnya adalah melihat apakah poolnya bekerja. Masuk ke database virtual pgbouncer:


psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=admin_stat"

Lalu tiga perintah ini:


SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

Di SHOW POOLS kita mencari kolom sv_active, sv_idle, dan cl_active. Kalau cl_active sudah 80 sementara sv_active hanya 6, berarti PgBouncer melakukan tugasnya: 80 klien dilayani 6 proses PostgreSQL. Sebaliknya, kalau sv_active menyentuh pool_size dan ada klien berstatus waiting, berarti pool terlalu kecil atau ada query lambat yang menahan koneksi terlalu lama.

SHOW STATS menambah satu metrik yang jarang diperhatikan: avg_wait_time. Kalau angka ini naik terus, tambah default_pool_size sedikit demi sedikit, bukan langsung dilipatgandakan.

Jebakan yang Paling Sering Kena: Prepared Statement

Ini bagian yang membuat banyak orang menyerah di hari pertama. Di mode transaction, koneksi server bisa berpindah antar klien, sehingga prepared statement yang dibuat di satu koneksi tidak dikenal di koneksi lain. Gejalanya khas:


SQLSTATE[26000]: Invalid sql statement name: 7 ERROR:
prepared statement "pdo_stmt_00000001" does not exist

Munculnya acak, sering lolos di staging, dan baru terlihat saat produksi ramai. Ada dua jalan keluar yang sah:

Pertama, biarkan PgBouncer yang melacak. Set max_prepared_statements ke nilai selain nol. Fitur ini ada sejak PgBouncer 1.21: PgBouncer menyimpan query unik dengan nama internal PGBOUNCER_<id>, lalu menyiapkannya ulang di koneksi server mana pun yang dipakai klien. Catatan penting dari FAQ resminya: untuk PHP/PDO, fitur ini hanya kompatibel kalau PHP 8.4+ dan libpq 17+. Di Ubuntu 26.04 keduanya sudah terpenuhi (libpq5 di repositori berjalan di 18.6), tapi tetap cek di server kita:


php -i | grep "PostgreSQL(libpq) Version"

Kedua, matikan prepared statement di sisi klien dengan PDO::ATTR_EMULATE_PREPARES => true. Ini bukan string concatenation: PDO tetap meng-escape setiap nilai yang di-bind, hanya saja interpolasinya terjadi di PHP sehingga SQL selesai dikirim dalam satu round trip. Yang hilang cuma manfaat plan cache di server.

Kalau aplikasinya Laravel

Mulai Laravel 13.17.0 (23 Juni 2026, PR #60425), Laravel menangani ini sendiri. Cukup tambahkan pooled => true dan endpoint direct di config/database.php:


'pgsql' => [
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST', '127.0.0.1'),
    'port'     => env('DB_PORT', '6432'),
    'database' => env('DB_DATABASE', 'appdb'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'pooled'   => env('DB_POOLED', true),
    'direct'   => array_filter([
        'host'     => env('DB_DIRECT_HOST', '127.0.0.1'),
        'port'     => env('DB_DIRECT_PORT', '5432'),
        'sslmode'  => env('DB_DIRECT_SSLMODE'),
    ]),
],

Saat pooled aktif, Laravel otomatis menyalakan emulated prepares untuk koneksi pool, sementara koneksi direct tetap memakai native prepares. Migrasi, schema:dump, db:wipe, dan db:show diarahkan ke koneksi langsung, jadi DDL tidak pernah lewat pooler. Kalau perlu memanggil koneksi langsung secara manual, tambahkan sufiks ::direct:


DB::connection('pgsql::direct')->statement('create extension if not exists "uuid-ossp"');

Untuk Laravel 12 ke bawah, set PDO::ATTR_EMULATE_PREPARES => true sendiri, lalu uji kolom boolean. Di emulated prepares, PHP true/false bisa terkirim sebagai 1/0 dan ditolak kolom bertipe boolean; ini bug kedua yang muncul setelah bug pertama “diperbaiki”, dan penyebabnya sering tidak kelihatan di log.

Satu Catatan soal Versi 1.26

Kalau nanti kita upgrade ke PgBouncer 1.26.x, ada perubahan perilaku yang perlu diketahui. Sejak 1.26.0, PgBouncer melacak semua parameter yang dilaporkan PostgreSQL secara default, termasuk search_path (di PostgreSQL 18 ke atas) dan default_transaction_read_only. Ini justru menutup kebocoran lama: dulu klien yang menjalankan SET search_path di mode transaction bisa memengaruhi klien lain yang memakai koneksi server yang sama.

Efek sampingnya, 1.26.1 mencatat bahwa ignore_startup_parameters = search_path sempat tidak berfungsi sejak 1.26.0; rilis itu memperbaikinya sehingga nilainya kembali dihormati. Kalau aplikasi kita memakai SET search_path di dalam transaksi dan hasilnya terasa aneh setelah upgrade, cek dulu apakah parameter itu masuk daftar yang dilacak. Jangan langsung menyalahkan aplikasi.

Kalau VPS masih menjalankan 1.25.1 dari repo Ubuntu 26.04, ada dua hal yang perlu disadari sebelum menunda upgrade. Pertama, versi itu termasuk rentang yang terdampak CVE-2026-107349: klien tanpa autentikasi bisa membuat PgBouncer berputar selamanya di satu inti CPU lewat parameter startup berisi nama opsi kosong, dan selama itu ia berhenti melayani semua klien. Kedua, CVE-2026-107350 memungkinkan seseorang yang tahu password satu user di auth_file masuk sebagai user lain yang memakai SCRAM — termasuk admin. Keduanya baru ditambal di 1.26.1. Kalau paket Ubuntu belum naik, jangan tunggu: ambil rilis hulu, atau minimalkan paparan dengan mengikat listen_addr ke 127.0.0.1 seperti di konfigurasi atas dan tidak membuka port 6432 ke internet.

Kesimpulan

PgBouncer bukan pengganti tuning query. Query yang lambat tetap lambat walaupun lewat pooler. Yang ia selesaikan adalah masalah yang berbeda dan sering lebih mendesak: terlalu banyak koneksi menganggur yang menghabiskan RAM PostgreSQL. Dengan pool_mode = transaction, default_pool_size yang wajar, dan prepared statement yang ditangani benar, satu VPS kecil bisa melayani trafik yang tadinya butuh server dua kali lebih besar.

Mulai dari yang kecil saja: pasang paketnya, set max_client_conn dan default_pool_size sesuai jumlah worker, lalu pantau SHOW POOLS selama beberapa hari sebelum menaikkan angka apa pun. Perubahan yang bisa diukur selalu lebih baik daripada perubahan yang cuma terasa lebih cepat. Selamat mencoba, dan semoga angka sv_active kita tetap rendah sementara pengunjung terus naik.