How does SQLAlchemy's yield_per execution option change an ORM select(Product) over 200,000 rows, and what will it not combine with?
answer
- default: fetch all, then return
- stream_results plus Result.yield_per
- iterate or partitions(), never all()
- uniquing and joined collections
basics
~20 sBy 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 sNormally 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 linesfrom 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
Recall that yield_per is set with execution_options() and that you iterate the result instead of calling all().
Explain the default full buffering, the stream_results plus Result.yield_per equivalence, partitions(), and why unique() and joined collections are refused.
Show you pair streamed reads with bulk writes by primary key and keep modified objects from piling up in the identity map.
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.