Connection pooling

Article.

A connection pool keeps a set of authenticated database connections open and lends them to requests, so a query does not pay a TCP handshake, TLS and authentication (typically 5-50 ms) first. That part is easy. The interview question is how many connections a service needs, and why the right number is smaller than you think, and the answer is a formula plus one bug that causes most pool outages.

What a pool buys

• Latency. A warm connection skips setup on every request.
• Backpressure. A pool of N caps the service’s concurrent load on the database at N. Requests beyond that wait in the application, where you can see and bound the wait, instead of piling onto the database.
• Server memory. Every Postgres connection is a backend process with its own memory, and the default max_connections is 100. Connections are not free to open even when they are idle.

The mechanism: Little’s law, and where the queue lives

Connections in use follow Little’s law: in use = request rate × time each request holds a connection.

200 req/s × 10 ms held   =   2 connections busy on average
200 req/s × 500 ms held  = 100 connections busy on average

The hold time, not the request rate, is the lever: the same traffic needs 50 times more connections when each request keeps its connection for the whole request instead of the query.

The second half: a database executes roughly as many queries at once as it has cores (plus some I/O overlap). Connections beyond that do not add throughput; the extra queries wait inside Postgres, contending for locks, CPU and memory. So the pool should be small and the queue should live in the application, where pool_timeout bounds it. PostgreSQL’s own starting point is (cores × 2) + effective spindles; measure from there.

The multiplication that breaks max_connections

Every process has its own pool:

12 pods × 4 gunicorn workers × (pool_size 10 + max_overflow 10)
  = 960 possible connections, against max_connections = 100

Nothing fails at deploy. The first traffic spike that fills the overflow gets FATAL: sorry, too many clients already. Size per process from the total: instances × processes × (pool_size + max_overflow) < max_connections, with headroom for migrations and admin sessions, or put PgBouncer in between.

Holding the connection is the bug

The most common pool outage is not a small pool. It is a request that holds its connection while doing something slow that is not a query, usually an external API call. SQLAlchemy 2.1.4’s QueuePool with five connections and twenty concurrent requests that each make a 1-second outbound call:

def handler():
    with engine.connect() as conn:          # checked out for the whole request
        conn.execute(text("select ..."))
        call_payment_api()                  # 1 s, holding the connection
        conn.execute(text("update ..."))

connection held across the 1 s call: 5/20 ok in 1.0 s
  e.g. QueuePool limit of size 5 overflow 0 reached, ...
connection released before the call: 20/20 ok in 1.0 s

Same pool, same traffic, same latency for the successful requests. Releasing the connection between the read and the write let all twenty through, because each held a connection for milliseconds instead of a second. In FastAPI the usual culprit is a get_db dependency that yields one session for the whole request: the connection is checked out at the first query and returned when the response is sent, so every slow await in between holds it.

Short sessions around the slow call

async def create_payment(order_id: int):
    async with SessionLocal() as db:        # short session: read
        order = await db.get(Order, order_id)
    receipt = await payments.charge(order)  # no connection held
    async with SessionLocal.begin() as db:  # short session: write
        db.add(Payment(order_id=order_id, receipt=receipt.id))

The split needs the write to be safe on its own (an idempotency key on the charge, a unique constraint on the payment), which is the honest cost of not holding a transaction across the network.

SQLAlchemy’s knobs

engine = create_engine(
    url,
    pool_size=10,         # kept open
    max_overflow=10,      # extra under burst, closed when returned
    pool_timeout=30,      # wait this long for a free connection, then raise
    pool_recycle=1800,    # replace connections older than 30 min
    pool_pre_ping=True,   # test each checkout; survives failovers and idle kills
)

• pool_pre_ping costs a round trip per checkout and removes “server closed the connection unexpectedly” after a failover or an idle-timeout kill by a proxy.
• pool_recycle must be below any idle timeout between you and the database (cloud proxies, NAT gateways, load balancers).
• pool_timeout firing is the signal: something holds connections too long, or the pool is genuinely too small, in that order of likelihood.

Async engines pool the same way; a forked worker must not inherit a parent’s pooled sockets (engine.dispose() after fork). Both are in SQLAlchemy connection pooling.

PgBouncer

PgBouncer is a separate process that multiplexes many client connections onto a few server connections, so hundreds of app processes share one small pool.

Transaction mode is the usual choice. What breaks is anything that outlives a transaction on a server connection.

Session-level SET breaks (use SET LOCAL, which is transaction-scoped and fine), and so do session advisory locks, LISTEN/NOTIFY, WITH HOLD cursors and SQL-level PREPARE.

Protocol-level prepared statements, the kind drivers use, are supported since PgBouncer 1.21 and on by default since 1.24 (max_prepared_statements = 200), so disabling the driver’s statement cache is often no longer needed; check your driver against PgBouncer’s prepared-statement notes before relying on it.

Where it breaks in production

• A connection held across slow work. The demo above: an external call, a large file upload, or an LLM request inside an open session.
• Leaks. A code path that never returns the connection: a missing with, an early return before close(), an exception path. Each one shrinks the pool by one until a restart.
• Too many clients. The multiplication above, discovered at the first traffic spike after scaling out.
• Stale connections. After a failover or an idle kill, the first query on each pooled connection fails. pool_pre_ping and pool_recycle below the idle timeout.
• No statement timeout. One runaway query holds a connection, blocks vacuum and migrations, and the pool drains behind it:

ALTER ROLE app SET statement_timeout = '30s';     -- Postgres default: 0, unlimited

Diagnosing exhaustion

Look at what the connections are doing before touching the pool size:

SELECT pid, state, now() - xact_start AS in_transaction, left(query, 60)
FROM pg_stat_activity
WHERE datname = current_database() AND state <> 'idle'
ORDER BY xact_start;

Rows in idle in transaction for seconds are the held-connection bug: a session that ran a query and is now waiting on something else. Long active rows are slow queries (Reading a query plan). Only when neither explains it is the pool genuinely too small.

The trade not taken: a bigger pool

The reflex fix for QueuePool limit reached is pool_size=100 and max_connections=1000. It moves the queue from the application, where it was visible and bounded, into Postgres, where a thousand backends contend for the same cores and each costs memory. Throughput stays flat or falls and latency gets worse for everyone. The held-connection bug is still there, now with more room to grow.

The fix that holds: shorten hold times first, keep pools near the database’s real concurrency, and put PgBouncer in front when the process count, not the query load, is what exceeds max_connections.

Related

• SQLAlchemy connection pooling - pool classes, async engines, fork safety
• N+1 query problem - N+1 queries, a common way to lengthen hold time
• Reading a query plan - finding the slow query holding the connection
• aiohttp — ClientSession Lifecycle and Connection Pooling - the same pooling idea for outbound HTTP