Connection pooling — sharing database connections

Day 7 taught you load balancing: distribute HTTP requests across multiple app servers. Today we go one layer deeper. Behind your app servers sits the database — and connecting to it is expensive. Connection pooling is how you survive that cost.

The problem: connections are expensive

Here’s what happens when your app opens a new database connection:

1. TCP handshake — multiple network round trips to establish the socket.
2. Authentication — the database verifies credentials, often another round trip.
3. Backend process — PostgreSQL forks a new OS process for the connection. Each backend gets its own memory (shared buffers are shared, but the process overhead is per-connection).

This is not free. PostgreSQL’s default max_connections is 100. Each connection consumes memory and a process slot. If every request opens a new connection, you hit that ceiling fast — and the database spends more time managing connections than running queries.

We benchmarked the cost on a real PostgreSQL 16 server (Podman container, not WASM — real TCP connections, real process forking). The result: opening a fresh connection per query takes a median of 22.5 ms per request. Reusing a pooled connection: 1.5 ms. That’s a 15× speedup, and the 21ms difference is pure connection overhead — TCP handshake, auth, process fork — not query time. See benchmark/connection-pooling-results.md for the full 30-run methodology.

The solution: reuse connections

A connection pool keeps a small number of connections open permanently. When an app server needs the database, it borrows a connection from the pool, runs its query, and returns it. No handshake, no fork, no auth — the connection is already warm.

The math is dramatic: with a pool of 20 connections, thousands of requests can share those 20 connections. Without a pool, 1,000 concurrent requests try to open 1,000 connections — and the database keels over at 100.

Where the pool lives

Connection pooling can live in two places:

• In the application — your ORM or database driver maintains the pool. Examples: HikariCP (Java), pg Pool (Node.js), SQLAlchemy’s pool (Python). The pool is per-process; each app server has its own.
• In a dedicated proxy — a separate process sits between your apps and the database. PgBouncer is the standard for PostgreSQL. One external pool serves all app servers, centralizing connection management.

The dedicated proxy (PgBouncer) is the more powerful pattern: if you have 50 app servers each with a 20-connection pool, that’s 1,000 connections to the database — still over the limit. PgBouncer multiplexes them down to a small set of real database connections.

Session pooling vs. transaction pooling

PgBouncer has two modes you should know:

• Session pooling — a client gets a server connection for its entire session, then returns it when it disconnects. Simple, but connections sit idle between requests.
• Transaction pooling — a client gets a server connection only for the duration of a transaction. The moment the transaction commits or rolls back, the connection returns to the pool. Far more efficient — thousands of sessions can share dozens of connections.

Transaction pooling is the default recommendation. The tradeoff: it breaks features that rely on session state (e.g., SET variables, temporary tables, advisory locks). For most web apps, that’s fine — you shouldn’t be keeping session state anyway.

Connection to Day 7 (load balancing)

Connection pooling is the load balancer’s cousin — same idea, different layer:

• Load balancer (Day 7): sits between clients and app servers. Distributes HTTP requests. Solves “too many requests for the app.”
• Connection pool (Day 8): sits between app servers and the database. Multiplexes DB connections. Solves “too many connections for the database.”

Both take a burst of demand and funnel it through a fixed number of workers. The load balancer protects the app layer. The connection pool protects the database layer. A production system uses both.

Load balancer = few app servers, many clients. Connection pool = few DB connections, many app requests. Same shape, two layers.

The transfer question

From Day 7 → Day 8: A load balancer and a connection pool both “funnel many into few.” What resource does each protect, and why can’t one do the other’s job?

Answer (click to reveal)

A load balancer (Day 7) protects app server capacity — it distributes HTTP requests across multiple app servers so no single server is overwhelmed. It cannot help the database because it never sees database connections; it only sees HTTP traffic.

A connection pool (Day 8) protects database connections — it multiplexes many app requests through a small number of warm database connections so the database isn’t flooded with connection overhead. It cannot help the app layer because it only manages database connections, not HTTP requests.

You need both because they protect different layers with different resources: the LB protects CPU/capacity at the app layer, the pool protects connections/memory at the database layer. A spike in traffic hits the load balancer first; the queries that make it through then hit the connection pool. Neither can substitute for the other.

Check your understanding