Which PgBouncer pooling mode should I pick — Session, Transaction, or Statement?

Short answer: for a web/microservice fleet, pick Transaction mode — for multi-statement traffic, it’s the most practical option for maximizing connection reuse.

Short answer

What you’re hitting is clear: 50 pods × min connections exhausts max_connections. PgBouncer solves exactly this — it multiplexes many client connections onto a few server connections, so you rescue RAM without inflating max_connections. Don’t forget the query itself has a share in a saturated pool; I covered indexing the hot query in the composite index order answer.

Why

1. Transaction mode gives the highest reuse. It returns the server connection to the pool at the end of each transaction; two consecutive transactions may land on different server connections. For hundreds of short-lived microservice requests this is the right mode.

2. Session mode throws the benefit away. It binds one server connection to each client for its whole session — so your 50 pods still hold 50+ server connections and the multiplexing gain disappears. Only worth it if you genuinely need session state.

3. Statement mode breaks your app. It returns the connection at the end of each statement, which breaks multi-statement transactions. In practice it’s not used for web apps.

What to do

1. Pick Transaction mode. The entire pooling gain comes from this mode.

2. Close the prepared-statement trap. Protocol-level prepared statements and connection-bound state like SET, advisory locks and LISTEN are lost once you land on a different server connection. Disable client-side prepared statements (e.g. PDO ATTR_EMULATE_PREPARES) or use the named prepared statement support PgBouncer has had since 1.21 — with max_prepared_statements above zero it tracks those commands in transaction and statement pooling mode and re-prepares them on a new server connection when needed.

3. Never rely on session state across statements. In Transaction mode the connection identity isn’t stable.

4. Keep default_pool_size tight against measured concurrency. Don’t make the server-connection pool huge — the whole point of multiplexing is serving many clients with few server connections.

Bottom line: I’d pick Transaction mode, disable protocol-level prepared statements in the app (or upgrade PgBouncer to 1.21+), and keep default_pool_size tight against measured concurrency. Session mode doesn’t solve your problem, and Statement mode breaks your app. Solving connection pooling instead of multiplying max_connections and RAM vertically is often exactly the move that keeps PostgreSQL “enough”; I dug into that debate in the sade.dev piece.