One HTTP request spends 30 ms opening a fresh database connection. The actual query takes 1 ms. That means 99% of the time is wasted just getting ready to talk. At thousands of requests per second this overhead turns into an outage. The fix is simple — don't open a connection every time; keep a set of them open and reuse them. That's a connection pool. This guide covers why connections are expensive, what a pool actually is, pool exhaustion, sizing math, PgBouncer modes, and the serverless trap.
The cost of opening a DB connection
A "connection" is not a single action. It's a stack of round-trips plus server-side resource allocation:
Opening one fresh PostgreSQL connection:
1. TCP handshake (SYN / SYN-ACK / ACK) ~1 RTT
2. TLS handshake (cert exchange + key agree) ~2 RTT
3. Authentication (startup + password/scram) ~1-2 RTT
4. Server-side resources (fork a backend process) ~few ms
Total — milliseconds to tens of ms even within one datacenter.
Remote / cross-AZ inflates the RTTs, easily 100 ms.The last step is the killer. PostgreSQL forks a separate backend process per connection — several MB of memory plus OS process-creation cost. MySQL spawns a thread (lighter than a process, but still not free). A connection is a heavyweight object that holds real server resources.
# No pool — a fresh connection per request
request → connect (30ms) → query (1ms) → disconnect → respond
↑ 96% is connection overhead
# With a pool — reuse
request → acquire (0.1ms) → query (1ms) → release → respond
↑ the connection is already openWhat a connection pool is
A pool is a fixed set of already-open connections. The application never opens connections directly — it borrows (acquire), uses, and returns (release) them. Returning is not close — the connection stays alive and the next request reuses it.
At startup the pool opens 'min' connections up front
[conn1] [conn2] [conn3] [conn4] ← idle, waiting
Request A: acquire → borrows conn1 → query → release → returns conn1
Request B: acquire → borrows conn2 → query → release → returns conn2
(concurrent — parallelism equals borrowed connections)
Key insight: connection lifetime ≠ request lifetime.
Connections live long; requests borrow briefly.Pool lifecycle parameters:
- min size — connections always kept open. Warm-up at boot.
- max size — the ceiling on concurrent connections. Beyond it, requests wait.
- idle timeout — connections unused for a while are closed to free resources (down to min).
- max lifetime — too-old connections are retired and recreated. Avoids DB-side timeouts and slow leaks.
- validation / health check — run
SELECT 1before handing one out to confirm it's alive. Prevents serving dead connections.
Pool exhaustion
max = 10. Every connection is in use.
[conn1..conn10] ← all checked out (busy)
Request 11: acquire → none free → enters the wait queue
Request 12: acquire → waits
...
- Someone releases → handed to a waiting request
- If not served within the acquire timeout (e.g. 5s) → error
Error messages you'll see:
"connection pool timeout" (app side)
"remaining connection slots are reserved" (DB side)
"FATAL: sorry, too many clients already" (DB max exceeded)The usual cause of exhaustion is a slow query. If one query takes 2 seconds, that connection is pinned for 2 seconds. Under concurrency the pool drains in an instant. The other cause is a connection leak — if code forgets to release, that connection never comes back and the pool slowly dries up.
// Leak — not released when the query throws
const conn = await pool.acquire();
const rows = await conn.query(sql); // what if this throws?
pool.release(conn); // ← never reached, leaked forever
// Correct — always release in finally
const conn = await pool.acquire();
try {
return await conn.query(sql);
} finally {
pool.release(conn); // released on success or error
}Sizing — bigger is not faster
Counterintuitively, raising max does not raise throughput — it often lowers it. With 100 connections but 8 CPU cores, only ~8 queries truly make progress at once. The rest just add context-switch, lock, and disk-I/O contention.
Connections vs throughput (rough curve)
throughput
│ ___________
│ / \____
│ / \___ ← too many actually hurts
│ / (CPU/disk contention)
│/
└──────────────────────────────── connections
the sweet spot is usually smaller than you thinkThe PostgreSQL community rule of thumb — the right number is cores × a small factor. A frequently cited starting point:
connections ≈ (core_count × 2) + effective_spindle_count
e.g. 8 cores + SSD (spindle effectively 1) → about 17-20 connections
This is a starting point, not a cap: "start near here, then measure."
Most OLTP workloads are fine with a few dozen.Little's Law lands in the same place — concurrency_needed = arrival_rate(req/s) × avg_query_time(s). At 500 req/s with a 10 ms average query, you need 500 × 0.01 = 5 concurrent connections. There's no reason to size the pool at 50 or 100. Fast queries need few connections.
The most common accident is forgetting to multiply:
20 app servers × pool max 10 = up to 200 connection attempts
but PostgreSQL max_connections = 100
→ connection 101 onward gets "too many clients already"
→ half your fleet can't reach the DB — outage
Rule: (app instances × pool max) ≤ DB max_connections
(and subtract superuser-reserved slots + other services)Where pooling lives — in-app vs external pooler
You can pool at two layers:
- In-app pool — connections managed inside the application process. E.g.
HikariCP(Java),pg.Pool(Node),database/sql(Go). Simplest, lowest latency. Downside — every process keeps its own pool, so more instances reintroduce the multiplication problem. - External pooler — a proxy between app and DB. E.g.
PgBouncer,RDS Proxy. It accepts thousands of app connections and multiplexes them onto a small number of real DB connections. Keeps DB-side connection count bounded when you run many app instances.
# Without an external pooler — multiplication explodes
app1 (pool 20) ─┐
app2 (pool 20) ─┼→ DB (200-connection load)
... ×10 ─┘
# Add PgBouncer in front — multiplex
app1 (pool 20) ─┐
app2 (pool 20) ─┼→ PgBouncer → DB (only 20 connections)
... ×10 ─┘ ↑ thousands of client conns → few server connsPgBouncer pooling modes
PgBouncer offers three modes that differ in when a client connection is bound to a real DB connection. The tradeoffs are big, so understand them precisely:
session pooling (default)
A DB connection is held from the moment a client connects until it
disconnects.
→ almost no multiplexing. Same as plain connections.
→ SET / prepared statements / advisory locks are safe.
transaction pooling (recommended for serverless)
A DB connection is held only from BEGIN to COMMIT, then returned.
→ maximum multiplexing. Thousands of clients → tens of DB conns.
→ caution: session state is lost at transaction boundaries.
SET search_path, session variables, some prepared statements,
LISTEN/NOTIFY, and advisory locks may break.
statement pooling (most aggressive)
The connection is returned after every single statement.
→ multi-statement transactions are impossible. Very limited.Transaction mode is the balance point between multiplexing efficiency and safety, which is why it dominates serverless and high-concurrency setups. The catch — anything the driver pins per session stops working, so you disable prepared statements (or use simple-query mode) to match. For the meaning of transaction boundaries themselves, see the how-database-transactions-work guide.
The serverless trap — connection storms
In environments like Lambda or edge functions, instances auto-scale to hundreds or thousands based on load, and the premise of pooling collapses. Each instance opens its own separate pool:
Traffic spike → Lambda scales out to 1,000 concurrent instances
Each instance: "let me open my pool" → 5 DB connections each
→ 1,000 × 5 = 5,000 connection attempts
PostgreSQL max_connections = 100
→ instant "too many clients already"
→ every cold start pays TCP+TLS+auth again (connection storm)The root issue — serverless functions are short-lived and share no state, so an in-app pool never pays off (the instance is gone before the connection gets reused). That's why serverless Postgres requires an external transaction-mode pooler:
- PgBouncer (transaction mode) — absorbs thousands of function connections into a few DB connections.
- RDS Proxy — AWS-managed, designed to soak up Lambda's connection bursts.
- Supabase / Neon pooler endpoints, Cloudflare Hyperdrive — pooled access points built for edge.
Rule — in serverless, keep the in-function pool max tiny (1-2) and let an external pooler do the real multiplexing. The front-end routing that spreads function traffic across instances is the subject of the how-load-balancers-actually-work guide.
Common pitfalls
1. Forgetting to release
The most common leak. As shown above, always guarantee release with finally (or your language's RAII / context manager / using).
2. Blindly raising max
"It was slow so I bumped the pool to 200" almost always backfires. Find and fix the slow query first — reduce how long connections stay pinned.
3. No acquire timeout
Without a timeout, exhaustion makes requests wait forever, pinning threads / the event loop and spreading the outage. Set a sane timeout (e.g. 2-5s) and fail fast.
4. No max lifetime
Long-lived connections accumulate DB-side memory, hit network drops, and go stale after a failover. Recycle them periodically with max lifetime.
5. Session features under transaction mode
If you rely on prepared statements or session variables while running PgBouncer in transaction mode, things break intermittently. Match your driver config to the mode.
References
- PgBouncer — pooling modes — pgbouncer.org
- PostgreSQL wiki — Number Of Database Connections — wiki.postgresql.org
- HikariCP — About Pool Sizing — HikariCP wiki
- AWS — Using Amazon RDS Proxy — docs.aws.amazon.com
Summary
- A DB connection costs ms to tens of ms — TCP + TLS + auth + server-side process/thread creation. Opening one per request is waste.
- A pool is a fixed set of pre-opened connections. acquire → use → release; never close, reuse.
- Key parameters — min / max / idle timeout / max lifetime / health check.
- Exhaustion comes mostly from slow queries and leaks. Always release in finally.
- Bigger is not faster. Size by cores × a small factor and Little's Law. (app count × pool max) ≤ DB max_connections.
- In-app pool (HikariCP, etc.) vs external pooler (PgBouncer / RDS Proxy). Many instances → multiplex with an external pooler.
- PgBouncer modes — session (safe, inefficient) / transaction (balanced) / statement (aggressive, restrictive). Transaction is the standard.
- Serverless risks connection storms. A transaction-mode external pooler is mandatory; keep the in-function pool tiny.