When a team standardises its Python data layer on SQLAlchemy 2.1, how do you choose between the asyncio extension and synchronous Sessions run in threads?
answer
- same ORM under both
- concurrency, not faster SQL
- every IO point must be awaited
- what the surrounding program already is
basics
~20 sPick the asyncio extension when the program is already asyncio and waits on many concurrent queries; keep synchronous Sessions in threads when lazy loading, sync-only libraries or simplicity matter more. AsyncSession runs the same ORM underneath, so it buys concurrency, not faster SQL.
solid answer
~50 s`AsyncSession` is a proxy over the same `Session`, so statement compilation, flushes and mapping work the same; what changes is who waits. Choosing it commits the team to an async driver, the `sqlalchemy[asyncio]` extra in 2.1, an awaited call wherever IO can happen (lazy loads and expired attributes raise instead of querying, so loading must be planned), one session per task, engines per event loop and synchronous event listeners. Synchronous Sessions in threads keep lazy loading, any DBAPI driver and plain stack traces, at the price of a thread per concurrent unit of work and, inside an asyncio program, offloading every call to a thread. I decide by what the surrounding program already is, how many database waits overlap, how much existing code relies on lazy loading, and whether the team can enforce eager-loading rules. Whichever wins, one style per service, written down.
go deeper
Know that AsyncSession and Session run the same ORM code and build the same statements; the difference is whether waiting blocks a thread or yields to the event loop.
List what the asyncio extension demands in code: awaited calls, planned loading, a session per task and run_sync for sync-only APIs.
Weigh the failure modes each style brings in production: implicit IO errors and loop affinity on one side, thread and pool exhaustion on the other.
Own the decision per service from the surrounding runtime, overlap of database waits and reliance on lazy loading, then write the rules that make the chosen style safe.
## What the asyncio extension is, and is not SQLAlchemy's `AsyncSession` and `AsyncEngine` are **proxies** over the synchronous `Session` and `Engine`. Statements go through the same compilation (only the parameter placeholders follow whichever driver the URL names), the unit of work flushes in the same order, mapped classes are declared the same way. The extension changes one thing: while a query is in flight, the event loop runs other tasks instead of a thread blocking. That buys **concurrency of waiting**, many overlapping queries on few threads, and nothing else. A single query is not faster, and CPU spent building ORM objects still runs on the loop's one thread. So the choice is about the program around the data layer, the team's habits and how much waiting overlaps, rather than a property of the database. It is also a choice that is expensive to reverse: the session type shapes every function signature that touches data, every loading decision and every test fixture, so it belongs to the lead who owns the service's conventions. ## Costs a team takes on with AsyncSession - **Explicit IO everywhere.** Any attribute access that would lazy-load or refresh an expired attribute raises instead of querying. Teams adopt rules such as eager loading in every query and `expire_on_commit=False`; the implicit IO material covers the mechanics. - **Dependencies.** An asyncio driver such as asyncpg or aiosqlite and, since **2.1**, the `sqlalchemy[asyncio]` extra for greenlet, which some platforms build from source. - **Session discipline.** One `AsyncSession` per task; a shared one can raise `isce` errors or silently mix transactions. - **Loop affinity.** An `AsyncEngine` belongs to one event loop and must be disposed of with `await engine.dispose()`. - **Synchronous edges.** Event listeners stay synchronous and are registered on `sync_engine` or `sync_session`; DDL, reflection and old sync code go through `run_sync()`. - **Streaming is a separate API**: `stream()` and `stream_scalars()` instead of `yield_per` on `execute()`. - **Tests change shape too.** Every test that touches data runs inside an event loop and needs an async driver for its database, so fixtures and the test runner's configuration follow the same choice. ## Costs of synchronous Sessions in threads - **A thread per concurrent unit of work**, each holding a `Session` and a pooled connection while it waits; memory and context switches grow with concurrency. - **Inside an asyncio program**, every database call has to leave the loop for a worker thread, or it stalls every task; the hand-off is a Python concurrency concern, but the data layer must be written to respect it. - **Pool and thread pool must agree**: more threads than `pool_size + max_overflow` simply queue for connections. - In exchange: **lazy loading works**, any DBAPI driver works, and tracebacks are ordinary. ## Side by side | Concern | AsyncSession | Session in threads | |---|---|---| | Statement compilation and flush | same ORM code | same ORM code | | Lazy loads on attribute access | raise; must be planned | work as usual | | Driver | asyncio driver only | any DBAPI driver | | Extra install in 2.1 | `sqlalchemy[asyncio]` (greenlet) | none | | Concurrency unit | task, cheap | thread, heavier | | Event listeners | sync, on `sync_engine` / `sync_session` | sync, on the engine or session | | Sync-only APIs | through `run_sync()` | direct | ## How to decide 1. **What is the surrounding program?** If it already runs on an asyncio loop end to end, the extension avoids a thread hand-off on every call. A sync program gains nothing from it. 2. **How many database waits overlap?** A worker gathering hundreds of slow queries benefits; a CRUD service with short queries and a small pool may not notice. 3. **How much code relies on lazy loading?** A large model graph navigated by attribute access is expensive to convert; `AsyncSession.run_sync()` can carry it during a migration, but it is a bridge, not a destination. 4. **Can the team enforce the rules?** Eager loading, session per task and explicit awaits need review and lint discipline; without them, async adds failure modes without benefit. 5. **What else touches the database?** Batch jobs, admin scripts and migration tooling can stay synchronous on the same mapped classes with their own `create_engine()`. ## Rules to write down either way - One style per service, so no module mixes the two session types by accident. - For async: engine and `async_sessionmaker` per process, `expire_on_commit=False`, a loader policy, `await engine.dispose()` on shutdown, the extra pinned in dependencies. - For threads: `Session` per thread from a `sessionmaker`, the thread count matched to the pool, and no blocking call left on an event loop.
- Can one set of mapped classes serve both an async service and a synchronous batch job?Yes. Mapping is independent of the session type: `AsyncSession` wraps an ordinary `Session` over the same `DeclarativeBase` classes. The service uses `create_async_engine` with an asyncio driver, and the batch job uses `create_engine` with a synchronous driver against the same database. Only async-specific helpers such as `AsyncAttrs.awaitable_attrs` stay unused on the sync side.
- How can an existing synchronous codebase move to AsyncSession in steps?Switch the entry points to `create_async_engine` and `AsyncSession`, and run untouched sync service functions through `await session.run_sync(fn)`, where lazy loads still work inside the greenlet. Convert functions to awaited calls with explicit loading one at a time, and delete each `run_sync` wrapper as its function is converted. Track the remaining wrappers as debt, because they hide IO from reviewers.
- Expects AsyncSession to make each individual query run faster
- Assumes moving to AsyncSession needs no change in how relationships load
- Thinks the asyncio extension removes the need for a connection pool
- Claims a synchronous Session cannot be used from an asyncio program at all
- Plans to write SQLAlchemy event listeners as async def functions
Assumes SQLAlchemy 2.1, where adopting the asyncio extension also means installing the sqlalchemy[asyncio] extra, as greenlet is no longer a default dependency.