Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQLAlchemy QueuePool Limit of Size 5 Overflow 10 Reached

Either the pool leaks or it is too small, and the two need different fixes. Close sessions on every path, size workers against max_connections, and use NullPool when short-lived.

~5 min readPython
#Python#SQLAlchemy#FastAPI#Performance
3D illustration of connection slots filled with glowing pips and a queue of waiting cubes

Introduction & Quick Summary

Symptom. Under load, the application starts returning 500s with sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/20/3o7r). Traffic is modest, the database shows few active queries, and restarting the app fixes it for a few minutes.

Cause. SQLAlchemy's QueuePool allows pool_size + max_overflow checkout connections at once — 15 by default — and then makes the next caller wait pool_timeout seconds before raising. The pool is exhausted when connections are checked out and never returned: a Session that is not closed on an exception path, a sync engine called from an async def endpoint (blocking the event loop while it waits), a session held across a slow external HTTP call, or many worker processes each running its own 15-connection pool against a database with a much smaller max_connections.

Fix. Find out whether the pool is leaking or undersized. Leaks appear at low traffic and grow over hours; undersizing appears as a sharp wall at a predictable concurrency. Close sessions with a context manager on every path, move blocking calls outside the session scope, use the async engine for async endpoints, and size the total (workers × (pool_size + max_overflow)) below the server's connection limit.

Core Mechanics, Signatures & Reproducible Setup

create_engine() defaults are the numbers in the error: pool_size=5, max_overflow=10, pool_timeout=30. A checkout blocks until a connection is free, and the timeout counts from the moment the session asks.

python
from sqlalchemy import create_engine, text from sqlalchemy.orm import Session engine = create_engine( "postgresql+psycopg://app:secret@db/app", pool_size=10, # connections kept open max_overflow=5, # extra connections allowed above pool_size pool_timeout=10, # seconds to wait for a free connection pool_pre_ping=True, # validates a connection before handing it out pool_recycle=1800, # drop connections older than 30 minutes ) with Session(engine) as session: # always returned, even on exception session.execute(text("select 1"))

A leak reproduces in ten lines and is the fastest way to confirm the diagnosis:

python
# Leaks one connection per call; run 16 times and the pool is gone def leaky(): session = Session(engine) # no context manager, no close() session.execute(text("select pg_sleep(0.01)")) for _ in range(16): leaky() # sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached

The database side shows the same thing, and distinguishing "checked out and idle" from "actually working" is the whole diagnosis:

sql
SELECT state, count(*) FROM pg_stat_activity WHERE datname = 'app' GROUP BY state; -- 15 rows 'idle' with an old state_change = sessions checked out but unused

Key Comparison Table

Two different diseases, one message:

SignalLeakUndersized pool
When it startsGrows over hours, after a deployAt a fixed concurrency
pg_stat_activityMany idle connections, old state_changeConnections busy, short queries
Restart helps?Yes, until the leak refillsOnly briefly, under the same load
FixClose sessions, shorten scopesRaise pool_size, reduce workers, add a pooler

Pool classes and when each is right:

PoolConnectionsServerless friendlyUse when
QueuePool (default)Reused, boundedNoLong-running servers
NullPoolOpen/close per checkoutYesLambda, Vercel functions, tests
StaticPoolOne shared connectionNoSQLite in-memory, single-threaded tools
AsyncAdaptedQueuePoolReused, bounded, asyncNocreate_async_engine with asyncpg/psycopg3
PgBouncer transaction modeMultiplexed server sideYesMany app instances on one database

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. FastAPI dependencies that never close

A dependency that yields a session is closed only if the generator is finalised. Exceptions inside the endpoint still finalise it, but a BackgroundTasks job that captures the session, or a task created with asyncio.create_task, outlives the request and keeps the connection checked out:

python
# Broken: the background task holds the request's session def endpoint(session: Session = Depends(get_session), bg: BackgroundTasks = None): bg.add_task(send_report, session) # Fixed: the task opens its own short-lived session def endpoint(bg: BackgroundTasks): bg.add_task(send_report) def send_report(): with Session(engine) as session: ...

2. Sync engine inside an async endpoint

A single sync SQLAlchemy call in an async def handler blocks the event loop for the whole query. With enough concurrent requests the pool drains and every request stalls, because none of them can reach the code that would release a connection:

python
# Broken: blocks the loop, starves the pool @app.get("/items") async def items(): with Session(engine) as s: return s.execute(select(Item)).scalars().all() # Option A: async engine and AsyncSession from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker aengine = create_async_engine("postgresql+asyncpg://app:secret@db/app", pool_size=10) SessionLocal = async_sessionmaker(aengine, expire_on_commit=False) @app.get("/items") async def items(): async with SessionLocal() as s: return (await s.execute(select(Item))).scalars().all() # Option B: keep sync code, run it in a thread from fastapi.concurrency import run_in_threadpool

3. Sessions held across slow external calls

A session checked out while the handler waits on a third-party API holds a database connection for the duration, so a 3-second upstream makes the pool look 3 seconds smaller than it is. Never span an HTTP call with an open session:

python
# Broken with Session(engine) as s: order = s.get(Order, order_id) receipt = requests.post(payments_url, json=payload).json() # seconds order.receipt_id = receipt["id"] s.commit() # Fixed: fetch, close, call, reopen with Session(engine) as s: order = s.get(Order, order_id) payload = build_payload(order) receipt = requests.post(payments_url, json=payload).json() with Session(engine) as s: s.execute(update(Order).where(Order.id == order_id).values(receipt_id=receipt["id"])) s.commit()

4. Workers multiply the pool

Four Gunicorn workers with the default pool can reach 60 connections; PostgreSQL's default max_connections is 100 and a managed instance may allow far fewer. Compute rather than guess, and let the server refuse connections loudly instead of the pool timing out silently:

python
# workers = 4, pool_size = 5, max_overflow = 2 -> 28 connections worst case engine = create_engine(DATABASE_URL, pool_size=5, max_overflow=2, pool_timeout=5)

If the total must stay small across many instances, put PgBouncer in transaction mode in front and use NullPool in the app — two pools stacked on top of each other multiply the wait, they do not divide it.

5. Serverless and short-lived processes

A function runtime freezes between invocations; a QueuePool in a frozen process holds connections the database still counts. Use NullPool (or a provider's pooler) so nothing is retained:

python
engine = create_engine(DATABASE_URL, poolclass=NullPool, pool_pre_ping=True)

For async runtimes the equivalent is create_async_engine(url, poolclass=NullPool). pool_pre_ping is still worth keeping: a fresh connection per invocation hits dead ones often.

6. Tests that exhaust the pool

A test suite that creates an engine per module and never disposes it accumulates pools. engine.dispose() in a fixture teardown, or one session-scoped engine with transactional rollback per test, keeps the connection count flat:

python
@pytest.fixture(scope="session") def engine(): eng = create_engine(TEST_DATABASE_URL, pool_size=2, max_overflow=0) yield eng eng.dispose()

max_overflow=0 in tests turns a leak into a fast, obvious failure instead of a slow timeout that looks like a flaky CI run.

Common Compilation/Runtime Mistakes & Version Gotchas

  • session.close() without try/finally. It is skipped exactly on the paths that matter. with Session(engine) as session: closes on exception and on early return.
  • Raising pool_size as the first response. It buys time and hides the leak; the next incident arrives at a slightly higher traffic level.
  • pool_size with NullPool or StaticPool. Both ignore it silently, so a "fix" that changes nothing is easy to ship.
  • pool_recycle shorter than the pooler's idle timeout. With PgBouncer or a cloud proxy, a recycled connection is cheaper than a query on a connection the server already closed; pool_pre_ping covers the gap that pool_recycle misses.
  • SQLAlchemy 2.0 autobegin. Any statement starts a transaction implicitly, so an uncommitted SELECT leaves the connection idle in transaction and holds locks. Commit or roll back before returning a connection to the pool.
  • expire_on_commit=True with async. Accessing an attribute after commit triggers lazy IO outside the session's scope, often inside a different task — a confusing MissingGreenlet that is really a session-scope bug.
  • Forked workers sharing an engine. Building the engine before gunicorn --preload forks copies the pool's sockets into every child; create the engine after the fork, or call engine.dispose(close=False) in the child.

Related tools. When a failing query's parameters are JSON, format them with the JSON formatter to see the exact shape the driver sent, and paste the two versions of a session block into the text diff tool to confirm the connection scope — not the SQL — is what changed.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.