Fixing Connection Pool Exhaustion in a Production Python API

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 is the error that turns a good launch day into a bad one. Your API was fine at ten requests a second and now every endpoint hangs for thirty seconds and then returns a 500, including the health check, which means your host starts killing containers and replacing them with fresh ones that immediately do the same thing. This page walks the whole diagnosis: what the message actually says, the arithmetic that decides how many connections you may open, the leak that causes most real cases, and the timeout settings that turn a hang into a fast, honest failure. It is part of the Async Database Access with SQLAlchemy guide.

The decision this page resolves is the one everybody gets wrong under pressure: raise pool_size, or find the leak. Raising the pool is the instinct, it works for about forty minutes, and then it fails harder because you have moved the bottleneck onto the database server where recovery is slower. Find the leak first. Almost always there is one.

What the error is really telling you

SQLAlchemy's QueuePool is a fixed set of connection slots per process. pool_size is the number kept open permanently. max_overflow is how many extra connections it may open temporarily under load, closing them again when they go idle. Total capacity per process is the sum. When every slot is checked out, the next coroutine that asks for a session does not get an error — it waits, quietly, for up to pool_timeout seconds. Only after that does it raise.

That waiting is why exhaustion feels like a mystery outage rather than a database problem. Latency goes vertical, CPU sits near zero, the database reports a handful of active queries, and nothing in the logs mentions the database until the timeouts start. The system is not busy. It is queueing.

Before you change a setting, get the pool to describe itself. SQLAlchemy exposes live counters on the pool object and emits checkout events you can time. Twenty lines of instrumentation tell you in ten minutes what a day of guessing will not.

import os
import time
from sqlalchemy import event
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(os.environ["DATABASE_URL"])
SLOW_HOLD_SECONDS = float(os.getenv("DB_SLOW_HOLD_SECONDS", "2.0"))

@event.listens_for(engine.sync_engine, "checkout")
def _on_checkout(dbapi_conn, record, proxy) -> None:
    record.info["checked_out_at"] = time.monotonic()

@event.listens_for(engine.sync_engine, "checkin")
def _on_checkin(dbapi_conn, record) -> None:
    started = record.info.pop("checked_out_at", None)
    if started is None:
        return
    held = time.monotonic() - started
    if held > SLOW_HOLD_SECONDS:
        print(f"connection held {held:.2f}s — suspect a leak or a slow query")

def pool_stats() -> dict[str, int]:
    """Expose on an internal route; scrape it every 15 seconds."""
    pool = engine.pool
    return {
        "size": pool.size(),
        "checked_out": pool.checkedout(),
        "overflow": pool.overflow(),
        "waiters": pool.checkedout() - pool.size(),
    }

Read the two numbers together. If checked_out sits at maximum while your database reports only two or three active queries, connections are being held by code that is not querying — that is a leak. If checked_out is at maximum and the database shows the same number of long-running queries, your pool is fine and your queries are slow. Those two diagnoses lead to opposite fixes, and guessing between them is what makes these incidents last for days. Pipe the counters into your existing pipeline; structured logging with structlog makes them queryable rather than decorative.

The per-worker maths nobody does until it breaks

Here is the arithmetic that catches every builder exactly once. SQLAlchemy's pool is per process, not per container and certainly not per application. Run four Uvicorn workers behind Gunicorn and you have four independent pools. Scale to three containers and you have twelve. With the default-ish pool_size=5, max_overflow=5, that fleet demands 120 backend connections from a Postgres whose max_connections is 100, three of which are reserved for superusers.

The database does not queue politely like SQLAlchemy does. It refuses: FATAL: sorry, too many clients already. And it refuses your migration job and your psql session too, which is how a capacity problem becomes an outage you cannot log into.

So compute the pool from the fleet, not from a blog post default. Reserve headroom for migrations, your own psql sessions, and whatever monitoring agent your host attaches, then divide what is left by the number of processes you will ever run at peak.

import os

def pool_dimensions() -> tuple[int, int]:
    """Split the database's usable connections across every worker process."""
    max_conns = int(os.getenv("DB_MAX_CONNECTIONS", "100"))
    reserved = int(os.getenv("DB_RESERVED_CONNECTIONS", "13"))  # superuser + ops + migrations
    containers = int(os.getenv("MAX_CONTAINERS", "3"))
    workers = int(os.getenv("WEB_CONCURRENCY", "4"))

    budget = max(max_conns - reserved, 1)
    per_process = max(budget // (containers * workers), 2)
    pool_size = max(per_process * 2 // 3, 1)
    return pool_size, per_process - pool_size

POOL_SIZE, MAX_OVERFLOW = pool_dimensions()

With max_connections at 100, thirteen reserved, three containers and four workers, that yields two persistent connections and one overflow per process: twelve processes, thirty-six connections at peak. It looks alarmingly small next to the default. It is correct. An async worker multiplexes thousands of concurrent requests over a handful of connections precisely because most of a request is not spent talking to the database — and if your requests genuinely need more than two each, you have a query problem, not a pool problem. The Uvicorn vs Gunicorn worker configuration comparison covers the process count that feeds this formula.

The leak: sessions kept alive by background work

Here is the cause of most real exhaustion incidents, and it looks completely innocent. A route takes the request-scoped session from a dependency, hands it to a BackgroundTasks callback or a bare asyncio.create_task, and returns a response. FastAPI runs background tasks after the response is sent — but the dependency's teardown also runs after the response, and now two things race over the same session. Worse, if the task holds the session for eight seconds writing an audit row and calling Stripe, that connection is checked out for eight seconds instead of forty milliseconds. At twenty requests a second, you need 160 connections to sustain that, and you have three.

The fix is a rule with no exceptions: a session belongs to the scope that opened it. Anything running after the response opens its own session from the same async_sessionmaker, uses it inside a context manager, and closes it. The corrected version below also does the slow HTTP call outside the session, so the connection is held only for the write.

import os
import httpx
from fastapi import BackgroundTasks, Depends, FastAPI
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

engine = create_async_engine(os.environ["DATABASE_URL"])
Session = async_sessionmaker(engine, expire_on_commit=False)
app = FastAPI()

async def get_session() -> AsyncSession:
    async with Session() as session:
        yield session

async def record_usage(account_id: str, units: int) -> None:
    """Owns its own session. Never accepts one from a request."""
    async with httpx.AsyncClient(timeout=10.0) as client:
        await client.post(
            os.environ["METERING_WEBHOOK_URL"],
            json={"account_id": account_id, "units": units},
        )
    async with Session() as session:          # opened after the slow call
        async with session.begin():
            await session.execute(
                USAGE_INSERT, {"account_id": account_id, "units": units}
            )

@app.post("/v1/render")
async def render(
    account_id: str,
    tasks: BackgroundTasks,
    session: AsyncSession = Depends(get_session),
) -> dict[str, str]:
    await session.execute(TOUCH_ACCOUNT, {"account_id": account_id})
    await session.commit()
    tasks.add_task(record_usage, account_id, 1)   # pass data, never the session
    return {"status": "queued"}

Three more leaks worth grepping for. A Depends that returns a session without async with or a finally block never closes it on an exception path. A module-level session = Session() shared across requests holds one connection forever and corrupts state between users. And any synchronous call inside an async route — a blocking SDK, time.sleep, a requests call — pins the event loop while every checked-out connection sits idle. If the background work is heavier than a single insert, stop using in-process tasks entirely and move to running background jobs with Celery, where the worker has its own pool and its own failure budget.

Timeouts that fail fast instead of hanging

The default pool_timeout of thirty seconds is hostile in production. It converts a capacity problem into thirty seconds of held sockets, held memory, and a client that has already given up and retried twice — tripling the load that caused the problem. Set it to five seconds and return a 503 with Retry-After. Customers get a clear signal, retry storms shrink, and your load balancer stops routing to a container that cannot serve. Pair it with a server-side statement timeout so one accidental sequential scan cannot hold a slot for minutes, and a pool_recycle below whatever idle cutoff your host enforces.

import os
from sqlalchemy.ext.asyncio import create_async_engine

STATEMENT_TIMEOUT_MS = os.getenv("DB_STATEMENT_TIMEOUT_MS", "5000")

engine = create_async_engine(
    os.environ["DATABASE_URL"],
    pool_size=POOL_SIZE,
    max_overflow=MAX_OVERFLOW,
    pool_timeout=float(os.getenv("DB_POOL_TIMEOUT", "5")),
    pool_recycle=int(os.getenv("DB_POOL_RECYCLE", "1800")),
    pool_pre_ping=True,
    connect_args={
        "timeout": float(os.getenv("DB_CONNECT_TIMEOUT", "5")),
        "server_settings": {
            "statement_timeout": STATEMENT_TIMEOUT_MS,
            "idle_in_transaction_session_timeout": os.getenv("DB_IDLE_TXN_MS", "10000"),
        },
    },
)

idle_in_transaction_session_timeout is the underrated one. It kills any connection that opened a transaction and then went to sleep — the exact state a leaked session leaves behind — so the database reclaims what your process forgot. The connect_args shown target asyncpg; psycopg 3 spells them differently, which the asyncpg vs psycopg3 comparison sets out.

Finally, translate the exception into a proper HTTP answer rather than a generic 500:

from fastapi import Request
from fastapi.responses import JSONResponse
from sqlalchemy.exc import TimeoutError as PoolTimeout

@app.exception_handler(PoolTimeout)
async def pool_timeout_handler(request: Request, exc: PoolTimeout) -> JSONResponse:
    return JSONResponse(
        status_code=503,
        content={"error": "database_busy", "retry_after": 2},
        headers={"Retry-After": "2"},
    )

When to raise the pool, and when to refuse

Raise pool_size only when three things are true at once: holds are short, the database shows the same number of active queries as your checked-out count, and the fleet total still fits the server budget. That is the right-hand branch of the tree above and it is the rarest of the three.

Below roughly fifty requests per second, a correctly written async API on a small managed Postgres does not need a pooler at all — two or three connections per process is genuinely enough, and adding PgBouncer buys you an extra hop and a transaction-mode compatibility problem for nothing. Above that, or the moment autoscaling can multiply your process count, put a pooler in front and set the application pool small. Deploy-time failures are a different animal: if old containers keep their connections while new ones open theirs, you briefly need double, which is why zero-downtime deploys need a drain step, not just a health check.

The transition path, in order

Do these in sequence, and stop as soon as the graph flattens — most teams never reach step five.

1. Ship the checkout instrumentation and the pool_stats route. Do not change any setting yet. Watch for one traffic peak.
2. Cut pool_timeout to five seconds and add the 503 handler. This makes failures fast and visible instead of silently doubling your latency.
3. Grep for every Session( and Depends(get_session) that crosses a response boundary or a create_task. Fix them to own their session. This is usually the whole incident.
4. Apply the per-process formula and set statement_timeout and idle_in_transaction_session_timeout. Verify the fleet total against max_connections explicitly.
5. Only now, if reads dominate, add a cache in front of the hottest queries — caching API responses with Redis removes more database load than any pool tuning will.
6. If you are still at the limit, add a transaction-mode pooler and shrink the application pool further.

Cover the leak with a test so it never returns. Fire fifty concurrent requests against a pool of two, assert every response is a 200 or a 503, then assert pool.checkedout() returns to zero within a second — testing Python APIs with pytest has the async fixtures for it. That assertion catches a reintroduced leak in CI, which is the only place catching it is cheap.

Builder verdict

Fix the leak, shrink the pool, and cut the timeout. That is the order, and it is nearly always the answer. The instinct to raise pool_size is wrong because it treats a symptom that costs you nothing to observe and buys a failure that costs you a database restart — and every builder who has upgraded a Postgres instance to fix a leak has paid forty dollars a month, permanently, for a bug that a finally block would have closed. Small pools plus fast timeouts also make your capacity legible: when three connections per process saturate, you know exactly what a fourth container buys you, which is the number you need to price a plan properly. Work it through with calculating cost per API request once the graph is flat.

Spend the afternoon on instrumentation before you spend a dollar on a bigger database. Checkout timing and a pool-stats endpoint take twenty minutes to write, survive every future incident, and turn the next exhaustion event from a day of guessing into a ten-minute diagnosis.

FAQ

Does fixing pool exhaustion save real money, or just latency? Both, and the money is larger than it looks. The usual reflex is to upgrade the database tier so max_connections doubles, which costs forty to two hundred dollars a month forever. A leaked session fixed in one afternoon removes that spend permanently, and the smaller pool often lets you run fewer containers because requests stop piling up behind the queue.

How many connections should each worker really get? Take your database's max_connections, subtract about thirteen for superuser slots, migrations, and monitoring, then divide by the maximum number of worker processes you will ever run at peak. Two to five per process is normal and correct for an async API. If that feels too small, measure your hold times before adding capacity — a well-behaved request holds a connection for under fifty milliseconds.

Is adding PgBouncer worth the migration risk? Not until autoscaling can push your process count past the server budget. It adds a hop, and transaction pooling mode breaks prepared statements unless you configure the driver for it. Below fifty requests per second, shrink the application pool instead: same result, no new component to operate or debug at midnight.

Can I just raise pool_timeout so requests stop failing? No — that is the worst available change. A longer timeout means clients wait, give up, and retry, multiplying the load that caused the exhaustion. Five seconds plus a 503 with Retry-After sheds load and keeps your health check honest so the platform can route around a sick container.

Will this break customers during the migration to smaller pools? Only if you shrink the pool before fixing the leak. Deploy in the documented order — instrumentation, timeout, leak fix, then sizing — and each step is independently reversible via environment variables. Roll one container first and watch checked-out counts for an hour before applying the change fleet-wide.

Related

Same track:

• Async Database Access with SQLAlchemy — the parent guide covering engine lifecycle, per-request transactions, and pool defaults.
• asyncpg vs psycopg3 for FastAPI — driver choice decides how your connect args and pooler compatibility are spelled.
• Caching Python API Responses with Redis — the fastest way to cut database load once the leak is closed.
• Running Background Jobs with Celery — move heavy post-response work out of the request process and off its pool.

Other tracks:

• Uvicorn vs Gunicorn Worker Configuration — the process count that drives the per-worker connection maths.
• Render vs Railway vs Fly.io — how each host's autoscaling multiplies your pool without telling you.