Connection Pooling in Postgres: PgBouncer and the Limits That Bite

Postgres connections are not cheap threads — each one is a forked backend process with its own memory (work_mem, caches, parse state). A few hundred idle connections can eat gigabytes and thrash the scheduler, and throughput peaks well before max_connections. The fix isn't a bigger box; it's a connection pooler.

1. Why "just raise max_connections" fails

Active connections compete for CPU and for a shared lightweight-lock table. The sweet spot for active connections is often (cores × 2) + effective_spindle_count — frequently under 50, even on a big instance. Hundreds of app servers each holding 20 connections will blow past that and serialise on internal locks.

2. PgBouncer pool modes

PgBouncer sits between your app and Postgres and multiplexes many client connections onto a small server pool. The pool mode decides how aggressively.

• session — a server connection is tied to a client for its whole session. Safe, but barely better than no pooler.
• transaction — the server connection is returned to the pool after each transaction. The workhorse mode: thousands of clients, ~25 server connections.
• statement — returned after every statement. Fastest reuse, but forbids multi-statement transactions. Rarely worth it.

[databases]
app = host=127.0.0.1 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25

3. The transaction-mode footguns

In transaction mode you can't rely on anything that lives across transactions on the same connection:

• SET / session GUCs — they may leak to another client or vanish. Use SET LOCAL inside a transaction.
• Server-side PREPAREd statements — disable client-side prepared statements or set max_prepared_statements in newer PgBouncer.
• Advisory locks held across transactions, LISTEN/NOTIFY, temp tables — all unreliable.

4. Pool math

Your real connection ceiling is default_pool_size × number_of_databases × pooler_instances, and that must stay under Postgres max_connections minus a reserve for superuser and maintenance. Size the pool to the database's capacity, then let PgBouncer queue the rest — queuing for 5ms beats overwhelming the database.

5. Don't forget the app-side pool

Your ORM (HikariCP, node-postgres, SQLAlchemy) also pools. With PgBouncer in front, keep the app pool modest and let PgBouncer do the real fan-in. Two greedy pools stacked on top of each other just move the bottleneck around.

Rules of thumb

• Use transaction mode unless you have a specific reason not to.
• SET LOCAL, not SET. Assume nothing survives a transaction boundary.
• Size the server pool to the database, not to demand. Let clients queue.
• Watch pg_stat_activity — if most backends are idle in transaction, you have a leak, not a pooling problem.