Fixing asyncpg Connection Pool Exhaustion in FastAPI

Key takeaways:

  • Exhaustion means connections are not returned fast enough — a leak or a sizing issue.
  • Scope every session through a yield dependency so it always returns to the pool.
  • Keep slow, non-database work outside the transaction.
  • Size pool_size + max_overflow so total connections across workers fit the database limit.
  • Monitor checkouts and add a timeout to surface leaks early.

This guide is the failure-mode deep dive for Async Database Sessions. Read that page for the engine-and-session setup.

The Problem This Solves

Under load, requests start timing out with "QueuePool limit reached" or connection-acquire timeouts, even though the database itself is healthy. The pool is drained because connections are being held too long or never returned. This guide finds which and fixes it.

Prerequisites

  • An async SQLAlchemy engine on asyncpg.
  • Visibility into pool metrics or the ability to add them.

Step-by-Step Implementation

1. Ensure yield-scoped sessions

from collections.abc import AsyncGenerator

from sqlalchemy.ext.asyncio import AsyncSession


async def get_session(request) -> AsyncGenerator[AsyncSession, None]:
    async with request.app.state.session_factory() as session:
        # Context manager guarantees the connection returns, even on error.
        yield session

2. Keep transactions short

# Bad: external call inside the transaction holds the connection for its duration.
async def bad(session, order_id):
    async with session.begin():
        order = await session.get(Order, order_id)
        await charge_external_api(order)   # Connection idle-but-held during this.

# Good: do external work outside the transaction.
async def good(session, order_id):
    order = await session.get(Order, order_id)
    await charge_external_api(order)       # Connection already free.
    async with session.begin():
        order.status = "charged"

3. Size the pool deliberately

from sqlalchemy.ext.asyncio import create_async_engine

# Per worker: at most 20 in-flight connections. Across 4 workers that is 80 —
# keep it under the database's max_connections with headroom.
engine = create_async_engine(url, pool_size=15, max_overflow=5, pool_timeout=10)

4. Surface leaks with a timeout and metrics

# pool_timeout raises promptly instead of hanging, making leaks visible.
# Export the pool's checked-out count as a gauge for alerting.
gauge.set(engine.pool.checkedout())

Edge Cases and Gotchas

  • Background workers. They have their own pool; include them in the connection budget.
  • Long-lived sessions. A session stored beyond a request never returns; keep them request-scoped.
  • Migrations. A migration tool opening connections during a deploy can tip a tight budget over; leave headroom.

Verification

async def test_connections_return_under_load(app):
    pool = app.state.engine.pool
    before = pool.checkedout()
    # Fire many concurrent requests, then confirm checkouts return to baseline.
    await hammer(app, n=100)
    assert pool.checkedout() == before