Connection Pooling PostgreSQL with PgBouncer

PostgreSQL backends are processes. Each connection is a process with memory, a snapshot, and a slice of work_mem. An application server that opens a connection per HTTP worker will ask for hundreds of backends, and max_connections will either refuse them or the VPS will swap. PgBouncer sits in front, holds a small pool of real backends, and multiplexes many client connections onto those backends. The cluster stays at 40 processes. The app thinks it has 400 connections.

This is the default deployment for a two-tier VPS in Lucknow DC01: app talks to PgBouncer on 127.0.0.1:6432 or on the private IP, PgBouncer talks to PostgreSQL on 10.0.0.4:5432, max_connections stays low.

Install PgBouncer and pick a pool mode

Transaction pooling is the mode you want for most web apps. A backend is assigned for the duration of a transaction, then returned. Session pooling assigns a backend for the whole client session, which is safer for SET, temp tables, and prepared statements, and uses more backends. Statement pooling is rare.

If the app uses SET search_path, prepared statements, or session advisory locks, either keep those in the connection after transaction mode with ignore_startup_parameters, or use session mode for that service. Django and many Rails setups work in transaction mode if you avoid session-level state.

# /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=10.0.0.4 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
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
max_client_conn = 200
max_db_connections = 40
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits
admin_users = pgbouncer_admin
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

default_pool_size of 20 means 20 backends for that database in the default pool. max_db_connections caps the server connections PgBouncer will open. Keep that under PostgreSQL max_connections minus superuser_reserved_connections minus a few for humans.

Auth: userlist and scram

PgBouncer must know the password hash. For scram-sha-256, pull the secret from pg_authid and put it in userlist.txt. Do not put the plaintext password in that file if you can avoid it.

sudo apt-get install -y pgbouncer
sudo -u postgres psql -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_runtime';"
# put the SCRAM secret into /etc/pgbouncer/userlist.txt as:
# "app_runtime" "SCRAM-SHA-256$ remainder copied from rolpassword"
sudo chmod 640 /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo systemctl enable --now pgbouncer
psql "host=127.0.0.1 port=6432 dbname=appdb user=app_runtime" -c "SELECT 1;"

auth_query is an alternative: PgBouncer runs a SQL query as a dedicated user to fetch hashes. That avoids copying hashes into a file when roles change. For a single app role, userlist.txt is simpler and has fewer moving parts.

Point the application connection string at port 6432, not 5432. Leave 5432 bound to the private IP and allowed only from PgBouncer's host. If PgBouncer runs on the same VPS as PostgreSQL, listen_addr of 127.0.0.1 for PgBouncer and a unix socket or 127.0.0.1 for PostgreSQL is the tightest bind. If the app is on another VPS, PgBouncer can listen on the private IP and PostgreSQL can listen only on localhost, with PgBouncer as the only local client. That is a good shape: the database socket never leaves the machine.

Watch the pool, not just max_connections

SHOW POOLS on the pgbouncer admin console tells you cl_active, cl_waiting, sv_active, sv_idle. cl_waiting growing means the pool is too small or queries are too slow. Raising default_pool_size is the last move. First find the slow query.

-- connect to pgbouncer database as admin
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

A healthy web pool has sv_active well below default_pool_size and cl_waiting at zero. If sv_active is pegged and CPU on the VPS is idle, you are waiting on locks or IO, not on pool size. Intel Xeon Platinum will run 20 backends easily; 200 backends will spend their lives in memory accounting.

Prepared statements in transaction mode need Postgres 16 plus PgBouncer 1.21 or newer with max_prepared_statements set, or you disable server-side prepares in the driver. Test that before you cut over. A driver that issues PREPARE on a backend and then gets a different backend on the next transaction will error in ways that look like flaky SQL.

Reload PgBouncer after ini changes that support SIGHUP. Restart for listen_addr. Keep PostgreSQL max_connections at 60 even after the pooler is in place. The pooler is why that number can stay small.

Size the pool from measured active queries, not from worker count. Twenty backends on an 8 GB Intel Xeon Platinum VPS will feed a typical web app. Four hundred backends will not make the same SQL faster. They will raise RAM, raise context switches, and hide the slow query behind a connection error. If cl_waiting spikes only at deploy time, the deploy is opening too many migrations at once, not the pool being wrong.

Takeaway

Transaction pooling, a pool well under max_connections, scram in userlist.txt, and the app DSN pointed at 6432. That is the whole cutover. Launch an Ubuntu VPS on Netbay in Lucknow DC01 in under 60 seconds and put PgBouncer in front before you raise max_connections — netbayhosts.in.