What is a SQLAlchemy async session, and how does it relate to the engine and connection pool?
Async SQLAlchemy has three objects, and each one has a different lifetime.
AsyncEngine: create one per process, and use it only on the event loop where it was created. It owns the connection pool, which by default is a queue pool adapted for asyncio. asyncpg connections are bound to the loop that opened them, so an engine created on one loop and used on another fails in ways that are hard to trace.async_sessionmaker: a configured factory, also created once at startup. It holds settings such asexpire_on_commitand produces sessions.AsyncSession: create one per request or unit of work, then discard it.
Sessions are cheap to create. Pooled connections are shared and scarce. Most production failures come from giving one of these objects the lifetime of another, such as a session that outlives its request or an engine created per request.
A new session holds no connection. It checks one out from the pool on the first statement it executes and keeps it until you commit, roll back, or close the session. Any slow work you do between the first query and the commit, such as an HTTP call or a large serialization, holds a pool slot for that whole time.
An AsyncSession is not safe for concurrent use. If you run several of its queries at once with asyncio.gather, they all go to the same asyncpg connection. That connection allows one operation at a time, so you get errors like "another operation is in progress". For concurrent queries, give each task its own session.
Async helps services whose concurrency is limited by I/O, where many requests spend most of their time waiting on the network or the database. It is not automatically faster. The pool size still caps how many queries run at once, and CPU-heavy code blocks the event loop for every request.
import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
engine = create_async_engine(
"postgresql+asyncpg://app:change-me@db.internal:5432/appdb",
pool_size=5,
max_overflow=5,
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
async def count_rows(table: str) -> int:
# One session per unit of work. No connection is checked out yet.
async with SessionLocal() as session:
# First execute() checks a connection out of the pool...
result = await session.execute(text(f"SELECT count(*) FROM {table}"))
return result.scalar_one()
# ...and leaving the block closes the session, returning it to the pool.
async def main() -> None:
# WRONG: one session shared by concurrent tasks sends overlapping
# queries to one asyncpg connection -> "another operation is in progress".
# async with SessionLocal() as s:
# await asyncio.gather(s.execute(q1), s.execute(q2))
# RIGHT: each task gets its own session (and its own pooled connection).
users, orders = await asyncio.gather(count_rows("users"), count_rows("orders"))
print(users, orders)
await engine.dispose() # close pooled connections before the loop ends
asyncio.run(main())
One engine and one sessionmaker for the whole process, with a short-lived AsyncSession per task, so concurrent queries run on separate pooled connections instead of colliding on a shared one.
Who should use async SQLAlchemy, and who is this guide for?
Async SQLAlchemy fits teams running asyncio-based services, such as FastAPI, Starlette, or aiohttp applications, against PostgreSQL through asyncpg. The strongest case is a service that handles many concurrent requests, each spending most of its time waiting on the database or other network calls. In that situation, one event loop can keep many requests in flight without a thread per request.
This guide is written for:
- Application engineers writing request handlers, dependencies, and background tasks who need to know when a session holds a connection and when it releases one.
- Engineers debugging intermittent failures such as pool
TimeoutError,MissingGreenlet, "another operation is in progress", "attached to a different loop", or sporadic errors on the first query after an idle period. - Platform and operations engineers who size pools across workers and replicas against PostgreSQL's
max_connections, or who are deciding whether to add an external pooler such as PgBouncer.
It is a poor fit for services whose handlers are mostly CPU-bound, that depend on synchronous libraries, or whose ORM code relies on implicit lazy loading throughout. A sync engine is usually the simpler choice there, as covered in the PgBouncer and NullPool section below.
How does async SQLAlchemy work under the hood?
The async API is a thin layer over the synchronous core and ORM, not a separate implementation.
AsyncEnginewraps a regular syncEngine, exposed asengine.sync_engine.AsyncSessionwraps a regular syncSession, exposed assession.sync_session. Querying, unit-of-work flushing, identity-map handling, and pooling are all the same code the sync API uses.- When you
await session.execute(...), SQLAlchemy runs the sync ORM code inside a greenlet. When that code reaches the point where the driver must talk to the network, the greenlet switches back to the event loop, which awaits asyncpg's coroutine. Once the result arrives, the greenlet resumes and the sync code continues as if the call had blocked. The event loop serves other tasks in the meantime. - Because that switch can only happen inside an awaited SQLAlchemy call, any I/O triggered outside one has nowhere to yield. A plain attribute access that would need a lazy load is the common example, and it produces
MissingGreenlet. - The pool is an asyncio-adapted queue pool. A session checks a connection out on its first statement and returns it when the transaction ends through commit or rollback, or when the session closes. A checkout that finds the pool full waits up to
pool_timeoutseconds for another task to return a connection. - Since the async objects wrap sync ones, pool events and other event listeners attach to the sync objects, such as
engine.sync_engine. This matters for the instrumentation described in the diagnostics section.
Each of the pitfalls in this article follows from this model. A connection is held for the life of a transaction. Only one operation runs on a connection at a time. Connections are bound to their event loop. All ORM work outside the I/O waits runs on the event loop thread.
How do you implement a correct FastAPI session dependency?
A correct dependency opens the session with async with from an async_sessionmaker, yields it, commits if the handler returns normally, and rolls back on any exception. The async with block guarantees close() runs, which returns the connection to the pool even when commit or rollback itself fails. Do not share a session between requests or store it globally. An AsyncSession is not safe for concurrent use.
Whether the dependency or the service layer owns the transaction decides how long a connection stays checked out:
| Approach | Transaction boundary | Connection held | Main risk |
|---|---|---|---|
Dependency commits after yield |
Whole request | Until dependency cleanup runs | Slow handlers or external calls hold a connection idle |
Service-layer session.begin() blocks |
Each unit of work | Only inside each block | Partial work if a later block fails |
When post-yield code runs relative to sending the response depends on the FastAPI version and how the dependency is declared. Do not assume either order. If cleanup runs after the response is sent, the connection stays checked out while the client receives the response. If cleanup runs before a streaming body is iterated, the generator ends up using a closed session.
Streaming generators, BackgroundTasks, and fire-and-forget asyncio.create_task calls should therefore each open their own session from the sessionmaker. They should not borrow the request's session.
Short transactions release connections sooner but give up request-level atomicity. Running concurrent queries with asyncio.gather requires one session per task, and each session checks out its own connection. One request that fans out to ten tasks can take ten pool slots, so a handful of such requests can exhaust the pool.
from fastapi import BackgroundTasks, Depends, FastAPI
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
engine = create_async_engine("postgresql+asyncpg://app:changeme@db:5432/appdb", pool_size=10)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
app = FastAPI()
async def get_session():
# One session per request; async with guarantees close() returns the connection.
async with SessionLocal() as session:
try:
yield session
await session.commit() # handler returned normally
except Exception:
await session.rollback() # any error in the handler or in commit
raise
async def record_audit(order_id: int) -> None:
# Background work opens its own session; the request session may already be closed.
async with SessionLocal() as session:
async with session.begin(): # short transaction, commits on exit
await session.execute(
text("INSERT INTO audit_log (order_id) VALUES (:id)"), {"id": order_id}
)
@app.post("/orders/{order_id}/ship")
async def ship_order(
order_id: int,
tasks: BackgroundTasks,
session: AsyncSession = Depends(get_session),
):
await session.execute(
text("UPDATE orders SET status = 'shipped' WHERE id = :id"), {"id": order_id}
)
# Pass the ID, not the session, to work that runs outside the request.
tasks.add_task(record_audit, order_id)
return {"id": order_id, "status": "shipped"}
A yield-based FastAPI dependency that commits or rolls back and always closes its AsyncSession, with a background task that opens its own short-lived session from the sessionmaker rather than reusing the request's session.
Why does lazy loading raise MissingGreenlet errors in async SQLAlchemy?
AsyncSession runs the synchronous ORM inside a greenlet. When it reaches network I/O, it switches back to the event loop, so that I/O can happen only inside an awaited call such as execute(). Accessing a plain attribute like user.orders is not awaited, so the ORM has no point at which to yield. If that attribute is unloaded, the lazy load would need blocking I/O. SQLAlchemy raises MissingGreenlet rather than block the event loop.
The fix is to load relationships explicitly in the query:
| Strategy | How it loads | Trade-off |
|---|---|---|
selectinload |
Second query using IN (...) on parent keys |
Extra round trip; usually best for collections |
joinedload |
JOIN in the same query |
On collections, each child multiplies parent rows, inflating transfer and dedup work |
lazy='raise' |
Never loads implicitly | Raises on access, so missing eager loads fail in tests instead of production |
awaitable_attrs (via AsyncAttrs) |
await obj.awaitable_attrs.orders |
Escape hatch that hides N+1 queries in loops |
Eager loading everything risks over-fetching. Load only what the response actually serializes.
After commit(), the session expires all loaded attributes by default. Returning an ORM object from an endpoint then triggers a refresh during serialization. Setting expire_on_commit=False on the sessionmaker avoids this, but the object keeps whatever state it had before the commit, including server-side defaults and trigger effects. Refresh those values explicitly if the response needs them.
Building ORM objects is CPU work. Identity-map bookkeeping and row processing run on the event loop thread, so a query returning tens of thousands of entities stalls every other request on that worker. For large reads, use Core select() returning plain rows, or stream() to process results in batches.
from sqlalchemy import ForeignKey, select
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, selectinload
class Base(AsyncAttrs, DeclarativeBase): # AsyncAttrs adds .awaitable_attrs
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
# lazy="raise": an access the query didn't plan for fails loudly in tests
orders: Mapped[list["Order"]] = relationship(back_populates="user", lazy="raise")
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
total: Mapped[int]
user: Mapped[User] = relationship(back_populates="orders") # default lazy load
engine = create_async_engine("postgresql+asyncpg://app:example-password@db.internal/shop")
Session = async_sessionmaker(engine, expire_on_commit=False)
async def get_user(user_id: int) -> User:
async with Session() as session:
# Load exactly what the response serializes: one extra IN (...) query
stmt = select(User).where(User.id == user_id).options(selectinload(User.orders))
return (await session.execute(stmt)).scalar_one()
async def owner_of(order: Order) -> User:
# Escape hatch: an awaited lazy load. Calling this in a loop is an N+1.
return await order.awaitable_attrs.user
async def export_totals():
async with engine.connect() as conn:
# Core rows skip identity-map work; chunks give the event loop a chance to serve others
result = await conn.stream(select(Order.id, Order.total))
async for chunk in result.partitions(1000):
yield chunk
Shows lazy='raise' to surface unplanned loads, selectinload for an eager collection, awaitable_attrs as an explicit awaited lazy load, expire_on_commit=False on the sessionmaker, and Core streaming for large reads.
How should you size the connection pool for PostgreSQL max_connections?
Each worker process creates its own engine and therefore its own pool. The ceiling you must respect is (pool_size + max_overflow) × workers per replica × replicas, and that total must fit inside PostgreSQL's max_connections after reserving headroom for migrations, psql sessions, cron jobs, replication, monitoring, and any other service sharing the database. Rolling deploys briefly run old and new replicas side by side, so count the surge replicas as well.
Illustrative estimate. Assume max_connections = 200, with 30 reserved for non-application clients. That leaves 170 for the app. The app runs 3 replicas with 4 workers each, and a rolling deploy adds 1 surge replica. With pool_size=5, max_overflow=5, the peak is 10 × 4 × 4 = 160, which fits. Raising max_overflow to 10 gives 15 × 4 × 4 = 240, and new connections start failing during deploys.
pool_timeout controls what clients experience once a pool is saturated. A request waits up to that many seconds for a connection to be returned, then raises TimeoutError. That wait is a queue inside your process, and you can observe and bound it.
| Aspect | Small pool, short timeout | Oversized pool |
|---|---|---|
| Failure mode under load | Fast, explicit timeouts | Slow queries across the board |
| Server memory | Bounded | One backend process per connection |
| Lock and CPU contention | Limited concurrency | Many backends competing |
| Signal to operators | Clear pool-wait metric | Diffuse latency rise |
PostgreSQL rarely runs more concurrent queries faster than it runs a modest number of them. Extra connections mostly add per-backend memory and contention, so it is usually better to queue requests in the client than to push that load onto the server.
import os
from sqlalchemy.ext.asyncio import create_async_engine
MAX_CONNECTIONS = 200 # PostgreSQL max_connections
RESERVED = 30 # migrations, psql, cron, replication, monitoring
WORKERS = int(os.environ.get("WORKERS_PER_REPLICA", "4"))
REPLICAS = int(os.environ.get("REPLICAS", "3"))
SURGE = int(os.environ.get("DEPLOY_SURGE", "1")) # extra replicas during rolling deploy
POOL_SIZE = 5
MAX_OVERFLOW = 5
peak = (POOL_SIZE + MAX_OVERFLOW) * WORKERS * (REPLICAS + SURGE)
budget = MAX_CONNECTIONS - RESERVED
if peak > budget:
# Fail at startup instead of failing new connections mid-deploy.
raise RuntimeError(f"pool peak {peak} exceeds connection budget {budget}")
engine = create_async_engine(
"postgresql+asyncpg://app:change-me@db.internal:5432/app",
pool_size=POOL_SIZE, # connections kept open
max_overflow=MAX_OVERFLOW, # temporary extra connections under burst
pool_timeout=5, # seconds a request waits for a connection,
# then sqlalchemy.exc.TimeoutError is raised
pool_pre_ping=True, # discard dead connections before use
)
A startup check that computes (pool_size + max_overflow) × workers × (replicas + surge) against max_connections minus reserved headroom, then builds an async engine with a short pool_timeout so saturation surfaces as a fast, explicit TimeoutError.
How do you keep pooled connections healthy across loops, forks, and network drops?
Pooled connections fail silently. A load balancer, NAT gateway, or firewall drops an idle TCP flow. A database failover moves the primary, or a server-side idle timeout closes the backend. The pool doesn't learn about any of this until it hands the dead connection to a request. The result is a sporadic error on the first query after a quiet period, often during low-traffic hours. It rarely reproduces under load, because busy connections never sit idle long enough to be dropped.
pool_pre_ping=True tests each connection at checkout and transparently replaces it if the test fails. The cost is one extra round trip per checkout. On a same-region network that is usually negligible. Across regions it becomes noticeable. pool_recycle (in seconds) replaces connections older than the limit at checkout, with no per-request cost. Set it below the shortest idle timeout on the path. Recycling only bounds connection age, so a failover or a mid-interval drop still produces errors until a connection is recycled. Many teams use both settings.
Connections are also bound to the event loop that created them. An engine built at import time attaches its pool to whichever loop first uses it. That breaks under test runners that create a loop per test, where you see errors about a different loop. It also breaks under forking servers, where children inherit parent sockets. Create the engine in the lifespan startup handler, keep it on application state, and call await engine.dispose() on shutdown. If you fork after creating an engine, dispose of the inherited pool in the child before first use, so that parent and child never share sockets.
When should you use PgBouncer or NullPool instead of the built-in pool?
The built-in async pool is the right default for a modest number of long-lived processes talking directly to PostgreSQL. Its limits appear when process count grows. Each replica holds its own pool, so the database sees replicas × (pool size + overflow) connections. Autoscaling multiplies that number without any coordination between processes.
An external pooler such as PgBouncer in transaction mode solves this by multiplexing many client connections onto a small set of server connections. Pair it with NullPool so SQLAlchemy does not stack a second pool on top. The catch is prepared statements. asyncpg prepares statements on a server connection and caches them by name. In transaction mode, your next transaction may run on a different server connection, where that name either does not exist or refers to another statement. Either disable asyncpg's statement cache and SQLAlchemy's dialect-level cache, or make statement names unique per connection. The relevant connect_args and dialect options have changed across releases, so confirm them against the documentation for your installed SQLAlchemy, asyncpg, and PgBouncer versions.
| Option | Strength | Cost |
|---|---|---|
| Built-in async pool | Warm connections, statement cache works | Connections scale with replicas |
NullPool + PgBouncer (transaction mode) |
Central connection cap, scales replicas | Per-checkout setup, lost statement-cache speed, no session-level state |
| Sync engine in a threadpool | Mature tooling, lazy loading works | Thread overhead, threadpool caps concurrency |
Skip async SQLAlchemy entirely when request handlers are mostly CPU-bound or call synchronous libraries. It is also a poor fit when your ORM code depends heavily on lazy loading. In those cases, a sync engine is simpler and costs little in practice.
How do you diagnose leaked sessions and pool exhaustion in production?
A pool that times out under load is usually a lifetime bug. Two signals help you find it: how many connections are checked out, and how long each one stays out.
Instrument the pool
For an async engine, attach listeners to engine.sync_engine, because pool events fire on the underlying sync pool. In a checkout listener, record a timestamp on the connection record. In the matching checkin listener, compute the elapsed time and emit it as a metric. Log a warning, with the request ID, whenever a checkout exceeds your slowest expected request. Export engine.pool.checkedout() as a gauge, and log engine.pool.status() when you see a pool timeout. A checked-out count that sits at pool_size + max_overflow while traffic is flat points to a leak, not to load.
Let PostgreSQL enforce limits
Set statement_timeout to cap runaway queries. Set idle_in_transaction_session_timeout so the server kills sessions that opened a transaction and then stopped sending queries. Then query pg_stat_activity for rows where state = 'idle in transaction', sorted by xact_start. Set application_name per service so you can tell which service owns each connection. The query column shows the last statement that ran before the session went quiet.
Map signals to causes
Long checkouts combined with idle-in-transaction sessions usually mean one of these mistakes:
- A session is held across an
awaiton an HTTP call. - A dependency yields without committing or rolling back.
- A background task reuses a request's session.
Rising checked-out counts with no slow queries suggest that sessions are never closed.
When every session has one owner and a clear end, these metrics point to a specific code path. Without that, you reconstruct timelines from intermittent timeouts after an incident, which costs far more engineering time than the instrumentation does.
How can getting session lifetimes and pooling right save engineering time?
The failures in this article are expensive mainly because they are intermittent. Pool exhaustion, idle-in-transaction sessions, dead connections after quiet periods, and loop-binding errors rarely reproduce on a laptop. They tend to appear under production traffic, during deploys, or overnight, and each one can take hours of log correlation to trace back to a single misplaced await or a borrowed session. The practices above save time by moving these failures earlier, or by making them point directly at their cause:
- One session dependency and one sessionmaker. Transaction and connection lifetime is decided in one place. Reviewers check a single pattern instead of auditing every handler for
commit()andclose()calls. - Separate sessions for background and streaming work. This removes a class of "session is closed" and "another operation is in progress" bugs that only appear under concurrency.
lazy='raise'on relationships. A missing eager load fails in a unit test with a clear error, instead of surfacing asMissingGreenletin production or as a silent N+1 query.- A startup check on the connection budget. A misconfigured
max_overflowor worker count fails at deploy time with an explicit message, instead of exhaustingmax_connectionspartway through a rolling deploy. pool_pre_ping,pool_recycle, and engine creation in the lifespan handler. These remove the "first query after a quiet period fails" errors and the test-suite loop errors that otherwise produce repeated investigations.- Pool metrics plus PostgreSQL timeouts. A pool timeout arrives with a checked-out gauge, long-checkout warnings tagged with request IDs, and
pg_stat_activityrows. That turns an open-ended investigation into a lookup.
Illustrative estimate. Assume a team sees two intermittent pool-related incidents per quarter, and each takes an assumed 8 engineer-hours to investigate without instrumentation and an assumed 2 hours with it. That is roughly 12 engineer-hours per quarter returned. Assume also a one-time cost of about a day to add the dependency pattern, the startup check, and the pool listeners. Under those assumed inputs, the work pays for itself within the first few months. Substitute your own incident rate and investigation times. The structure of the comparison matters more than these particular numbers.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to sqlalchemy async session, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Consumers learn about a breaking change at review time rather than from production errors.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.