Module 5 · Lesson 4 of 5
Connection pooling
Why a thousand servers can't each hold a hundred connections.
Gatepass autoscales to 40 API servers for the sale, each configured to keep up to 100 database connections open. The arithmetic takes a second: 4,000 connections, against a database that comfortably handles a couple of hundred. Postgres starts refusing connections with "too many clients already" and checkout dies, even though the database's CPU is at 20%.
Why connections are expensive
A database connection is not a cheap thing. To open one, the client and server do a TCP handshake, usually TLS, authentication, and set up session state. That's 20 to 100 ms. On the server side, Postgres starts a dedicated process for each connection and reserves memory for it, commonly several megabytes.
So a database has a practical connection ceiling. Postgres defaults to max_connections = 100. Tuned servers go to a few hundred, and past that performance gets worse, because hundreds of processes compete for the same CPU cores and cache. A database with 8 cores does not run 4,000 queries in parallel. It runs about 8 at a time, and the rest wait.
What a pool does
A connection pool opens a fixed set of connections once and lends them out. A request borrows a connection, runs its queries, and returns it. Nobody pays the connection setup cost again, and the number of real connections stays at a number you chose.
When all connections are in use, new requests wait in a queue for one to come back, up to a timeout. That's the important behaviour: waiting in an orderly line is far better for the database than a stampede.
Pools live in two places:
- In the application (a library pool). Each server process has its own, say 10 connections. Simple, and enough for a handful of servers.
- In a separate proxy (PgBouncer, Odyssey, RDS Proxy, ProxySQL). All app servers connect to the proxy, which multiplexes them onto a small set of real database connections. This is what you want once you have dozens of servers, serverless functions, or autoscaling, since the total no longer grows with the server count.
Per-server pools add up. Forty servers times a pool of 10 is 400 connections, and autoscaling to 100 servers makes that 1,000 without anybody touching the config.
How big should a pool be?
Smaller than you think. Connections beyond what the database can run in parallel only add queueing and context switching. A well-known starting rule is:
pool size ≈ (CPU cores × 2) + number of disks
For an 8-core database that's about 17 connections in total, shared by all servers. A request spends most of its time outside the database (calling a payment provider, rendering JSON), so 17 busy connections can serve hundreds of requests per second.
Use arithmetic to find the real limit. If a query takes 5 ms on average and you have 20 connections, you can run 20 ÷ 0.005 = 4,000 queries per second. If each request makes 4 queries, that's about 1,000 requests per second from 20 connections. If queries average 50 ms because of a missing index, the same pool does a tenth of that, and no pool size fixes slow queries.
Pool modes in a proxy
A proxy like PgBouncer can lend connections at different granularity:
- Session pooling: a client keeps the same connection until it disconnects. Safe, but saves little, since idle clients still hold connections.
- Transaction pooling: a connection is lent for one transaction, then goes back. This gives the best multiplexing, and thousands of clients can share dozens of connections. The catch: anything that depends on session state, such as
SETcommands, advisory locks, or some prepared statements, can misbehave, because the next transaction may run on a different connection. - Statement pooling: per statement. Rarely used, as it breaks multi-statement transactions.
Most systems with many app servers choose transaction pooling and avoid session-level features.
Failure modes
- Pool exhaustion. All connections busy, requests wait, the queue fills, timeouts fire. The symptom is latency, not database CPU. Watch "connections in use", "requests waiting" and wait time.
- Connection storms. After a restart or failover (see the previous lesson), every server reconnects at the same moment. Use backoff with jitter, and a proxy that absorbs the herd.
- Leaks. Code that borrows a connection and forgets to return it on an error path. The pool slowly drains until the app hangs. Always return connections in a
finallyblock or use a helper that does. - Stale connections. A firewall silently closes idle connections and the pool hands out a dead one. Use a validation query or a maximum connection lifetime.
Check yourself
Gatepass autoscales from 10 to 60 API servers. Each has a library pool of 20 connections. The database allows 300 connections. What happens at 60 servers, and what's the best fix?
Your turn
Size the database connections for Gatepass: 8-core Postgres, up to 60 API servers during the sale, queries averaging 8 ms with about 3 queries per request. Where does the pool live and how big is it?
Key takeaways
- Connections are costly to open and costly to hold. Databases work best with tens, not thousands.
- A pool reuses a small set of connections and makes extra requests wait in line instead of overwhelming the database.
- Per-server pools multiply with autoscaling. A proxy in transaction mode keeps the total fixed.
- Keep pools small, keep transactions short, and never hold a connection during a slow external call.
- Watch for exhaustion, reconnect storms and leaks. Pools fix connection costs, not slow queries.
Finished reading?
Mark it complete to tick it off in the course.