An async SQLAlchemy export job loads a huge events table with AsyncSession.scalars() and runs out of memory; why, and how do stream() and stream_scalars() fix it?
answer
- buffered before the await returns
- server-side cursor
- async for over partitions
- yield_per needs the streaming call
basics
~20 sAsyncSession.scalars() and execute() return buffered results: every row is fetched and every object built before the await returns. stream() and stream_scalars() use a server-side cursor and return an async result you iterate in batches set by yield_per.
solid answer
~40 sThe awaitable `execute()` and `scalars()` on `AsyncSession` return a buffered `Result`: the ORM fetches all rows and constructs every object before handing the result back, so a table of millions of rows has to fit in memory at once. `AsyncSession.stream()` and `stream_scalars()` run the statement with `stream_results=True`, so the driver uses a server-side cursor, and return an `AsyncResult` or `AsyncScalarResult` that you consume with `async for` or `partitions()`. Add `execution_options(yield_per=1000)` to cap how many rows and ORM objects exist per batch. Two guards catch the old habit: `yield_per` or `stream_results` passed to `execute()`/`scalars()` raises `AsyncMethodRequired`, and so does `create_async_engine(..., server_side_cursors=True)`. Consume the stream while the session is open, close it in a `finally`, and keep no references to rows already written.
code
python · 44 linesimport asyncio
from sqlalchemy import String, insert, select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Event(Base):
__tablename__ = "event"
id: Mapped[int] = mapped_column(primary_key=True)
source: Mapped[str] = mapped_column(String(50))
engine = create_async_engine("sqlite+aiosqlite:///export.db")
SessionFactory = async_sessionmaker(engine)
async def export_events() -> int:
written = 0
async with SessionFactory() as session:
stmt = select(Event).order_by(Event.id).execution_options(yield_per=1000)
result = await session.stream_scalars(stmt)
try:
async for batch in result.partitions():
written += len(batch) # write the batch out, keep no references
finally:
await result.close()
return written
async def main() -> None:
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
await conn.execute(insert(Event), [{"source": f"s{i}"} for i in range(2500)])
print(await export_events())
await engine.dispose()
asyncio.run(main())go deeper
Recall that execute() and scalars() on an AsyncSession load everything at once, while stream() and stream_scalars() hand rows over gradually through async for.
Explain buffered versus server-side results, what yield_per caps, and why the streaming call is a separate method rather than an option on execute().
Diagnose the out-of-memory export, recognise the AsyncMethodRequired guards, and keep the stream's connection, transaction and object references bounded.
Decide when a long export belongs in the service at all, given that it pins a connection and a transaction for its whole run.
## Why the buffered call runs out of memory With the ORM, a normal execution **fetches all rows immediately**, builds an ORM object for each one and assembles them into a single buffer before the `Result` is handed back. SQLAlchemy does this on purpose: joined eager loading, uniquing and a consistent identity map all rely on seeing the whole result. Under asyncio the same holds for `await session.execute(stmt)` and `await session.scalars(stmt)`. By the time the `await` returns, every row is in memory, so an export of a table with tens of millions of rows grows until the process is killed, however carefully the loop afterwards writes and discards. ## The streaming API | Call | Returns | Cursor | |---|---|---| | `await session.execute(stmt)` / `await session.scalars(stmt)` | `Result` / `ScalarResult` (buffered, sync iteration) | client-side, fully fetched | | `await session.stream(stmt)` | `AsyncResult` | server-side | | `await session.stream_scalars(stmt)` | `AsyncScalarResult` | server-side | | `conn.stream(stmt)` / `conn.stream_scalars(stmt)` on an `AsyncConnection` | `AsyncResult` / `AsyncScalarResult`, usable with `async with` | server-side | The streaming methods add the `stream_results=True` execution option, which asks the driver for a **server-side cursor**: the database keeps the result and sends rows as they are fetched. The async result objects are consumed with awaits: - `async for row in result:` row by row; - `async for batch in result.partitions():` a list at a time; - `await result.fetchmany(500)` for manual batching; - `await result.close()` to release the cursor early. Both async result types also offer the familiar shaping methods: `AsyncResult.scalars()`, `mappings()` and `columns()` return async result objects that stream in the same way, and `partitions(size)` accepts an explicit batch size when `yield_per` is not set. ## Bounding the ORM's buffer with yield_per Streaming the cursor alone does not stop the ORM from building objects in large chunks. The `yield_per` execution option caps both: `select(Event).execution_options(yield_per=1000)` makes the ORM build and hand over **1,000 objects at a time**, and `partitions()` then yields lists of that size. `yield_per` itself implies `stream_results`, which is why it belongs on a streaming call. ## Writing the export 1. Open one session for the whole export: `async with SessionFactory() as session:`. 2. Build the statement with a stable `order_by()` and `execution_options(yield_per=1000)`. 3. Call `result = await session.stream_scalars(stmt)`. 4. Loop with `async for batch in result.partitions():`, write each batch out and let it go. 5. Close the result in a `finally` block, then let the session close. ## Guards SQLAlchemy puts in the way 1. `await session.scalars(stmt.execution_options(yield_per=1000))` or `stream_results=True` on `execute()` closes the cursor and raises `AsyncMethodRequired: Can't use the AsyncSession.execute() method with a server-side cursor. Use the AsyncSession.stream() method for an async streaming result set.` `AsyncConnection.execute()` raises the same error with its own class name. 2. `create_async_engine(url, server_side_cursors=True)` raises `AsyncMethodRequired` too: server-side cursors cannot be switched on globally for an async engine. 3. On a dialect without server-side cursor support, `AsyncConnection.stream()` raises `InvalidRequestError` saying so. asyncpg and aiosqlite both support them. ## Memory beyond the cursor - **Keep no references.** The `Session` holds loaded objects only weakly, so once your loop drops a batch it can be collected; appending everything to a list rebuilds the problem. - **Do not modify the streamed objects.** Objects with pending changes are held strongly until the next flush. - **Never call `await result.all()`** on a stream you meant to bound; it reads everything back into one list. - **Keep loader options light.** `yield_per` is not compatible with joined eager loading of collections or with `Result.unique()`, and selectin loading works with it only where the driver supports multiple independent cursors. ## Holding the connection and the transaction A server-side cursor lives inside a transaction on one pooled connection, so an export holds both open for its whole run: - consume the stream **inside** the session's block; iterating after `await session.close()` fails with `InvalidRequestError` ("this identity map is no longer valid. Has the owning Session been closed?"); - close the result in a `finally`, or use `async with conn.stream(stmt) as result:` on an `AsyncConnection`, which closes the `AsyncResult` even when iteration is interrupted; - keep the per-batch work short, because a slow consumer stretches the transaction and keeps a pool slot busy. ## When rows are enough An export that only writes columns to a file does not need ORM objects at all. `async with engine.connect() as conn:` followed by `async with conn.stream(select(Event.id, Event.source)) as result:` streams plain `Row` tuples, skipping object construction and the identity map entirely. The ORM path is worth its cost when each batch needs mapped behaviour, such as relationships or hybrid attributes.
- Why does await session.scalars(stmt.execution_options(yield_per=1000)) raise instead of streaming?`yield_per` implies `stream_results=True`, which asks for a server-side cursor. `AsyncSession.execute()` and `scalars()` must return a buffered, synchronously iterable result, so SQLAlchemy closes the cursor and raises `AsyncMethodRequired`, telling you to use `AsyncSession.stream()`. Keep the same statement and call `await session.stream_scalars(stmt)` instead.
- How do you make sure the server-side cursor is released if the export loop breaks early?On an `AsyncConnection`, use `async with conn.stream(stmt) as result:`; SQLAlchemy calls `AsyncResult.close()` unconditionally on exit. `AsyncSession.stream()` and `stream_scalars()` are plain awaitables, so wrap the loop in `try`/`finally` with `await result.close()`. Closing the session afterwards returns the connection to the pool.
- Can you turn on streaming for every query with create_async_engine(url, server_side_cursors=True)?No. `create_async_engine` raises `AsyncMethodRequired` ("Can't set server_side_cursors for async engine globally; use the connection.stream() method for an async streaming result set"). Streaming is chosen per statement by calling `stream()` or `stream_scalars()`.
- Thinks await session.scalars() fetches rows lazily as the loop iterates
- Adds yield_per to AsyncSession.scalars() and expects it to stream
- Iterates a stream_scalars() result after the session has been closed
- Calls await result.all() on a stream and still expects bounded memory
- Sets server_side_cursors=True on create_async_engine to stream everything