PostgreSQL Connection Pooling: Why max_connections=200 is Killing Your Performance

Your app throws too many clients already. You bump max_connections to 500. It feels like more lanes on a highway, but in Postgres it is more like hiring 500 librarians - they spend all their time talking to each other instead of finding books.

What each connection actually costs

Postgres is process-per-connection. A freshly forked backend uses ~1.5-2 MB RSS due to Linux Copy-on-Write, but once it performs any real work resident memory typically reaches 10-20 MB. 500 processes on 16 cores is a kernel context-switching nightmare - empirical benchmarks show a clear performance inflection point around 128-256 clients. You also hit ProcArray lock contention during snapshot acquisition and transaction commit.

Transaction mode vs session mode

Use PgBouncer in transaction mode. It lets 1,000 clients share 20-30 DB connections. Session mode reduces TCP/TLS handshake overhead but does not reduce the PostgreSQL backend process count - it solves the wrong problem for high-concurrency workloads.

The work_mem multiplier

work_mem is per-operation. A query with 3 sorts and 4 parallel workers uses 12× work_mem:

Total ≈ concurrent_queries
       × ops_per_query
       × (max_parallel_workers_per_gather + 1)
       × work_mem

Note: the relevant setting is max_parallel_workers_per_gather(workers per query node), not max_parallel_workers (the global cap). Keeping active connections low via a pooler is the only way to safely increase work_mem for heavy queries.

The compatibility trade-off

Transaction mode breaks several protocol-level features. Verify before deploying:

• Prepared statements - the default in Rails (ActiveRecord), many Java drivers, and some Python drivers. You will see prepared statement does not exist errors immediately after deploy. Fix: disable prepared statements in your ORM, or enable max_prepared_statements in PgBouncer 1.21+.
• Advisory locks - released at transaction end, not session end. Session-scoped advisory locks silently break.
• SET commands - SET search_path, SET timezone, and similar session-level settings do not persist across transactions. Add server_reset_query = DISCARD ALL to your pgbouncer.ini to reset state between clients.
• LISTEN/NOTIFY - not supported in transaction mode.
• Port change - applications must connect to PgBouncer on port 6432, not to PostgreSQL on port 5432.

Calculate your max_connections, work_mem, and PgBouncer pool size for your server with the PostgreSQL Connection Pool Sizer.