PgBouncer

PgBouncer is a lightweight PostgreSQL connection pooler. Applications connect to PgBouncer, PgBouncer opens a smaller number of server connections to PostgreSQL, and client work is multiplexed onto those server connections according to the selected pooling mode.

The main reason PgBouncer exists is that PostgreSQL’s classic connection model uses one backend process per client connection. Too many database backends consume memory, increase context switching, amplify lock and cache pressure, and make failover harder. PgBouncer lets the system accept many client sockets while limiting the number of active PostgreSQL server connections.

Command Examples

psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW POOLS;"
psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW STATS;"
psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW CLIENTS;"
psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW SERVERS;"
psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW DATABASES;"
psql "postgresql://<user>@<pgbouncer-host>:6432/pgbouncer" -c "SHOW CONFIG;"

Example output and meaning:

These commands use PgBouncer’s admin console database named pgbouncer. They show whether clients are waiting, how many server connections exist, which databases and users have pools, and whether the running configuration matches the intended one.

How Pooling Works

PgBouncer has two sides:

Pooling mode decides when a server connection can be returned to the pool:

Transaction pooling is the usual reason to deploy PgBouncer, but it changes the contract. A client cannot assume the next transaction will use the same PostgreSQL backend.

Transaction Pooling Caveats

In transaction pooling, avoid or redesign features that depend on backend session identity:

• session-level SET state not tracked by PgBouncer,
• temporary tables expected across transactions,
• session-level advisory locks,
• server-side cursors spanning transactions,
• LISTEN/NOTIFY patterns that need a stable backend connection,
• SQL-level PREPARE/EXECUTE assumptions,
• migrations that change result types while clients reuse prepared statements.

Modern PgBouncer can track protocol-level named prepared statements when max_prepared_statements is nonzero. That is not the same as making every session feature safe. It adds CPU and memory work because PgBouncer must inspect, cache, and rewrite prepared statement traffic.

If an application truly needs session state, use session pooling for that workload or keep it off PgBouncer. Mixing incompatible applications into one transaction pool creates intermittent failures that look like random database bugs.

server_reset_query is an easy place to get misled. PgBouncer’s documentation says it is not used in transaction pooling by default because transaction pooling assumes clients do not depend on session state. server_reset_query = DISCARD ALL is a session-pooling cleanup pattern, not a magic fix that makes transaction pooling safe. server_reset_query_always exists for broken setups, but it adds overhead and should be treated as a workaround while the application is fixed.

Sizing

Size PgBouncer from PostgreSQL capacity backward:

Raising max_client_conn can require higher OS file descriptor limits. It also does not mean PostgreSQL can handle that many active queries. The purpose of PgBouncer is often to let excess clients wait in PgBouncer instead of becoming PostgreSQL backends.

Watch these admin-console fields:

If cl_waiting is always high, the pool is too small, PostgreSQL is slow, or the application is sending too much concurrency. Increasing pool size only helps if PostgreSQL has unused CPU, memory, IO, and lock capacity.

HA and Failover Placement

PgBouncer does not decide which PostgreSQL node is primary. It should point at a primary endpoint managed by something else:

• a cloud managed database endpoint,
• a Kubernetes operator Service such as a CloudNativePG -rw Service,
• a VIP or load balancer controlled by an HA manager,
• DNS updated by automation,
• a local PgBouncer per app node pointing at the current primary endpoint.

During failover, client behavior depends on where PgBouncer sits:

Useful commands:

For planned switchover, prefer draining or pausing so transactions finish cleanly. For emergency failover, expect some clients to reconnect and retry failed transactions. PgBouncer can reduce connection storm impact after failover, but it cannot make in-flight transactions survive primary loss.

Configuration Skeleton

[databases]
app = host=postgres-rw port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
reserve_pool_size = 10
admin_users = postgres
stats_users = postgres,monitoring

Treat this as a shape, not a copy-paste answer. Real values depend on workload concurrency, number of app instances, PostgreSQL CPU and memory, transaction duration, failover design, TLS/auth requirements, and file descriptor limits.

Authentication is part of the design, not a footnote. PgBouncer can use static credentials from auth_file or ask PostgreSQL through auth_query, depending on how roles are managed. TLS can terminate at PgBouncer, pass through on the server side, or be used on both sides. Make sure authentication, TLS, and pool mode are documented together so failover or credential rotation does not strand clients.

Failure Modes

Study Cards

References

• PgBouncer Features
• PgBouncer Configuration
• PgBouncer Usage and Admin Console
• PostgreSQL High Availability, Load Balancing, and Replication
• PostgreSQL Monitoring Statistics