How does PgBouncer transaction mode work?

In transaction mode, PgBouncer assigns a Postgres server connection to a client only when that client starts a transaction. It returns the connection to the pool as soon as the transaction commits or rolls back. A statement run in autocommit counts as its own transaction. Because the client stays connected to PgBouncer but not to any particular backend, two consecutive transactions on the same app connection can run on different Postgres backends. That is the source of every pitfall later in this guide: anything a backend remembers between transactions is no longer guaranteed to be there.

Mode Server connection held for Multi-statement transactions Session state preserved
Session Lifetime of the client connection Yes Yes
Transaction One transaction Yes No, only within a transaction
Statement One statement No, rejected No

Monoliths adopt transaction mode because their connection count grows with every app instance multiplied by its pool size, plus background workers, cron jobs and consoles. Each Postgres connection is a full backend process with its own memory. If Postgres has to absorb that product directly, you end up raising max_connections with every scale-out. Transaction mode puts a ceiling on that growth. Thousands of mostly idle client connections share a backend pool whose size you choose.

App instances (A, B, C … N), each with an app-side pool of ~10–20 connections ↓ hundreds or thousands of client connections PgBouncer (transaction mode, one pool per database/user pair) ↓ a few dozen server connections Postgres backends

Transaction mode works best for teams with many short OLTP transactions, where each backend is busy for only a few milliseconds before another client can use it.

+--------------+  +--------------+  +--------------+       +--------------+
| App inst. A  |  | App inst. B  |  | App inst. C  |  ...  | App inst. N  |
| [pool 10-20] |  | [pool 10-20] |  | [pool 10-20] |       | [pool 10-20] |
+------+-------+  +------+-------+  +------+-------+       +------+-------+
       |                 |                 |                      |
       +-----------------+--------+--------+----------------------+
                                  |
           hundreds to thousands of client connections
           (held open to PgBouncer, mostly idle)
                                  |
                                  v
        +-------------------------------------------------------+
        | PgBouncer (pool_mode = transaction)                   |
        | one server pool per database/user pair                |
        | tx starts -> borrow backend; commit/rollback -> return|
        +-------------------------------------------------------+
                                  |
                   a few dozen server connections
                                  |
                                  v
        +-------------------------------------------------------+
        | Postgres                                              |
        | +---------+ +---------+ +---------+     +---------+   |
        | | backend | | backend | | backend | ... | backend |   |
        | |    1    | |    2    | |    3    |     |    M    |   |
        | +---------+ +---------+ +---------+     +---------+   |
        +-------------------------------------------------------+

In transaction mode, many app-side client connections share a small pool of Postgres backends, and each backend is borrowed only for the duration of one transaction, so consecutive transactions from the same client can run on different backends.

Which session state breaks in transaction mode, and what replaces it?

Any state that lives on the backend after COMMIT belongs to whichever client happens to get that server connection next. PgBouncer does not reset it between transactions. Some of it can be scoped to a single transaction instead, and the rest needs a session-mode or direct connection.

Session-scoped feature What goes wrong Transaction-safe replacement
SET search_path, timezone, statement_timeout Setting persists on the backend and affects other clients SET LOCAL or set_config(name, value, true) inside the transaction
pg_advisory_lock Lock outlives the request; unlock may run on a different backend pg_advisory_xact_lock, released at commit or rollback
LISTEN Notifications are delivered to a backend that no longer belongs to the listener Dedicated direct or session-mode connection (NOTIFY itself is transactional and safe)
Temporary tables Survive commit, collide by name, or are missing on the next transaction ON COMMIT DROP, or a regular table keyed by request
WITH HOLD cursors Cursor stays on a backend the client may never see again Non-holdable cursor consumed within one transaction
Session variables for row-level security Tenant or user context leaks to another request set_config('app.tenant_id', ..., true) per transaction

Leakage rarely raises an error. A stale search_path resolves a query against the wrong schema. A leaked tenant ID returns another customer's rows under a policy that is working exactly as written. These are correctness and security failures, so a clean error log does not prove correctness.

Some library features have no transaction-scoped form. Examples include ORM connection hooks that run SET once per connection, job queues built on session advisory locks, and LISTEN-based cache invalidation. Replace these features or move them onto a separate pool.

-- Unsafe under transaction pooling: these outlive COMMIT on the backend.
-- SET search_path = tenant_42, public;
-- SET app.tenant_id = '42';
-- SELECT pg_advisory_lock(1001);

-- Transaction-safe version of the same request.
BEGIN;

-- Scoped to this transaction; reverts at COMMIT or ROLLBACK.
SET LOCAL search_path = tenant_42, public;
SET LOCAL statement_timeout = '5s';

-- RLS context: third argument true means transaction-local.
SELECT set_config('app.tenant_id', '42', true);

-- Advisory lock released automatically at transaction end;
-- no unlock call that might land on a different backend.
SELECT pg_advisory_xact_lock(1001);

-- Temp table cannot leak to the next client of this backend.
CREATE TEMP TABLE import_batch (id bigint, payload jsonb) ON COMMIT DROP;

-- Policy reads the transaction-local setting (missing_ok = true).
-- e.g. USING (tenant_id = current_setting('app.tenant_id', true)::bigint)
SELECT id, name FROM invoices WHERE status = 'open';

COMMIT;

-- Verify nothing persisted after COMMIT (run before the next BEGIN):
SHOW search_path;                                -- back to the server default
SELECT current_setting('app.tenant_id', true);   -- empty or NULL, not '42'

A single request rewritten with SET LOCAL, transaction-local set_config, pg_advisory_xact_lock and ON COMMIT DROP so that no state survives COMMIT on a pooled backend.

Why do prepared statements fail with PgBouncer transaction mode?

There are two kinds of prepared statement, and only one is usually visible in your code. SQL-level PREPARE and EXECUTE appear in application queries and are easy to grep for. Protocol-level named statements are created by drivers and ORMs through the extended query protocol's Parse message. The driver then reuses them by name with Bind and Execute. Both kinds live on a specific server connection.

In transaction mode, PgBouncer can hand your next transaction to a different backend. A statement parsed on backend A doesn't exist on backend B, so you get "prepared statement does not exist". A client that lands on a backend where another client already used the same statement name gets "already exists". Under light load, clients often get the same backend back, which hides the problem. Under load, backends rotate and the errors become intermittent.

Fix one: stop using named server-side statements

Turn the feature off in the driver. Each setting below does that for its stack.

Stack Setting
Rails (ActiveRecord) prepared_statements: false
pgjdbc prepareThreshold=0
asyncpg statement_cache_size=0
psycopg 3 prepare_threshold=None

The cost is that every execution is parsed and planned again. You lose plan caching, and hot paths spend more CPU on parsing. Measure that cost on your own workload before deciding it is acceptable.

Fix two: let PgBouncer track them

Newer PgBouncer releases can track protocol-level prepared statements in transaction mode. The feature is controlled by max_prepared_statements. It does not cover SQL-level PREPARE. Confirm the version and configuration you actually run, including any managed pooler in front of your database, rather than assuming support. With this fix, query correctness depends on a specific PgBouncer version and setting. An upgrade, a rollback, or a config drift can bring the errors back.

; pgbouncer.ini: transaction pooling with protocol-level prepared statement tracking
[databases]
; Placeholder host and database name
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

; Server connections are returned to the pool after each transaction,
; so consecutive transactions from one client can run on different backends.
pool_mode = transaction
default_pool_size = 20

; A non-zero value makes PgBouncer track named statements that drivers create
; with Parse messages, and prepare them on whichever backend the client gets.
; 0 disables tracking. Releases without this setting do not support tracking.
; SQL-level PREPARE/EXECUTE is NOT covered and still breaks in this mode.
max_prepared_statements = 100

; Before relying on this, check the pooler you actually run:
;   psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
;   pgbouncer=# SHOW VERSION;
;   pgbouncer=# SHOW CONFIG;   -- confirm max_prepared_statements is non-zero
; Managed poolers may not expose this setting, so check your provider's
; documentation instead of assuming it is enabled.

A PgBouncer config that enables transaction pooling with protocol-level prepared statement tracking through max_prepared_statements, plus admin-console commands to confirm the deployed version and setting.

How do you audit a monolith before switching to transaction mode?

That failure is silent, which is why the audit should happen in the codebase before anything changes in production. Search the application and its dependencies, including vendored code, and classify each hit.

  • Session-level SET. Search for SET\s+(SESSION\s+)?[a-z_]+\s*(=|TO) and set_config\(. A match outside an explicit transaction means the setting will apply to whichever client gets that server connection next. Rewrite it as SET LOCAL inside the transaction, or move it to role-level or database-level defaults.
  • Advisory locks. Search for pg_advisory_lock and pg_try_advisory_lock, and check job queue and migration libraries, which often use them internally. Session-scoped locks can be released on a different connection than the one that acquired them, or never released at all. pg_advisory_xact_lock is safe. Migration tools usually need a direct connection instead.
  • LISTEN. Search for LISTEN\s in cache invalidation and job notification code. A listener only receives notifications while it holds the same server connection, so these features stop working without raising errors. NOTIFY on its own is safe to send through the pooler.
  • Connect callbacks. Look for ORM or driver hooks that run when a connection opens. Hooks that issue SET configure one server connection, and later transactions may run on others.
  • Long transactions around network I/O. Code review won't find all of these reliably. Watch pg_stat_activity in staging for sessions in the idle in transaction state.

Most findings can be fixed with configuration or a one-line change to SET LOCAL. LISTEN-based features and libraries built on session locks usually require code changes, a dedicated direct connection, or a replacement library. Budget for those separately.

#!/usr/bin/env bash
# Pre-migration audit for PgBouncer transaction mode.
# Scan app code AND dependencies; job queues and migration tools hide session state.
set -u
DIRS="app lib vendor ${DEPS_DIR:-.venv/lib}"   # adjust to your layout

scan() {  # $1 = label, remaining args = grep flags and pattern
  local label="$1"; shift
  echo "== ${label}"
  grep -rnE --include='*.py' --include='*.rb' --include='*.js' --include='*.ts' \
       --include='*.sql' "$@" $DIRS 2>/dev/null || echo "   (no matches)"
}

# Session-level SET: leaks to the next client on that server connection.
# SET LOCAL does not match this pattern, so remaining hits need review.
scan "session SET" -i 'SET[[:space:]]+(SESSION[[:space:]]+)?[a-z_]+[[:space:]]*(=|TO)[[:space:]]'
scan "set_config()" 'set_config\('

# Session advisory locks (pg_advisory_xact_lock is safe and is not matched).
scan "advisory locks" 'pg_(try_)?advisory_(lock|unlock)'

# LISTEN only works while the client keeps one server connection.
scan "LISTEN" 'LISTEN[[:space:]]'

# Connect hooks that configure a single server connection.
# Examples: SQLAlchemy event.listens_for(engine, "connect"), node-postgres pool.on('connect').
scan "connect callbacks" "listens_for\(.*['\"]connect['\"]|\.on\(['\"]connect['\"]"

# Long transactions around network I/O: observe staging under load instead.
psql "${STAGING_DATABASE_URL:-postgres://audit@staging-db.internal:5432/app}" -c "
  SELECT pid, application_name,
         now() - xact_start AS xact_age,
         left(query, 80)    AS last_query
  FROM pg_stat_activity
  WHERE state = 'idle in transaction'
  ORDER BY xact_age DESC;"

A shell audit script that greps application and dependency code for session-level SET, set_config(), session advisory locks, LISTEN, and connect callbacks, then queries pg_stat_activity in staging for idle-in-transaction sessions.

When should you avoid transaction mode and route traffic elsewhere?

Transaction mode is the wrong default when only a small part of the monolith needs connection multiplexing. If you run a few app instances whose combined pool sizes fit comfortably under max_connections, the app-side pool alone may be enough, and adding PgBouncer brings cost without much benefit.

When you do need PgBouncer, route by workload rather than forcing everything through one pool:

Workload Path Why
Web requests, short background jobs Transaction-mode pool Short transactions; no session state between them
Schema migrations Direct connection DDL, session SETs and migration-tool locks need one stable backend
LISTEN consumers Direct or session-mode pool Notifications go to the session that issued LISTEN
Long-running reports Session-mode pool or replica Long transactions hold a server connection for their whole duration
Session advisory lock users Direct or session-mode pool The lock belongs to a backend the app no longer controls

In the app, express this as separate named database configs or URLs, such as a default DATABASE_URL pointing at the transaction pool and a DIRECT_DATABASE_URL for migrations and listeners. Choosing the connection should be an explicit decision in code, not something a library infers.

This has costs. You have more configuration to maintain, and developers must know which connection each feature uses. A misrouted migration or LISTEN consumer often does not fail cleanly: locks seem to vanish, or notifications arrive intermittently.

PgBouncer is also an extra network hop. It needs high availability, health checks, and capacity planning of its own. A single PgBouncer process runs on one CPU core, so under heavy load it can become CPU-bound while Postgres still has headroom.

How should you size app pools, default_pool_size, and max_connections?

Treat the numbers below as an illustrative estimate with assumed inputs, not a recommendation: 20 app instances, each running 10 workers, each worker holding an app-side pool of 5 connections. That gives 1,000 potential client connections into PgBouncer. A default_pool_size of 40 for the single database/user pair means at most 40 Postgres backends do the actual work.

Layer Setting Illustrative value Sized from
App per-worker pool 5 worker concurrency
PgBouncer client side max_client_conn ≥ 1,000 plus margin total app pools
PgBouncer server side default_pool_size 40 per db/user pair database CPU and I/O
Postgres max_connections 40 + other pairs + direct + reserved sum of all server pools plus headroom

Postgres max_connections must cover every PgBouncer pool plus migrations, cron jobs, admin sessions, and superuser_reserved_connections. This headroom lets you log in during an incident.

The app-side pool still matters. It caps how many connections one worker can open and keeps a misbehaving instance from exhausting max_client_conn.

Size the server pool from what the database can execute concurrently, not from client count:

  • Oversizing moves contention into Postgres as lock waits and context switching.
  • Undersizing queues clients inside PgBouncer, which shows up as unexplained app latency.

Long transactions are the real limit

Long-running and idle-in-transaction sessions are what actually exhaust capacity. A transaction that calls an external API holds a server connection for the whole round trip, so 40 slow calls can stall the entire pool. Set idle_in_transaction_session_timeout in Postgres as a backstop. The real fix is in the application: do network calls before BEGIN or after COMMIT, and keep transactions to database work only.

How do you roll out and monitor PgBouncer transaction mode safely?

Rollout sequence

Change one variable at a time so each failure has a single cause.

  1. Put PgBouncer in front in session mode. Behavior matches direct connections, so this step only tests connectivity, auth, TLS, and timeouts.
  2. Fix driver prepared-statement settings while still in session mode. Errors here come from configuration, not pooling semantics.
  3. Convert session state (SET, temp tables, session advisory locks) to transaction-scoped equivalents, and deploy those changes.
  4. Route session-dependent workloads such as migrations, LISTEN, and long batch jobs to a separate session-mode pool or a direct connection.
  5. Switch a canary slice to transaction mode, for example one app instance or one service endpoint. Watch for "prepared statement does not exist" errors, lock timeouts, and advisory-lock anomalies before you widen the rollout.

What to watch

In the PgBouncer admin console, SHOW POOLS reports clients waiting (cl_waiting), the longest current wait (maxwait), and active versus idle server connections. SHOW STATS gives average transaction and wait times. In Postgres, group pg_stat_activity by state.

Signal Likely cause
cl_waiting rising, database CPU low, server connections at pool size Transactions are held open too long. Adding database capacity won't help.
Same as above, many idle in transaction rows The application is doing slow work inside BEGIN/COMMIT.
cl_waiting rising, database CPU saturated The database is the bottleneck. A bigger pool will make it worse.
Many idle server connections, no waits default_pool_size is larger than needed.

Low CPU combined with client waits means a pool slot is occupied but no query is running on it. In that case, look for the open transaction that is holding the slot before you add hardware.

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 mode, 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.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

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.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • 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.