PgBouncer and Protocol-Level Prepared Statements #

An application switches from session pooling to transaction pooling to support ten times the connections, and immediately starts failing with prepared statement "S_1" does not exist. The obvious fix — disabling prepared statements in the driver — works, and quietly removes plan caching from every query in the system.

The wider topic is connection pooling and the plan cache. This page is about PgBouncer specifically, which is where most applications meet the problem.

The Condition #

A prepared statement is server-side session state. PREPARE (or the protocol-level Parse message) stores a parsed, analysed statement in the backend’s memory under a name; EXECUTE (Bind/Execute) references it. The state lives in the backend and disappears when the session ends.

PgBouncer’s pooling modes interact with that state differently:

PgBouncer 1.21 added support for protocol-level prepared statements in transaction mode. It tracks which statements each client has prepared and re-prepares them transparently on whichever server connection a transaction lands on, keeping up to max_prepared_statements per connection (default 0, meaning the feature is off).

Two limits remain. It supports the extended query protocol — what drivers use for Parse/Bind/Execute — but not SQL-level PREPARE/EXECUTE statements, which are still just session state PgBouncer cannot track. And other session state is still not pooled: SET outside a transaction, LISTEN, advisory locks, temporary tables and cursors held across transactions all behave unpredictably in transaction mode.

What each pooling mode does to plan caching Under session pooling one client holds one server connection, so prepared statements and all session state survive and plan caching works normally, at the cost of a server connection per client. Under transaction pooling connections are shared per transaction, so PgBouncer must re-prepare statements itself, which it does from version 1.21 for protocol-level statements only. session pooling one server connection per client prepared statements survive all session state survives no connection multiplexing gain transaction pooling connection assigned per transaction 1.21+ re-prepares protocol statements SQL PREPARE still unsupported large multiplexing gain plan caching and multiplexing were a trade-off until 1.21

Annotated Evidence #

The failure, before configuring it:

ERROR:  prepared statement "S_3" does not exist
-- the driver prepared S_3 on one backend and executed it on another

The PgBouncer configuration that fixes it:

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200      ; 0 disables the feature entirely

What the server sees afterwards:

-- on the server, through the pooler
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
      name       | generic_plans | custom_plans
-----------------+---------------+--------------
 s_4             |           412 |            5
-- the statement exists on this backend and its plan is being reused

And the cost of the workaround people reach for instead:

SELECT calls, round(total_plan_time) AS plan_ms, round(total_exec_time) AS exec_ms
FROM pg_stat_statements WHERE queryid = 8123456789012345678;
   calls   | plan_ms  | exec_ms
-----------+----------+---------
  41204880 | 1840220  |  824104
-- planning cost exceeds execution cost: statements are re-planned every call
Three signals behind a pooler Three items are annotated. The error prepared statement does not exist indicates transaction pooling without prepared statement support. Planning time exceeding execution time in statement statistics indicates prepared statements were disabled as a workaround. Generic plan counts rising in pg_prepared_statements confirms caching is working again. ERROR: prepared statement "S_3" does not exist pooled without 1.21 support total_plan_time > total_exec_time preparation disabled as a workaround generic_plans rising in pg_prepared_statements caching working again the middle signal is the expensive one, because nothing errors

Step-by-Step Resolution #

  1. Establish the pooling mode in pgbouncer.ini. Transaction mode is the one with the constraint; session mode has none but multiplexes nothing.

  2. Upgrade PgBouncer to 1.21 or later if it is older, and set max_prepared_statements to a value covering the application’s distinct statements — a few hundred is typical. It is per server connection, and each cached statement costs memory in the backend.

  3. Use the extended query protocol. Most drivers do by default; some disable it when they detect a pooler. In libpq terms, PQprepare/PQexecPrepared or parameterised PQexecParams rather than PQexec with interpolated SQL.

  4. Remove application-level workarounds once the pooler supports it — prepareThreshold = 0 in JDBC, prepare_threshold = None in psycopg, statement_cache_size = 0 in asyncpg. These disable caching entirely and cost planning time on every execution.

  5. Verify with pg_prepared_statements on the server and with total_plan_time in pg_stat_statements: planning should become a small fraction of execution.

  6. Audit other session state. SET statements outside transactions, LISTEN, session advisory locks and temporary tables are still unreliable in transaction mode; move settings into the transaction or into role defaults.

  7. Watch backend memory. Each server connection now caches up to max_prepared_statements plans, and plans for queries over many partitions are not small.

How re-preparation works in transaction mode Four stages. The client sends a Parse message naming a statement. PgBouncer records the statement text against that client. When a transaction is assigned a server connection, PgBouncer ensures the statement is prepared there, preparing it if needed. Execute then runs against the correct backend. client Parse statement named PgBouncer records text kept per client assign connection prepare if absent Execute runs on that backend only protocol-level statements are tracked this way

Before and After #

-- BEFORE: transaction pooling, preparation disabled in the driver
pg_stat_statements: plan_ms 1,840,220  exec_ms 824,104   (planning is 69% of the time)

-- AFTER: PgBouncer 1.21, max_prepared_statements = 200, driver caching restored
pg_stat_statements: plan_ms 41,204     exec_ms 831,208   (planning is 5% of the time)

Sizing max_prepared_statements #

The setting bounds how many statements PgBouncer keeps prepared on each server connection, evicting the least recently used beyond that. Too low and statements are re-prepared constantly, which reintroduces the cost it was meant to remove; too high and every backend holds plans for every statement the application has ever issued.

A reasonable starting point is the number of distinct statements the application issues in normal operation — countable from pg_stat_statements — with headroom. If that number is in the thousands rather than the hundreds, the more likely explanation is statement-text fragmentation from interpolated values, which is worth fixing for its own sake before sizing anything around it.

Memory is the practical limit. A cached plan for a simple statement is kilobytes; one for a query over a thousand partitions can be tens of megabytes, and it is held per backend. Multiplying the worst case by the pool size and the statement count is a quick sanity check that the configuration fits the machine.

Two layers of pooling #

Most deployments have a pool in the application as well as PgBouncer, and the two interact. The driver’s pool hands a logical connection to application code and keeps its own statement cache keyed to that connection; PgBouncer then multiplexes those logical connections onto fewer server backends. With 1.21’s support in place the arrangement works, but the caches are counted independently: the driver may believe it has a hundred statements prepared while PgBouncer keeps only max_prepared_statements alive per backend.

The consequence is that the effective cache size is the smaller of the two, and raising the driver’s cache without raising PgBouncer’s achieves nothing. Setting both from the same figure — the application’s distinct statement count — keeps them consistent.

It is also worth checking whether both layers are needed. A driver pool sized to the server’s connection capacity, talking directly to PostgreSQL, removes an entire class of problem at the cost of the multiplexing PgBouncer provides; that trade is covered in application-side pools and server-side state.

Common Pitfalls #

Disabling prepared statements to make the pooler work. Every execution pays planning cost. Diagnostic signal: total_plan_time comparable to total_exec_time. Fix: upgrade PgBouncer and enable the feature.

Using SQL-level PREPARE behind transaction pooling. PgBouncer does not track it. Diagnostic signal: errors despite max_prepared_statements being set. Fix: use the extended protocol through the driver.

Leaving max_prepared_statements = 0. The default disables the feature. Diagnostic signal: 1.21 installed, errors unchanged. Fix: set it explicitly.

Assuming other session state is pooled. It is not. Diagnostic signal: settings, LISTEN or temp tables behaving inconsistently. Fix: keep state inside transactions or in role defaults.

Frequently Asked Questions #

Do prepared statements work with PgBouncer in transaction mode? #

Yes, from PgBouncer 1.21, for protocol-level prepared statements, with max_prepared_statements set to a non-zero value. PgBouncer re-prepares statements on whichever server connection a transaction is assigned. SQL-level PREPARE is still not supported.

Why does disabling prepared statements hurt performance? #

Every execution is parsed, analysed and planned again. For statements that are cheap to execute and expensive to plan — anything over many partitions or many joins — planning can exceed execution time across the workload.

What session state is unsafe in transaction pooling? #

Anything that outlives a transaction: SET outside a transaction, LISTEN, session-level advisory locks, temporary tables, WITH HOLD cursors, and SQL-level prepared statements.

Up: Connection Pooling and the Plan Cache