Connection Hold Time

A team sizes its database pool from query latency: 400 requests per second, 15 ms of SQL, so six connections plus headroom. In production the pool of 20 is permanently exhausted while the database reports 2 ms queries and 8% CPU.

The handler takes a connection on entry and returns it on exit, and in between it calls a partner API that takes 300 ms. The connection is held for 320 ms and used for 15 of them. Little's Law sizes the pool from hold time, so the real requirement is 400 × 0.32 ≈ 128.

Hold time is the metric that makes this visible: not how long a query takes, but how long a connection is withheld from everyone else.

Why it matters

Pool size is one of the few capacity numbers that fails in both directions. Too small and requests queue on checkout; too large and the database drowns in backend processes. A PostgreSQL backend costs on the order of 5–10 MB of private memory, so 128 connections on each of 40 instances is roughly 5,000 backends and tens of gigabytes before a single query runs.

Hold time is also the quantity a transaction-mode pooler needs to be small. Multiplexing a server connection between transactions is highly effective when transactions are short and useless when one request keeps a transaction open across a call it does not control.

Implementation patterns

• Instrument it directly. A histogram of checkout-to-release duration, per endpoint, beside the query histogram. The gap between the two is the number this term exists to expose.
• Acquire late and release early. Read what you need, release, make the external call, reacquire to write: two short transactions instead of one long one.
• Never hold across an uncontrolled call. Partner APIs, object storage, a queue publish, an LLM inference call — each can move from 50 ms to 5 s without warning.
• Replace the long transaction with a state machine when the write must be consistent with an external outcome: persist an intent, call out, record the result idempotently, reconcile stragglers.
• Alert on pool utilisation and checkout wait rather than database CPU, and keep the checkout timeout below the request deadline.

Industry example

The canonical shape appears in any checkout flow that calls a payment provider inside its own transaction. A commerce service of the kind that runs flash sales holds a row lock while a card authorisation runs: the lock duration becomes the authorisation's p99, the hold time becomes the provider's tail, and a provider slowdown from 200 ms to 3 s multiplies the pool requirement by 15 at unchanged traffic. The incident presents as a database outage, and the database is innocent.

The tooling encodes the lesson: PgBouncer has offered a transaction mode since the late 2000s precisely because applications hold connections far longer than they use them, and its documentation is explicit that the mode is incompatible with session state held across a request. A pooled connection is shared state rather than a performance detail.

Failure scenarios

• Checkout exhaustion with an idle database, so the team adds instances, each with its own pool, and the database starts refusing connections instead.
• Connections leaked on an exception path, where hold time per leaked connection is effectively infinite and the pool drains over hours — a soak-test finding, not a load-test one.

Trade-offs

Splitting a handler into two transactions gives up atomicity and buys a 20× smaller pool. That exchange is usually right and not free: there is now a window where the intent is persisted and the outcome is not, so an idempotency key and a reconciliation job become part of the feature.

Keeping one long transaction buys simple correctness and pays in pool size, in coupling your capacity to someone else's latency, and in giving up transaction-mode pooling at the proxy layer.

When not to use it

If every call inside the hold window is local and bounded, this distinction is noise. A handler that holds a connection for 20 ms to do 15 ms of SQL does not need splitting, and the two-transaction pattern would add a reconciliation path for no gain.

A replication connection or a streaming cursor is held deliberately for hours, and the instrument there is a dedicated connection budget.

Interview question

Q: "Our pool is sized from measured query latency and it is exhausted in production while the database is at 10% CPU. Where do you look, and what is the fix you would ship this week?"

What a strong answer covers: the difference between query time and hold time; L = λW with W as hold time; instrumenting checkout duration per endpoint to find the worst handler; acquiring late and releasing early as the week-one fix; why raising the pool moves the queue into the database and costs memory per backend; and the move to a state machine if the transaction genuinely spans the external call.

Quick check

Quiz: 400 requests per second, 15 ms of SQL, and a 300 ms partner call inside the transaction. Pool size? About 128, versus about 6 if the connection is released around the call.

Flashcard: Which W belongs in L = λW when sizing a connection pool? — The hold time from checkout to release, including every wait inside it, not the query execution time.