Why "More Connections" Makes Things Worse
When a database starts slowing down, the instinctive response is to increase the connection pool size. If 100 connections are overwhelmed, surely 500 will help? This is almost always wrong, and understanding why is fundamental to database reliability.
A database server has a fixed amount of CPU cores — say, 16. Each active query occupies one core (approximately). If you have 500 concurrent connections all running queries, the operating system must context-switch between them, the database must maintain lock state for all of them, and memory pressure increases. You get worse throughput with 500 connections than with 100, because the overhead of managing 500 concurrent workers exceeds the benefit of parallelism.
The research-backed formula for PostgreSQL connection pool sizing (from the PgBouncer documentation and Percona benchmarks) is:
optimal_connections = (num_cpu_cores * 2) + num_effective_spindles
For a 16-core server with SSDs (spindles ≈ 1):
optimal_connections = (16 * 2) + 1 = 33
Yes, 33. Not 500. This is counterintuitive but well-validated in production.
Admission Control
If the optimal pool size is 33 but your application has 1,000 threads that might want database connections, you need admission control — a mechanism that queues or rejects excess requests rather than letting them all pile into the database.
PgBouncer is the standard tool for this in the PostgreSQL ecosystem. It sits between your application and PostgreSQL, maintaining a small pool of real database connections and multiplexing thousands of application connections onto them.
1000 app threads → PgBouncer (pool of 33) → PostgreSQL
When all 33 connections are busy, PgBouncer queues incoming requests. If the queue exceeds a threshold (configurable via max_client_conn), it rejects new connections with a fast error rather than letting them pile up indefinitely. The key parameters are max_client_conn (how many application-side connections PgBouncer accepts), default_pool_size/max_db_connections (the real, capped pool it maintains against PostgreSQL), and query_wait_timeout (how long a queued request waits before being rejected) — all set alongside pool_mode = transaction.
pool_mode = transaction is the most efficient mode: a real database connection is only held for the duration of a single transaction, then returned to the pool. This allows 1,000 application threads to share 33 real connections, as long as each transaction is short.
Interviewers frequently ask: "Your database is slow. What do you check first?" A strong answer mentions connection pool saturation before jumping to query optimization. Check pg_stat_activity for connections in waiting state, check PgBouncer's SHOW POOLS output for cl_waiting (clients waiting for a connection). If the pool is saturated, adding more connections to the pool is the wrong fix — you need to find and fix the slow queries causing the saturation.