--- title: "Transaction Pooling Breaks Only Under Load" description: "Transaction pooling is what makes PgBouncer worth deploying, and it silently removes session state your application may depend on." url: https://sqlpress.com/blog/pgbouncer-pooling-modes/ category: PostgreSQL tags: ["connection-pooling", "reliability", "architecture"] author: "Elias Rowe" published: 2026-09-13T00:00:00.000Z measured: false --- # Transaction Pooling Breaks Only Under Load ## Key takeaways - Session pooling holds a server connection for the client's whole session and multiplexes almost nothing; transaction pooling releases it at each commit. - Transaction pooling breaks anything that lives in a session rather than a transaction — SET, LISTEN, session advisory locks, WITH HOLD cursors, temporary tables. - Failures appear only under load, when the pool starts reusing server connections, so a test environment with low concurrency will not reproduce them. ## Test environment - System: PostgreSQL 18 - Workload: Illustrative (examples verified, nothing timed) - Notes: PostgreSQL session semantics as of 18; PgBouncer behavior checked against 1.21, which added protocol-level prepared statements in transaction mode. With one client and no concurrency, a server connection is effectively dedicated even in transaction mode. Every session-scoped assumption holds in development and starts failing intermittently in production, which reads as flakiness rather than as a configuration problem. What separates the two is `pool_mode`, and each mode trades a different amount of PostgreSQL semantics for the reuse it buys. ## Session pooling A client connection is assigned a server connection when it connects and keeps it until it disconnects. Nothing about PostgreSQL's behavior changes, because there is no multiplexing while the client is connected. What you gain is avoiding process creation for short-lived clients, and a hard cap on server-side connections with clients queueing in front of it. What you do not gain is much reuse. An application with fifty mostly idle connections still holds fifty server connections. For the common case — an application pool that keeps connections open — session mode is close to a no-op. ## Transaction pooling A server connection is assigned when a transaction begins and returned to the pool when it commits or rolls back. Between transactions, the client holds no server connection at all. This is the mode that produces the ratios PgBouncer is deployed for: hundreds of client connections served by a few dozen server connections, because most clients are idle at any moment. It also means consecutive statements from one client may run on different server connections. Anything scoped to a session rather than a transaction is now unreliable: - **`SET` outside a transaction.** A `SET statement_timeout` or `SET search_path` applies to whichever server connection happened to serve it, then leaks to the next client that gets that connection. Use `SET LOCAL` inside the transaction. - **`LISTEN`.** Receiving a notification requires a persistent session, so a listener does not work. `NOTIFY` itself is unaffected: it runs inside the transaction and is delivered at commit. - **Session-level advisory locks.** `pg_advisory_lock` is held by the session; the session goes back to the pool. Use the transaction-scoped variants. - **`WITH HOLD` cursors** and **temporary tables**, both of which outlive the transaction that created them. - **Prepared statements**, historically. PgBouncer 1.21 added support for protocol-level prepared statements in transaction mode via `max_prepared_statements`, which removed the most common of these failures — but only when it is configured and only for the protocol-level form. ## Why the failures are intermittent None of those fails on the first commit. They fail when a second client gets the connection, and how often that happens is a function of concurrency: with one client the pool never has to hand a used connection to anyone else, so the state left behind is always found by the client that left it. The two questions worth asking before switching a running application to transaction pooling: does anything call `SET` outside a transaction, and does anything hold a lock or a cursor across statements? The ORM's own connection initialization is the place both usually hide, which puts it in the same class as [the extra queries an ORM issues per request](/blog/n-plus-one-queries/): database behavior the application never states outright, some distance from the code that pays for it. ## Statement pooling, and the transaction it removes Transaction mode returns the connection at commit; statement mode returns it one statement earlier, and that step removes multi-statement transactions altogether. This is for workloads that are entirely autocommit, and the constraint is severe enough that it is rarely the right answer for an application. It exists for cases where the client cannot be trusted to end transactions. ## How the three layers fit together An application-side pool sized to the concurrency the application actually needs, in front of PgBouncer in transaction mode, in front of a PostgreSQL instance whose `max_connections` is set to what the hardware can run. Each layer bounds the one behind it, and the queue forms at the outermost layer, where it is cheapest and most visible. That order is what makes the trade worth taking. Transaction pooling's price is session state; what it buys is reuse, and reuse only materializes when the layer in front of it is bounded. An application pool that opens connections without a limit pays the price and collects none of the benefit — the queue moves to the layer where it is least visible. ## FAQ ### What breaks when PgBouncer runs in transaction pooling mode? Transaction pooling returns the server connection at each commit or rollback, so consecutive statements from one client may run on different server connections. Anything scoped to a session rather than a transaction becomes unreliable: SET outside a transaction, LISTEN, session-level advisory locks, WITH HOLD cursors, and temporary tables. Prepared statements were on that list historically. ### What is the difference between session pooling and transaction pooling in PgBouncer? Session pooling assigns a server connection when the client connects and keeps it until the client disconnects, so no PostgreSQL behavior changes and there is almost no reuse. Transaction pooling assigns a server connection when a transaction begins and returns it at commit or rollback, which is what produces ratios of hundreds of client connections served by a few dozen server connections. ### Why does PgBouncer transaction pooling work in development but fail in production? With one client and no concurrency, a server connection is effectively dedicated even in transaction mode, so every session-scoped assumption holds. Under production concurrency the pool starts reusing server connections and those assumptions break intermittently, which reads as flakiness rather than as a configuration problem. A test environment with low concurrency will not reproduce it. ### Can PgBouncer support prepared statements in transaction mode? PgBouncer 1.21 added support for protocol-level prepared statements in transaction mode through max_prepared_statements, which removed the most common of the session-state failures. That support applies only when the setting is configured, and only to the protocol-level form of prepared statements. ## Sources - [PgBouncer documentation — Features](https://www.pgbouncer.org/features.html) (docs) - [PgBouncer documentation — Configuration](https://www.pgbouncer.org/config.html) (docs) — pool_mode and max_prepared_statements are defined here. - [PostgreSQL 18 documentation — Advisory Locks](https://www.postgresql.org/docs/18/explicit-locking.html) (docs) - [PgBouncer changelog — 1.21.0](https://www.pgbouncer.org/changelog.html) (release notes) — Protocol-level named prepared statements and max_prepared_statements were added here.