PostgreSQL and PgBouncer Transaction Mode: Why Your Prepared Statements Suddenly Break

We moved PgBouncer from session pooling to transaction pooling to squeeze far more application connections onto a small number of database connections. It worked — connection pressure on PostgreSQL dropped immediately. Then the errors started: prepared statement "S_1" does not exist, settings that silently did nothing, advisory locks that never seemed to be held.

Nothing was wrong with PostgreSQL or with PgBouncer. Transaction pooling changes a fundamental assumption your application was quietly relying on: that it keeps the same database connection for the life of a session. Once that is gone, anything tied to a session breaks.

The three pooling modes

PgBouncer has three modes. In session pooling, a client holds a server connection for its whole session — closest to talking to PostgreSQL directly. In statement pooling, a server connection is returned after every statement. In transaction pooling, the popular high-efficiency mode, a server connection is assigned to a client only for the duration of a transaction and then handed to whoever needs it next.

Transaction pooling is what lets thousands of mostly-idle clients share a few dozen database connections. The catch is right there in the definition: between transactions, your next query might run on a completely different backend.

Why session state evaporates

Anything that lives on a specific backend connection across transactions stops being reliable, because you are not guaranteed the same backend twice. That breaks a whole category of features at once:

Server-side prepared statements (PREPARE / the protocol-level prepare many drivers use) live on one backend — the next transaction may land on another that never saw the PREPARE. Session-level SET (not SET LOCAL) is lost. Session-level advisory locks can be taken on one backend and 'released' against another. Temporary tables, LISTEN/NOTIFY subscriptions, and WITH HOLD cursors all assume a stable session and quietly fail.

-- Breaks under transaction pooling: SET persists only within a transaction
SET statement_timeout = '5s';   -- gone after this transaction

-- Works: SET LOCAL is scoped to the current transaction
BEGIN;
SET LOCAL statement_timeout = '5s';
-- ... queries ...
COMMIT;

Fixing prepared statements

Prepared statements are the most common casualty because many drivers use them automatically. The historical fix was to disable server-side prepared statements in the driver (for example, set the prepare threshold so it never prepares, or use simple query mode).

Modern PgBouncer (1.21+) added support for protocol-level prepared statements in transaction mode by tracking them across server connections. If you can run a recent PgBouncer and enable max_prepared_statements, you can keep prepared statements and the pooling efficiency. Check your version before assuming you have to give them up.

; pgbouncer.ini — let PgBouncer track prepared statements (1.21+)
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

Fixing the rest

For everything else, the rule is: keep all session state inside a transaction or push it elsewhere. Use SET LOCAL instead of SET. Use transaction-scoped advisory locks (pg_advisory_xact_lock) instead of session-scoped ones. Avoid relying on temp tables across statements, and run LISTEN/NOTIFY consumers on a dedicated session-mode connection or a direct connection that bypasses the transaction pool.

A clean pattern many teams use: route the bulk of traffic through a transaction-pooled port for efficiency, and expose a second session-pooled (or direct) port for the few workloads that genuinely need session state.

• Transaction pooling reassigns the backend between transactions — no stable session.
• Breaks prepared statements, session SET, session advisory locks, temp tables, LISTEN.
• PgBouncer 1.21+ can track prepared statements via max_prepared_statements.
• Use SET LOCAL and pg_advisory_xact_lock to stay transaction-scoped.
• Give session-dependent workloads a separate session-mode/direct connection.

The practical standard

The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.