Modos de pool por transação versus por sessão

Each PostgreSQL backend process costs memory (work_mem, catalog caches, plan caches) and CPU. A few thousand idle client connections can exhaust a server even when nothing is running.

PgBouncer sits between your app and PostgreSQL, multiplexing many client connections onto a small set of real server connections. The pool_mode setting decides when a server connection is handed back to the pool.

• session — server held for the client's whole session
• transaction — server held only for one transaction
• statement — server released after every statement

In session pooling, a server connection is assigned to a client when it connects and is only returned to the pool when the client disconnects.

This is the safest mode: the client gets a dedicated backend for its entire lifetime, so every PostgreSQL feature behaves exactly as if it connected directly. The cost is poor reuse — an idle but connected client still pins a server connection.

In transaction pooling, the server connection is assigned only for the duration of a single transaction. The instant the transaction commits or rolls back, the backend goes back to the pool and may serve a different client next.

This gives dramatically better reuse: thousands of mostly-idle clients can share a tiny pool, because a server connection is only borrowed during active work. It is the recommended mode for web apps with many short-lived requests.

The decision is reuse versus feature compatibility:

• Session: full compatibility, low connection reuse.
• Transaction: high reuse, but anything that relies on state outside a transaction can break.

The key insight: in transaction mode, consecutive transactions from the same client may land on different backends. Anything that lives on the connection between transactions is unsafe.

Because a backend is shared across clients between transactions, any session-scoped state set in one transaction can leak to another client or be lost.

These commonly break under transaction pooling:

• SET / SET SESSION session GUCs (e.g. SET statement_timeout, SET search_path outside a transaction)
• Session-level advisory locks (pg_advisory_lock)
• LISTEN / NOTIFY subscriptions
• Unparameterized session variables and WITH HOLD cursors

Named prepared statements are stored on a specific backend. In transaction mode, your next execution may hit a different backend that has never seen that prepared statement, causing errors like prepared statement "sN" does not exist.

Mitigations:

• Disable client-side prepared statements, or use simple/unnamed protocol.
• PgBouncer 1.21+ supports max_prepared_statements to track and re-prepare named statements per backend automatically.

If you need a GUC like statement_timeout or search_path under transaction pooling, scope it to the transaction with SET LOCAL. It applies only until the transaction ends, so it can never leak to the next client on that backend.

Use this pattern instead of a bare SET.

Transaction pooling only reuses backends between transactions. A long-running or idle-in-transaction query holds its backend the entire time, just like session mode would.

If many clients hold open transactions, the small pool drains and new requests queue. Guard against this:

• Set a low idle_in_transaction_session_timeout on the server.
• Keep transactions short; never BEGIN then wait on app-side I/O.

Under transaction pooling, default_pool_size is the number of real backends per (database, user) pair. Because work is interleaved, you need far fewer backends than clients.

A common starting point is roughly the number of CPU cores available for queries, not the number of clients. Oversizing the pool just recreates the connection-storm problem you used PgBouncer to avoid.

PgBouncer exposes a virtual admin database. Connect to it and run SHOW POOLS; to see, per pool, how many clients are active/waiting and how many server connections are active/idle.

If cl_waiting is consistently above zero, clients are queuing for a backend — either raise default_pool_size or shorten transactions.

You do not have to pick one mode globally. Set a default pool_mode and override it per database. A typical split:

• Main OLTP app database in transaction mode for maximum reuse.
• A legacy or admin database that uses LISTEN/NOTIFY, advisory locks, or temp tables in session mode for correctness.

Choose the correct behavior under PgBouncer transaction pooling.

Session vs transaction pooling, distilled:

• Session mode: backend held until client disconnects. Full feature compatibility, low reuse. Use it for databases needing LISTEN/NOTIFY, session advisory locks, or persistent prepared statements.
• Transaction mode: backend released per transaction. High reuse for many short requests, but session-level state can leak or vanish.
• What breaks in transaction mode: bare SET GUCs, named prepared statements, session advisory locks, LISTEN/NOTIFY, WITH HOLD cursors.
• Fixes: SET LOCAL inside a transaction, max_prepared_statements (1.21+), short transactions, idle_in_transaction_session_timeout, and per-database pool_mode overrides.