Prepared Statements Across a Connection Pooler
Transaction pooling and named prepared statements disagree about what a session is. Here is the protocol-level fix.
The error arrived at 2am in 0.4% of requests: prepared statement "_pg3_0" does not exist. The service had been stable for months; the only change was a PgBouncer rollout to transaction mode.
This is not a PgBouncer bug. It is a disagreement about session semantics, and it appears with every driver that caches prepared statements by name.
What happens on the wire
Postgres extended query protocol is a three-step conversation:
Parse(name, sql) -> Bind(name, params) -> ExecuteThe statement name is scoped to the session. Transaction pooling gives each transaction whichever server connection is free, so Parse and Bind can land on different backends.
Sometimes the second backend has a statement with the same name but different SQL. Then Bind does not fail — it binds your parameters to the wrong query. That is the failure mode that should worry you, and it is why "it mostly works" is not evidence.
What the error messages mean
| Error | What happened |
|---|---|
prepared statement "S_1" does not exist | Parse and Bind landed on different backends |
bind message supplies 0 parameters, but prepared statement requires 2 | same name, different SQL on the second backend |
prepared statement already exists | two clients prepared the same name on one backend |
The middle row is the dangerous one. A name collision can silently change the query shape, and it may only show up as a wrong result under load.
Three fixes, in order of preference
1. PgBouncer 1.21+ with max_prepared_statements. PgBouncer tracks prepared statements per client and re-prepares them on whichever server connection the transaction lands on. You keep transaction pooling and named statements.
2. Disable driver-side statement caching. This costs a Parse per execution, typically 0.2–0.5ms, which the network round trip often hides.
| Driver | Setting |
|---|---|
| asyncpg | statement_cache_size=0 |
| JDBC | ?prepareThreshold=0 |
| psycopg3 | prepare_threshold=None |
| pgx (Go) | statement_cache_capacity=0 |
3. Session pooling for that database. Honest, but you give up multiplexing. Fine when the connection count is small.
The configuration we run
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 300300 is not a magic number. It is roughly the number of distinct statements the service issues; check pg_prepared_statements on a backend if unsure.
Verify with a matrix, not a demo
We test 3 drivers × 2 pool modes × named and unnamed statements, 10,000 queries with 50 concurrent clients, and assert zero errors. The test also checks statement count on backends, because "no errors" can coexist with unbounded statement growth.
Results after the fix: error rate 0.4% → 0, p99 unchanged at 92ms, backend prepared statements capped at 300 per connection.
The general rule
If a feature is scoped to a session, it cannot survive transaction pooling. Prepared statements, temporary tables, advisory locks, SET, and LISTEN are all the same category. Either give them a session (session pooling), or remove the assumption (driver settings), or let the pooler emulate it (1.21+).
The pooler did not break prepared statements. It revealed that the application had been relying on a session that was never promised.