Transaction Pooling Breaks Only Under Load

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