pgBouncer connection pool exhaustion: a real production postmortem
At 2:14 AM the pager went off: every API request was timing out with remaining connection slots are reserved for non-replication superuser connections. The database was up. The app was up. And nothing could talk to anything. This is the postmortem of how a single misunderstood pgBouncer setting took the whole service down, and how I stopped it from happening again.
The problem I hit
We ran a fleet of async Python workers behind pgBouncer, which sat in front of a single Postgres instance capped at 100 connections. The mental model I had was simple and wrong: “pgBouncer pools connections, so I can open as many as I want and it’ll sort it out.”
Under normal load it did. Then a downstream job slowed Postgres down for a few seconds, queries started taking longer, and every worker grabbed and held a server connection waiting for its slow query to return. pgBouncer dutifully opened more and more server connections to satisfy them — until Postgres hit its 100-connection ceiling and started refusing everything, including our health checks.
The killer detail: we were running pgBouncer in session pooling mode. In session mode a client holds its server connection for the entire duration of the client session, not just a transaction. So our long-lived async workers each pinned a server connection and never gave it back.
My approach: transaction pooling + honest limits
The fix had three parts: switch the pool mode, set limits that actually reflect Postgres’s ceiling, and make the app tolerant of waiting for a connection instead of failing.
First, the pgBouncer config that mattered:
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
pool_mode = transaction is the whole game: a server connection is only held for the length of a transaction, then returned to the pool. A thousand clients can now share twenty server connections, because no single client hogs one between transactions.
default_pool_size = 20 is deliberately well under Postgres’s max_connections = 100. With a couple of databases and a small admin headroom, the math has to leave room — if your pools can collectively exceed Postgres’s limit, you’ve just moved the exhaustion one layer down.
Here’s the corrected flow:
flowchart LR
workers["Async workers"] --> pgb["pgBouncer (transaction mode)"]
pgb --> pool["Pool of 20 server conns"]
pool --> pg["PostgreSQL (max 100)"]
pgb -->|"pool full"| wait["Client waits, not errors"]
Transaction pooling has one sharp edge: you lose session-level features. Server-side prepared statements, SET that persists across queries, advisory-lock-per-session, and LISTEN/NOTIFY all break, because consecutive statements may land on different server connections. We had to disable prepared statements in our driver:
# or you'll hit "prepared statement \"__asyncpg_stmt_1__\" does not exist".
import asyncpg
async def connect():
return await asyncpg.connect(
dsn,
statement_cache_size=0, # disable prepared-statement caching
server_settings={"jit": "off"}, # avoid per-connection JIT surprises
)
Second, the app had to wait politely instead of erroring when the pool was busy. We were treating “no connection available” as a hard failure; it should be backpressure. Bounding our own client-side concurrency to roughly the pool size turned a stampede into an orderly queue:
# Cap in-flight DB work near the pgBouncer pool size so we queue instead of
# opening an unbounded number of clients that all block.
db_semaphore = asyncio.Semaphore(20)
async def run_query(pool, sql, *args):
async with db_semaphore:
async with pool.acquire() as conn:
return await conn.fetch(sql, *args)
Results
After the change, the same load spike that took us down became a non-event. During a deliberate load test that previously exhausted connections within 90 seconds, server connections now plateaued at 20 and stayed there; client requests queued for a few hundred milliseconds at the peak instead of failing.
database | pool_mode | cl_active | cl_waiting | sv_active | sv_idle
———–+————-+———–+————+———–+———
app | transaction | 180 | 12 | 20 | 0
cl_waiting sitting at 12 with sv_active pinned at 20 is exactly the healthy picture: clients are briefly queuing, server connections are fully utilized but not exceeding the limit, and Postgres never sees more than the pool.
Lessons
- Know your pool mode. Session pooling with long-lived clients is connection exhaustion waiting to happen. For most web/async workloads, transaction pooling is what you actually want.
- Your pool sizes must sum to less than Postgres
max_connections. pgBouncer doesn’t protect you from this; it will happily open connections until Postgres refuses them. - Disable prepared statements (and session-scoped state) in transaction mode. Find this in a staging load test, not at 2 AM.
- Treat “pool full” as backpressure, not failure. Bound client concurrency near the pool size so traffic queues instead of stampeding.
The uncomfortable truth of this incident is that pgBouncer worked exactly as configured. The outage wasn’t a bug — it was me asking session pooling to do a job only transaction pooling can do.