PgBouncer Transaction Mode and Prepared Statements #

The error prepared statement "S_1" does not exist is one of the most confusing failures a PostgreSQL application can hit, because it appears intermittently, only under concurrency, and only after PgBouncer is inserted between the application and the database. Nothing in the SQL changed. What changed is which backend runs each statement — and a prepared statement lives on exactly one backend. This page walks the diagnostic path from the error message to a chosen fix, and quantifies the cost estimation impact of the fix that most teams reach for first: disabling server-side prepare and re-planning every execution.

Why Transaction Pooling Breaks Prepared Statements #

A prepared statement is server state. When a client sends PREPARE, one PostgreSQL backend parses the SQL, plans it, and stores the plan under a name like S_1 in that backend’s session memory. A later EXECUTE S_1 only works if it reaches the same backend.

PgBouncer’s pool_mode = transaction deliberately breaks that assumption. To multiplex thousands of clients over a handful of backends, it assigns a backend to a client only for the span of a single transaction. The moment COMMIT (or ROLLBACK) fires, the backend returns to the pool and can be handed to a different client. The next transaction from the original client may land on a completely different backend — one that never saw the PREPARE.

Split routing of PREPARE and EXECUTE under transaction pooling A client sends PREPARE in transaction one, which PgBouncer routes to backend A where statement S_1 is stored. In transaction two the same client sends EXECUTE, which PgBouncer routes to backend B. Backend B has no S_1, so it raises prepared statement does not exist. Client Txn 1: PREPARE Txn 2: EXECUTE PgBouncer pool_mode = transaction Backend A S_1 stored here (from Txn 1) Backend B no S_1 here → error Txn 1 → A Txn 2 → B Same client, two transactions, two backends — S_1 exists on only one of them.

This is a routing problem, not a SQL problem, which is why it evades unit tests and staging environments with low concurrency. It is the runtime counterpart to the planning behavior described in generic vs custom plan selection: both are about the lifecycle of a cached plan, but here the plan simply vanishes because the connection underneath it moved.

Reading the Evidence #

First confirm the pool mode. Under session pooling this class of error cannot occur, so ruling it in is step one.

$ psql -h pgbouncer_host -p 6432 pgbouncer -c "SHOW CONFIG" | grep pool_mode
pool_mode  transaction  ...        -- ← transaction pooling confirmed

Now reproduce the failure. A driver that uses the extended protocol with named statements — the default for many — will emit something like this on the second call:

ERROR:  prepared statement "S_1" does not exist        -- ← EXECUTE hit a backend with no S_1

To prove the statement is per-backend and not shared across the pool, query pg_prepared_statements. This catalog view is session-local: it shows only what the current backend has prepared. Run it twice through the pooler and you may see different contents, or none at all:

-- Runs on whichever backend PgBouncer hands this transaction
SELECT name, statement, prepare_time, from_sql
FROM pg_prepared_statements;
 name |                 statement                  |   prepare_time    | from_sql
------+--------------------------------------------+-------------------+----------
 S_1  | SELECT * FROM orders WHERE customer_id=$1  | 2026-07-06 09:14  | f
(1 row)                                           -- ← present on Backend A only

Reconnect through the pooler and the same query may return (0 rows) because you landed on a different backend. That divergence is the smoking gun: the plan cache is not shared, and transaction pooling does not keep you on the backend that owns it.

The Planning-Time Cost of the Common Fix #

The most common quick fix is to stop using server-side prepared statements — tell the driver to send unnamed, fully-inlined statements every time. That makes the error impossible, but it moves planning work back into every execution. To quantify it, look at the Planning Time line, which is normally amortized away by a cached plan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.total_amount, u.email
FROM orders o
JOIN users u ON u.user_id = o.customer_id
WHERE o.status = 'SHIPPED' AND o.created_at >= now() - interval '7 days';
-- BEFORE (server-side prepared, plan cached once, reused):
Planning Time: 0.031 ms          -- ← plan taken from cache, near-zero
Execution Time: 4.12 ms

-- AFTER (server-side prepare disabled, re-planned every call):
Planning Time: 1.84 ms           -- ← full parse + plan on every execution
Execution Time: 4.09 ms

The units on the cost= figures in the plan body are unitless planner estimates and do not change; only the wall-clock Planning Time (in milliseconds) moves. For this two-table join the added ~1.8 ms per call is trivial next to a 4 ms execution. But the same measurement on a ten-table reporting join can show planning time in the tens of milliseconds, at which point re-planning every execution becomes the dominant cost. Always measure Planning Time on the actual statement before accepting this trade-off — the planner’s work scales with join count and predicate complexity, not with how long the query runs.

Step-by-Step Resolution Workflow #

Step 1 — Confirm the pool mode #

Read the PgBouncer config and SHOW CONFIG. If pool_mode is session, this error has a different cause and you should stop here. If it is transaction (or statement), continue.

Step 2 — Reproduce and capture the exact error #

Drive the workload until you see prepared statement "S_1" does not exist. Note the driver in use — JDBC, libpq, asyncpg, and node-postgres each name and cache statements differently.

Step 3 — Prove the statement is per-backend #

Query pg_prepared_statements through the pooler more than once. Divergent results (present on one checkout, absent on the next) confirm the plan cache is not shared across the pool.

Step 4 — Measure the re-planning cost #

Run EXPLAIN (ANALYZE, BUFFERS) and record Planning Time. This is the cost you would pay per execution if you disable server-side prepare. Compare it against Execution Time to judge whether the trade-off is acceptable for this statement.

Step 5 — Choose a fix #

Three viable options, from least to most invasive:

Step 6 — Re-run and validate #

Repeat the workload under concurrency. Confirm the S_1 error is gone and that Planning Time (if you disabled prepare) is within budget. Watch pg_stat_statements for a spike in total planning time across the fleet.

Before/After Plan Comparison #

-- BEFORE: server-side prepared statement across transaction pooling
--   Txn 1: PREPARE S_1  → Backend A   (plan cached on A)
--   Txn 2: EXECUTE S_1  → Backend B   → ERROR: prepared statement "S_1" does not exist
Planning Time: 0.031 ms   (when it worked — cached)   Execution Time: 4.12 ms

-- AFTER (fix = max_prepared_statements=200 on PgBouncer 1.21+):
--   Txn 2: EXECUTE S_1  → Backend B   → PgBouncer re-prepares S_1 on B transparently
Planning Time: 0.048 ms   (re-prepared once per backend)   Execution Time: 4.10 ms

The protocol-level fix keeps planning near-zero because each backend prepares the statement at most once and PgBouncer handles the bookkeeping — unlike disabling prepare, which pays full planning on every call.

Common Pitfalls #

Silent fallback to re-planning. Some drivers, on seeing the S_1 error, silently retry with an unnamed statement instead of surfacing the failure. The application “works,” but every affected query now re-plans on every execution, inflating Planning Time fleet-wide. Watch pg_stat_statements.total_plan_time — a sudden climb with unchanged execution time is the fingerprint of silent re-planning.

DISCARD ALL wiping statements between transactions. If PgBouncer’s server_reset_query is DISCARD ALL, it drops all prepared statements when a backend is returned to the pool. Even with max_prepared_statements set, a DISCARD ALL reset defeats protocol-level tracking. The two settings must be reconciled — protocol-level prepared statements require a reset query that does not discard them.

Mixing prepared and simple statements on one connection. An application that prepares some statements and sends others via the simple protocol can leave a backend in a state where the cached plans and the client’s expectations diverge after a re-route. Standardize on one path per service rather than mixing them.

Assuming staging proves it. Low-concurrency environments rarely multiplex enough clients over few enough backends to trigger the split routing. The error is a production-load phenomenon; validate the fix under realistic concurrency, not a single-client smoke test.

Frequently Asked Questions #

Why does prepared statement “S_1” does not exist appear only under load? Under transaction pooling, PgBouncer only pins a server backend to a client for the duration of one transaction. A PREPARE issued in transaction A and an EXECUTE issued in transaction B can be routed to two different backends. The second backend never saw the PREPARE, so it raises prepared statement "S_1" does not exist. The error surfaces under load because that is when the pool multiplexes many clients across fewer backends, making the split routing likely.

Does disabling server-side prepared statements slow down my queries? It adds planning work. Without a cached plan, PostgreSQL parses and plans the statement on every execution, so the Planning Time line in EXPLAIN ANALYZE reappears on each call instead of being amortized. For simple point lookups this is negligible; for large multi-join analytical queries the repeated planning can dominate. Measure Planning Time before deciding, and consider PgBouncer 1.21+ protocol-level prepared statement support to keep both pooling and plan caching.

Can I keep transaction pooling and still use prepared statements? Yes, with PgBouncer 1.21 or newer. Setting max_prepared_statements to a non-zero value makes PgBouncer track prepared statements at the protocol level and transparently re-prepare them on whichever backend a client lands on. This preserves plan caching across the pool without moving the workload to session pooling.

Related