skip to content

How does SQLAlchemy's yield_per execution option change an ORM select(Product) over 200,000 rows, and what will it not combine with?

level: middleimportance: nice to knowfreq 22%

answer

  1. default: fetch all, then return
  2. stream_results plus Result.yield_per
  3. iterate or partitions(), never all()
  4. uniquing and joined collections

basics

~20 s

By default the ORM fetches every row and builds every Product before returning. With execution_options(yield_per=1000) it streams, using a server-side cursor where the driver supports one, building 1,000 objects at a time; unique() and joined-eager collections raise.

solid answer

~40 s

Normally an ORM `select(Product)` fetches **all** rows and builds every object into one buffer before the `Result` hands anything back, so 200,000 rows means 200,000 objects in memory. `select(Product).execution_options(yield_per=1000)` is the same as setting `stream_results=True`, which uses a server-side cursor where the driver supports one, plus `Result.yield_per(1000)`, so rows are fetched and objects built in batches of 1,000. Consume it by iterating or with `.partitions()`, which yields lists of up to 1,000; calling `.all()` defeats it. It refuses `Result.unique()` with an `InvalidRequestError`, and refuses eager loaders that need uniquing or row buffering, such as `joinedload()` of a collection and `subqueryload()`; `selectinload()` can work.

code

python · 36 lines
python
from decimal import Decimal

from sqlalchemy import Numeric, create_engine, insert, select, update
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "product"
    id: Mapped[int] = mapped_column(primary_key=True)
    sku: Mapped[str] = mapped_column(unique=True)
    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    session.execute(
        insert(Product),
        [{"sku": f"SKU-{n}", "price": Decimal("10.00")} for n in range(1, 2501)],
    )

new_prices = []
with Session(engine) as session:
    stmt = select(Product).order_by(Product.id).execution_options(yield_per=1000)
    for batch in session.scalars(stmt).partitions():  # lists of up to 1000
        new_prices.extend(
            {"id": p.id, "price": p.price * Decimal("1.05")} for p in batch
        )

with Session(engine) as session, session.begin():
    session.execute(update(Product), new_prices)
    print(session.scalar(select(Product.price).where(Product.id == 2500)))

go deeper

for a junior

Recall that yield_per is set with execution_options() and that you iterate the result instead of calling all().

for a middle

Explain the default full buffering, the stream_results plus Result.yield_per equivalence, partitions(), and why unique() and joined collections are refused.

for a senior

Show you pair streamed reads with bulk writes by primary key and keep modified objects from piling up in the identity map.

for a principal

Decide when a batch job should stream through the ORM at all, versus selecting only the needed columns, given what each costs to maintain.

## The default: fetch everything, then return An ORM `select()` executed by a `Session` normally **reads every row and constructs every object** before the `Result` yields the first one. SQLAlchemy does this on purpose: joined eager loading, `unique()` and the identity map all assume the complete result is in hand. For a pricing job reading 200,000 products, it also means 200,000 objects built at once and a long pause before the first one arrives. ## What yield_per changes `yield_per` is an **execution option** taking an integer batch size: ```python stmt = select(Product).order_by(Product.id).execution_options(yield_per=1000) for product in session.scalars(stmt): ... ``` It is equivalent to two settings combined: - `stream_results=True`, which asks the dialect for a **server-side cursor** where the driver supports one, so the database client does not buffer the whole result either. - `Result.yield_per(1000)`, which fetches 1,000 rows at a time and builds at most 1,000 ORM objects per batch. You can set it on the statement, as above, or pass `execution_options={"yield_per": 1000}` to `Session.execute()` or `Session.scalars()`. It works for Core statements too, where it behaves as similarly as it can. ## Consuming the result 1. Iterate it: `for product in session.scalars(stmt):`. 2. Or take batches with `.partitions()`, which yields lists sized to the `yield_per` value; handy when each batch feeds a bulk UPDATE by primary key. 3. Never call `.all()` on it: that collects everything into one list and undoes the point. Calling `.partitions()` **without** `yield_per` is not a substitute: the ORM has already fetched and built every object, and `.partitions()` merely slices the buffer. ## What it will not combine with | Combined with | Result | |---|---| | `Result.unique()` | `InvalidRequestError: Can't use the ORM yield_per feature in conjunction with unique()` when rows are fetched | | `joinedload()` of a collection, or `subqueryload()` | `InvalidRequestError`: these need uniquing or row buffering; the message suggests `selectinload()` | | `selectinload()` | Can work, emitting its IN query per batch, if the driver supports several independent cursors | | `joinedload()` of a many-to-one | Allowed; it adds no duplicate parent rows | The reasoning is the same in each case: anything that needs to see **all** rows for a parent, or every identity seen so far, cannot run while the result is streamed in pieces. ## Whether a server-side cursor is used `stream_results=True` is a request, not a guarantee. Where the dialect supports server-side cursors, as SQLAlchemy's PostgreSQL dialect does, the rows stay on the server and are fetched in batches. SQLite's dialect offers no server-side cursor, but `yield_per` still limits how many **ORM objects** exist at once, which is usually the larger cost of an ORM read. ## Choosing what to stream and how much - Stream **columns** instead of entities when the job only needs a few values: `select(Product.id, Product.price).execution_options(yield_per=1000)` yields lightweight `Row` objects and skips identity-map bookkeeping entirely. SQLAlchemy's performance FAQ names fetching individual columns as the first way to cut ORM loading cost. - Pick a batch size that balances round trips against memory; a few hundred to a few thousand rows is a common range, and with `selectinload()` it also sets how many keys go into each IN list. - Add `order_by()` when the job's output must be stable or resumable; without it the database may return rows in any order. ## Checking that it really streams 1. With `echo=True`, a streamed entity read shows **one** SELECT, however many batches you consume. 2. With `selectinload()` added, the log shows one `SELECT ... WHERE product.supplier_id IN (...)` per batch of parents, each IN list no longer than the batch size. 3. A conflicting option such as `unique()` does not fail when you call `session.scalars()`; the error is raised when rows are fetched, so a test must actually iterate the result to catch it. 4. Memory should stay roughly flat as the loop advances; steady growth means something, often your own list, is keeping finished batches alive. ## Memory and the identity map Objects loaded through `yield_per` still go into the Session's identity map, which holds them only **weakly**: once your code drops a finished batch, unmodified objects can be garbage collected. Objects you modify are held strongly until the next flush. For a read-then-write job, the cleanest shape is to read with `yield_per`, build plain dicts of new values per partition, and write them with `session.execute(update(Product), dicts)` rather than modifying the streamed objects.

  • Why does calling `.all()` on a SQLAlchemy result read with `yield_per` defeat the option?
    `yield_per` only limits how many rows are fetched and objects built per batch while you iterate. `.all()` keeps iterating until the end and collects every object into one list, so you are back to 200,000 objects in memory at once. Iterate the result or use `.partitions()` instead.
  • Why can `selectinload()` work with `yield_per` when `joinedload()` of a collection cannot?
    A joined collection repeats each parent once per child row and needs uniquing across the whole result, which a streamed result cannot provide. `selectinload()` instead loads children with a separate SELECT ... IN for each batch of parents. That needs a second cursor while the first is still open, so it works where the driver supports several independent cursors.

  • yield_per is a LIMIT, so only the first batch of rows is returned.
  • Calling .partitions() alone streams results without yield_per.
  • yield_per works with unique() by uniquing each batch.
  • Objects loaded with yield_per bypass the identity map.