Choosing PgBouncer pooling modes

PgBouncer has three pooling modes. The fastest one is also the one most likely to silently break your application. After an incident where an advisory lock leaked into another tenant's session, I sat down and made an explicit matrix of what we use.

Mode	Connection returned when	Breaks
session	client disconnects	nothing
transaction	`COMMIT`/`ROLLBACK`	session state across txns
statement	each statement completes	multi-statement txns entirely

What "session state" means in practice

SET variables (work_mem, search_path, application_name) leak in or out.
Prepared statements: created in one connection, used in another. They will not be found.
Advisory locks: pg_advisory_lock is session-scoped. With transaction pooling, you almost never get the same backend back, so the lock is effectively orphaned.
Temporary tables: created in one txn, queried in another. Gone.
LISTEN/NOTIFY: needs a stable backend. Use session mode or do not use it at all.

What I do now

The default is transaction mode. Every consumer that needs anything from the "Breaks" list above goes through a separate PgBouncer instance with session pooling, on its own port, with a smaller pool size.

; /etc/pgbouncer/pgbouncer.ini
[databases]
appdb        = host=pg01 port=5432 dbname=app pool_mode=transaction
appdb_locks  = host=pg01 port=5432 dbname=app pool_mode=session

[pgbouncer]
listen_port = 6432
max_client_conn = 5000
default_pool_size = 80
reserve_pool_size = 10
server_idle_timeout = 60

Two extra rules I picked up the hard way:

Set server_reset_query = DISCARD ALL only on session pools. On transaction pools it is a no-op because the connection is returned mid-prepared-statement-cache; throwing it away every cycle defeats the cache.
Run with application_name set per consumer. Without it, pg_stat_activity shows you a wall of pgbouncer entries and you cannot tell who is holding the long-running query.