Tuning PgBouncer for a Read-Heavy API

The setup

A REST API serving ~2k req/s, backed by PostgreSQL. Query latency looked fine in isolation, but under load the p99 would climb to 800ms+.

The usual suspects (slow queries, missing indexes) were clean. The bottleneck? Connection contention.

What we changed

Three settings in pgbouncer.ini:

# Before
pool_mode = session
default_pool_size = 25
reserve_pool_size = 5

# After
pool_mode = transaction
default_pool_size = 40
reserve_pool_size = 10

Session → Transaction: This was the big one. Session pooling holds the connection for the entire client session. Transaction pooling returns it after each transaction — which is all a typical API request needs.

The result

What I learned

Connection pooling isn’t set-and-forget. The defaults work for toy apps, but the ratio of pool_size × max_client_conn to your workload’s transaction duration determines everything.