PgBouncer

PgBouncer is a lightweight connection pooler. In transaction mode it lets thousands of client connections share a few dozen PostgreSQL backends, because most clients are idle between transactions.

Configuration

[databases]
shop = host=10.20.1.5 port=5432 dbname=shop
 
[pgbouncer]
listen_addr = 10.20.0.5
listen_port = 6432
 
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
# Transaction mode is what makes pooling effective. See the caveats below.
pool_mode = transaction
 
# Server connections per (database, user) pair.
default_pool_size = 25
# Extra connections allowed briefly under a burst.
reserve_pool_size = 5
reserve_pool_timeout = 3
 
# Client connections PgBouncer itself will accept.
max_client_conn = 2000
 
# Total server connections across all pools — must stay below the server's
# max_connections, leaving room for replication and administrative sessions.
max_db_connections = 150
 
server_idle_timeout = 600
query_wait_timeout = 5        # fail fast rather than queue without bound
 
ignore_startup_parameters = extra_float_digits

The relationship that matters: max_client_conn is what clients see, max_db_connections is what PostgreSQL sees, and the second must stay comfortably below the server's max_connections.

What transaction mode breaks

Sizing the pool

Start from the server's capacity, not the client's demand. A pool larger than the database can usefully execute concurrently just moves the queue from PgBouncer into PostgreSQL, where it is more expensive.

Read the pool statistics to decide:

$ psql -h 10.20.0.5 -p 6432 -U pgbouncer pgbouncer
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW STATS;

• cl_waiting consistently above zero — clients are queueing for a server connection. Either the pool is too small, or the queries are too slow and the pool is doing its job by protecting the database.
• maxwait growing — the longest a client has waited. This is the number to alert on.
• sv_idle always high — the pool is larger than needed; it costs the server memory for nothing.

Operational notes

• PgBouncer is single-threaded per process. On a busy system, run several instances with so_reuseport, or one per application host.
• It is on the critical path: it needs redundancy, health checks and monitoring like any other component.
• Health checks should execute a trivial query through the pooler, not merely open a TCP connection — PgBouncer accepts connections happily while the database behind it is unreachable.
• Keep auth_file in sync with the server's roles, or use auth_query so PgBouncer looks credentials up in PostgreSQL itself.