PgBouncer transaction vs session pooling — finally fixed our 'too many clients' issue

Been running PgBouncer in front of a fairly busy PostgreSQL 16 instance (a few hundred app servers connecting) and finally nailed down a weird issue that cost me two days.

We were seeing random 'too many clients' errors even though our max_client_conn was set way above the actual connection count. Turned out the pool_mode was set to 'session' from an old config file that got copy-pasted when we migrated to a new host. Switching to 'transaction' mode dropped our active server connections from ~800 down to ~60 under the same load.

One gotcha: if you use advisory locks, SET LOCAL, or LISTEN/NOTIFY anywhere in your app, transaction pooling will break things silently. We had one background job using pg_try_advisory_lock() that started misbehaving. Had to move that service to its own PgBouncer pool in session mode.

Also worth noting — pgBouncer 1.22 added better support for SCRAM-SHA-256 auth passthrough, so if you're still on 1.18/1.19 and hitting auth issues, upgrading is worth it.

Anyway, hope this saves someone else a couple hours. Happy to share our pgbouncer.ini if useful.

Good catch. Session mode is basically a connection multiplier—each app connection gets its own DB connection for its entire session, which tanks your pool efficiency. Transaction mode is usually the sweet spot for most workloads. Pro tip: monitor pool_size and reserve_pool_size separately; if you're still hitting limits, tune those before going back to session mode.