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).