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.

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.

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.

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