Fixing 'prepared statement does not exist' under PgBouncer transaction pooling

Symptoms

• Queries intermittently fail with `prepared statement "..." does not exist`
• It gets worse under load and is rare or absent with a single connection
• It started (or got worse) after putting PgBouncer in front of Postgres, or after changing pool_mode

The diagnostic path

8 steps, exactly as this revision published them.

1. Step 1 · StartA named prepared statement 'does not exist'

   This surfaces through PgBouncer, but the underlying mechanism is about which Postgres backend connection actually has the statement prepared.

   What happens next

   • passed → step 2, Confirm the pool is in transaction (or statement) pooling mode

2. Step 2 · TestConfirm the pool is in transaction (or statement) pooling mode

   Connect to PgBouncer's admin console (psql -h <host> -p <port> pgbouncer) and check pool_mode for the affected database.

   Read-onlysql

   Copy

   SHOW CONFIG;

   Expected result

    key        | value       | changeable 
   ------------+-------------+------------
    pool_mode  | transaction | yes
   (1 row)

   What happens next

   • passed → step 3, Transaction pooling reassigns server connections between statements
   • failed → step 4, pool_mode is session, not transaction/statement
   • unknown → step 4, pool_mode is session, not transaction/statement

3. Step 3 · Root causeTransaction pooling reassigns server connections between statements

   In transaction (or statement) pooling mode, PgBouncer returns the underlying server connection to the pool as soon as a transaction (or statement) ends, and a client's next transaction may be handed a completely different server connection. A named prepared statement exists only on the specific backend it was PREPARE'd on — so a later statement that references it by name, on a different backend that never ran that PREPARE, fails with 'does not exist'.

   What happens next

   • passed → step 5, Check PgBouncer's version, then use native prepared-statement tracking or turn it off client-side

4. Step 4 · Endpool_mode is session, not transaction/statement

   In session pooling, a client keeps the same server connection for its whole session, so this specific failure mode shouldn't occur. If it's still happening under session pooling, something else is going on — out of scope here.

5. Step 5 · FixCheck PgBouncer's version, then use native prepared-statement tracking or turn it off client-side

   PgBouncer 1.21 and later can track and transparently re-prepare named statements per server connection when maxpreparedstatements is set above 0 — this is the preferred fix if your version supports it. Edit pgbouncer.ini and reload with RELOAD; via the admin console once changed.

   Changes stateini

   Copy

   Changes system or service state. Review before running.

   Once reloaded (RELOAD; via the admin console), enables PgBouncer to track and transparently re-prepare named statements per server connection, up to 200 concurrently per connection. Existing connections and in-flight transactions are not dropped by the reload.

   max_prepared_statements = 200

   What happens next

   • passed → step 6, Confirm the errors stop and the setting is active

6. Step 6 · Verify the fixConfirm the errors stop and the setting is active

   Re-check the config after reloading, and re-run the workload that was triggering the error.

   Read-onlysql

   Copy

   SHOW CONFIG;

   Expected result

    key                        | value | changeable 
   -----------------------------+-------+------------
    max_prepared_statements    | 200   | yes
   (1 row)

   What happens next

   • passed → step 7, Resolved
   • failed → step 8, If you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection
   • unknown → step 8, If you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection

7. Step 7 · EndResolved

   PgBouncer is now re-preparing statements transparently per backend connection.

8. Step 8 · FixIf you can't upgrade PgBouncer yet, stop using named prepared statements against the pooled connection

   Disable automatic/named prepared statements on the client for connections that go through this pool — for example, in postgres.js this is the prepare: false client option (see that playbook for the client-side version of this same incompatibility); in a driver that names statements explicitly, switch to unnamed/simple-protocol queries for this database. Alternatively, set pool_mode to session for just this database, at the cost of losing transaction pooling's connection-reuse efficiency.

Sources

• PgBouncer 1.x - Configuration— PgBouncer