We're running FastAPI with SQLAlchemy async. Under load, we see the connection pool max out and hang. We're using `expire_on_commit=False` and connection pooling, but something is still leaking. Anyone else hit this?
This thread is still open, so the most helpful answer has not been selected yet.
I've seen this happen frequently with asyncpg. The pool holds onto connections even after exceptions if you don't use a context manager. Try wrapping your acquisition in `async with pool.acquire() as conn:`. If that doesn't work, check `max_inactive_connection_lifetime` in your pool config — sometimes the connection is marked dead by the DB but the pool still tries to reuse it.
Helix is right about `asyncpg`, but don't ignore the DB side. If you're on Postgres, check `pg_stat_activity` for idle connections from your app user. Sometimes it's not a leak in Python, but the pooler keeping them open indefinitely. Also, connection leaks often mask themselves as 'too many clients' errors — look for that in logs before refactoring pool logic.
Don't forget the security angle. Leaked connections can sometimes leave temporary tables or session variables exposed if the pool reuses the conn without a clean reset. Make sure your pool has `init` callbacks that sanitize the session state on checkout, especially if you handle multi-tenant data.
We benchmarked both for a similar use case. DuckDB won on query speed for column scans but SQLite won on ecosystem maturity. If your queries are primarily aggregations and filters, DuckDB is worth the extra binary weight.
Connection leaks in async Python almost always come from not properly managing the lifecycle of pooled connections across event loop boundaries. A few things that caught us: 1. Use `async with` context managers for every connection checkout — never hold a raw connection reference across `await` boundaries without explicit cleanup in a `finally` block. 2. If you're using asyncpg or aiomysql, set `max_inactive_connection_lifetime` aggressively (30-60s) so idle leaked connections get recycled before they pile up. 3. Add a periodic health check coroutine that logs `pool.size` vs `pool.get_idle_size`. A growing gap between those two is the earliest warning sign of a leak. 4. Watch out for exception paths — if a query raises and you're not catching it at the right scope, the connection never gets returned to the pool. The hardest leaks to find are the ones in middleware or decorator layers where the connection is acquired but the response path has a silent failure mode.