Observing Connection Pool Exhaustion

Connection pool exhaustion has one of the most misleading signatures in backend performance: every query in the trace is fast, CPU is low, the database reports itself healthy, and requests are slow. The waiting happens in the call that acquires a connection, before any query span starts, so query-level instrumentation shows nothing at all. This page covers measuring acquisition, hold time and the pool's own counters, and finding what is holding connections too long — which is almost always the real problem. It is a task article under database and I/O performance observability, part of the Python profiling and performance observability section.

Prerequisites

pip install "sqlalchemy>=2.0.0,<3.0.0" \
            "prometheus-client>=0.20.0,<1.0.0" \
            "opentelemetry-api>=1.27.0,<2.0.0"

Implementation

Step 1 — Time acquisition as its own span and histogram. The pool's checkout is the step to measure, and it must be measured from the caller's side, because the pool's own events fire after the wait is over. Wrapping connection acquisition gives both a span — so the wait appears in the trace as a named interval instead of a gap — and a histogram for alerting.

import time
from contextlib import contextmanager

from opentelemetry import trace
from prometheus_client import Histogram

ACQUIRE = Histogram(
    "db_pool_acquire_seconds", "Time waiting for a pooled connection", ["pool"],
    buckets=(0.0005, 0.001, 0.005, 0.01, 0.05, 0.1, 0.25, 0.5, 1, 2.5, 5))
tracer = trace.get_tracer(__name__)


@contextmanager
def connection(engine, pool_name: str = "main"):
    started = time.perf_counter()
    with tracer.start_as_current_span("db.pool.acquire") as span:
        conn = engine.connect()
        waited = time.perf_counter() - started
        span.set_attribute("db.pool.wait_ms", round(waited * 1000, 2))
    ACQUIRE.labels(pool_name).observe(waited)
    try:
        yield conn
    finally:
        conn.close()                    # returns it to the pool

Step 2 — Measure hold time. How long each connection stays checked out is the number that usually explains exhaustion. A pool of ten connections serving requests that hold them for four milliseconds supports thousands of requests per second; the same pool serving requests that hold them for eight hundred supports about twelve. The pool's checkout and checkin events bracket exactly that interval.

from sqlalchemy import event
from prometheus_client import Gauge, Histogram

HOLD = Histogram("db_pool_hold_seconds", "Checkout to checkin", ["pool"],
                 buckets=(0.001, 0.005, 0.01, 0.05, 0.1, 0.25, 0.5, 1, 2.5, 5, 10))
IN_USE = Gauge("db_pool_in_use", "Connections checked out", ["pool"])
SIZE = Gauge("db_pool_size", "Configured pool size plus overflow", ["pool"])

SIZE.labels("main").set(engine.pool.size() + engine.pool._max_overflow)

@event.listens_for(engine, "checkout")
def _on_checkout(dbapi_conn, record, proxy):
    record.info["checked_out_at"] = time.perf_counter()
    IN_USE.labels("main").set(engine.pool.checkedout())

@event.listens_for(engine, "checkin")
def _on_checkin(dbapi_conn, record):
    started = record.info.pop("checked_out_at", None)
    if started is not None:
        HOLD.labels("main").observe(time.perf_counter() - started)
    IN_USE.labels("main").set(engine.pool.checkedout())

Step 3 — Chart in-use against the ceiling. The two gauges on one chart turn exhaustion into a line touching its limit. An average hides it: a pool that is saturated during every peak and idle overnight averages to a comfortable number.

Step 4 — Count timeouts and overflow. SQLAlchemy's pool raises a timeout error when acquisition exceeds its pool_timeout, and creates overflow connections beyond the pool size up to max_overflow. Both are the pool announcing it is under pressure. Counting timeouts is essential, since they are errors users see; counting overflow is the early warning, because overflow happens well before timeouts do.

Expected Output: the four signals during an exhaustion episode.

db_pool_size{pool="main"}                 15
db_pool_in_use{pool="main"}               15
db_pool_acquire_seconds p99               1.84
db_pool_hold_seconds p99                  0.82
db_pool_timeouts_total                    37

In use at the ceiling, acquisition near two seconds, and hold time of eight hundred milliseconds for queries that take a few — the combination says the pool is being held rather than used.

Step 5 — Attribute hold time to code paths. Knowing hold time is long is half the diagnosis; knowing which route holds connections longest is the other half. Recording hold time on the current span, or labelling the histogram with a bounded route template, identifies the handler. The usual culprits are a transaction opened at the start of a request and committed at the end, with slow non-database work in between — an external API call, template rendering, a file upload.

Sizing the pool once holds are short

Once hold time reflects actual database work, the pool can be sized from arithmetic rather than guesswork, and the arithmetic has two constraints that pull in opposite directions.

The first is demand. By Little's law, the average number of connections in use equals the rate of checkouts multiplied by the average hold time. A service making two hundred checkouts per second with a hold time of ten milliseconds uses two connections on average. The pool needs headroom above the average for bursts, and a common target is keeping peak usage around seventy percent of the pool size.

The second is the database's capacity, which is shared. Every replica of every service contributes its pool size, plus overflow, to the total connection count the database must accept. Forty replicas with pools of fifteen and overflow of ten can open a thousand connections, which exceeds the default limit of many database servers and would degrade the database well before reaching it. The per-service pool therefore has to fit a fleet-level budget, and raising it to fix one service's exhaustion can cause connection failures in every other service.

Where the two constraints conflict — the demand genuinely needs more connections than the budget allows — the answer is a connection pooler in front of the database, which multiplexes many application connections onto fewer server connections. That moves the constraint rather than removing it, and the same measurements apply at the pooler.

The order of operations that avoids most of these problems is: measure hold time, shorten it, then size the pool from the new numbers. Doing it in the other order — enlarging the pool first — hides the long holds, consumes the database's shared budget, and leaves the underlying problem to reappear at the next increase in traffic.

Async pools and the invisible queue

Async database drivers change one aspect of this picture, and it makes exhaustion harder rather than easier to see.

A synchronous pool that is exhausted blocks the calling thread, so the waiting is visible as threads parked in the pool's acquisition path — a wall-clock profile shows it, and a thread count climbing towards the server's limit shows it. An async pool that is exhausted suspends the calling coroutine instead. No thread is consumed and nothing is parked in a way a profiler would notice; the event loop continues serving other coroutines, and the waiting ones accumulate silently in the pool's internal queue.

That makes the acquisition span from step 1 even more important, since it is often the only evidence. It also makes a second measurement worthwhile: the number of coroutines currently waiting for a connection. Most async pools expose it or can be wrapped to track it, and a count that climbs during peaks is the direct equivalent of the queue wait in observing thread pool saturation.

There is one further async-specific hazard. A coroutine that acquires a connection and then awaits something slow — an HTTP call, a message queue, another coroutine — holds that connection across the await, exactly as a synchronous handler holds it across a blocking call. The async version is easier to write accidentally, because an await inside an async with block looks harmless. The hold time histogram catches it the same way.

Configuration options

Verification

Confirm the change by comparing hold time and acquisition wait under the same load.

histogram_quantile(0.99, rate(db_pool_hold_seconds_bucket{pool="main"}[10m]))
histogram_quantile(0.99, rate(db_pool_acquire_seconds_bucket{pool="main"}[10m]))

Expected Output: hold time falling to roughly the query time, and acquisition wait falling to near zero.

before  hold p99 0.82 s   acquire p99 1.84 s
after   hold p99 0.011 s  acquire p99 0.0004 s

Common mistakes

Instrumenting only queries. Error signature: slow requests whose traces contain a long unexplained gap before fast queries. Root cause: the wait is in acquisition, which has no span. Remediation: wrap acquisition as in step 1.

Enlarging the pool first. Error signature: exhaustion returning at the next traffic increase, and other services failing to connect. Root cause: long holds hidden rather than fixed, and the database's shared budget consumed. Remediation: measure and shorten hold time before resizing.

Transactions spanning slow external work. Error signature: hold time far above query time, and idle-in-transaction connections on the database. Root cause: a transaction opened at request start and committed at the end. Remediation: release connections before external calls and reopen for the final write.

Averaging the in-use gauge. Error signature: a pool that looks half-used and times out every afternoon. Root cause: averages hide peaks. Remediation: chart the gauge at full resolution against the ceiling.

No timeout. Error signature: requests hanging for tens of seconds during database trouble. Root cause: acquisition waiting indefinitely. Remediation: set pool_timeout so callers fail quickly and the failure is counted.

Frequently Asked Questions