🚨 PostgreSQL Error Guide: 'prepared statement "S_1" already exists' — Fix Pooler Conflicts

Overview

PostgreSQL raises this error when a session tries to create a server-side prepared statement using a name that is already registered on that backend connection:

ERROR:  prepared statement "S_1" already exists

Driver-generated names vary — you will also see pstmt_1_2, lrupsc_1_5, or similar — but the shape is identical: the client thinks the name is free, the server says it is taken. The near-universal cause is a connection pooler in transaction pooling mode sitting between the application and Postgres, where server-side prepared statements are fundamentally unsafe.

The complementary error, seen on the same setup when a client tries to execute a prepare it believes exists on a connection that has since been swapped, is:

ERROR:  prepared statement "S_1" does not exist

Both are two faces of the same mismatch: prepared statements live on a specific backend connection, but transaction pooling hands out a different backend for each transaction.

Symptoms

• Errors appear only under load or after the pool warms up, not in local testing without a pooler.
• The same query works, then intermittently fails with “already exists” or “does not exist.”
• Turning off the pooler (connecting directly to Postgres) makes the problem vanish.
• The app uses a driver with server-side prepares on by default (Rails/pg, JDBC with prepareThreshold, asyncpg, Npgsql, psycopg with prepared statements).
• PgBouncer is configured with pool_mode = transaction.

Common Root Causes

• PgBouncer (or similar) in transaction pooling mode. Each transaction may run on a different physical backend, so a prepared statement created earlier is not present on the connection serving the next transaction — and reused names collide.
• Server-side prepared statements enabled in the driver. ORMs and drivers cache prepared statements per logical connection, assuming a stable backend. Behind a transaction pooler that assumption is false.
• Reused, predictable statement names. Drivers name statements sequentially (S_1, S_2); when a pooled backend already carries an S_1 from an earlier client, the new client’s PREPARE S_1 collides.
• Long-lived logical connections over a churning physical pool. The application connection stays open while the pooler rotates backends underneath it.
• Mixed application versions / an older PgBouncer without the protocol-level prepared-statement support added in PgBouncer 1.21+.

Diagnostic Workflow

First confirm a pooler is involved and which mode it is in — this is the decisive fact:

# In pgbouncer.ini:
grep -E 'pool_mode|max_prepared_statements' /etc/pgbouncer/pgbouncer.ini

From inside PgBouncer’s admin console, inspect pools and modes live:

-- psql to the pgbouncer admin database:
SHOW CONFIG;     -- look at pool_mode and max_prepared_statements
SHOW POOLS;      -- pool_mode per database, active/waiting clients

On the Postgres side, see the prepared statements currently registered on your session (note this only shows the backend you are attached to):

SELECT name, statement, prepare_time, from_sql
FROM pg_prepared_statements;

Confirm the client driver is issuing protocol-level prepares by watching the logs with statement logging on, in a controlled test:

-- On a test instance only:
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();
-- Look for Parse/Bind/Execute (extended protocol) and PREPARE lines.

Reproduce deterministically by pointing the app directly at Postgres (bypassing the pooler): if the error disappears, the pooler + server-side prepares combination is confirmed.

Example Root Cause Analysis

A Rails app moved from a direct connection to PgBouncer to survive a connection storm. Within minutes of deploy, background jobs began failing:

ActiveRecord::StatementInvalid: PG::DuplicatePstatement:
ERROR:  prepared statement "a1" already exists

SHOW CONFIG on the PgBouncer admin console showed pool_mode = transaction. The Rails pg adapter defaults to server-side prepared statements, caching them per logical connection. Under transaction pooling, PgBouncer handed each transaction whatever backend was free — sometimes one that already carried a prepared statement named a1 from a different client — and the PREPARE a1 collided.

Two safe fixes were available. The immediate one was to disable server-side prepares in the application so no PREPARE is issued at all:

# config/database.yml
production:
  adapter: postgresql
  prepared_statements: false
  # (statement_limit tuning is unnecessary once prepares are off)

The forward-looking fix was to upgrade PgBouncer to 1.21+ and enable its protocol-level prepared-statement support, which tracks and re-prepares statements per backend:

# pgbouncer.ini (PgBouncer 1.21+)
pool_mode = transaction
max_prepared_statements = 200

After disabling prepares in the app, the jobs stopped erroring immediately; the team then scheduled the PgBouncer upgrade to get prepared-statement performance back safely.

Prevention Best Practices

• If you run a pooler in transaction mode, either disable server-side prepared statements in every client driver, or run PgBouncer 1.21+ with max_prepared_statements set so the pooler manages them safely.
• Match pool mode to your workload: use session pooling if you truly need per-session state (prepared statements, SET, advisory locks), accepting fewer multiplexed connections.
• Standardize the prepared-statement setting across every service that shares a pool — one service leaving prepares on can poison shared backends for others.
• Keep PgBouncer and drivers current; protocol-level prepared-statement support is a relatively recent feature and removes the need to disable prepares entirely.
• Load-test through the pooler, not against a direct connection, so this class of bug surfaces before production.
• Monitor for DuplicatePstatement/InvalidSqlStatementName errors in application logs as an early signal of a pool-mode/driver mismatch after any infra change.

Quick Command Reference

grep -E 'pool_mode|max_prepared_statements' /etc/pgbouncer/pgbouncer.ini

SHOW CONFIG;                       -- pgbouncer admin: pool_mode, max_prepared_statements
SHOW POOLS;                        -- pgbouncer admin: per-db pool mode and clients
SELECT * FROM pg_prepared_statements;             -- prepares on the current backend
DEALLOCATE ALL;                    -- drop all prepares on the current session
DISCARD ALL;                       -- reset session state (prepares, temp tables, SET)

Conclusion

prepared statement "S_1" already exists (and its “does not exist” twin) is almost never a bug in your SQL — it is the well-known clash between server-side prepared statements and a connection pooler in transaction mode, where prepared statements bound to one backend collide as the pooler rotates connections. Confirm the pooler’s pool_mode, then choose one of two clean fixes: disable server-side prepares in the driver, or move to PgBouncer 1.21+ with max_prepared_statements so the pooler tracks them for you. Align the setting across every service on the shared pool and load-test through the pooler so the mismatch never reaches production again.