What is connection pooling and why does it matter?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Opening a database connection is expensive (TCP + TLS + auth + backend process), so a pool keeps a set of warm, reusable connections that app threads borrow and return. It caps concurrent backends to what the database can handle, queues the overflow, and prevents connection storms from toppling the database.
Detailed explanation
Each new PostgreSQL connection forks a backend process (~several MB plus setup handshakes) — at hundreds of app instances × dozens of threads, direct connections exhaust memory, CPU, and the max_connections limit, and new clients get refused. A pooler (PgBouncer, RDS Proxy, or in-process pools like HikariCP/pg-pool) holds N established connections and multiplexes app requests over them: a request borrows a connection, runs its queries, returns it. Sizing follows Little's Law thinking: pool_size ≈ (threads × avg_query_time) / tolerated_wait — small pools with short queries beat huge pools; oversized pools just move the queue into the database where every backend fights for CPU. Modes matter in PgBouncer: session pooling (one server connection per client connection — safe, least sharing), transaction pooling (server connection reassigned per transaction — efficient, but breaks session-level features like prepared statements, advisory locks, LISTEN/NOTIFY), statement pooling (per statement — rarely usable). Operational essentials: set statement timeouts so one bad query can't pin the pool, monitor wait queues and checkout latency (the pool is full long before the database is slow), and keep transactions short — an idle-in-transaction connection is a pooled slot doing nothing while others queue.
Example
-- PgBouncer in front of PostgreSQL (transaction pooling)
-- app → PgBouncer (port 6432, pool_size=25) → PostgreSQL (max_connections=200)
-- 500 app workers share 25 server connections; overflow waits in the pool queue.
-- App-side pool (Node pg): borrow and always release
-- const client = await pool.connect();
-- try { await client.query('SELECT ...'); }
-- finally { client.release(); } -- leaked clients starve the poolInterview tip
Explain the cost being amortized (connection setup) and the failure being prevented (connection storm past max_connections). Naming transaction vs session pooling mode is the senior-level detail.
Common mistakes
- Sizing pools by guesswork (huge numbers) instead of from query latency and concurrency.
- Using transaction pooling with features that need session affinity.
- Leaking checked-out connections so the pool drains under load.