Module 08 · Database scaling · Lesson 9 of 9
Connection pooling
Database connections are expensive and finite, and a thousand instances each opening twenty is an outage. How pools reuse connections, sizing with Little's law, external poolers like PgBouncer, and serverless connection storms.
Why it matters
A Postgres connection is a server process with several megabytes of memory and real setup cost; the server can handle a few hundred well and degrades badly past that. Applications, meanwhile, scale out: fifty instances with a pool of twenty each is a thousand connections, and a serverless function fleet can open thousands in a burst. Many "database is down" incidents are really "the database ran out of connections", and the fix is a pool in the right place with the right size.
The idea
A connection pool keeps a set of open database connections and lends them to requests for the duration of a query or transaction, then takes them back. Opening a connection (TCP, TLS, authentication, process or thread spawn) happens once per pooled connection rather than once per request, and the number of concurrent connections the database sees is capped by the pool size regardless of request volume.
Sizing a pool
More connections is not more throughput. The database has a fixed number of cores and disks; beyond a small multiple of that, extra connections just contend. A well-known rule of thumb puts the optimal total at roughly twice the number of cores plus the number of spindles, often a few dozen for a whole database. Use Little's law to size from demand: concurrent connections needed equals request rate times average time holding a connection. A service doing 500 queries per second at 10 ms each needs about 5 connections in steady state; a pool of 10 to 20 absorbs bursts. Pools far larger than that only move the queue from the application into the database, where it is more expensive.
Where the pool lives
| In-process pool (HikariCP, pgx, pg-pool) | External pooler (PgBouncer, PgCat, RDS Proxy, Pgpool) | |
|---|---|---|
| Connections to the DB | pool size × number of instances | Pooler's server pool, independent of instance count |
| Latency | Lowest; no extra hop | One extra hop, sub-millisecond |
| Fits | A handful of long-lived instances | Many instances, autoscaling, serverless, multiple services sharing one database |
| Transaction pooling | No; a connection is held per client session | Yes: a server connection is lent only for the duration of a transaction, multiplexing thousands of clients over dozens of connections |
| Caveats | Total grows with instances; must coordinate sizes | Transaction mode breaks session state: prepared statements, SET, advisory locks, LISTEN |
Most scaled systems run both: a small in-process pool per instance for latency, pointing at an external pooler in transaction mode that caps what the database sees.
Serverless and connection storms
Functions and edge runtimes spin up per request and have no shared process to hold a pool, so each invocation may open its own connection and a traffic spike opens thousands at once: a connection storm that exhausts the database before any query runs. Answers: an external pooler in front of the database, provider proxies (RDS Proxy, Neon's pooled endpoint, Supabase's Supavisor), HTTP-based data APIs that pool on the server side, or keeping connection-heavy work on long-lived services.
Hygiene
Return connections promptly: holding one across an external HTTP call or user think time starves everyone else. Set connection and statement timeouts so a stuck query cannot pin a connection forever. Keep transactions short for the same reason. Validate idle connections or recycle them periodically so a database failover does not leave the pool full of dead sockets. Monitor pool wait time and saturation: a rising wait time means the pool is too small or queries are too slow, and the plan from the previous lessons tells you which.