Connection Pooling
A pool of pre-established DB connections shared by app workers, keeps connection counts bounded and reuses expensive TCP+auth handshakes.
What it is
Database connections are expensive: TCP (Transmission Control Protocol) handshake, TLS (Transport Layer Security) negotiation, authentication, and (for Postgres) a forked backend process per connection. A connection pool maintains a fixed-size pool of open connections that app code borrows and returns. The pool can live in-process (HikariCP, pgx) or as a separate proxy (PgBouncer, RDS Proxy). Pooling modes vary: session pooling assigns one connection per client for the session's life (compatible but limits multiplexing); transaction pooling assigns connections per transaction (much higher concurrency but breaks features like prepared statements and SET LOCAL); statement pooling per statement (rare, most restrictive).
Why senior interviewers ask
'Why is your app running out of connections?' is the single most common production database incident. A senior engineer should be able to name the pooling layer, the mode, and the math: app workers × pool size <= DB max connections.
Key points
- Postgres max_connections defaults to 100; each connection is a forked process consuming ~10MB RAM.
- Without pooling, serverless/Lambda apps overwhelm the DB instantly, each cold start opens a connection.
- PgBouncer transaction mode is the standard for high-concurrency Postgres deployments.
- Transaction mode breaks server-side prepared statements, SET LOCAL, advisory locks, app must adapt.
- Pool size math: total app workers × per-worker pool size must fit DB connection limit.
- HikariCP (Java) is the gold standard for in-process pools: minimal overhead, fast checkout.
- RDS Proxy / Aurora Proxy abstract pooling for managed deployments.
- Connection leaks (forgetting to release) silently exhaust the pool; instrument checkout/checkin.
Pros
- Bounds DB connection count regardless of app worker count.
- Amortizes TCP+TLS+auth handshake cost across many requests.
- Allows serverless/horizontally scaled apps to use Postgres without melting it.
- Transaction mode multiplexes thousands of clients onto dozens of connections.
Cons
- Transaction mode forbids session-scoped features (prepared statements, SET LOCAL, temp tables).
- Adds a network hop (external pooler), small latency penalty.
- Misconfigured pool size causes either connection exhaustion or DB overload.
- Connection leaks are silent until the pool runs dry under load.
When to choose
- Any Postgres deployment with >50 concurrent clients, PgBouncer is mandatory.
- Serverless apps (Lambda, Cloud Functions) hitting Postgres, RDS Proxy or PgBouncer.
- Java/JVM apps, HikariCP per service instance.
- Microservices fleets where each service has its own pool sized for its workload.
When to avoid
- Transaction mode when app relies on prepared statements or session state, use session mode or fix the app.
- Pool inside Lambda when invocations are extremely short, RDS Proxy at the edge is better.
Real systems
Interview probe
'Your Lambda function works in dev but fails with too many connections in prod. Fix it.' Answer: introduce a pooler (RDS Proxy or PgBouncer) between Lambda and Postgres in transaction mode. Don't open a connection per invocation against a raw Postgres instance.
Dive deeper
Transaction pooling is the magic that lets a few hundred Postgres connections serve tens of thousands of app clients, but it costs you session state. Prepared statements created on connection A may not exist when the next request arrives on connection B. ORMs (Object-Relational Mappers) that rely on server-side prepares (Hibernate with default settings, asyncpg) break in transaction mode. Either disable server-side prepares, use session mode (with lower multiplexing), or use a driver that handles transaction-mode poolers natively (pgx, node-postgres with pool=false on the driver).