Running Background Tasks Without Exhausting the SQLAlchemy Pool

This guide is part of FastAPI SQLAlchemy Pool Configuration. FastAPI’s BackgroundTasks run after the response is sent, in the same process and the same event loop. They are convenient for sending an email, writing an audit record or warming a cache — and they quietly compete with request handlers for the same connection pool. A task that reuses the request’s session is worse still: the dependency that created that session has already closed it by the time the task runs.

sqlalchemy.exc.ResourceClosedError: This Session's transaction has been rolled back
sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 5 reached,
connection timed out, timeout 30.00

The objective is background work that opens its own session, holds a connection only while it needs one, and cannot consume more than a defined share of the pool — with a clear threshold for when the work belongs in a queue worker instead.

Rapid incident diagnosis

Check whether tasks share the request’s session. The dependency pattern makes this easy to get wrong:

grep -rn "BackgroundTasks\|background_tasks.add_task" --include=*.py app/ | head
grep -rn "add_task(.*db\b\|add_task(.*session" --include=*.py app/ | head

Passing db or session into add_task is the bug: the session is closed when the request’s dependency exits, so the task either fails immediately or resurrects a closed session.

Measure the pool. SQLAlchemy’s pool reports its state synchronously:

p = engine.pool
print({"size": p.size(), "checked_in": p.checkedin(), "overflow": p.overflow(), "checked_out": p.checkedout()})

checkedout() at pool_size + max_overflow with request latency rising means the pool is the constraint; compare the number with the request concurrency to see whether background work accounts for the difference.

Attribute connections on the database. A separate application_name for background work makes the split visible at a glance:

SELECT application_name, state, count(*)
FROM pg_stat_activity WHERE application_name LIKE 'orders-%' GROUP BY 1, 2 ORDER BY 3 DESC;

Look for long tasks. Background work that runs for seconds — an export, a report, a fan-out of HTTP calls — holds a connection for its whole duration if it opens a session at the start and closes it at the end:

SELECT pid, state, now() - xact_start AS txn_age, left(query, 60)
FROM pg_stat_activity
WHERE application_name = 'orders-bg' AND xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

Mathematical sizing or parameter formula

Background work adds a second term to pool demand:

request_in_flight   = requests_per_second × queries_per_request × mean_query_seconds
task_in_flight      = tasks_per_second × mean_task_connection_seconds
pool_size + overflow ≥ ceil( (request_in_flight + task_in_flight) ÷ 0.7 )

The second term is usually the surprise. A task that runs for 400 ms holding one connection, fired once per request at 50 requests per second, needs 50 × 0.4 = 20 connections continuously — twice the default pool.

Bound the share explicitly. A semaphore is the simplest way to cap concurrent background database work:

max_concurrent_tasks ≤ (pool_size + max_overflow) − reserved_for_requests

With a pool of 10 plus 5 overflow and 10 reserved for requests, background tasks may use 5 at a time.

Know when to move the work. If the required background share approaches the request share, the work belongs in a separate process:

move_to_worker when: task_in_flight > request_in_flight ÷ 2
                  or mean_task_seconds > 1
                  or the task must survive a deploy

A task that takes longer than a second is also a task that a rolling restart will interrupt, because background tasks do not survive the process exiting.

Hold time, not task duration, is what counts. A task that runs for five seconds but only queries at the start and the end can hold a connection for milliseconds if it opens two short sessions instead of one long one.

Exact remediation & configuration

Give each task its own session, created inside the task:

from contextlib import asynccontextmanager
from fastapi import BackgroundTasks, Depends, FastAPI
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

engine = create_async_engine(
    DATABASE_URL, pool_size=10, max_overflow=5, pool_timeout=3, pool_pre_ping=True,
    connect_args={"server_settings": {"application_name": "orders-api"}},
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_db() -> AsyncSession:
    async with SessionLocal() as session:
        yield session            # request-scoped; closed when the request ends

async def write_audit(order_id: str, actor: str) -> None:
    async with SessionLocal() as session:        # the task's own session
        async with session.begin():
            await session.execute(
                insert(AuditLog).values(order_id=order_id, actor=actor))

@app.post("/orders/{order_id}/cancel")
async def cancel(order_id: str, tasks: BackgroundTasks, db: AsyncSession = Depends(get_db)):
    await db.execute(update(Order).where(Order.id == order_id).values(status="cancelled"))
    await db.commit()
    tasks.add_task(write_audit, order_id, "api")   # pass values, never the session
    return {"status": "cancelled"}

Bound the background share with a semaphore:

import asyncio

_bg_slots = asyncio.Semaphore(5)      # at most 5 background tasks holding connections

async def with_bg_slot(fn, *args, **kwargs):
    async with _bg_slots:
        await fn(*args, **kwargs)

# usage
tasks.add_task(with_bg_slot, write_audit, order_id, "api")

Tasks beyond the limit wait for a slot rather than for a connection, which keeps the pool’s queue for requests.

Keep sessions short inside long tasks. Open a session per unit of work rather than for the whole task:

async def export_orders(customer_id: str) -> None:
    last_id = 0
    while True:
        async with SessionLocal() as session:                 # short session per page
            rows = (await session.execute(
                select(Order.id, Order.total)
                .where(Order.customer_id == customer_id, Order.id > last_id)
                .order_by(Order.id).limit(500))).all()
        if not rows:
            break
        last_id = rows[-1][0]
        await upload_chunk(rows)                              # no connection held here

Use a separate engine for background work when the split needs to be enforced rather than agreed:

bg_engine = create_async_engine(
    DATABASE_URL, pool_size=4, max_overflow=0, pool_timeout=10,
    connect_args={"server_settings": {"application_name": "orders-bg"}},
)
BgSession = async_sessionmaker(bg_engine, expire_on_commit=False)

Two engines mean two pools and two budgets, but the guarantee is absolute: background work cannot take a connection a request needed. It also makes the database’s attribution exact, which is what the application_name split is for.

Move genuinely long work to a queue. FastAPI background tasks do not survive a process restart and have no retry. Anything that must complete belongs in Celery, RQ, Dramatiq or a queue consumer, where the worker’s own pool is sized for it — see Closing ORM Connections in Background Job Workers.

Choosing between a task, a worker and a transaction

The decision is usually made implicitly by whoever writes the endpoint; making it explicit avoids most pool incidents.

In the request’s transaction. Work that must be atomic with the request — writing an outbox row, updating a counter — belongs inside the same transaction. It costs nothing extra in connections because the connection is already held.

As a background task. Work that is short, best-effort and does not need to survive a restart: an audit record, a cache invalidation, a metric. The cost is a brief second connection after the response.

As a queued job. Work that is slow, must be retried, must survive deploys, or is heavy enough to need its own concurrency control: sending email, generating a report, calling a slow third party. The cost is infrastructure, and the benefit is that the web tier’s pool is untouched.

A useful rule: if the work would be acceptable to lose when a pod is replaced, a background task is fine; otherwise it needs a queue.

Validation & verification

Tasks never receive a request session. A grep in CI is enough to keep it that way:

! grep -rn "add_task([^)]*\b\(db\|session\)\b" --include=*.py app/

The background share is bounded. Under a load test that fires a task per request, the database’s session count for orders-bg must not exceed the semaphore limit or the background engine’s pool:

SELECT count(*) FROM pg_stat_activity WHERE application_name = 'orders-bg';

Requests do not queue behind tasks. Compare request p99 latency with and without background work enabled at the same request rate; the difference should be small. A large difference means the bound is too generous or the tasks hold connections too long.

No session leaks. After a load test, engine.pool.checkedout() must return to zero:

assert engine.pool.checkedout() == 0, engine.pool.status()

Tasks do not outlive the process silently. Trigger a graceful shutdown while tasks are running and confirm the shutdown waits for them or that losing them is acceptable — whichever the design intends. FastAPI waits for tasks started before shutdown began, but not for work queued after.

Frequently Asked Questions

Related

• FastAPI SQLAlchemy Pool Configuration — the parent guide to engine and session configuration.
• Scoping Async SQLAlchemy Sessions in FastAPI — the request-scoped session pattern this page extends.
• Tuning SQLAlchemy pool_size and max_overflow — sizing both terms.
• Closing ORM Connections in Background Job Workers — where longer work belongs.