Connection pooling notes: pgbouncer in front of Postgres

Postgres forks a backend process per connection, and those processes aren't cheap — a few megabytes each, plus context-switch overhead once you have hundreds of them. A service that opens connections freely can exhaust max_connections and fall over while the database itself is barely loaded. The usual fix is a pooler in between, and the usual pooler is pgbouncer. Here are the notes I wish I'd had before deploying it.

The three pool modes, briefly

pgbouncer has three pooling modes, and picking the wrong one is where the pain comes from:

• session — a client holds a server connection for its whole session. Safest, least sharing. Barely better than connecting directly.
• transaction — a server connection is assigned only for the duration of a transaction, then returned to the pool. This is the mode people actually want it for, and the one with sharp edges.
• statement — returns the connection after every single statement. Forbids multi-statement transactions. Niche.

Transaction pooling is where the big win is: a handful of real Postgres backends can serve hundreds of application clients, because most clients are idle between transactions. The config is small:

# pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

A thousand clients, twenty real backends. That ratio is the whole point.

The thing that broke

The day after I switched to transaction mode, errors appeared: prepared statement "stmtcache_1" does not exist. The cause is fundamental, not a bug. A prepared statement lives on one specific server connection. In transaction mode, the next query from the same client might land on a different backend — one that never saw the PREPARE. So the statement is gone.

Server-side prepared statements and transaction pooling are, by default, incompatible. The fixes, in order of how I'd reach for them:

• Tell your driver to stop using them. Many drivers can run queries in "simple query" mode or disable the statement cache. In Go's pgx, for instance, setting the query exec mode to simple protocol avoids the server-side prepare entirely.
• Let pgbouncer track them. Newer pgbouncer can emulate prepared statements across pooled connections if you set max_prepared_statements above zero. This didn't exist when I first hit the problem; it's the cleaner answer now.
• Use session mode for the one service that needs them. A sledgehammer, but sometimes the pragmatic one.

The settings that matter more than pool size

Everyone fixates on default_pool_size. In practice three others caused me more grief:

server_idle_timeout. How long an unused server connection stays open before pgbouncer closes it. Too low and you churn connections under spiky load; too high and you pin backends you don't need. I settled around 600 seconds.

query_wait_timeout. If every pooled connection is busy, new queries queue. This sets how long they'll wait before erroring out. The default is long enough that a stall looks like a hang. I'd rather fail fast and see it in metrics, so I set it to something like 20 seconds.

The arithmetic with Postgres. Your default_pool_size times the number of databases and users, across every pgbouncer instance, must stay under Postgres's max_connections. It's easy to configure a pooler that can itself exhaust the thing it's meant to protect. Do the multiplication explicitly.

Is it running?

pgbouncer exposes an admin console on its own port — connect with psql and query its virtual tables:

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

SHOW POOLS is the one I watch: cl_waiting is clients queued for a connection, and if it's ever above zero for long, the pool is too small or something is holding transactions open. That number tells you more than any guess at the right default_pool_size up front.