why pg‑bouncer can still choke your service under burst load

problem statement

in a typical microservice, each instance opens its own pg client pool. during a traffic spike the aggregate connection count can exceed the postgres max_connections limit, leading to FATAL: remaining connection slots are reserved for non-replication superuser errors. the usual fix is to front the database with pg‑bouncer, but many teams still see latency spikes and dropped requests.

why a naive pool still hurts

pg‑bouncer operates in transaction pooling mode by default. it hands out a physical backend connection only for the duration of a transaction, then returns it to the pool. if your service opens a transaction, performs a long async task, then commits, the connection sits idle while the task runs. bursts of such patterns quickly exhaust the pool, and pg‑bouncer starts queuing new client connections, inflating response times.

a tighter configuration

switching to statement pooling can mitigate the idle‑time problem, but it introduces its own edge cases: session‑level settings (e.g., SET timezone) and temporary tables are lost between statements. a pragmatic compromise is to keep transaction pooling but enforce a strict client‑side timeout and keep transaction bodies minimal.

# pgbouncer.ini
[databases]
mydb = host=10.1.2.3 port=5432 dbname=mydb pool_mode=transaction

[pgbouncer]
max_client_conn = 1000          # total connections from services
default_pool_size = 20          # per‑database backend connections
reserve_pool_size = 5           # extra connections for spikes
reserve_pool_timeout = 5        # seconds before using reserve pool
server_idle_timeout = 300       # close idle backends after 5 minutes

the reserve_pool_* settings give a small buffer for sudden bursts without permanently allocating more backends. combine this with a service‑level circuit breaker that backs off when the pool queue length exceeds a threshold.

trade‑offs to watch

• statement vs transaction pooling – statement mode removes idle time but breaks any session‑state reliance; audit your code for SET commands or temp tables before switching.
• reserve pool size – setting it too high can starve other databases on the same postgres instance; too low and spikes still cause queuing.
• client timeout – aggressive timeouts prevent long‑running transactions from hogging connections, but may surface hidden latency bugs in upstream services.
• monitoring – expose SHOW POOLS from pg‑bouncer and track total_wait and maxwait_us; a sudden rise signals that your reserve pool is being consumed.

production lesson

in our SaaS, moving from the default max_client_conn=200 to a tiered reserve pool cut 95th‑percentile latency from 800 ms to 250 ms during flash‑sale traffic, without increasing the postgres max_connections. the key was tightening client‑side transaction boundaries and adding a lightweight back‑off in the HTTP layer.

closing thought

how do you balance pg‑bouncer pooling mode, reserve pool sizing, and service‑side timeouts in a high‑concurrency stack