Connection pooling is a correctness decision

PgBouncer in transaction mode is the default choice for a busy PostgreSQL service, and for good reason: it lets a few hundred application connections share a few dozen server connections. It also changes what a connection is allowed to remember. That second part is a correctness decision, not a performance knob.

What transaction pooling keeps

In transaction mode, a server connection is assigned to a client for the duration of one transaction and then returned to the pool. The next transaction from the same client may run on a different server connection. Anything scoped to the session rather than the transaction is no longer guaranteed to be there.

That is fine for the common case: a short transaction that reads and writes rows. It is not fine for:

• prepared statements created with PREPARE, unless PgBouncer is configured for protocol-level prepared statements;
• session-level SET values that later queries depend on;
• advisory locks taken with pg_advisory_lock and released at session end;
• temporary tables that outlive the transaction;
• LISTEN/NOTIFY subscriptions;
• cursors declared WITH HOLD.

Each of these is a feature that assumes the same server connection for the whole client session. Transaction pooling removes that assumption.

The configuration that makes it safe

PgBouncer 1.21 and later can track protocol-level prepared statements. If the application uses a driver that prepares statements automatically, turn this on before switching the pool mode:

[pgbouncer]
pool_mode = transaction
max_client_conn = 400
default_pool_size = 40
reserve_pool_size = 10
server_reset_query = DISCARD ALL
server_reset_query_always = 1
max_prepared_statements = 200

server_reset_query = DISCARD ALL clears session state between clients. server_reset_query_always = 1 makes that happen even when the pool mode would otherwise skip it. max_prepared_statements gives the pool room to track the statements the driver creates.

If the driver does not support protocol-level prepared statements, set max_prepared_statements = 0 and make sure the application does not use them. A silent failure here is worse than a slower query: the statement may be prepared on one connection and executed on another.

What to verify, not assume

We test the pool the same way we test the application, because the failure is invisible until a specific query hits a specific connection.

1. Prepared statements. Run the same parameterised query more than default_pool_size times, across enough clients to rotate every server connection. It must succeed every time.
2. Session settings. Set a value in one transaction and read it in the next. It must not leak, and it must not be missing when the application expects it.
3. Advisory locks. Take a session lock, then run a second transaction on the same client. If the application relies on the lock, transaction pooling is the wrong mode.
4. Reset behaviour. Query pg_stat_activity after a burst and confirm no session state survives the reset query.

The rule we use

Use transaction pooling when the application is stateless between transactions. Use session pooling when it is not. Do not mix the two by accident: a driver that prepares statements, an ORM that sets session variables, and a pool in transaction mode is a combination that passes smoke tests and fails under concurrency.

The pool is part of the correctness surface. Treat its mode as an architectural decision, document it next to the schema, and test the assumptions it makes.