Fixing postgres.js 'prepared statement already exists' under PgBouncer

Symptoms

• postgres.js throws `PostgresError: prepared statement "s0" already exists` (or another sN name)
• It only happens once traffic goes through PgBouncer — connecting straight to Postgres doesn't reproduce it
• It's intermittent and worse under concurrent load

The diagnostic path

8 steps, exactly as this revision published them.

1. Step 1 · Startpostgres.js reports a prepared statement already exists

   This is specific to postgres.js's own automatic prepared-statement feature, not a generic Postgres or PgBouncer error message.

   What happens next

   • passed → step 2, Confirm transaction/statement pooling is in use and prepare wasn't already disabled

2. Step 2 · TestConfirm transaction/statement pooling is in use and prepare wasn't already disabled

   Check the postgres.js connection options for an explicit prepare: false — if absent, automatic prepare defaults to on. Also confirm PgBouncer's pool_mode via its admin console.

   Read-onlysql

   Copy

   SHOW CONFIG;

   Expected result

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

   What happens next

   • passed → step 3, Automatic prepare assumes a stable server connection
   • failed → step 4, Not transaction/statement pooling, or prepare already off
   • unknown → step 4, Not transaction/statement pooling, or prepare already off

3. Step 3 · Root causeAutomatic prepare assumes a stable server connection

   postgres.js transparently prepares each distinct query text once per connection and reuses it by name on later calls. That assumption — that the same TCP connection always reaches the same Postgres backend — breaks under PgBouncer transaction (or statement) pooling, where each transaction can be handed a different backend process. postgres.js can end up trying to prepare a statement name on a backend where PgBouncer's own connection/prepared-statement bookkeeping doesn't match what postgres.js believes was prepared there, producing 'already exists' (the mirror-image failure, 'does not exist', comes from the same root incompatibility). This is a known limitation of client-side named prepared statements against transaction/statement-mode pooling in general, not something specific to one direction of the error.

   What happens next

   • passed → step 5, Disable postgres.js's automatic prepared statements for pooled connections

4. Step 4 · EndNot transaction/statement pooling, or prepare already off

   If pool_mode is session and prepare wasn't disabled, this specific failure mode shouldn't occur — session pooling keeps a client on the same backend for its whole session. If it still reproduces under those conditions, something else is going on, outside this playbook.

5. Step 5 · FixDisable postgres.js's automatic prepared statements for pooled connections

   Pass prepare: false when creating the postgres.js client that connects through PgBouncer. This makes postgres.js send plain queries instead of named prepared statements, which is safe under transaction pooling because there is no per-connection prepared-statement state for it to lose track of.

   Read-onlyjavascript

   Copy

   const sql = postgres(connectionString, { prepare: false });

   What happens next

   • passed → step 6, Confirm the errors stop under pooled load

6. Step 6 · Verify the fixConfirm the errors stop under pooled load

   Re-run the workload against PgBouncer and watch for either 'already exists' or 'does not exist' prepared-statement errors from this client.

   What happens next

   • passed → step 7, Resolved
   • failed → step 8, Still happening after disabling prepare
   • unknown → step 8, Still happening after disabling prepare

7. Step 7 · EndResolved

   postgres.js is issuing plain queries against the pooled connection and the prepared-statement errors are gone.

8. Step 8 · EndStill happening after disabling prepare

   If disabling client-side prepare doesn't resolve it, another client sharing the same PgBouncer pool may still be issuing named prepared statements, or PgBouncer's own configuration needs attention — see the PgBouncer prepared-statement playbook. The two mechanisms are independent and, in a mixed-client environment, both may need adjusting.

Sources

• postgres.js— porsager/postgres (GitHub)
• PgBouncer 1.x - Configuration— PgBouncer