Prepared statements vs connection pools

This is part three of the Postgres series (part one, part two), and it’s about the most common production surprise in the Postgres world: the application that works flawlessly in development and starts throwing prepared statement "S_1" does not exist the moment it meets a connection pooler under load.

Everyone hits this eventually. Almost nobody hits it in staging, because it needs three ingredients that only production reliably supplies: a driver that prepares statements, a pooler in transaction mode, and enough concurrency that the pool actually shuffles connections. Let’s build the failure from the wire up.

Prepared statements are connection state

Recall from part one that drivers speak the extended query protocol: Parse compiles SQL into a named prepared statement, Bind attaches values, Execute runs it. The part that matters today: that named statement lives inside one server connection. It is not global, not shared, not persistent. It’s an entry in a per-connection hash table, and it dies with the connection.

Drivers exploit this for speed. The JDBC driver, for instance, counts executions of each query, and once the same statement has run five times (the default prepareThreshold), it quietly switches to a server-side named statement: parse once, then just bind and execute on every subsequent call, skipping the planning overhead. node-postgres does it when you give a query a name. Most ORMs do some version of this without telling you.

On a plain connection this is pure win. The driver’s assumption is: the connection I prepared on is the connection I’ll execute on. And that assumption is exactly what a pooler breaks.

What the pooler does to your assumption

Poolers like pgbouncer exist because Postgres connections are expensive and applications hold them idle. They multiplex: many client connections share few server connections. The sharing granularity is the pooling mode, and it decides everything:

• Session mode: a client keeps one server connection from connect to disconnect. Safe and boring, but you save the least, because idle clients still monopolise server connections.
• Transaction mode: a client borrows a server connection per transaction and returns it at commit. This is the mode people actually deploy, because it’s where the multiplexing wins are.
• Statement mode: a connection per statement. Maximal sharing, maximal breakage; rare in practice.

Now replay the driver’s behaviour in transaction mode. Your app runs the same query five times. Those five executions happen to ride on server connection A, so the driver sends Parse naming the statement S_1 on A and remembers “S_1 is ready.” The next transaction gets assigned server connection B. The driver, believing its own bookkeeping, sends Bind for S_1 on B. Connection B has never heard of S_1:

ERROR: prepared statement "S_1" does not exist

And there’s a nastier variant: connection B does have an S_1, prepared by some other client of the pool for a completely different query. Depending on driver and pooler behaviour you can get type mismatches and confusing failures far from the real cause. The bug reads like data corruption; it’s actually two parties disagreeing about whose hash table they’re talking to.

This is why it “works locally”: your dev setup either has no pooler, or so little concurrency that the pool hands you the same server connection every time. The bug was always there. Production concurrency just bought it a lottery ticket.

Prepared statements are one case of a general law

Before the fixes, zoom out, because this failure has siblings. The general law: in transaction pooling, anything session-scoped is a lie. Prepared statements are the famous victim, but the same shuffle breaks:

• SET settings (use SET LOCAL, which is transaction-scoped, instead)
• temporary tables
• advisory locks acquired at session level
• LISTEN/NOTIFY subscriptions
• sequence currval() bookkeeping

If you remember one sentence from this post: audit your app for session state before turning on transaction pooling, and prepared statements are just the first item on that audit.

The fixes, ranked

1. Let the pooler track prepared statements (best, if you can). pgbouncer 1.21 added protocol-level prepared statement support: set max_prepared_statements above zero and it intercepts Parse/Bind at the wire level, keeps its own map of which server connection has which statement, and transparently re-prepares when a client’s statement lands on a fresh connection. The driver’s assumption becomes true again because the pooler maintains the illusion. If your pgbouncer is new enough, this is the answer: you keep both the pooling and the prepared-statement speedup.

2. Disable server-side prepares in the driver. The classic fix, and still correct for older poolers: JDBC prepareThreshold=0, node-postgres by not naming queries, or your ORM’s equivalent flag. You keep parameterised queries (the Parse with an unnamed statement is discarded after the Sync, which transaction pooling handles fine); you lose the plan-caching speedup, which for most OLTP workloads costs single-digit percent. Measure before mourning it.

3. Session pooling for the connections that need it. Nothing says the whole app must share one mode. A common shape: transaction pooling for the high-volume stateless traffic, and a small session-mode pool for the workers that genuinely need session state. Poolers happily run both on different ports.

4. DISCARD ALL is for session mode, not a fix for this. You’ll see server_reset_query = DISCARD ALL suggested. Understand what it does: it wipes connection state between clients in session pooling, preventing state leaks. In transaction mode it doesn’t run between transactions (and if it did, it would destroy the statements mid-conversation). It’s hygiene, not a cure.

Takeaways

• A prepared statement is per-connection state. The driver caches its name; the server holds the reality. Transaction pooling makes those diverge.
• The bug needs concurrency to appear, which is why the pool always looks innocent in staging.
• Modern fix: pgbouncer 1.21+ with max_prepared_statements. Legacy fix: turn off server-side prepares. Structural fix: session pooling for whatever genuinely needs session state.
• Prepared statements are just the loudest member of the family. SET, temp tables, advisory locks, and LISTEN all break the same way, more quietly.

The pool in the cover photo has lane ropes for a reason: swimmers who assume the whole pool is theirs collide. Connections are lanes. The protocol assumed you owned yours; the pooler rents it out between your laps.