How Switching PgBouncer to Transaction Pooling Broke asyncpg's Prepared Statement Cache for 14 Minutes

03:14 UTC. The orders API's error-rate panel goes from a flat 0.1% to 9% in under a minute. PagerDuty pages the on-call engineer. The only thing that shipped in the last hour is a PgBouncer config change, deployed forty minutes earlier, with a changelog line that reads like good news: pool_mode: session -> transaction, fixes connection exhaustion under load.

the setup

The stack: a FastAPI service talking to Postgres through asyncpg, sitting behind PgBouncer, autoscaled on ECS between 8 and 30 tasks depending on traffic. Each task opened its own asyncpg pool of 20 persistent connections on boot and held them for the task's lifetime, which is fine at 8 tasks and a slow-burning problem at 30: 600 held connections against an RDS instance capped at 500, with PgBouncer's own backend pool to Postgres capped tighter still. Session pooling meant every one of those 600 client connections mapped to a real, dedicated Postgres backend connection for as long as the task lived, whether it was actively running a query or not.

The fix looked standard, because it is: switch PgBouncer's pool_mode from session to transaction, so a client only holds a backend connection for the duration of a single transaction and hands it back to the pool immediately after.

[pgbouncer]
pool_mode = transaction
max_client_conn = 3000
default_pool_size = 30
server_reset_query = DISCARD ALL

Load tests against staging the day before had passed clean. Staging runs three tasks, never scales past five, and the load test script hit one endpoint in a tight loop. None of that came close to reproducing what thirty production tasks running a real request mix would do to the new pooling mode.

the scramble

First theory: the pooling change itself caused some kind of cutover glitch, a burst of dropped connections as PgBouncer re-negotiated backend assignments. The on-call engineer pulled up PgBouncer's SHOW POOLS, expecting to see cl_waiting climbing. It sat at zero. Connections were available and being handed out. Requests were still failing.

Second theory: an unrelated schema migration that had merged the same afternoon, still queued behind a feature flag, had somehow gone live early and regressed the query planner on a hot path. Ruled out in about five minutes: pg_stat_statements showed identical plans and identical mean execution times for the failing queries. Whatever was breaking, it wasn't the SQL itself.

Third observation, not yet a theory: the failures weren't spread evenly across endpoints. They clustered on GET /orders/{id} and POST /orders/{id}/status, both hit thousands of times a minute with the same query shape and different parameters. Low-traffic endpoints were clean. That pattern got noted and initially shelved as "probably just where the traffic is," which delayed the real lead by several minutes.

the hunt

The generic 500s in the API logs were unhelpful. The asyncpg exception underneath them was not:

asyncpg.exceptions.InvalidSQLStatementNameError:
  prepared statement "__asyncpg_stmt_23__" does not exist
  code: 26000

A minute later, a different error, on a different pod, for the same endpoint:

asyncpg.exceptions.DuplicatePreparedStatementError:
  prepared statement "__asyncpg_stmt_23__" already exists
  code: 42P05

Both error codes point at the same mechanism from opposite sides. asyncpg defaults to server-side prepared statements: the first time a connection object runs a given query text, it issues a real Postgres PREPARE under a generated name (__asyncpg_stmt_N__, incrementing per connection object) and caches that name locally, up to statement_cache_size entries, so the next call with the same query text skips straight to EXECUTE. That's a real win under session pooling, where a connection object maps to one stable backend for its whole life.

Under transaction pooling, it doesn't. PgBouncer hands a client a backend connection only for the length of one transaction, then returns that backend to the shared pool for the next client in line, which might be a completely different task. asyncpg's connection object has no way to know this happened. It still believes it prepared __asyncpg_stmt_23__ on "its" connection, and sends a bare EXECUTE for that name on whatever backend PgBouncer hands it next. If that backend never saw the PREPARE, Postgres returns 26000. If a different pod's asyncpg pool independently generated the same sequential name and its PREPARE is still sitting on that backend from an earlier transaction, Postgres returns 42P05 instead. Which error you get is just a coin flip based on backend connection history seconds earlier.

the find

Root cause: asyncpg's default client-side prepared statement cache assumes a stable, dedicated connection to the backend it prepared against. PgBouncer's transaction pooling mode breaks that assumption by design, reassigning physical backend connections between transactions. The two layers had been individually correct and mutually incompatible the entire time; nothing caught it earlier because session pooling had been quietly holding the assumption true.

the fix

Immediate mitigation: disable asyncpg's statement cache for the pool, which forces every query through the extended protocol without a named, reusable prepared statement.

# before
pool = await asyncpg.create_pool(dsn=DATABASE_URL, min_size=5, max_size=20)

# after
pool = await asyncpg.create_pool(
    dsn=DATABASE_URL,
    min_size=5,
    max_size=20,
    statement_cache_size=0,
)

That change alone stopped both error codes within one deploy cycle, at the cost of re-parsing and re-planning every query on every execution instead of reusing a cached plan, a real but acceptable tradeoff against a live 500 storm.

The durable follow-up was upgrading PgBouncer to 1.21, which added protocol-level support for prepared statements under transaction pooling via max_prepared_statements, letting PgBouncer track and replay PREPARE statements across backend reassignment instead of forcing every client to give up caching entirely.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

With that setting in place, statement_cache_size was restored to its default on the application side and re-verified under a load test that actually mirrored production: thirty concurrent tasks, mixed endpoints, run for twenty minutes against a staging PgBouncer configured identically to prod. The earlier staging test hadn't caught this because it never generated enough concurrent, cross-pod prepared-statement traffic to expose the collision.

the aftermath

A synthetic check now runs the same parameterized query twice in quick succession through PgBouncer every thirty seconds and pages on 26000 or 42P05 specifically, rather than waiting for the generic error-rate threshold to trip. The staging load test was rewritten to scale to production pod counts and mix endpoints instead of hammering a single route, since that was the exact gap that let this ship.

What session pooling had actually been doing this whole time was holding a promise nobody in the app ever wrote down: that a connection stays a connection long enough for a prepared statement's name to still mean something the next time you reach for it.