Connection Pooling & Plan Cache: PgBouncer and Per-Backend Reuse #

A connection pooler multiplexes hundreds of client connections onto a handful of PostgreSQL backends, and that multiplexing quietly reshapes everything PostgreSQL caches per backend: protocol-level prepared statements, the plan cache entries behind them, and session GUCs. The plan reuse you carefully arranged in the driver can evaporate the moment a pooler hands your next transaction to a different backend.

This page explains how PgBouncer’s three pool modes interact with those per-backend caches, how to detect defeated plan reuse, and how to decide between protocol-level prepared statements and client-side simple queries. It is part of the broader runtime environment picture of why an app’s plan diverges from psql.

Pool Modes and What They Preserve #

PgBouncer offers three pool modes, and the choice governs how long a client keeps a backend — which in turn governs what per-backend state survives.

The trap is transaction mode, because it is the default recommendation for high connection counts and it silently breaks server-side prepared statements unless you take extra steps.

Transaction-mode multiplexing scatters a client's transactions across backends A single client issues three transactions. The pooler routes transaction 1 to backend A where a prepared statement is created and cached, but routes transaction 2 to backend B and transaction 3 to backend C, neither of which holds the prepared statement, so reuse fails. Client tx1, tx2, tx3 Pooler transaction mode backend A PREPARE p cached ✓ tx1 lands here — statement created backend B p not present ✗ tx2 lands here — EXECUTE p fails backend C p not present ✗ tx3 lands here — re-plan again tx1 tx2 tx3

Per-Backend Prepared Statements and the Plan Cache #

A server-side prepared statement — created either by an explicit PREPARE or, far more commonly, by your driver using the extended query protocol (Parse/Bind/Execute) — is a named object owned by exactly one backend process. Behind that name sits a plan cache entry that PostgreSQL uses to skip re-parsing and re-planning. Both the named statement and its cached plan are private to that backend.

Under transaction-mode pooling, that ownership is the whole problem. Observe it directly:

-- Issued on a pooled connection right after the driver "prepared" 40 statements.
SELECT name, statement, generic_plans, custom_plans
FROM pg_prepared_statements;
 name | statement | generic_plans | custom_plans
------+-----------+---------------+--------------
(0 rows)
-- The named statements were prepared on a DIFFERENT backend than the one
-- serving this SELECT. This backend's plan cache never saw them.

Because the plan cache lives per backend, cache state — including whether a statement has crossed the five-execution threshold into a generic plan — varies from transaction to transaction. One transaction lands on a warm backend that has executed the statement six times and pinned a generic plan; the next lands on a cold backend that re-plans a fresh custom plan. The behavior described in prepared statement plan pinning becomes non-deterministic under pooling, because the execution counter that drives it is per backend.

To see the actual executed plan under these conditions, auto_explain is again the tool, since the client never surfaces which plan ran:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- capture everything (test only)
SET auto_explain.log_analyze = on;
-- run the workload through the pooler; read plans from the server log,
-- noting whether Index Cond shows a literal (custom) or $1 (generic).

Resource Behavior: Connection Churn Defeats Plan Reuse #

The performance cost of defeated reuse is concrete: planning time is paid on every execution instead of once. Parsing and planning a moderately complex join can cost as much as executing it for a selective query, so re-planning on each call can double effective latency without a single row changing.

Connection churn — clients rapidly acquiring and releasing pooled backends — makes this worse by ensuring no single backend accumulates enough executions of a statement to benefit from its cache. Measure the impact directly:

-- Compare planning cost to execution cost per statement.
-- A high plan-to-exec ratio on a hot query signals defeated reuse.
SELECT queryid,
       calls,
       round(total_plan_time::numeric, 1)          AS plan_ms,
       round(total_exec_time::numeric, 1)           AS exec_ms,
       round(total_plan_time / nullif(total_exec_time,0), 2) AS plan_exec_ratio
FROM   pg_stat_statements
WHERE  calls > 100
ORDER  BY plan_exec_ratio DESC NULLS LAST
LIMIT  15;
 queryid | calls | plan_ms  | exec_ms | plan_exec_ratio
---------+-------+----------+---------+-----------------
   88213 | 42000 | 512340.2 | 61020.5 |            8.40
-- plan time is 8.4x exec time: this statement is re-planned on almost
-- every call. With reuse it would be planned once and this column ~0.

total_plan_time is only populated when track_planning is on. A ratio well above 1 on a high-calls statement is the clearest signal that pooling is preventing the plan cache from doing its job. The underlying planning work itself is governed by the cost estimation models — pooling does not make each plan more expensive, it just forces you to pay for that estimation repeatedly.

Three pool modes, three levels of preserved state Session pooling keeps everything for the client's lifetime, transaction pooling keeps state only within a transaction, and statement pooling keeps nothing between statements. session pooling prepared statements survive SET values survive fewest connections saved transaction pooling state survives one transaction plan cache reuse is fragile the common production choice statement pooling nothing survives every statement re-plans rarely worth it moving right saves connections and costs plan reuse — that is the whole trade

Numbered Diagnostic Workflow #

Follow these steps when a query behind a pooler plans differently, or more often, than it should.

Step 1 — Check the pool mode.

; pgbouncer.ini
[databases]
appdb = host=127.0.0.1 dbname=appdb pool_mode=transaction
-- Or query it live from the PgBouncer admin console:
SHOW DATABASES;   -- inspect the pool_mode column for appdb

If pool_mode = transaction, server-side prepared statements are at risk unless you have explicitly enabled support for them.

Step 2 — Check server_reset_query.

; A DISCARD ALL here wipes prepared statements, GUCs, and temp tables
; every time a backend returns to the pool.
server_reset_query = DISCARD ALL

SHOW CONFIG; in the admin console shows the effective value. A DISCARD ALL reset guarantees no per-backend state carries across transactions. The session reset and DISCARD ALL effects page details exactly what each reset variant clears.

Step 3 — Measure planning time.

Run the pg_stat_statements query above. If plan_exec_ratio is high on your hot statements, reuse is defeated and Step 4 applies.

Step 4 — Decide: protocol-level prepared statements or client-side simple query.

Choose protocol-level support when planning time dominates; choose client-side simple query when generic-plan misfires dominate.

The re-planning tax With plan reuse, planning happens once and execution repeats; without it, every execution pays the planning cost again, which dominates short statements. plan reused one plan, four executions plan discarded planning repeated every time planning execution

Common Pitfalls #

DISCARD ALL wipes prepared statements every transaction. Diagnostic: pg_prepared_statements is empty and planning time never amortizes. Fix: either remove the reliance on server-side prepared statements, or use PgBouncer’s prepared-statement tracking so the statements are replayed after the reset.

Transaction-mode pooling plus a JDBC server-prepared driver. Diagnostic: intermittent prepared statement "S_3" does not exist errors under load. Fix: set the driver to disable server-side statements (prepareThreshold=0 for pgJDBC) or enable PgBouncer 1.21+ prepared-statement support so the statement follows the client across backends.

Cold plan cache after every checkout. Diagnostic: the first execution on each newly checked-out backend is slow, then fast, in a repeating sawtooth. Fix: this is the plan cache warming from cold on each backend; increase backend affinity with a larger min_pool_size, or accept it and ensure the cold plan is still acceptable.

SET not persisting into the next transaction. Diagnostic: a SET work_mem issued in one call has no effect on the next query. Fix: transaction-mode reset (or a different backend) discarded it; pin the value with ALTER ROLE ... SET or ALTER DATABASE ... SET instead of a runtime SET.

A pooler changes two things that matter to the planner, and it is worth separating them. The first is identity: with transaction pooling, consecutive statements from one client may run on different backends, so anything stored per backend — prepared statements, cached plans, session GUCs, temporary tables — cannot be relied upon between them. The second is concurrency: the pool size, not the client count, is the number of statements that can be executing at once, which is the multiplier for every per-backend memory allowance.

Both have practical consequences that are easy to get wrong in opposite directions. Sizing the pool from the client count leads to a pool far larger than the machine can support, where every additional backend adds memory pressure and context switching without adding throughput. Sizing it purely from CPU count can leave connections queuing behind long transactions. The workable approach is to size for the concurrency the hardware can genuinely sustain, then keep transactions short enough that queuing stays brief.

The plan-cache interaction deserves its own attention because it is silent. A client library that prepares statements will either fail outright behind a transaction pooler or fall back to re-planning every execution, and the second failure mode looks like nothing at all — just slightly more CPU and slightly higher latency on every statement. Confirming which one you have takes a single look at planning time under load.

Sizing the pool from the client count. Ten thousand application connections do not need ten thousand backends; they need as many as the server can genuinely execute concurrently, which is a function of CPU, storage and memory rather than of client convenience. Diagnostic signal: a pool sized to match the application’s connection limit, with most backends idle. Fix: size for sustainable concurrency and let the pooler queue, which is what it is for.

Ignoring what the pooler does on check-in. The reset query the pooler runs between clients determines what survives, and its default is usually the safest and most destructive option. Diagnostic signal: planning time forming a large share of total time on short statements. Fix: read the pooler’s reset configuration, and narrow it deliberately once you know what state the application actually leaves behind.

Frequently Asked Questions #

Why is pg_prepared_statements empty on a pooled connection? #

Server-side prepared statements live inside one backend process. A transaction-mode pooler routes each transaction to whichever backend is free, so a statement your client prepared earlier exists on a different backend than the one now serving you. From the current backend’s view no statements were prepared, so pg_prepared_statements returns zero rows even though the application believes it has prepared many. This is expected behavior, not a bug, and it is the root cause of most pooler plan-reuse surprises.

Which PgBouncer pool mode should I use with prepared statements? #

Session mode preserves prepared statements and session GUCs because each client keeps a dedicated backend for the life of its connection. Transaction mode gives far better connection density but breaks server-side prepared statements unless you run PgBouncer 1.21 or later with max_prepared_statements set, which tracks and replays prepared statements per backend. Statement mode disallows multi-statement transactions and is the most restrictive of the three. See PgBouncer transaction mode and prepared statements for the detailed trade-offs.

Does connection pooling increase planning time? #

It can. When pooling defeats prepared-statement reuse, every execution is parsed and planned from scratch instead of reusing a cached plan, so planning time is paid on every call. You can measure the effect directly in pg_stat_statements by comparing total_plan_time to total_exec_time; a high plan-to-exec ratio on a frequently called query is the signature of defeated plan reuse. The fix is to restore reuse or to accept custom plans deliberately.

Why do my SET commands not persist across queries behind a pooler? #

In transaction mode the pooler runs server_reset_query, usually DISCARD ALL, when it returns a backend to the pool, which clears session GUCs, prepared statements, and temporary tables. Even without a reset query, your next transaction may land on a different backend that never saw your SET. Pin settings with ALTER ROLE or ALTER DATABASE instead of relying on a runtime SET surviving, so the value is applied deterministically on every backend.

Up: Runtime Environment