Connection pool sizing: an interactive simulator
A connection pool keeps a few database connections open and lends them to requests, so no request pays to open one. Its size also caps how many queries run on the database at once, and that is the part that surprises people: a database does the most work with a modest number of queries in flight, and a bigger pool past that point makes it slower, not faster.
The playground puts app servers with pools in front of an 8-core database and sends it a two second spike of traffic. Change the pool size, the number of servers and whether PgBouncer sits in between.
4 app servers with 5 connections each: up to 20 queries at once. The 8-core database does the most work with about 20 queries running; past that they slow each other down. During the 2 s spike to 2,500 a second, at most 20 queries run and the rest wait in the app servers; the database keeps working near its peak, 437 requests time out waiting, and p99 latency is back under 100 ms 3 s after the spike ends. 97.1% answered.
Why a bigger pool can be slower
A database has a fixed number of cores and disks. Once every core is busy, another running query does not add work done; it adds context switches, lock waits and cache misses, and every query gets slower. The playground models this with the Universal Scalability Law: throughput climbs to 1,587 queries a second at 20 running queries and then falls, to 376 a second at 200.
With 4 servers of 5 connections, the spike queues in the app servers while the database keeps working near its peak: 97.1% of requests are answered and latency is back to normal 3 seconds after the spike ends. With 4 servers of 50, the spike fills all 200 connections, the database slows to fewer queries than normal traffic needs, and it never catches up: only 46% answered. The bigger pool turned a spike into an outage. A pool that is far too small fails the other way: 4 connections cannot keep up with normal traffic, and requests time out waiting while the database runs at under half of what it could do.
How big should the pool be?
- Size for the work, with Little’s law. Connections busy = queries a second × average query time: 1,000 queries a second at 7 ms each keep about 7 busy. Add headroom for bursts. See queueing and Little’s law.
- Cap the total at what the database does best, often around twice its cores, as the HikariCP pool sizing guide suggests with
cores × 2 + disks. Measure your own peak: it depends on how much each query waits on disk and locks. - Count every client. The database sees servers × pool size, plus workers, cron jobs and migrations. Autoscaling multiplies it: 16 servers of 10 is 160 connections, and a rolling deploy can briefly run old and new servers side by side.
- Let excess requests wait in the app, briefly, and fail fast after a pool timeout. Waiting for a connection is cheap; a query that runs and makes every other query slower is not.
Pool in the app, or in front of the database
An app-side pool (HikariCP, SQLAlchemy, pgx) is per process, so the total grows with every server and every serverless function instance. A proxy pool such as PgBouncer or RDS Proxy sits in front of the database, accepts thousands of client connections and runs their transactions over a few dozen real ones. In the playground, 16 servers of 10 behind a 20-connection PgBouncer behave exactly like 4 servers of 5. Postgres needs this more than most, because each connection is a separate server process with its own memory, and its default limit is 100 connections.
In transaction mode a client gets a server connection only for the length of a transaction, so anything that lives on a session, such as SET commands, advisory locks or, in older PgBouncer versions, prepared statements, needs care.
A small pool and PgBouncer in code
from sqlalchemy import create_engine
# per app server: small, fixed, and fail fast when it runs dry
engine = create_engine(
"postgresql+psycopg://app@pgbouncer:6432/shop",
pool_size=5, # connections kept open
max_overflow=0, # never open more under load
pool_timeout=1, # seconds to wait for a free one, then raise
pool_pre_ping=True, # drop connections the server closed
); pgbouncer.ini: thousands of client connections, a few dozen to Postgres
[pgbouncer]
pool_mode = transaction ; a server connection is lent for one transaction
default_pool_size = 20 ; per database and user: about 2x the cores
max_client_conn = 5000 ; app servers, workers and functions togetherIn a system design interview
When the database is a single primary, say how connections reach it: “Each of up to 40 API servers keeps a pool of 5, and they connect through PgBouncer in transaction mode with 32 server connections, about twice the primary’s cores. Peak load is 3,000 queries a second at 5 ms, so about 15 are busy. When the pool is exhausted, requests wait at most 200 ms and then get a 503, which also tells the autoscaler that adding API servers will not help.” Expect the follow-ups: read replicas and their own pools, what serverless functions do to connection counts, and how you notice pool exhaustion.
Read the guide to scaling Postgres or capacity planning, see bulkheads for pools that isolate failures and load shedding for what to do with excess requests, or watch a strong candidate design a ticketing system.
Questions people ask
What is a database connection pool?
A set of open database connections that an application reuses. A request borrows a connection, runs its queries and returns it, so it never pays to open one, which can take several milliseconds with TCP, TLS and authentication. The pool size also caps how many queries one app server runs at once.
How big should a connection pool be?
Big enough for the work, small enough for the database. By Little’s law the connections in use equal queries a second times average query time, so 1,000 queries a second at 7 ms keep about 7 busy. Across all app servers the total should stay near what the database handles best, often around twice its cores.
Why can a larger connection pool make the database slower?
Because a database has a fixed number of cores and disks. Past the point where they are busy, more concurrent queries add context switching, lock contention and cache misses, so every query slows down and total throughput falls. Queueing extra requests in the app is cheaper than running them all at once.
What is PgBouncer and when do you need it?
A lightweight proxy that accepts many client connections and runs their transactions over a small pool of real Postgres connections. You need it, or a managed equivalent such as RDS Proxy, when many app servers, workers or serverless functions would otherwise open more connections than the database can handle well.
What happens when a connection pool is exhausted?
New requests wait for a connection to be returned. If none frees up within the pool timeout, the request fails with an error. Exhaustion usually means the database is at capacity or queries are slow, so raising the pool size tends to make it worse; shed load, speed up queries or add database capacity instead.
How does autoscaling affect database connections?
Every new app server brings its own pool, so connections to the database grow with the number of servers: 16 servers with 10 connections each is 160. Rolling deploys briefly add more. Size pools for the largest number of servers you will run, or put a proxy pool in front of the database.
Why are Postgres connections expensive?
Postgres runs a separate server process for each connection, with its own memory, so thousands of connections cost a lot of memory and scheduling even when idle. Its default limit is 100 connections. That is why Postgres deployments usually put PgBouncer or a similar proxy in front of the database.