Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Relationships and Loading

Loading Strategies: Lazy, Joined, Selectin and More

Control when related rows load. Compare lazy, joined, selectin, subquery, raise and noload strategies, count the SQL each emits and avoid the N+1 query problem.

When you read author.books, where do the books come from? That depends on the loader strategy of the relationship. The strategy decides whether related rows are fetched on demand, together with the parent in one query, or in a follow-up query for all parents at once. Choosing well is the difference between one query and a thousand.

This lesson shows every common strategy, counts the SQL each one sends and teaches you to spot and fix the famous N+1 problem.

A helper that counts queries

All the examples below share a tiny setup: two tables, five authors with three books each, and a listener that counts every statement sent to the database. Reading the count is how you prove a strategy works.

Python Lazy loading and the N+1 problem

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The default strategy is lazy="select": a relationship loads the first time you touch it, with one extra SELECT per parent. For 5 authors that is 6 queries. For 5,000 authors it is 5,001 round trips to the database, and the page becomes slow even though each query is fast.

The strategies at a glance

Strategy SQL shape Best for
select (lazy, default) One query per parent, on first access Relationships you rarely touch
joined LEFT OUTER JOIN in the parent query Many-to-one / one-to-one, small collections
selectin Second query: WHERE parent_id IN (...) One-to-many and many-to-many collections
subquery Second query re-using the original query as a subquery Legacy; selectin is usually better
raise Raises an error on lazy access Catching accidental N+1 in tests
noload Never loads; always empty Rare; write-only scenarios
write_only / dynamic Returns a query object instead of a collection Huge collections

You can set a strategy as a default on the relationship (relationship(lazy="selectin")) or per query with options. Per-query is usually better: the default stays cheap and each query asks for exactly what it needs.

Per-query options: selectinload and joinedload

Python Fixing N+1 with selectinload and joinedload

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the printed SQL. selectinload sends the parent query and then one SELECT ... WHERE book.author_id IN (?, ?, ?, ?, ?). joinedload sends one query with a LEFT OUTER JOIN. Either way the count is fixed no matter how many authors there are.

When you joinedload a collection, the join repeats each parent once per child. SQLAlchemy 2.0 requires you to call .unique() on the result to de-duplicate parents, which is why the helper above uses .unique(). selectinload does not have this problem, which is one reason it is the usual choice for collections.

Choosing between joined and selectin

  • Many-to-one and one-to-one (book.author): use joinedload. The join adds at most one row per parent, so there is no multiplication.
  • One-to-many and many-to-many (author.books): use selectinload. It avoids the row multiplication, works with LIMIT on the parent query and keeps the SQL simple.
  • A limit on the parent with joinedload of a collection forces SQLAlchemy to wrap the query in a subquery. That is another reason to prefer selectinload there.
  • Nested paths chain: selectinload(Author.books).joinedload(Book.publisher).
flowchart TD
    Q["Need related rows?"] -->|No| L["lazy (default)"]
    Q -->|Yes| K{"Kind of relationship"}
    K -->|"many-to-one / one-to-one"| J["joinedload"]
    K -->|"collection"| S["selectinload"]
    K -->|"huge collection"| W["write_only + paginate"]

A quick decision guide. Start with the default and add loader options only where a query needs the related rows.

raiseload: make N+1 impossible

Setting lazy="raise" (or raiseload() per query) turns any lazy load into an InvalidRequestError. It is perfect in test suites and APIs: if a serializer touches a relationship you did not load, you find out immediately instead of in production.

Python raiseload catches accidental lazy loads

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Loading only the columns you need

Relationships are not the only thing you can tune. load_only() limits the columns of an entity, and defer() postpones a heavy column (such as a large text body) until you touch it:

Python
from sqlalchemy.orm import load_only, defer

select(Author).options(load_only(Author.id, Author.name))
select(Book).options(defer(Book.title))       # fetched on first access

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the default loader strategy of relationship()?

    By default each relationship is loaded lazily with its own SELECT the first time you access it.
  2. 2. You loop over 200 authors and read author.books for each. How many queries run with the default strategy?

    One query for the authors plus one lazy load per author: the N+1 problem.
  3. 3. Which option is usually best for loading a one-to-many collection for many parents?

    selectinload runs one extra IN query and does not multiply parent rows, so it is the usual choice for collections.
  4. 4. What does raiseload do?

    raiseload turns accidental lazy loads into errors so N+1 problems surface early.

Summary

  • A relationship loads lazily by default: one extra query per parent on first access.
  • joinedload uses a LEFT OUTER JOIN; call .unique() when the target is a collection.
  • selectinload issues one extra IN query and is the best default for collections.
  • Prefer per-query options over changing the relationship's default.
  • raiseload and lazy="raise" make N+1 bugs fail loudly.
  • load_only and defer trim columns as well as rows.

Next: Cascades and Self-Referential Relationships