PgBouncer Transaction Pooling: Prepared Statements and Session State

How PgBouncer pool_mode works: session, transaction and statement

PgBouncer sits between your application and Postgres and hands out a small number of real server connections to a much larger number of client connections. The pool_mode setting in the [pgbouncer] section of pgbouncer.ini decides when a server connection is returned to the pool so another client can use it. For example, pool_mode = transaction sets the mode globally, and individual database entries can override it.

Session mode limits connections but doesn't share them. A client holds a backend for its whole lifetime, even while idle. Transaction mode is where the multiplexing happens. Most application connections spend most of their time idle between requests, so a backend only needs to be attached while a transaction is actually open.

Here is an illustrative estimate with assumed inputs. Suppose 1,000 client connections each run a transaction lasting 5 ms, about ten times per second. Each client then keeps a backend busy roughly 5% of the time. That works out to about 50 concurrently active backends rather than 1,000.

Fewer backends means less per-process memory on the Postgres server. It also means less contention and context switching. This is why transaction mode suits API servers, workers and serverless functions that open many connections but keep each transaction short.

Copy;  1,000 client connections          PgBouncer             Postgres
;  [api-1] [api-2] ... [api-N] --->  +-----------+  --->  [backend 1]
;  [worker-1] ... [worker-M]   --->  |   pool    |  --->  [backend 2]
;  [lambda-1] ... [lambda-K]   --->  | (txn mode)|  --->  ...
;                                    +-----------+  --->  [backend 50]
;  A backend is attached only while a transaction is open,
;  then released at COMMIT/ROLLBACK for the next client.

[databases]
; Inherits the global pool_mode (transaction).
appdb = host=10.0.0.5 port=5432 dbname=appdb

; Per-database override for a tool that relies on session state.
appdb_admin = host=10.0.0.5 port=5432 dbname=appdb pool_mode=session

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Return the server connection to the pool at COMMIT or ROLLBACK.
pool_mode = transaction

; Many clients may connect to PgBouncer...
max_client_conn = 1000

; ...but each user/database pair gets at most this many backends.
default_pool_size = 50

A minimal pgbouncer.ini that sets transaction pooling globally, overrides one database entry to session mode, and multiplexes up to 1,000 clients onto at most 50 Postgres backends per user/database pair, as the diagram in the comments shows.

The core invariant: every transaction may run on a different backend

The trade-off is a single rule. PgBouncer assigns a server connection when a transaction begins and returns it to the pool when the transaction ends. Any state that outlives that transaction stays on the backend and does not follow the client. Two consecutive transactions from one client can run on two different backends. The next client to receive that backend inherits whatever was left on it. In transaction mode PgBouncer does not run its reset query between transactions by default, so nothing cleans up the leftover state.

Loud errors are the easy cases because they fail in testing. Silent leaks are worse. A leaked search_path can send another tenant's queries to the wrong schema. A leaked SET ROLE runs someone else's transaction with your privileges. Neither raises an error, and both appear only under concurrent load. That makes them correctness and security bugs, not performance quirks.

Why pgbouncer prepared statements fail and how to fix them

Prepared statements fail differently: loudly, but only some of the time. Most drivers use the extended query protocol, where a Parse message can create a named statement that lives on the backend that received it. Some drivers switch to named statements after a query has run a set number of times. pgJDBC does this with its prepareThreshold. Others, such as asyncpg, keep a per-connection statement cache from the start. Either way, the driver assumes the name persists for the life of its connection. Under transaction pooling, that assumption breaks.

The next transaction may land on a backend that never saw the Parse, which produces prepared statement "..." does not exist. Or it may land on one where another client already registered the same generated name, which produces already exists. At low traffic, clients often get their previous backend back, so tests pass. As concurrency rises, reassignment becomes common and the errors appear.

Two fixes

Disable named statements in the driver. Examples are prepareThreshold=0 in pgJDBC and statement_cache_size=0 in asyncpg. Queries then use unnamed statements, which are parsed and executed within one transaction. The cost is that every execution is parsed and planned again. Measure the CPU impact on hot queries instead of assuming it is negligible.

Let PgBouncer track them. In recent releases, setting a nonzero max_prepared_statements in the [pgbouncer] section makes the pooler intercept protocol-level Parse messages. It then re-prepares statements on whichever backend serves the transaction. This preserves plan reuse, but PgBouncer pays for it in memory and bookkeeping. It also does not intercept SQL-level PREPARE/EXECUTE. Check your version's documentation for availability and defaults before relying on it.

Copy; pgbouncer.ini: transaction pooling with protocol-level prepared statement tracking

[databases]
; Placeholder target. Clients connect to "appdb" on PgBouncer, not on Postgres.
appdb = host=10.0.0.12 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Each transaction may run on a different backend.
; Named statements created by an earlier Parse are not guaranteed to exist there.
pool_mode = transaction
default_pool_size = 20

; Fix 2: a nonzero value tells PgBouncer to track protocol-level named
; statements and re-prepare them on whichever backend serves the transaction.
; Higher values let PgBouncer remember more statements per server connection,
; which costs more pooler memory. Confirm that your version supports this
; setting and check its default before relying on it.
; This does NOT cover SQL-level PREPARE / EXECUTE.
max_prepared_statements = 100

; Fix 1 (alternative): leave tracking off and disable named statements in the
; driver so it uses unnamed statements instead:
;   pgJDBC:  jdbc:postgresql://pgbouncer.internal:6432/appdb?prepareThreshold=0
;   asyncpg: await asyncpg.connect(dsn, statement_cache_size=0)
; Every execution is then parsed and planned again. Measure the CPU cost
; on your hot queries.

A PgBouncer transaction-pooling config that enables max_prepared_statements tracking, with the driver-side alternative of disabling named statements shown in comments.

How to audit and migrate an application to transaction mode

Step 1: Inventory session state

Grep application code, migrations and ORM configuration for anything that outlives a single transaction: plain SET (including SET search_path and SET statement_timeout in connection-init hooks), pg_advisory_lock, LISTEN, CREATE TEMP TABLE without ON COMMIT DROP, DECLARE ... WITH HOLD cursors, and SQL-level PREPARE. Connection-init hooks are the easiest to miss because they run once per physical connection. Under transaction pooling they run on whichever backend the client happened to get first.

Step 2: Check driver statement caches

Many drivers prepare statements automatically after a threshold, for example pgjdbc's prepareThreshold and asyncpg's statement_cache_size. Decide explicitly whether to rely on PgBouncer's protocol-level prepared statement support or to disable the client-side cache.

Step 3: Test through the pooler

Point the integration suite at PgBouncer in transaction mode, not at Postgres directly, and run it with several concurrent workers against a deliberately small pool. A single client often gets the same backend back every time, which hides leaks. Concurrency forces transactions onto different backends, so a search_path set by one test can surface as a wrong-schema error in another test.

Step 4: Rewrite to transaction-scoped patterns

• Replace SET with SET LOCAL or set_config(name, value, true), or attach the setting to a function with a SET clause.
• Replace session advisory locks with pg_advisory_xact_lock, which releases at commit or rollback.
• Move LISTEN to a dedicated direct connection.

Why do this upfront

State leaks in production show up as rare, load-dependent errors that disappear when you reconnect to debug them. A grep and a concurrent test run find the same problems before deployment, in an environment where you can reproduce them reliably.

Copy#!/usr/bin/env bash
# Audit a codebase for session state that breaks under PgBouncer transaction pooling.
# Usage: ./audit-session-state.sh path/to/repo
# Hits are candidates for review, not proof of a bug.
set -euo pipefail
ROOT="${1:-.}"
INCLUDES=(--include='*.sql' --include='*.py' --include='*.java' --include='*.ts' \
          --include='*.js' --include='*.go' --include='*.rb' --include='*.yml' \
          --include='*.yaml' --include='*.properties' --include='*.toml')

scan() {  # $1 = label, $2 = extended regex
  echo "== $1"
  grep -rniE "${INCLUDES[@]}" -- "$2" "$ROOT" || echo "   (none)"
}

# Plain SET persists for the life of the backend. SET LOCAL is filtered out.
# UPDATE ... SET col = ... is not matched because only known GUC names are listed.
echo "== session-level SET"
grep -rniE "${INCLUDES[@]}" -- \
  'SET[[:space:]]+(SESSION[[:space:]]+)?(search_path|statement_timeout|lock_timeout|role|time[[:space:]]*zone|application_name|work_mem)' \
  "$ROOT" | grep -viE 'SET[[:space:]]+LOCAL' || echo "   (none)"

# Session advisory locks; pg_advisory_xact_lock is safe and not matched.
scan "session advisory locks" 'pg_(try_)?advisory_lock(_shared)?[[:space:]]*\('
scan "LISTEN"                 '\bLISTEN[[:space:]]+[a-z_"]'
scan "temp tables"            'CREATE[[:space:]]+(TEMP|TEMPORARY)[[:space:]]+TABLE'
scan "WITH HOLD cursors"      'WITH[[:space:]]+HOLD'
scan "SQL-level PREPARE"      '^[[:space:]]*PREPARE[[:space:]]+[a-z_]'

# Driver-side statement caches (pgjdbc, asyncpg, psycopg 3).
scan "driver statement caches" 'prepareThreshold|statement_cache_size|prepare_threshold'

# Review temp-table hits by hand: ON COMMIT DROP may sit on another line.

A grep-based audit script that lists session-level SET, session advisory locks, LISTEN, temp tables, WITH HOLD cursors, SQL PREPARE and driver statement-cache settings so each hit can be reviewed before switching PgBouncer to transaction mode.

When to avoid transaction mode and what to use instead

Some workloads break the core invariant by design, and no amount of auditing will make them fit:

• LISTEN/NOTIFY consumers. Notifications go to the backend session that issued LISTEN. Once PgBouncer returns that backend to the pool, the listener stops receiving them, or another client receives them instead.
• Long-lived cursors. A WITH HOLD cursor outlives its transaction but stays tied to the session that created it.
• Migrations. Migration tools often take session-level advisory locks, set lock_timeout with SET, or run DDL that needs a predictable session.
• Admin tasks. Interactive psql sessions, dump and restore jobs, and manual maintenance assume one continuous backend.
• Heavy SQL PREPARE or session state. Pooler-side prepared statement handling targets protocol-level statements, not SQL-level PREPARE. Code that relies on temp tables or SET across transactions also has nowhere to keep that state.

Split pools have their own costs. You maintain two sets of configuration, and you divide a fixed max_connections budget between them, so either pool can run out while the other has capacity to spare. The worst failure is a misrouted connection string. A service that uses session state but points at the transaction pool fails intermittently, which is the same failure you split the pools to avoid. Give the pools clearly different names or ports, and check in CI which pool each service uses.

Sizing pools and the long-transaction problem

A server connection is assigned when a transaction begins and returned only at COMMIT or ROLLBACK. Everything in between counts against the pool: query execution, network round trips, and any time the client spends doing other work while the transaction is still open. A client that sits idle in a transaction holds the backend exactly as long as one running a heavy query.

Pools are sized per database/user pair. default_pool_size applies to each pair, and max_db_connections and max_user_connections cap the totals. Add up every pair, plus reserve_pool_size, across every PgBouncer instance in front of the database. That sum must stay below Postgres max_connections, with headroom left for superuser, replication and maintenance connections.

Illustrative estimate. These inputs are assumed, not measured. By Little's law, busy connections equal arrival rate multiplied by hold time. At 2,000 transactions/s averaging 5 ms, about 10 server connections are in use. Now add one service at 50 transactions/s that calls an external API inside its transaction, holding each one for 500 ms. That service alone needs 25 connections. One low-traffic service has more than tripled the requirement. If its latency doubles, it consumes the whole pool and fast clients queue behind it.

For this reason, timeouts are mandatory on both sides:

• Postgres: set idle_in_transaction_session_timeout and statement_timeout.
• PgBouncer: set idle_transaction_timeout, query_timeout and query_wait_timeout, so clients fail quickly instead of waiting indefinitely for a slot.

Copy; pgbouncer.ini (one of two identical instances in front of the same Postgres)
[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000

; Per database/user pair. Budget across BOTH instances:
;   (default_pool_size + reserve_pool_size) * 2 = (20 + 5) * 2 = 50
; Postgres max_connections = 100 leaves headroom for superuser,
; replication and maintenance sessions.
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
; Hard cap per instance, even if more user pairs appear later
max_db_connections = 25
max_user_connections = 25

; Pooler-side timeouts, in seconds
; Close a client connection left idle inside an open transaction
idle_transaction_timeout = 10
; Backstop slightly above Postgres statement_timeout
query_timeout = 35
; Fail fast instead of queueing forever when the pool is exhausted
query_wait_timeout = 5

; --- postgresql.conf (server side, shown here for reference) ---
; max_connections = 100
; idle_in_transaction_session_timeout = '15s'
; statement_timeout = '30s'

A transaction-mode PgBouncer config whose pool sizes sum below Postgres max_connections across two instances, with idle-in-transaction, query and wait timeouts set on both the pooler and Postgres sides.

Observability and impact on cost and engineering time

Reading the admin console

Connect to the pgbouncer admin database and run SHOW POOLS first. A non-zero cl_waiting with a rising maxwait means clients are queued for a server connection. If sv_active equals the pool size at the same moment, the pool is saturated. SHOW CLIENTS shows which clients are waiting. SHOW SERVERS shows which clients hold each server connection.

To find the cause, match the local_addr and local_port of a busy server in SHOW SERVERS against client_addr and client_port in pg_stat_activity. Sessions with state = 'idle in transaction' and an old xact_start hold a backend while doing no work. These sessions are the usual reason the queue grows even though the database itself looks idle.

What it costs, and what it returns

Transaction mode has engineering costs:

• An audit of session state in every code path.
• Split pools to maintain for the clients that still need session mode.
• Extra CPU when statements are re-planned instead of reused.
• Slower incident diagnosis when engineers expect a client to map to one backend.

The returns are fewer backends, lower memory use on Postgres, and fewer connection-exhaustion pages.

Illustrative estimate (assumed numbers, not a benchmark):

memory saved ≈ (direct connections − pooled backends) × per-backend memory

Assume 400 direct connections, a pool of 40, and 10 MB per backend. That gives (400 − 40) × 10 MB ≈ 3.6 GB.

Measure per-backend memory on your own workload before you rely on this figure.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to pgbouncer transaction pooling, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.