pgBouncer: what breaks when you switch to transaction pooling

Switching pgBouncer from session to transaction pooling is a two-word configuration change that can break an application in ways that only appear under load, days later, in a code path nobody associated with the change.

The reason is simple: session mode gives each client its own backend connection for the life of that client, and anything the client sets on that connection stays set. Transaction mode hands the backend back after every COMMIT, so the next statement may land on a completely different one.

Everything below follows from that single fact.

The three modes

Transaction mode is what people are after: it is the setting that lets a few dozen backend connections serve hundreds or thousands of clients. Statement mode is a specialist tool, rarely the right answer for an application.

What breaks in transaction mode

Session-level SET

A SET issued outside a transaction lands on whichever backend happened to be free, and the next query on that client may use a different backend that never saw it. This includes search_path, statement_timeout, time zone and application_name.

-- session mode: reliable. transaction mode: a coin flip.
SET search_path TO tenant_42;
SELECT * FROM orders;     -- may run on a backend with the default search_path

-- transaction mode: scope it to the transaction
BEGIN;
SET LOCAL search_path TO tenant_42;
SELECT * FROM orders;
COMMIT;

Multi-tenant applications that switch schema with SET search_path are the classic casualty. The failure is not an error — it is querying the wrong tenant's data, which is far worse than a crash.

Prepared statements

Historically the most common breakage. A protocol-level prepared statement lives on one backend; the execute may arrive on another, producing "prepared statement does not exist" under concurrency while working perfectly in testing.

pgBouncer 1.21 and later can track prepared statements in transaction mode, which removes most of this pain. Check both the pgBouncer version and max_prepared_statements before assuming it applies — on an older build the answer is still to disable server-side prepared statements in the driver.

# pgbouncer.ini
pool_mode = transaction
max_prepared_statements = 200   # 0 disables tracking, pre-1.21 behaviour

# JDBC alternative on older pgBouncer: prepareThreshold=0
# psycopg alternative: prepare_threshold=None

Temporary tables

A temp table belongs to a session. Create one in a transaction, commit, and the next statement may run somewhere it does not exist. There is no configuration that fixes this; either the whole workflow lives inside one transaction, or it needs a real table, or that particular connection needs session mode.

Advisory locks

pg_advisory_lock is session-scoped and will not be released when the backend is returned to the pool — it leaks, and eventually something waits forever on a lock held by a connection nobody owns. Use pg_advisory_xact_lock, which releases at COMMIT.

LISTEN / NOTIFY

LISTEN registers interest on a specific backend. In transaction mode that backend goes back to the pool and the notification arrives somewhere else, or nowhere. Anything using LISTEN needs a direct connection or a session-mode pool of its own.

Two pools, not one

The usual resolution is not choosing a mode but running both. Point the bulk of the application at a transaction-mode pool, and give the few components that genuinely need session semantics a separate session-mode pool on another port.

[databases]
app        = host=10.0.0.10 port=5432 dbname=app pool_mode=transaction
app_legacy = host=10.0.0.10 port=5432 dbname=app pool_mode=session

[pgbouncer]
default_pool_size = 25
max_client_conn = 2000
reserve_pool_size = 5
server_idle_timeout = 600

Sizing the pool

The instinct is to make default_pool_size large. It is the wrong instinct: the point of pooling is that PostgreSQL handles a modest number of concurrent backends far better than a large one. Beyond a certain concurrency, throughput falls while everything slows down together.

A workable starting point is a small multiple of CPU cores, then adjust from measurement rather than from feel. What to watch:

-- from the pgbouncer admin console
SHOW POOLS;    -- cl_waiting > 0 persistently means the pool is too small
SHOW STATS;    -- avg_query_time rising means the database, not the pool

-- distinguishing the two matters: waiting clients with fast queries
-- means raise the pool; slow queries mean fix the queries first.

Raising pool size to hide slow queries moves the queue from pgBouncer into PostgreSQL, where it does more damage. Check avg_query_time before touching default_pool_size.

With Patroni in front

In an HA setup, pgBouncer should point at whatever routes to the current primary rather than at a fixed host, or a failover leaves it faithfully connected to a demoted node. Route through HAProxy checking the Patroni REST endpoint, and confirm that pgBouncer actually drops and re-establishes backends when the primary moves — that is a test, not an assumption.

A safe migration path

• •Audit the codebase for SET outside transactions, temp tables, advisory locks, LISTEN and driver-level prepared statements.
• •Fix what is cheap: SET becomes SET LOCAL, pg_advisory_lock becomes pg_advisory_xact_lock.
• •Stand up a transaction-mode pool beside the existing one rather than switching in place.
• •Move one service across, under real load, and watch for errors that appear only with concurrency.
• •Keep a session-mode pool for the components that need it. Two pools is a normal outcome, not a failure.

Where this fits

Connection pooling is usually the cheapest performance work available on a PostgreSQL system — and the easiest to get subtly wrong. We audit pooling as part of PostgreSQL performance work at €65/hour, including the code review that finds the session-dependent patterns before they reach production.