PgBouncer Transaction Mode Pitfalls: Session State, Locks, Prepared Statements

How pool_mode settings decide when a server connection is released

PgBouncer is a lightweight proxy that sits between applications and PostgreSQL. Clients connect to it, and it multiplexes them onto a smaller set of server connections. This form of postgres connection pooling matters because each PostgreSQL connection is a backend process with its own memory. Thousands of mostly idle clients cost real resources even when they do nothing.

The pool_mode setting decides one thing: when a server connection stops belonging to a client and returns to the pool.

The release point causes every pitfall covered later in this article. Anything PostgreSQL attaches to a backend process can outlive the client's transaction or vanish before the client's next one. That includes SET parameters, advisory locks, temporary tables, LISTEN registrations, and prepared statements. The server keeps no record of which client created it.

Session mode is the safe option, but it gives little multiplexing. A server connection is held for as long as the client stays connected, even while that client is idle.

Transaction mode fits high-concurrency workloads with short transactions, such as web request handlers, serverless functions, and horizontally scaled workers. These clients spend most of their connected time outside any transaction. In transaction mode, a few dozen server connections can serve far more clients. The cost is that applications have to give up session-scoped assumptions.

Statement mode releases the connection after every statement, so it rejects explicit multi-statement transactions. Most application code depends on those transactions for correctness, which leaves statement mode useful mainly for narrow autocommit-only workloads.

Copy Many clients          PgBouncer                   PostgreSQL
+-----------+      +--------------------+      +-----------------------+
| client A  |----->|                    |----->| backend 1             |
+-----------+      |  accepts N client  |      | (process, own memory, |
+-----------+      |  connections       |      |  SET, locks, temp     |
| client B  |----->|                    |      |  tables, prepared     |
+-----------+      |  hands out M       |      |  stmts live here)     |
+-----------+      |  server conns      |      +-----------------------+
| client C  |----->|  per pool_mode     |----->| backend 2             |
+-----------+      +--------------------+      +-----------------------+
   ... N              multiplex N -> M          M backends, M << N

 Release point: when a server connection goes back to the pool
 [ ] = span during which one client holds one server connection

 session      [BEGIN..COMMIT  idle  BEGIN..COMMIT]  -> on disconnect
 transaction  [BEGIN..COMMIT] idle [BEGIN..COMMIT]  -> on COMMIT/ROLLBACK
 statement    [SELECT] [UPDATE] [SELECT]            -> after each statement

 After release, any state left on the backend is seen by whichever
 client is handed that server connection next.

PgBouncer multiplexes many client connections onto fewer PostgreSQL backends, and pool_mode sets when each server connection returns to the pool: on disconnect, at transaction end, or after each statement.

Session-scoped features that break in transaction mode, and their safe replacements

In transaction mode, everything that lives on the backend outside a transaction belongs to whichever client next borrows that server connection. PostgreSQL has no way to tell your client apart from the next one. Nothing errors. The state simply follows the backend instead of following you.

For defaults that every connection should share, use ALTER ROLE ... SET or ALTER DATABASE ... SET. PostgreSQL applies these when a backend starts, so every pooled connection for that role or database begins in the same state. Server connections that are already open keep their old values until they reconnect.

Session advisory locks and LISTEN/NOTIFY have no transaction-mode equivalent. Both depend on a backend held across transactions, and transaction mode exists to take that away.

Copy-- Per-transaction settings: SET LOCAL and set_config(..., true)
-- revert at COMMIT/ROLLBACK, so nothing leaks to the next client on this backend.
BEGIN;
SET LOCAL search_path = app, public;
SET LOCAL statement_timeout = '5s';
SELECT set_config('app.tenant_id', '42', true);  -- is_local = true
SELECT current_setting('app.tenant_id');         -- read in the same transaction

-- Transaction-scoped advisory lock: released automatically at commit or rollback,
-- so no unlock call is needed and it cannot be orphaned on a pooled backend.
SELECT pg_advisory_xact_lock(1001);

-- Temp table that lives and dies inside this transaction.
CREATE TEMP TABLE staging_rows (id int, payload text) ON COMMIT DROP;
INSERT INTO staging_rows VALUES (1, 'example');

-- Plain cursor (no WITH HOLD), fully consumed before commit.
DECLARE batch CURSOR FOR SELECT id FROM staging_rows;
FETCH ALL FROM batch;
CLOSE batch;
COMMIT;

-- Shared defaults: applied when a new backend starts for this role/database.
-- Server connections already open keep old values until they reconnect.
ALTER ROLE app_user SET search_path = app, public;
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER DATABASE app_db SET timezone = 'UTC';

-- No transaction-mode equivalent: route these through a direct or session-pooled connection.
-- LISTEN order_events;
-- SELECT pg_advisory_lock(1001);

Transaction-safe replacements for session state under PgBouncer transaction mode: SET LOCAL, set_config with is_local = true, pg_advisory_xact_lock, ON COMMIT DROP temp tables, plain cursors, and ALTER ROLE/DATABASE ... SET for shared defaults.

How session state leaks between clients, and why server_reset_query_always is a weak fix

Consider two clients sharing a pool of five server connections. Client A runs a plain SET statement_timeout = '2s' before a batch job. In autocommit mode that statement is its own transaction, so when it completes PgBouncer returns the server connection to the pool, and the setting is still applied on that backend. A few milliseconds later, client B is assigned the same connection for a long-running report query, which fails with a statement timeout that B never set. Swap in search_path, role, or TimeZone and the result is queries reading the wrong schema, running with the wrong privileges, or formatting timestamps in the wrong zone.

Don't expect server_reset_query to prevent this. PgBouncer runs it only when pooling in session mode, where it fires once as a client disconnects. In transaction mode it is skipped unless server_reset_query_always is enabled.

Enabling that setting has two costs. First, the reset query (typically DISCARD ALL) runs every time a connection is released, adding a round trip to every transaction and lowering the throughput you adopted pooling to gain. Second, it hides the bug. Client A still believes its SET persists, so its later transactions run with defaults and behave differently from what the code implies. The correct fix is to scope settings with SET LOCAL inside an explicit transaction, or to configure them per role or per database.

These failures are intermittent. With low traffic, a client often gets the same backend back and nothing appears wrong. The leak only shows up under concurrency, when connections are reassigned quickly, which is why it tends to surface in production instead of in local testing.

Copy-- Leaky: in autocommit mode this SET is its own transaction.
-- PgBouncer (transaction mode) releases the backend right after it,
-- and the setting stays on that backend for the next client.
SET statement_timeout = '2s';

-- Client A's next statement may run on a different backend (default timeout),
-- while some other client inherits the 2s timeout on this one.
SELECT run_batch_job();

-- Safe: scope the setting to one explicit transaction.
-- SET LOCAL reverts automatically at COMMIT or ROLLBACK,
-- so nothing survives when PgBouncer hands the backend to another client.
BEGIN;
SET LOCAL statement_timeout = '2s';
SET LOCAL search_path = reporting, public;
SELECT run_batch_job();
COMMIT;

-- Also safe: make the setting a default for a role or database.
-- Every session for that role or database starts with it,
-- so it does not depend on which client last touched the backend.
-- New values apply to sessions started after the change,
-- so existing pooled server connections keep the old defaults until they reconnect.
ALTER ROLE batch_worker SET statement_timeout = '2s';
ALTER DATABASE reports_db SET timezone = 'UTC';

-- Check which value the current backend actually has.
SHOW statement_timeout;

A plain SET stays on the pooled backend after its transaction ends. SET LOCAL inside an explicit transaction, or a per-role or per-database default, keeps settings from leaking to other clients in transaction mode.

PgBouncer prepared statements: failure modes and alternatives

Prepared statements are session state too, but they usually fail loudly instead of leaking silently. Two mechanisms are involved. SQL-level PREPARE/EXECUTE creates a named statement through ordinary SQL text. Protocol-level prepared statements are created by drivers through the extended query protocol: a Parse message registers a named statement on the backend, and later Bind/Execute messages refer to it by name. Neither kind is visible to clients on other backends.

In transaction mode, a client might send Parse on backend A and then, in its next transaction, send Bind on backend B. Backend B has never seen that name, so it returns prepared statement "..." does not exist. The opposite error happens when a second client lands on backend A and its driver generates the same name, such as a counter-based identifier. Backend A already holds that name, so it returns prepared statement "..." already exists.

Newer PgBouncer releases can track protocol-level prepared statements through a configuration setting (max_prepared_statements). With tracking enabled, the pooler rewrites statement names and re-prepares them on whichever backend the client receives. SQL-level PREPARE is generally not covered by this. Check your version's documentation to confirm what it supports.

Copy; pgbouncer.ini: transaction pooling with protocol-level prepared statement tracking.
; max_prepared_statements exists only in newer PgBouncer releases; confirm in your
; version's documentation before relying on it.

[databases]
; Placeholder target; route the app database to the primary.
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

; Backends are reassigned at each transaction boundary, so any named
; statement a client prepared may not exist on its next backend.
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

; Non-zero enables tracking: PgBouncer rewrites client statement names and
; re-sends Parse on whichever backend the client gets. The value caps how
; many distinct statements are cached per server connection, so memory scales
; with distinct statements x server connections. 0 disables tracking.
; SQL-level PREPARE/EXECUTE is generally not covered by this setting.
max_prepared_statements = 200

; Alternatives if tracking is unavailable (set in the driver, not here):
;   pgjdbc:     prepareThreshold=0 in the JDBC URL (no named server-side prepares)
;   psycopg 3:  connection.prepare_threshold = None (never prepare server-side)
; Both trade statement reuse for parse and plan on every execution.

A PgBouncer transaction-mode config that enables protocol-level prepared statement tracking with a bounded per-connection cache, with comments noting driver-side settings that disable server-side prepares instead.

When to avoid transaction mode: split pools and pinned connections

Some workloads need a real session. Migration tools often take advisory locks or run DDL across several statements. LISTEN consumers must keep one backend for as long as they want notifications. Admin consoles set session variables and expect them to persist. The usual fix is a split pool: define two entries in PgBouncer's [databases] section that point to the same PostgreSQL database, one with pool_mode = session and one with pool_mode = transaction. For stronger isolation, run a separate PgBouncer instance. Request-path services connect to the transaction-mode entry. Migrations, listeners, and operator tools use the session-mode entry.

This costs more than one config line. There are now two pools to size against the same max_connections budget, and session-mode clients hold backends for as long as they stay connected. The bigger risk is misrouting. If a service copies the wrong connection string, it either loses session guarantees without any warning, or it takes up session slots meant for migrations. Make the database names clearly different, and check in deployment that each service points at the pool it expects.

Transaction mode also has a pinning problem of its own. A server connection returns to the pool only at COMMIT or ROLLBACK. A client that runs BEGIN and then waits on an HTTP call or a slow loop keeps that backend while the other clients queue. PostgreSQL's idle_in_transaction_session_timeout sets a limit by terminating such sessions, so the client gets an error instead of the pool slowly running out of connections. The more lasting fix is in application code: open transactions late, commit early, and never wait on network I/O while a transaction is open.

Sizing pools and observing health with SHOW POOLS and SHOW STATS

default_pool_size caps server connections per user/database pair, not globally. max_client_conn caps client sockets across all pools. PostgreSQL's max_connections must cover the sum of every pool's size plus reserve_pool_size, with room left for admin and replication sessions.

Application-side pools add a second layer, and that changes where requests wait. Checking a connection out of the app pool succeeds immediately because it only connects to PgBouncer. The wait happens later, when PgBouncer queues the transaction. As a result, the app pool's acquire timeout stops bounding latency. PgBouncer's query_wait_timeout and PostgreSQL's statement_timeout become the controls that matter. Keep the app pool's maximum size times the number of instances below max_client_conn.

Illustrative estimate. Assume 30 pods with 20 app connections each, giving 600 client connections against max_client_conn = 1000. Two database users with default_pool_size = 40 and reserve_pool_size = 5 can open 90 server connections, so max_connections = 120 leaves headroom. If the average transaction holds a connection for 5 ms, each pool tops out near 8,000 transactions per second (40 ÷ 0.005).

In SHOW POOLS, check three columns together. If cl_waiting stays above zero, maxwait keeps climbing, and sv_active equals the pool size, the pool is exhausted. If cl_waiting is near zero but SHOW STATS reports high average query or transaction times, the problem is slow queries. Exhaustion is often caused by slower transactions, so check transaction times before you raise the pool size.

Undersizing causes queueing and timeouts. Oversizing moves contention into PostgreSQL, where each backend uses memory and competes for locks and CPU.

Pre-migration audit checklist and its impact on cost and engineering time

Work through these steps before changing pool_mode:

1. Find SET without LOCAL. Check connection hooks and ORM configuration as well as application code, since search_path, statement_timeout and role changes often live there. Convert each to SET LOCAL inside a transaction, or move it to role or database defaults.
2. Find session advisory locks. Replace pg_advisory_lock with pg_advisory_xact_lock, or send that code path to a session-mode pool.
3. Find LISTEN. Listeners need a dedicated session connection.
4. Find temp tables and WITH HOLD cursors. Both outlive the transaction that created them.
5. Check each driver's prepared-statement behavior. Find out whether it creates named statements automatically, after how many executions, and how to turn that off.
6. Test under concurrency. Run many clients against a deliberately tiny pool, such as two server connections. A lone client usually gets the same backend back, so leaked state looks correct. Contention forces reassignment and exposes the bug.

The audit's cost is bounded. Debugging after the switch is not, because these failures are intermittent, depend on load, and rarely reproduce locally.

Illustrative estimate (assumed numbers, not a benchmark): time saved = (incidents × engineers per incident × hours per incident) − audit hours. Assume 3 incidents, 2 engineers, and 8 hours each, against a 16-hour audit: 48 − 16 = 32 engineer-hours saved. This excludes customer impact.

Split pools add ongoing cost. You have two configurations to size and monitor, two connection strings, and a shared max_connections budget. You also need a review rule so that new session-dependent code goes to the session pool.

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.
• 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.