Cara Pasang PgBouncer agar Koneksi PostgreSQL Tidak Habis

Pasang PgBouncer di antara aplikasi dan PostgreSQL, lalu arahkan aplikasi ke PgBouncer. Ratusan koneksi dari aplikasi akan dilayani oleh segelintir koneksi nyata ke database, sehingga error FATAL: sorry, too many clients already berhenti muncul.

Kenapa ini penting? PostgreSQL membuat satu proses backend untuk setiap koneksi. Setiap proses memakan memori, dan jumlahnya dibatasi max_connections, yang nilai bawaannya 100. Aplikasi web dengan banyak worker, job antrean, dan cron yang masing-masing membuka koneksi sendiri bisa menghabiskan jatah itu dengan cepat, padahal sebagian besar koneksi hanya diam menunggu.

Bagaimana PgBouncer bekerja?

PgBouncer adalah connection pooler yang ringan. Aplikasi tersambung ke PgBouncer seolah-olah ke PostgreSQL biasa. PgBouncer menyimpan sekumpulan koneksi nyata (pool) ke database, lalu meminjamkannya ke klien yang sedang butuh dan menariknya kembali begitu selesai.

Kapan sebuah koneksi dikembalikan ke pool ditentukan oleh pool mode:

Untuk mengatasi koneksi yang habis, mode transaction paling efektif, karena koneksi nyata hanya dipakai selama transaksi berlangsung.

Langkah memasang PgBouncer

Panduan ini memakai Ubuntu 24.04 dengan PostgreSQL di server yang sama, seperti hasil pemasangan PostgreSQL 17 di Ubuntu 24.04.

1. Pasang paketnya:

sudo apt update
sudo apt install pgbouncer

2. Ambil hash password user aplikasi dari PostgreSQL:

sudo -u postgres psql -Atc "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'appuser';"

Salin hasilnya ke /etc/pgbouncer/userlist.txt. Bentuknya "appuser" "SCRAM-SHA-256$...". Atur pemilik file ke user postgres dan izin 640 agar tidak bisa dibaca sembarang user.

3. Edit /etc/pgbouncer/pgbouncer.ini:

[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
stats_users = appuser

4. Restart dan cek statusnya:

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

5. Uji koneksi lewat PgBouncer:

psql -h 127.0.0.1 -p 6432 -U appuser appdb

6. Ubah konfigurasi aplikasi agar memakai port 6432 (bukan 5432), lalu restart aplikasi.

Berapa ukuran pool yang tepat?

Dua angka yang paling penting adalah max_client_conn dan default_pool_size. Yang pertama adalah jumlah koneksi klien yang boleh diterima PgBouncer. Yang kedua adalah jumlah koneksi nyata ke PostgreSQL untuk setiap pasangan database dan user.

Aturannya sederhana: total koneksi nyata dari semua pool harus di bawah max_connections PostgreSQL, dengan sisa ruang untuk koneksi admin, backup, dan replikasi. Jika ada dua database dengan masing-masing satu user dan default_pool_size = 20, PgBouncer akan memakai paling banyak 40 koneksi nyata.

Pool yang lebih besar belum tentu lebih cepat. Bila query lambat, menambah koneksi hanya membuat lebih banyak query lambat berjalan bersamaan dan saling berebut CPU serta disk. Mulai dari angka moderat, lalu pantau antrean klien di konsol admin.

Fitur yang perlu diwaspadai di mode transaction

Banyak framework punya opsi khusus untuk berjalan di belakang PgBouncer. Django misalnya punya opsi DISABLE_SERVER_SIDE_CURSORS untuk mode transaction. Baca dokumentasi driver yang kamu pakai, lalu uji alur penting seperti login, checkout, dan migrasi sebelum dipindah ke produksi. Migrasi skema sebaiknya tetap dijalankan langsung ke port 5432.

Bagaimana memantau PgBouncer?

PgBouncer punya konsol admin berupa database virtual bernama pgbouncer:

psql -h 127.0.0.1 -p 6432 -U appuser pgbouncer

User yang terdaftar di stats_users boleh menjalankan perintah SHOW. Lalu jalankan SHOW POOLS; untuk melihat jumlah klien aktif, klien yang menunggu, dan koneksi nyata yang terpakai. SHOW STATS; menampilkan jumlah transaksi dan query. Bila kolom cl_waiting sering lebih dari nol, klien sedang antre karena pool penuh. Periksa dulu query lambat sebelum menaikkan ukuran pool.

Kapan PgBouncer belum diperlukan?

Tidak setiap aplikasi butuh pooler terpisah. Bila aplikasimu hanya punya beberapa worker dengan connection pool bawaan framework yang ukurannya terkendali, dan jumlah koneksi di pg_stat_activity jauh di bawah max_connections, menambah PgBouncer hanya menambah satu komponen yang harus dirawat.

PgBouncer mulai masuk akal saat ada banyak proses yang masing-masing membuka koneksi sendiri, misalnya PHP-FPM dengan puluhan worker, fungsi serverless, atau beberapa aplikasi yang memakai satu database. Tanda lainnya adalah banyak koneksi berstatus idle di pg_stat_activity, atau pemakaian memori PostgreSQL naik seiring jumlah koneksi meski query-nya ringan. Cek dengan:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Kalau beban database terus naik, langkah berikutnya biasanya memindahkan database ke server sendiri. Pertimbangannya dibahas di kapan database perlu dipisah.