skip to content

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?

level: seniorimportance: should knowfreq 25%

answer

  1. buffered before the await returns
  2. server-side cursor
  3. async for over partitions
  4. yield_per needs the streaming call

basics

~20 s

AsyncSession.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 s

The 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 lines
python
import 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

for a junior

Recall that execute() and scalars() on an AsyncSession load everything at once, while stream() and stream_scalars() hand rows over gradually through async for.

for a middle

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().

for a senior

Diagnose the out-of-memory export, recognise the AsyncMethodRequired guards, and keep the stream's connection, transaction and object references bounded.

for a principal

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