In SQLAlchemy 2.1, how do load_only(), defer() and undefer() decide which columns load for a page listing 10,000 Event rows?
answer
- columns, not relationships
- primary key always comes along
- unloaded column lazy-loads per object
- raiseload=True on the column option
basics
~20 sload_only() names the columns to fetch, plus the primary key; defer() drops one column; undefer() fetches a column the mapping defers. Reading an unloaded column runs one SELECT per object unless raiseload=True makes it raise.
solid answer
~30 sColumn loader options shape the SELECT for mapped columns the way relationship options shape related rows. `load_only(Event.kind, Event.created_at)` emits `SELECT events.id, events.kind, events.created_at`: the named columns plus the primary key. `defer(Event.payload)` keeps every column except `payload`, and `mapped_column(Text, deferred=True)` makes that the mapping default, which `undefer(Event.payload)` reverses per query. A deferred column read later emits `SELECT events.payload FROM events WHERE events.id = ?` for that one object, so touching it in a loop over 10,000 events runs 10,000 statements. `defer(..., raiseload=True)`, `load_only(..., raiseload=True)` or `mapped_column(deferred_raiseload=True)` turns that read into `InvalidRequestError` instead.
code
python · 48 linesfrom datetime import datetime
from sqlalchemy import ForeignKey, Text, create_engine, select
from sqlalchemy.exc import InvalidRequestError
from sqlalchemy.orm import (DeclarativeBase, Mapped, Session, defer, load_only,
mapped_column, undefer)
class Base(DeclarativeBase):
pass
class Event(Base):
__tablename__ = "events"
id: Mapped[int] = mapped_column(primary_key=True)
customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
kind: Mapped[str]
created_at: Mapped[datetime]
payload: Mapped[str] = mapped_column(Text, deferred=True) # deferred on the mapping
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(primary_key=True)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
s.add(Customer(id=1))
s.add_all(Event(customer_id=1, kind="click", created_at=datetime(2026, 1, 1),
payload="{}") for _ in range(3))
page = select(Event).where(Event.customer_id == 1).order_by(Event.created_at)
print(page.options(load_only(Event.kind, Event.created_at)))
# SELECT events.id, events.kind, events.created_at ... (primary key always added)
print(page.options(undefer(Event.payload)))
# SELECT events.id, events.customer_id, events.kind, events.created_at, events.payload ...
with Session(engine) as s:
events = s.scalars(page.options(defer(Event.created_at, raiseload=True))).all()
print(events[0].payload) # deferred on the mapping: lazy-loads with its own SELECT
try:
events[0].created_at
except InvalidRequestError as err:
print(err) # 'Event.created_at' is not available due to raiseload=Truego deeper
Remember that load_only() names the columns to keep, defer() names one to drop, and undefer() loads a column the mapping defers.
Explain that a deferred column lazy-loads with one SELECT per object, and show how raiseload=True or deferred_raiseload=True turns that into an error.
Spot a template that touches a deferred column in a loop, fix it with undefer() or load_only() on that query, and narrow columns under selectinload() for collections.
Decide which wide columns a team defers on the mapping with deferred_raiseload, trading safer defaults for an explicit undefer() wherever a column is needed.
## Column deferral in one paragraph A mapped class normally loads every mapped column in its SELECT. On a page that lists one customer's 10,000 events, a wide `payload` text column can dominate the bytes transferred even though the page shows only `kind` and `created_at`. SQLAlchemy 2.1 lets a statement, or the mapping, **defer** columns: leave them out of the SELECT and load them only if code asks. These are **column loader options**; `raiseload()`, `selectinload()` and the other relationship options do not apply to columns. ## The three options - **`load_only(*attrs)`** names the columns to load. Everything else outside the primary key is deferred. `select(Event).options(load_only(Event.kind, Event.created_at))` emits `SELECT events.id, events.kind, events.created_at FROM events ...`. The primary key is added because the ORM needs it to give each row an identity; other columns such as `customer_id` are left out. - **`defer(attr)`** removes one column and keeps the rest; use it when the list of columns to drop is shorter than the list to keep. - **`undefer(attr)`** does the opposite for a column the **mapping** defers. With `payload: Mapped[str] = mapped_column(Text, deferred=True)`, every query skips `payload` unless it says `undefer(Event.payload)`. `undefer_group()` does the same for a named group of deferred columns. | Tool | Where | Effect | |---|---|---| | `load_only(a, b)` | statement | load `a`, `b` and the primary key | | `defer(c)` | statement | load everything except `c` | | `mapped_column(..., deferred=True)` | mapping | skip the column unless a query undefers it | | `undefer(c)` | statement | load a mapping-deferred column up front | | `raiseload=True` / `deferred_raiseload=True` | either | reading the unloaded column raises | ## What happens when code reads a deferred column A deferred column is lazy: the first read on an object emits a SELECT for that column and that row only, inside the Session's transaction. ```text SELECT events.payload FROM events WHERE events.id = ? ``` That is harmless for one object and expensive in a loop. A template that prints `event.payload` for each of 10,000 rows turns one page query into 10,001 statements, the column equivalent of a lazy-loaded collection. Two ways to make the mistake visible: 1. Pass `raiseload=True`: `defer(Event.payload, raiseload=True)` or `load_only(Event.kind, Event.created_at, raiseload=True)`. Reading an unloaded column then raises `InvalidRequestError: 'Event.payload' is not available due to raiseload=True`. 2. Put it on the mapping: `mapped_column(Text, deferred_raiseload=True)` implies `deferred=True` and raises unless a statement undefers the column. ## Combining with relationship options Column options also chain under relationship options, which matters when the events arrive as a collection rather than as the main entity: - `select(Customer).options(selectinload(Customer.events).load_only(Event.kind, Event.created_at))` narrows the second SELECT's column list. - `defaultload(Customer.events).load_only(...)` keeps the relationship's own loading style and only narrows its columns. ## Planning the 10,000-event page A sensible order of work for that page: 1. List the attributes the template actually reads, here `kind` and `created_at`. 2. Write the query with `load_only(Event.kind, Event.created_at, raiseload=True)`, so a template change that reads `payload` fails in tests instead of adding 10,000 statements. 3. If `payload` is wide and rarely needed anywhere, defer it on the mapping with `deferred_raiseload=True`, and add `undefer(Event.payload)` to the one detail view that shows it. 4. Read the emitted SELECT in the engine log and confirm the column list matches what step 1 found. The same pattern applies to any wide column: large text, JSON documents, binary blobs. Deferring them is cheap to write and easy to verify in the log, which is why it is often the first fix applied to a slow list page that already has its relationships under control. ## Limits worth knowing - Column options cut **columns**, not **rows**. Ten thousand events are still ten thousand objects; paging or streaming the rows is a separate job, and a collection that should never load whole belongs with relationship configuration, where write-only collections live. - Options apply when an object is loaded. An `Event` already in the Session's identity map keeps the columns it already has; a later query with `load_only()` does not unload them. - If the page needs plain values rather than objects, selecting the columns themselves (`select(Event.kind, Event.created_at)`) returns rows, not `Event` objects; that choice is covered with entity selects and result shapes.
- Why does load_only(Event.kind) still fetch events.id?The ORM needs the primary key to place each row in the Session's identity map, so it is always fetched; load_only() defers every other mapped column outside the primary key.
- How would you make Event.payload never load unless a query asks for it, and fail loudly if code forgets?Map it with mapped_column(Text, deferred_raiseload=True). Queries skip the column, a statement that needs it says undefer(Event.payload), and any other read raises InvalidRequestError instead of emitting a per-row SELECT.
- raiseload(Event.payload) makes a column raise on access
- load_only() fetches only the named columns, not even the primary key
- Reading a deferred column returns None instead of loading it
- Deferring columns reduces how many rows the page loads
- A deferred column loads for all objects at once on first access