Using Prepared Statements with PgBouncer Transaction Mode

This guide is part of PgBouncer Transaction vs Statement Pooling. It addresses one of the most common production failures after deploying PgBouncer in front of PostgreSQL: server-side prepared statements stop working under transaction pooling, and the application starts logging ERROR: prepared statement "S_1" does not exist (SQLSTATE 26000) intermittently and under load. The error is non-deterministic, passes in staging, and reappears the moment concurrency rises — a signature of backend socket multiplexing colliding with per-connection statement state.

A server-side prepared statement is created on a specific PostgreSQL backend with PARSE/BIND/EXECUTE at the wire-protocol level. The statement name (S_1, pdo_stmt_00000001, etc.) is meaningful only on the backend that ran PARSE. Under pool_mode = transaction, PgBouncer returns the backend to the pool at COMMIT/ROLLBACK and hands the client a different backend for the next transaction — one that never saw the PARSE. The driver issues BIND against a name the new backend does not recognize, and PostgreSQL raises 26000. This guide explains the failure precisely, then covers the three durable fixes: PgBouncer 1.21+ max_prepared_statements, disabling driver-side prepared statements, and protocol-level prepares that are scoped to a single transaction.

Rapid incident diagnosis

The failure mode is identifiable from three signals correlated in time. Triage in this order before changing any configuration.

First, scan PostgreSQL logs for the exact string prepared statement "..." does not exist. The quoted name reveals the driver: S_1 is libpq/pgx/asyncpg, S_n ascending is the JDBC driver, pdo_stmt_* is PHP PDO. The presence of an ascending counter (S_1, S_2, S_3) confirms server-side prepared statement caching is active in the driver.

Second, confirm the pool mode. Run SHOW CONFIG; on the PgBouncer admin console and check pool_mode. If it reads transaction or statement, server-side prepared statement reuse across transactions is structurally impossible without max_prepared_statements. Only session mode preserves a 1:1 client-to-backend binding for the life of the connection.

Third, check whether errors correlate with pool churn rather than query content. The same query that fails under load succeeds when run alone because, at low concurrency, the client frequently gets the same backend back. This is the tell that distinguishes a pooling-mode bug from a genuine SQL error.

Log / metric signal Threshold Indicates
prepared statement "S_n" does not exist any occurrence server-side prepares under txn/statement mode
SHOW CONFIG → pool_mode transaction or statement no cross-transaction backend affinity
SHOW CONFIG → max_prepared_statements 0 PgBouncer prepared-statement support disabled
Error rate vs cl_active rises with concurrency multiplexing collision, not bad SQL
SHOW POOLS → sv_active ≪ cl_active sustained high multiplexing ratio (more collisions)

The deeper mechanics of how PgBouncer multiplexes clients across a small set of backends are covered in the parent topic, PgBouncer Transaction vs Statement Pooling, and the framework-side caching behavior is detailed in Prepared Statement Caching Under Transaction Pooling.

Prepared statement collision under transaction pooling A client parses S_1 on backend A in transaction 1, then PgBouncer routes transaction 2 to backend B which never saw the PARSE, raising error 26000. Client caches S_1 name PgBouncer pool_mode=transaction Backend A has PARSE S_1 Backend B no S_1 — error 26000 txn 1: PARSE S_1 routes to A txn 2: BIND S_1 routes to B
Transaction 2 lands on a backend that never received the PARSE, so the cached statement name is invalid.

Why server-side prepares break under multiplexing

Server-side prepared statements provide two benefits: the planner parses and plans the query once, and the client sends only parameters on subsequent executions. Both benefits depend on the statement living on a stable backend. Transaction pooling deliberately destroys that stability — that is the entire point of multiplexing, which lets 1,000 clients share 25 backends.

The collision rate is a function of the multiplexing ratio. With a default_pool_size of 25 serving 1,000 clients, any given client is statistically unlikely to receive the same backend on consecutive transactions. The probability that a cached statement is valid on the next transaction is roughly 1 / default_pool_size per active backend, so the error is near-certain at scale and nearly invisible in a single-connection test. This is why the bug survives staging and detonates in production.

There are exactly three structural resolutions, in order of preference for most stacks: let PgBouncer track and replay prepared statements (max_prepared_statements), stop the driver from creating server-side prepares, or constrain prepares to live and die within one transaction.

Exact remediation and configuration

Option 1 — PgBouncer 1.21+ max_prepared_statements (preferred)

PgBouncer 1.21.0 added native support for protocol-level prepared statements in transaction and statement modes. PgBouncer intercepts PARSE messages, assigns each client statement a name on whichever backend it is currently using, and transparently re-prepares (replays the PARSE) on a new backend when the client is multiplexed elsewhere. The application keeps using prepared statements and the error disappears.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 25
ignore_startup_parameters = extra_float_digits

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

max_prepared_statements is the number of distinct prepared statements PgBouncer caches per server connection. Set it to comfortably exceed the count of distinct statements your hottest path prepares — 200 covers most ORM workloads. A value of 0 (the default) disables the feature entirely. Two constraints matter: the feature requires clients to use the extended query protocol (named or unnamed protocol-level prepares), and server_reset_query = DISCARD ALL must remain, since PgBouncer manages statement deallocation internally and re-prepares as needed.

Apply with zero downtime by editing pgbouncer.ini and issuing RELOAD; on the admin console (or pgbouncer -R). New server connections pick up the directive; existing ones drain. No client restart is required.

Option 2 — Disable driver-side prepared statements

If you cannot run PgBouncer 1.21+, force the driver to stop creating server-side prepares. The query is then sent as a simple or one-shot extended query each time, which is always backend-agnostic. The cost is losing plan caching, which is acceptable for most CRUD workloads.

// JDBC: PostgreSQL driver — never use named server-side statements
jdbc:postgresql://pgbouncer:6432/mydb?prepareThreshold=0
// or programmatically on the HikariCP datasource properties:
ds.addDataSourceProperty("prepareThreshold", "0");

prepareThreshold=0 tells the PostgreSQL JDBC driver never to switch a statement to a named server-side prepared statement, regardless of reuse count. This is the canonical fix for HikariCP behind PgBouncer; see HikariCP Configuration Deep Dive for the full datasource property set.

# asyncpg: disable the per-connection statement cache
import asyncpg
conn = await asyncpg.connect(
    dsn="postgres://pgbouncer:6432/mydb",
    statement_cache_size=0,            # no server-side prepared statements
)
# psycopg (libpq, v3): use the simple query protocol or cap prepares
import psycopg
conn = psycopg.connect("postgres://pgbouncer:6432/mydb", prepare_threshold=None)
# prepare_threshold=None disables automatic server-side preparation
// pgx (Go): use the simple protocol so no PARSE/BIND is emitted
config, _ := pgx.ParseConfig("postgres://pgbouncer:6432/mydb")
config.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
conn, _ := pgx.ConnectConfig(ctx, config)

For asyncpg, statement_cache_size=0 alone is necessary but historically insufficient on older versions because the driver still introspected types via prepares; on current versions it is the correct switch. For pgx, QueryExecModeSimpleProtocol eliminates the protocol-level prepare entirely and is the safest choice behind transaction pooling.

Option 3 — Protocol-level (unnamed) prepares scoped to one transaction

A middle path: keep the parameter-binding safety of the extended protocol but use the unnamed prepared statement (empty statement name ""), which PostgreSQL implicitly discards after the next BIND/EXECUTE. Because nothing persists beyond the current message exchange, there is no cross-transaction name to invalidate. Most modern drivers expose this as the default “extended protocol without caching” mode (for example pgx’s QueryExecModeExec). This preserves server-side parameterization within a transaction while remaining safe under multiplexing.

Validation and verification

Confirm the fix from both sides — the driver must stop emitting stale BINDs, and PgBouncer must report healthy multiplexing.

On the PgBouncer admin console, verify the directive took effect and the pool is multiplexing without errors:

-- PgBouncer admin DB (psql -p 6432 pgbouncer)
SHOW CONFIG;     -- confirm pool_mode and max_prepared_statements
SHOW POOLS;      -- cl_active high, sv_active low = healthy multiplexing
SHOW STATS;      -- total_query_count rising, no error spikes

On PostgreSQL, confirm prepared statements are either absent (Options 2/3) or correctly scoped, and that the error has stopped:

-- On the target database
SELECT name, statement FROM pg_prepared_statements;   -- expect empty for Option 2/3
SELECT count(*) FROM pg_stat_activity
  WHERE state = 'idle in transaction';                 -- expect 0 sustained

For a load-test assertion, drive the endpoint at production concurrency for several minutes and assert zero occurrences of SQLSTATE 26000 in PostgreSQL logs. The error is concurrency-sensitive, so the test must exceed default_pool_size in concurrent clients to be meaningful. A single-connection smoke test will pass even with the bug present.

Validation step Command Expected result
Pool mode + feature flag SHOW CONFIG; pool_mode=transaction, max_prepared_statements > 0 (Option 1)
Multiplexing health SHOW POOLS; cl_active ≫ sv_active, cl_waiting=0
Statement persistence SELECT * FROM pg_prepared_statements; empty (Option 2/3)
Error elimination grep PostgreSQL log for 26000 zero occurrences under load
Latency regression check application APM P95 no increase vs session-mode baseline

Frequently Asked Questions

Why does the prepared statement does not exist error only appear under load?
At low concurrency the client usually receives the same backend on its next transaction, so the cached statement happens to still exist. As concurrency exceeds default_pool_size, PgBouncer routes consecutive transactions to different backends, and the cached statement name becomes invalid on the new socket. The error probability scales with the multiplexing ratio, which is why it surfaces in production but not in single-connection tests.
Does disabling prepared statements hurt performance?
For most CRUD and ORM workloads the impact is negligible — PostgreSQL still parses and plans efficiently, you only lose the cached plan reuse. The exception is hot, complex analytical queries executed thousands of times per second, where re-planning cost is measurable. For those, prefer PgBouncer 1.21+ max_prepared_statements, which keeps plan reuse while remaining multiplexing-safe.
Is max_prepared_statements a substitute for server_reset_query?
No. max_prepared_statements manages prepared-statement replay across backends; server_reset_query = DISCARD ALL clears other session state (temporary tables, SET variables, search_path) between transactions. Keep both. PgBouncer coordinates its prepared-statement cache with the reset cycle internally.
Can I keep using prepared statements if I switch to session pooling?
Yes — pool_mode = session binds one backend to one client for the life of the connection, so server-side prepared statements remain valid. The trade-off is losing multiplexing: you need roughly as many backends as concurrent clients, which defeats the main reason most teams deploy PgBouncer. Use session mode only when statement reuse is critical and connection counts are bounded.
Which driver setting maps to which fix?
JDBC uses prepareThreshold=0; asyncpg uses statement_cache_size=0; psycopg v3 uses prepare_threshold=None; pgx uses DefaultQueryExecMode = QueryExecModeSimpleProtocol. All achieve Option 2 (no server-side prepares). If you run PgBouncer 1.21+, you can leave these at their defaults and rely on max_prepared_statements instead.