PgBouncer Prepared Statement Already Exists: Escaping the Pooling Prepared-Statement Collision

Why the error appears

The beginning of the error is when a connection pooler like PgBouncer shifts the backend database connection for a client's transaction, and the client library sends a new prepared-statement request to the former connection. The typical sequence:

1. Client library sends a prepared-statement request to server connection S1
2. PgBouncer commits and assigns S1 to a new transaction
3. Client's next transaction uses server S2
4. Client library reuses the same prepared-statement name on S2, but S2 never received it

So it is not a generic PostgreSQL failure, but a pooling mismatch: a driver reuses prepared-statement names while PgBouncer transaction pooling reassigns backend connections between transactions. The latter reselects a server connection between prepare and execute, while the former assumes the pair remains on one server.

The old failure mode

The collision is not new, but its appearance depends on both client library and PgBouncer version.

In the time before PgBouncer 1.21, even protocol-level prepared statements were not supported in transaction pooling mode. PgBouncer buffered them while still unsupported, but lost them as soon as the transaction or client went away. Prepared-statement tracking existed, but not in transaction mode.

So pre-1.21, only session pooling could mix with prepared-statement tracking. To avoid the collision, you had to switch off server-side prepared statements in the client library. The only fix was make driver talk plain SQL, though you lost out on the performance gain of reused plan trees and execution trees.

Even when PgBouncer began protocol-level support in 1.21, it was not as if the collision simply vanished. PREPARE-style SQL statements were not implemented, and remain unsupported. So while SELECT, DELETE, and other protocol-level queries now persisted across transactions on a PgBouncer-assigned connection, hand-coded PREPARE and EXECUTE still collided with each reassignment.

The cheapest fix: disable client-side prepare

If a driver prepares SQL server-side before sending it, you can disable the driver's first-line strategy and avoid the collision. A typical example is PostgreSQL's Java driver: it auto-caches prepared statements server-side, assigning each a reuse name on first use.

The straightforward exchange is to nudge it to prepare less often. In JDBC, specifying a prepareThreshold of 0 disables the client-side caching of prepared statements, trading an efficiency gain on repeated execution for a collision-free pairing with transaction mode.

This is the quickest remedy, though it touches driver settings, and it's not universally available. If the driver natively defaults to client-side parsing, or does not expose a setting, patching is likely out of reach. If the cost of repeated statement-planning outweighs the delay of greetings (sever connections), it's best to look for backend support instead.

A fallback is to reset the driver session at the start of every transaction, with the PgBouncer server_reset_query to clear any stray session state. As a last resort, as long as the driver can't be patched, session pooling remains is an option of last resort.

The newer PgBouncer path

For many applications, switching off the whole client-side caching strategy is an option of last resort. More precise is to let the prepare and execute still occur the client side, and patch the PgBouncer instead.

The root of this path is to enable tracker mode in PgBouncer. When max_prepared_statements is set, PgBouncer starts to manage protocol-level prepared statements in transaction pooling mode. It synchronizes the state across server slots, mapping the names used on client-facing slots.

In the presence of max_prepared_statements with a non-zero value, the pool gives each unique query an internal identifier that maps the slot the client initially connected to the one that the client finally executes on, whether it's different or not.

This means:

1. Every unique statement string receives an internal name like PGBOUNCER_{unique_id}
2. When executing a statement on its assigned server, PgBouncer names the statement internally
3. If the same statement was originally prepared on a different server, the pooler synchronizes it on from one to the other
4. Afterwards, neither PgBouncer nor the application sends the prepare step a second time.

So the collision-prevention is largely invisible, in terms of the PgBouncer commands used, and highly efficient.

The blunt fallback: session mode

If neither the driver nor the pooler can be persuaded to handle statements cross-transaction, session mode is an alternative. On the plus side, switch to session pooling avoids the collision with one line of config. But all your transaction-level efficiencies are gone.

Even in PgBouncer 1.21, which has refined its transaction mode to track prepared statements, foundational limits still bind its manageability. PgBouncer supports only protocol-level prepared statements, and does not offer statement names as part of the Transact system, It clears unsupported SQL commands by default.

Using session pooling instead is a last resort, but it has a right to live. For legacy versions, or for drivers with hard-coded recursive SQL, session pooling ensures smooth sailing for even the vaguest prepared statement name. The vigilant user will count the cost on the first query in each session, but may decide it's a win.

How not to debug

This article explains broadly what to avoid. It frames the collision as a mismatch, and makes it concrete with examples. It shows both how the libraries mix, and the curveballs in each solution.

Here's what this does not prove. It does not list every possible case. Here's what this article does not prove. It does not provide a complete list of every possible generated name. It does not provide a unified syntax for every setting, nor allows us to predict the effect on performance. It only picks a single data layer to show the patches working. For a grounding, run it against the drivers on your own workload and see. Use under control.