TopolearnLazy loading in a loop is the most common ORM performance bug, turning one page view into hundreds of queries.
By default relationships use lazy loading: user.posts runs a SELECT the first time you touch it. Fine for one object, terrible in a loop. Listing 100 users and their posts costs 1 query for users plus 100 for posts: the N+1 problem.
Fix it per query with loader options:
selectinload(User.posts)runs one extra query,SELECT ... WHERE posts.user_id IN (...), for all loaded users. Best default for collections.joinedload(Post.author)adds a LEFT OUTER JOIN to the same query. Best for many-to-one; for collections it multiplies rows, and you must call.unique()on the result.raiseload("*")makes any unloaded relationship access raise, which catches accidental lazy loads in tests and APIs.
Chain options for nested paths: selectinload(User.posts).selectinload(Post.comments). You can also set a default strategy on the relationship itself, relationship(lazy="selectin"), if you almost always need it.
When you already join for filtering, contains_eager(User.posts) tells the ORM to populate the collection from the joined rows instead of querying again; just note the collection then contains only the rows your filter kept.
Lazy loading also fails outright on detached objects and under asyncio, so explicit loading is a good habit everywhere. Turn on echo=True and count queries to spot N+1 early.
Example
from sqlalchemy import ForeignKey, create_engine, select, event
from sqlalchemy.orm import (DeclarativeBase, Mapped, mapped_column, relationship,
Session, selectinload)
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
posts: Mapped[list["Post"]] = relationship()
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
engine = create_engine("sqlite://"); Base.metadata.create_all(engine)
queries = []
event.listen(engine, "before_cursor_execute", lambda *a: queries.append(1))
with Session(engine) as s:
s.add_all([User(posts=[Post(), Post()]) for _ in range(10)]); s.commit()
queries.clear()
for u in s.scalars(select(User)): len(u.posts) # lazy
print(len(queries)) # 11
s.expire_all(); queries.clear()
for u in s.scalars(select(User).options(selectinload(User.posts))): len(u.posts)
print(len(queries)) # 2Remember
- Default lazy loading = one query per object when used in a loop (N+1)
- selectinload for collections, joinedload for many-to-one
- raiseload catches accidental lazy loads
- contains_eager reuses a join you already wrote
Watch out for
- joinedload on a collection without .unique() on the result.
- Fixing N+1 globally with lazy='joined' everywhere and making every query huge.