Loading Strategies and N+1

The single biggest performance footgun in SQLAlchemy: lazy loading inside a loop. Knowing which loader to use for which shape of access is most of “make this ORM code fast.”

The default: lazy loading

class User(Base):
    posts: Mapped[list["Post"]] = relationship(back_populates="author")
    # default: lazy="select"

First access of user.posts issues:

SELECT * FROM posts WHERE author_id = :user_id

Subsequent accesses use the cached collection. Fine for one user. Disaster in a loop:

users = session.scalars(select(User).limit(100)).all()
for u in users:
    print(len(u.posts))     # 100 extra SELECTs — N+1

Total queries: 1 + 100. As User count grows, the second number grows linearly.

The five loading strategies

You configure the default in relationship(lazy=...), but the common pattern is to override per-query.

Per-query: joinedload, selectinload

Override the default for one query:

from sqlalchemy.orm import joinedload, selectinload

# joined: one query with outer JOIN
stmt = select(User).options(joinedload(User.posts))
users = session.scalars(stmt).unique().all()

# selectin: 2 queries — fetch users, then SELECT posts WHERE author_id IN (...)
stmt = select(User).options(selectinload(User.posts))
users = session.scalars(stmt).all()

.unique() on the result is needed with joinedload for collections — JOIN produces N rows per parent (one per child), and SQLAlchemy de-duplicates the parent.

When to use which

joined — one-to-one / many-to-one (one extra row at most per parent):

select(Post).options(joinedload(Post.author))    # adds JOIN users ON ...

Cheap, single round trip. Works great for “give me posts plus each post’s author.”

selectin — one-to-many / many-to-many (many children per parent):

select(User).options(selectinload(User.posts))
# Query 1: SELECT * FROM users WHERE ...
# Query 2: SELECT * FROM posts WHERE author_id IN (1, 2, 3, ...)

Two queries, but parent rows aren’t duplicated. For lots of children, this is much faster than joinedload (which multiplies parent rows).

Rule of thumb:

subquery — older, mostly replaced by selectin. Not commonly used in 2.0.

Nested eager loading

# Load users, their posts, and each post's comments
stmt = select(User).options(
    selectinload(User.posts).selectinload(Post.comments)
)

Chains: User → posts → comments. Three queries total, no N+1 anywhere.

Mixing strategies:

stmt = select(Post).options(
    joinedload(Post.author),                      # one JOIN for the FK
    selectinload(Post.comments).joinedload(Comment.user),  # selectin for comments, JOIN for each comment's user
)

Loading only specific columns — load_only

from sqlalchemy.orm import load_only

stmt = select(User).options(load_only(User.id, User.name))

Only id and name are loaded; other columns get deferred (loaded lazily on access). Useful when a model has many heavy columns (bio TEXT, avatar BLOB) you don’t need.

lazy="raise" — catch N+1 in development

Configure relationships to refuse lazy loading:

class User(Base):
    posts: Mapped[list["Post"]] = relationship(lazy="raise")

Now user.posts raises InvalidRequestError unless you explicitly selectinload(User.posts). The query plan must be explicit — no surprise round-trips.

For a milder version: lazy="raise_on_sql" raises only when a SQL query would happen (not when the attribute is already loaded).

For broad enforcement on every relationship, set RelationshipProperty.lazy = "raise" in a Base mixin. In production some teams use raise everywhere and require explicit loading; others use the default lazy and audit query counts in tests.

with_loader_criteria — filter on every load

For “always exclude soft-deleted” globally:

from sqlalchemy.orm import with_loader_criteria

stmt = select(User).options(
    selectinload(User.posts),
    with_loader_criteria(Post, Post.deleted_at.is_(None)),
)

Or as a global event:

from sqlalchemy import event
from sqlalchemy.orm import Session, with_loader_criteria

@event.listens_for(Session, "do_orm_execute")
def _filter_soft_deleted(execute_state):
    if execute_state.is_select:
        execute_state.statement = execute_state.statement.options(
            with_loader_criteria(SoftDeleteMixin, lambda cls: cls.deleted_at.is_(None), include_aliases=True)
        )

Now every query against any soft-deletable model auto-filters out deleted rows.

selectin_polymorphic for inheritance

For class inheritance (joined or single table inheritance), to avoid lazy loading the polymorphic subclasses:

stmt = select(Vehicle).options(selectin_polymorphic(Vehicle, [Car, Truck]))

Loads Car-specific and Truck-specific columns in batched IN queries. Without this, accessing subclass-specific attributes triggers per-row lazy loads.

Counting children efficiently

# Naive: lazy load every user's posts and len() them
users = session.scalars(select(User)).all()
for u in users:
    print(len(u.posts))    # N+1

# Better: count in SQL
stmt = (
    select(User, func.count(Post.id))
    .outerjoin(Post)
    .group_by(User.id)
)
for user, count in session.execute(stmt):
    print(user.name, count)

For ad-hoc counts in a serializer, prefer adding Column(server_default=func.count(...)) via subquery or DB view, or annotate at query time.

Detecting N+1 in tests

from sqlalchemy import event

query_count = 0

@event.listens_for(engine, "before_cursor_execute")
def count_queries(conn, cursor, statement, params, context, executemany):
    global query_count
    query_count += 1

# In test:
query_count = 0
response = client.get("/users")
assert query_count <= 3, f"too many queries: {query_count}"

Or use pytest-sqlalchemy, nplusone, or pytest-query-counter — they automate the assertion and surface offending stack traces.

When lazy loading IS what you want

For “load this one user, occasionally access their profile”:

user = session.get(User, 42)
if some_condition:
    print(user.profile.bio)   # 1 extra query — that's fine

If you’re touching the relationship rarely, lazy is the right default. The problem isn’t lazy loading — it’s lazy loading in a loop.

Loading vs query construction order

# Don't do this — issues queries during loop body, even though you "eagerly" loaded
stmt = select(User)
users = session.scalars(stmt).all()
for u in users:
    print(u.posts)   # N+1, the .options(...) wasn't applied

# Do this
stmt = select(User).options(selectinload(User.posts))
users = session.scalars(stmt).all()
for u in users:
    print(u.posts)   # already loaded

The loading strategy must be set on the original statement that produced the parents. Adding .options() after .scalars() does nothing.

Common pitfalls

• joinedload on a collection without .unique() — parent rows appear N times in the result. Raises MultipleResultsFound on .one() or returns duplicates on .all().
• joinedload on a deep tree — JOIN explodes (parent × children × grandchildren = quadratic+ rows). Switch to selectinload.
• with_loader_criteria applied per-query but you forget on one query path — those rows leak. Use the global event for “always exclude X.”
• load_only and then accessing a deferred column — triggers a lazy load (one query per object). Counterintuitive: it’s the column-level analog of relationship N+1.
• Combining selectinload with limit/offset on the parent — selectinload runs the second query against ALL parent IDs from the first, including all pages worth. Fine for small limits; weird for huge ones.

Common interview confusions

• “joinedload is always faster than lazy loading.” — for one-to-many it’s often worse: parent rows are duplicated by N child rows, network and memory blow up. Use selectinload instead.
• “selectinload is one extra query, that’s bad.” — two queries that avoid N+1 are way better than 101.
• “Setting lazy='joined' in the relationship makes it always eager.” — yes, but it’s a global default. Most teams set lazy='select' (default) and use .options() per query.

Interview angle

• “What’s the N+1 problem in SQLAlchemy?” — accessing a lazy-loaded relationship inside a loop runs one extra query per parent. Fix with joinedload (JOIN for to-one) or selectinload (separate IN query for to-many) on the query’s .options().
• “joinedload vs selectinload — when each?” — joined for many-to-one / one-to-one (one extra row per parent, cheap JOIN). Selectin for one-to-many / many-to-many (avoid duplicating parents N times in the result).
• “How would you guarantee a relationship isn’t lazy-loaded in production?” — set lazy="raise" on the relationship; any access without explicit eager loading raises. Catches N+1s in tests and dev.
• “How do you eagerly load nested relationships?” — chain: selectinload(User.posts).selectinload(Post.comments). Three queries total for User → posts → comments.
• “Why does joinedload need .unique() for collections?” — JOIN produces N rows per parent (one per child); SQLAlchemy de-duplicates the parent on .unique().
• “How do you detect N+1 in tests?” — query counter via before_cursor_execute event, or libraries like nplusone/pytest-query-counter. Assert max query count per endpoint.