Connection Pooling

Opening a TCP connection takes 1 round-trip. Adding TLS adds another. Adding Postgres handshake adds 2-3 more. Each connection: ~5-10ms of pure overhead before any query runs. If your app opens a new connection per request, that overhead kills you. Worse, every connection consumes ~10MB on the Postgres server — 1000 idle connections = 10GB of wasted RAM, and Postgres dies somewhere around 500-1000 connections regardless.

Connection pooling reuses a small set of long-lived connections for many requests. Saves the handshake overhead AND keeps the database alive. The most common production performance bug is not having one — or having one configured wrong.

The pool maintains N pre-opened connections. When code wants a connection: borrow from pool, use, return. If pool is empty, wait briefly; if still empty, create new (up to max), or fail.

Two key parameters:

• Min size — connections always kept warm. Pay startup cost once. Typical: 5-10.
• Max size — upper limit on connections. Caps memory + DB load. Typical: 20-100 per app instance.

Critical: connections × instances must stay below DB ceiling. 100 app instances × 50 max = 5000 connections. Postgres dies at maybe 800. You shipped a bug.

Common assumption: "More connections = more parallelism = faster." Wrong. Past a point, more connections make everything slower.

The math (from Little's Law): for a CPU with N cores serving queries that average M ms each, optimal connection count ≈ N × (1 + wait_time / service_time). For a 16-core Postgres serving CPU-heavy queries (no I/O wait): ~16 connections. For I/O-bound queries: maybe 32-64.

HikariCP (the standard Java pool) recommends "small pool, queue requests." A pool of 10 often outperforms a pool of 100 because contention drops. Counterintuitive but correct.

The classic Postgres scaling story. App has 1000 instances × 20 conn = 20,000 connections. Postgres caps at ~500. Without intervention, you crash.

pgbouncer in transaction mode sits between apps and Postgres. Apps connect to pgbouncer (cheap; pgbouncer holds them all in memory). pgbouncer maintains a small pool to Postgres (say, 100). When a client begins a transaction, pgbouncer allocates a Postgres connection for the duration; when the transaction commits, that connection returns to the pool — usable by any other client.

Magic: 10,000 concurrent app clients share 100 actual Postgres connections, because each client only holds one for the milliseconds it's mid-transaction. Throughput improves dramatically because Postgres isn't context-switching across thousands of connections.

Catch: transaction mode breaks anything stateful across statements — prepared statements, session variables, advisory locks (unless within one transaction). Most apps are fine; some need session mode (one connection per client for whole session) which loses much of the benefit.

URL shortener uses pgbouncer in front of Postgres for the redirect-write path. E-commerce uses connection pooling at every tier — DB, Redis, payment-provider HTTP. Payment gateway pools every external API call to keep latency tight.