Prepared Statement Caching Under Transaction Pooling

This guide is part of Transaction vs Statement Pooling Trade-offs. Prepared-statement caching and transaction-level connection multiplexing are individually beneficial and jointly hazardous. A client-side cache assumes a stable, dedicated backend connection on which a PREPARE survives for reuse. Transaction pooling breaks that assumption: it returns the backend connection to the pool at every commit and hands the next transaction whatever backend is free. The prepared statement the driver thinks it cached lives on a backend it no longer holds. The result is the classic PgBouncer failure: ERROR: prepared statement "S_3" does not exist, often paired with prepared statement "S_3" already exists when a name collides on a recycled backend.

This page explains how JDBC cachePrepStmts, asyncpg, and libpq statement caches interact with transaction multiplexing, exactly when they break, how PgBouncer’s max_prepared_statements changes the picture, when to disable caching, and what the performance trade-off costs.

Key operational takeaways:

• A driver-side prepared-statement cache is bound to one backend connection; transaction pooling does not keep you on that backend across transactions, so cached statements vanish.
• The breakage signatures are prepared statement "..." does not exist and prepared statement "..." already exists.
• PgBouncer’s max_prepared_statements (1.21+) makes named prepared statements work under transaction pooling by tracking and re-preparing them per backend.
• If you cannot enable that, disable client-side prepared statements (prepareThreshold=0, asyncpg statement_cache_size=0) or move the pool to session mode.

Rapid incident diagnosis

The error fires only under load and only behind transaction pooling — it never reproduces against a direct connection, which is what makes it confusing. The signature is unambiguous once you see it:

ERROR: prepared statement "S_3" does not exist

or, on a recycled backend where a name is reused before cleanup:

ERROR: prepared statement "S_3" already exists

Confirm the pool mode first. Query the proxy’s admin console — if pool_mode is transaction (or statement), client-side prepared statements are not safe without explicit support:

-- on the PgBouncer admin interface
SHOW CONFIG;
SHOW POOLS;

Look at the pool_mode column in SHOW DATABASES / SHOW CONFIG. If it reads transaction, the driver is preparing statements on a backend it will not see again. Cross-check that the driver is actually using server-side named prepared statements: in PostgreSQL JDBC, that happens once a statement has been executed prepareThreshold times (default 5), at which point pgjdbc switches from unnamed to named (S_n) statements. The error therefore typically appears after warm-up, not on the first request — another reason it slips through light testing. For the mode-selection background, see How to Choose Between Transaction and Statement Pooling in PostgreSQL.

How the caches interact with multiplexing

Three layers each hold a notion of “this statement is prepared,” and transaction pooling invalidates all three the same way.

JDBC (cachePrepStmts, prepareThreshold). pgjdbc keeps a per-connection cache of PreparedStatement objects and, after prepareThreshold executions, issues a server-side PREPARE with a generated name like S_3. The cache key is the SQL text; the value references a server statement on that physical backend. Under transaction pooling, the java.sql.Connection is a logical handle to PgBouncer, which may route the next transaction to a different backend. The driver still thinks S_3 exists and sends EXECUTE S_3, but the new backend never ran the PREPARE — hence does not exist.

asyncpg (statement_cache_size). asyncpg prepares every query by default and caches the prepared statement per connection, with a default statement_cache_size of 100. The same backend-binding problem applies, and asyncpg additionally generates statement names that can collide across backends, producing already exists.

libpq (PQprepare). Applications calling PQprepare directly create a named server statement on the current connection. Behind transaction pooling the name is invisible to any other backend, so subsequent PQexecPrepared on a re-routed backend fails identically.

The unifying rule: a prepared statement is server-side state scoped to one backend connection. Transaction pooling deliberately breaks the one-to-one mapping between client connection and backend connection. Any cache that assumes that mapping is stable will desynchronize. Session pooling keeps the mapping for the life of the client connection, which is why caching is safe there and not under transaction mode — the same distinction explored in PgBouncer Transaction vs Statement Pooling.

The trade-off: re-prepare cost versus multiplexing ratio

Disabling caching to make transaction pooling safe is not free. A prepared statement amortizes parse and plan cost across executions; without caching, every execution pays parse and plan again. Quantify it before deciding.

Let:

• t_parse = parse + plan time for the statement (typical 0.2–2 ms for non-trivial queries)
• t_exec = execution time
• n = executions of the same statement per backend lifetime

With caching, total cost is t_parse + n * t_exec. Without, it is n * (t_parse + t_exec). The wasted parse time is (n - 1) * t_parse.

Worked example. A query has t_parse = 1.0 ms and t_exec = 3.0 ms, executed n = 50 times per backend before recycling. Cached: 1.0 + 50 * 3.0 = 151 ms. Uncached: 50 * 4.0 = 200 ms — a 32% increase in total time spent, all of it CPU on the database parsing the same SQL repeatedly. For short, frequently-repeated statements where t_parse rivals t_exec, the penalty is severe; for rare or heavy analytical queries where t_exec dwarfs t_parse, disabling the cache is nearly free.

The conclusion is rarely “just disable it.” For hot statements the right move is max_prepared_statements, which keeps the amortization while remaining compatible with transaction pooling.

Exact remediation and configuration

There are three viable paths, in order of preference: enable protocol-level prepared-statement support in the proxy, keep caching by using session mode, or disable caching.

Path 1 — PgBouncer max_prepared_statements (preferred). PgBouncer 1.21+ understands extended-protocol prepared statements and tracks each prepared statement per backend, re-issuing the PREPARE transparently when a client’s statement lands on a backend that does not yet have it. Set a non-zero limit:

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

max_prepared_statements is the number of statements PgBouncer will track and replay per connection; size it to your application’s distinct hot statement count plus headroom. With this enabled, leave the JDBC and asyncpg caches on — the proxy reconciles them with whatever backend serves each transaction. This is the route covered in Using Prepared Statements with PgBouncer Transaction Mode.

Note that named (server-side) prepared statements are supported; pgjdbc must use the extended protocol, which it does by default. Avoid PREPARE/EXECUTE SQL text issued manually — PgBouncer only intercepts protocol-level prepares, not statements you write as SQL.

Path 2 — keep caching, use session pooling. If a hot-path service depends on prepared-statement performance and you cannot run a protocol-aware proxy, route that service through a session-mode pool where the client holds one backend for its whole connection lifetime. Caching is fully safe there. The cost is the multiplexing ratio: session mode needs roughly one backend per active client, so it scales far worse on connection count.

Path 3 — disable client-side prepared statements. When neither of the above fits, turn off named server-side prepares so every execution is a fresh parse the proxy can route freely.

For PostgreSQL JDBC, set prepareThreshold=0 to keep statements unnamed (they are still sent via the extended protocol but never promoted to a server-side S_n name):

jdbc:postgresql://pgbouncer:6432/app?prepareThreshold=0

For asyncpg, disable the statement cache and, on older PgBouncer, also disable implicit prepares:

pool = await asyncpg.create_pool(
    dsn="postgresql://pgbouncer:6432/app",
    statement_cache_size=0,            # do not cache prepared statements
    max_cacheable_statement_size=0,    # do not auto-prepare large statements
)

Apply changes by rolling restart with the new DSN parameters; connection-string changes take effect on new connections, so a rolling deploy swaps behavior without a flag day. Validate immediately afterward — the error stops the moment no client promotes a statement to a server-side name.

Validation and verification

Confirm the driver is no longer leaving orphaned server-side statements. After applying prepareThreshold=0 or statement_cache_size=0, drive the previously-failing path under load and watch for the absence of the error. On the server, check that no unexpected named prepared statements accumulate:

SELECT name, statement, prepare_time
FROM pg_prepared_statements;

Against a direct connection this lists the current backend’s prepared statements; behind a transaction pool you generally want this empty for the application role when caching is disabled, or populated-and-stable when max_prepared_statements is doing its job.

When using max_prepared_statements, verify the proxy is actually tracking them on its admin interface and that you are not exceeding the configured limit — exceeding it causes PgBouncer to evict and re-prepare, eroding the benefit you enabled it for. Run a load test that exercises more distinct statements than the limit and confirm throughput does not collapse; if it does, raise max_prepared_statements to cover your distinct hot-statement set.

Frequently Asked Questions

Related

• Transaction vs Statement Pooling Trade-offs — the parent topic weighing multiplexing modes against feature compatibility.
• Using Prepared Statements with PgBouncer Transaction Mode — the proxy-side max_prepared_statements setup in detail.
• PgBouncer Transaction vs Statement Pooling — how each mode binds client connections to backends.
• How to Choose Between Transaction and Statement Pooling in PostgreSQL — selecting the mode that fits your feature requirements.