Module 4: The N+1 problem with SQLAlchemy

`subqueryload`: the third strategy you almost never choose

Next →

Capsule overview

SQLAlchemy gives you three eager loading strategies: joinedload, selectinload, and subqueryload. In capsule 04 you mastered the first two, which cover 99% of real cases. This capsule covers the third.

Why dedicate a whole capsule to something you barely use? Because you'll run into subqueryload in legacy code. Python apps that migrated from SQLAlchemy 1.x still have subqueryload(...) scattered all over the codebase, and you don't know whether to:

  • Replace it with selectinload (the standard migration and almost always the right one).
  • Leave it as is (the cases where it still makes sense).
  • Remove it and use lazy (the cases where eager loading was never needed).

This capsule teaches you to:

  • Recognize the SQL that subqueryload generates (a correlated subquery).
  • Compare its plan with selectinload's.
  • Identify the few cases where subqueryload still wins (spoiler: very few in SQLAlchemy 2.0).
  • Migrate legacy code with subqueryload to selectinload with confidence.

By the end, you'll know whether to leave subqueryload when you find it, or change it, and why.

Mental model: the subquery that correlates with the parent

joinedload does 1 query with a LEFT JOIN. selectinload does 2 queries: parent + WHERE id IN (list). subqueryload does 2 queries too, but the second one isn't an IN — it's a correlated subquery that repeats the parent's WHERE.

Compare it like this:

You're at the supermarket with a list of 50 products. Three ways to shop:

  1. joinedload: you walk every aisle making a single combined list. One pass, a lot of walking, all the products in a giant cart with duplication.
  2. selectinload: the cashier gives you the 50 products with SKU IN (ids). Efficient, two questions: "which products do you want" and then "here are the 50 that have these SKUs".
  3. subqueryload: the cashier tells you "give me the 50 products your list asked for" — but re-runs your entire list on the server side to find them. The products-side query repeats your list's filter (the "correlated subquery").

The third is more complicated and, except in specific cases, slower. The historical reason for its existence: when it was designed, there was no elegant way to pass large lists of IDs in SQL queries. Today WHERE col IN (list) (which selectinload uses) is efficient and supported, and subqueryload lost relevance.

The SQL that subqueryload generates

Take a typical query:

from sqlalchemy import select
from sqlalchemy.orm import subqueryload

stmt = (
    select(Author)
    .options(subqueryload(Author.books))
    .where(Author.country == "AR")
    .limit(10)
)
result = (await session.scalars(stmt)).all()

With echo=True, you see two queries:

Query 1 (parent):

SELECT authors.id, authors.name, authors.country
FROM authors
WHERE authors.country = 'AR'
LIMIT 10

Same as with selectinload. It brings the 10 authors.

Query 2 (children with a correlated subquery):

SELECT books.id, books.title, books.author_id, anon_1.authors_id AS anon_1_authors_id
FROM (
    SELECT authors.id AS authors_id
    FROM authors
    WHERE authors.country = 'AR'
    LIMIT 10
) AS anon_1
JOIN books ON books.author_id = anon_1.authors_id
ORDER BY anon_1.authors_id

Compare it with selectinload's query 2:

SELECT books.id, books.title, books.author_id
FROM books
WHERE books.author_id IN (id1, id2, ..., id10)

Key difference:

  • selectinload uses the result of query 1 to build a WHERE id IN (list). Cleaner, easier for the planner.
  • subqueryload re-runs query 1 as a nested subquery. More complex, the planner has to evaluate the subquery first and then do the JOIN.

Why subqueryload repeats the WHERE

subqueryload was designed when passing large lists of IDs as literal parameters was problematic (query size limits, slow parsing). The idea was: "let PostgreSQL run the subquery, which is better than passing the IDs one by one".

Today that reason doesn't apply. WHERE col IN (list of hundreds) is efficient in PostgreSQL, and selectinload takes advantage of that support directly.

The PostgreSQL plan: subqueryload vs selectinload

Let's compare plans with the same dataset.

Setup

-- 5,000 authors, 100 with country='AR'
-- 50 books per author on average

selectinload plan

Query 1 (parent):

Index Scan using idx_authors_country on authors  (cost=0.42..15.20 rows=10 width=24) (actual time=0.085..0.124 rows=10 loops=1)
  Index Cond: (country = 'AR')
Limit  (cost=...)
Execution Time: 0.182 ms

Query 2 (children with IN):

Bitmap Heap Scan on books  (cost=24.30..1850.00 rows=500 width=44) (actual time=0.412..2.145 rows=500 loops=1)
  Recheck Cond: (author_id = ANY ('{id1,id2,...,id10}'::integer[]))
  Heap Blocks: exact=180
  ->  Bitmap Index Scan on idx_books_author_id  (cost=0.00..24.18 rows=500 width=0) (actual time=0.345..0.345 rows=500 loops=1)
        Index Cond: (author_id = ANY ('{id1,id2,...,id10}'::integer[]))
Execution Time: 2.418 ms

Total: ~2.6ms.

subqueryload plan

Query 1 (parent): identical to selectinload's.

Query 2 (children with a correlated subquery):

Hash Join  (cost=15.62..1925.50 rows=500 width=48) (actual time=0.245..3.012 rows=500 loops=1)
  Hash Cond: (books.author_id = anon_1.authors_id)
  ->  Seq Scan on books  (cost=0.00..1650.00 rows=100000 width=44) (actual time=0.012..1.245 rows=100000 loops=1)
  ->  Hash  (cost=15.20..15.20 rows=10 width=4) (actual time=0.182..0.182 rows=10 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Subquery Scan on anon_1  (cost=0.00..15.20 rows=10 width=4) (actual time=0.124..0.182 rows=10 loops=1)
              ->  Limit  (cost=0.42..15.10 rows=10 width=24) (actual time=0.118..0.180 rows=10 loops=1)
                    ->  Index Scan using idx_authors_country on authors  (cost=0.42..152.00 rows=100 width=24)
                          Index Cond: (country = 'AR')
Execution Time: 3.245 ms

Total: ~3.4ms.

Analysis

  • selectinload: 2.6ms total. Direct Bitmap Index Scan on books.
  • subqueryload: 3.4ms total. Hash Join with a nested subquery that re-runs the authors filter.

selectinload is ~25% faster in this case. The difference scales with the size of the dataset and the complexity of the parent's WHERE. For a simple WHERE with a low LIMIT, the difference is modest. For a complex WHERE with joins in the parent, subqueryload becomes significantly more expensive because the full subquery is re-run.

Worst case: a parent with a JOIN in its WHERE.

stmt = (
    select(Author)
    .join(Award, Award.author_id == Author.id)
    .where(Award.year == 2024)
    .options(subqueryload(Author.books))
)

subqueryload re-runs the JOIN with awards inside the nested subquery. selectinload only needs the resulting IDs — the complexity of the parent's WHERE doesn't affect query 2.

The few cases where subqueryload still makes sense

In SQLAlchemy 2.0, subqueryload is rarely the right choice. But there are niches:

Case 1: when selectinload's "IN list" becomes too large

If your parent query returns 50,000+ rows and you want to load children, selectinload's IN list would be:

WHERE author_id IN (1, 2, 3, ..., 50000)

PostgreSQL handles this, but the query parsing is non-trivial and some drivers have limits. subqueryload avoids the literal list by using the subquery.

In practice: if you're loading 50k parents in one request, you already have a design problem (paginate). subqueryload doesn't save you — it papers over the symptom.

Case 2: when the parent's WHERE is huge and expensive

If your parent has a WHERE that does a lot of work and you want to avoid replicating it:

stmt = (
    select(Author)
    .where(Author.country.in_(["AR", "BR", "CL", ...]))
    .where(Author.bio.contains("nobel"))
    .where(Author.created_at > some_date)
)

selectinload runs this WHERE once (in query 1) and then uses only the IDs. subqueryload replicates it as a subquery (in query 2 too).

But: selectinload is always the better case. If the WHERE is expensive, subqueryload does it twice. selectinload only does it once.

Case 3: compatibility with old code

If you work in a large codebase with subqueryload scattered around, it doesn't make sense to change everything at once. Migrate gradually, prioritizing the hottest endpoints.

Practical summary

In SQLAlchemy 2.0, subqueryload is almost never the best option. If you find it in new code, change it to selectinload. If you find it in legacy code, migrate it when you touch that part (not as a speculative refactor).

Migrating from subqueryload to selectinload

The migration is straightforward: change the import and the function name.

Before:

from sqlalchemy.orm import subqueryload

stmt = (
    select(Author)
    .options(subqueryload(Author.books))
    .where(Author.country == "AR")
)

After:

from sqlalchemy.orm import selectinload

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .where(Author.country == "AR")
)

Post-migration validation

After changing, verify with echo=True that the queries are what you expect:

-- Before (subqueryload)
SELECT books.id, ...
FROM (SELECT authors.id FROM authors WHERE country = 'AR') AS anon_1
JOIN books ON books.author_id = anon_1.id

-- After (selectinload)
SELECT books.id, ...
FROM books
WHERE books.author_id IN (id1, id2, ...)

A visible and expected difference. Measure latency with EXPLAIN ANALYZE to confirm the plan is the same or better.

Special case: subqueryload with ORDER BY on the parent

If your parent query has ORDER BY, subqueryload preserves the order in the nested subquery:

stmt = (
    select(Author)
    .options(subqueryload(Author.books))
    .order_by(Author.created_at.desc())
    .limit(10)
)

selectinload also preserves the parent's order — the authors follow query 1's ORDER BY, and query 2's books get assigned to the correct author. There's no functional difference.

Exception: if you depend on the specific order of the rows returned by query 2 (not the parent → children assignment), review it. SQLAlchemy 2.0 with selectinload doesn't guarantee a particular order for the children rows — if you want order, add .order_by() to the relationship or do the query manually.

Why this matters in real work

1. Refactoring SQLAlchemy 1.x → 2.0 apps.

If your team has an app with subqueryload everywhere, migrating to selectinload is typically a 5-25% latency improvement with zero risk. It's the easiest refactor to justify to your manager.

2. Detection in code review.

When you see subqueryload in a new PR, you know the dev is copying from an old blog or an out-of-date Stack Overflow answer. It's a sign that the team needs to update its internal documentation.

3. Understanding why selectinload is the recommended default.

The official SQLAlchemy 2.0 documentation says "use selectinload by default". If you don't understand why (the answer is "it's the modern version of subqueryload, without its downsides"), you can't justify the choice in reviews.

4. Avoiding over-engineering.

Some devs read a blog that says "subqueryload is better for X edge case" and start using it defensively. Knowing the real cases (which are few) prevents you from that bias.

Traps and common mistakes

Mistake 1 (conceptual): thinking subqueryload is "more efficient because it uses a subquery"

Symptom: "Subquery sounds more sophisticated, it must be better."

Why it's wrong: "subquery" in this context is just the SQL mechanism — it's not necessarily faster. subqueryload's correlated subquery re-runs the parent's WHERE, which is additional work vs selectinload's WHERE id IN (list).

How to distinguish: look at the generated SQL and the EXPLAIN plan. selectinload almost always has a simpler and faster plan.

Mistake 2 (practical): mixing subqueryload with selectinload in the same query

Symptom: select(Author).options(subqueryload(Author.books).selectinload(Book.reviews)) → confusing behavior.

Why it happens: SQLAlchemy allows mixing, but the generated SQL gets weird: a correlated subquery for the first level, an IN list for the second. It works, but it's hard to reason about and maintain.

How to fix it: consistency. If you're going to use selectinload, use it at all levels. If you have to keep subqueryload for some legacy reason, keep it at all levels of that query.

Mistake 3 (conceptual): assuming subqueryload avoids the "IN list limit"

Symptom: "I switched to subqueryload because my selectinload with 100k authors fails."

Why it's partially true: selectinload with 100k IDs generates a query with WHERE id IN (list of 100k numbers). Some drivers have limits; PostgreSQL technically supports it but the parsing is slow.

Why the solution isn't subqueryload: if you're loading 100k parents in a single request, you have a pagination problem, not an eager loading one. The correct solution is to paginate the endpoint to a maximum of 100-500 parents per page.

How to fix it: paginate. If you need to process 100k entities, do it in batches via a background job, not in an HTTP endpoint.

Mistake 4 (practical): not measuring before migrating

Symptom: "I changed subqueryload to selectinload in 50 files and noticed no improvement."

Why it happens: the improvement of selectinload vs subqueryload is typically 5-25%. If your endpoint was slow for other reasons (a missing index, a badly written query), changing the loader doesn't solve that.

How to fix it: measure before/after on specific endpoints. If the improvement is < 5%, don't prioritize the mass migration. Migrate opportunistically when you touch those files for other reasons.

Mistake 5 (conceptual): thinking subqueryload is deprecated

Symptom: "I saw in the docs that selectinload is the default now. Does that mean subqueryload is deprecated?"

Why it's nuanced: subqueryload is NOT deprecated in SQLAlchemy 2.0. It still works, is supported, and is maintained. It's just that selectinload is preferred for almost all new cases.

How to distinguish: "not recommended for new cases" ≠ "deprecated". Your code with subqueryload won't break in a future version. But when writing new code, don't choose it.

Exercises

Exercise 1: identify the SQL of subqueryload

Given this code, write the approximate SQL that SQLAlchemy generates:

stmt = (
    select(Author)
    .options(subqueryload(Author.books))
    .where(Author.name.like("a%"))
    .limit(20)
)
See solution

Query 1 (parent):

SELECT authors.id, authors.name, authors.country
FROM authors
WHERE authors.name LIKE 'a%'
LIMIT 20

Query 2 (children with a correlated subquery):

SELECT books.id, books.title, books.author_id, anon_1.authors_id AS anon_1_authors_id
FROM (
    SELECT authors.id AS authors_id
    FROM authors
    WHERE authors.name LIKE 'a%'
    LIMIT 20
) AS anon_1
JOIN books ON books.author_id = anon_1.authors_id
ORDER BY anon_1.authors_id

Notice how query 2 re-runs the parent's WHERE and LIMIT. That's the "correlated subquery" that defines subqueryload.

Exercise 2: compare plans with EXPLAIN ANALYZE

Take the bookstore. Run two versions of the same endpoint:

# Version A: subqueryload
stmt_a = select(Author).options(subqueryload(Author.books)).where(Author.id == 41)

# Version B: selectinload
stmt_b = select(Author).options(selectinload(Author.books)).where(Author.id == 41)

Capture the SQL and the plan of query 2 (the children one) in each case. Compare total time.

See solution

Version A — subqueryload:

SQL query 2:

SELECT books.id, books.title, books.author_id, anon_1.authors_id AS anon_1_authors_id
FROM (SELECT authors.id AS authors_id FROM authors WHERE authors.id = 41) AS anon_1
JOIN books ON books.author_id = anon_1.authors_id
ORDER BY anon_1.authors_id

Typical plan (with an index on books(author_id)):

Nested Loop  (cost=0.85..82.45 rows=20 width=48) (actual time=0.124..0.612 rows=20 loops=1)
  ->  Index Only Scan using authors_pkey on authors  (cost=0.42..2.44 rows=1 width=4) (actual time=0.045..0.046 rows=1 loops=1)
        Index Cond: (id = 41)
  ->  Index Scan using idx_books_author_id on books  (cost=0.42..79.85 rows=20 width=44) (actual time=0.075..0.560 rows=20 loops=1)
        Index Cond: (author_id = 41)
Execution Time: 0.682 ms

Version B — selectinload:

SQL query 2:

SELECT books.id, books.title, books.author_id
FROM books WHERE books.author_id IN (41)

Plan:

Bitmap Heap Scan on books  (cost=4.45..82.50 rows=20 width=44) (actual time=0.124..0.412 rows=20 loops=1)
  Recheck Cond: (author_id = ANY ('{41}'::integer[]))
  Heap Blocks: exact=20
  ->  Bitmap Index Scan on idx_books_author_id  (cost=0.00..4.44 rows=20 width=0) (actual time=0.085..0.085 rows=20 loops=1)
        Index Cond: (author_id = ANY ('{41}'::integer[]))
Execution Time: 0.482 ms

Comparison:

  • subqueryload: ~0.7ms (Nested Loop with a nested subquery).
  • selectinload: ~0.5ms (direct Bitmap Index Scan).

selectinload ~30% faster. For a simple WHERE with LIMIT, the difference is modest. For a complex WHERE, the gap widens.

Exercise 3: migrate legacy code

You have this function in a legacy codebase:

async def get_authors_with_books_legacy(country: str, session: AsyncSession):
    stmt = (
        select(Author)
        .options(
            subqueryload(Author.books).subqueryload(Book.reviews)
        )
        .where(Author.country == country)
        .order_by(Author.name)
        .limit(50)
    )
    return (await session.scalars(stmt)).all()

Migrate it to selectinload. Justify whether the migration is safe.

See solution

Migration:

async def get_authors_with_books(country: str, session: AsyncSession):
    stmt = (
        select(Author)
        .options(
            selectinload(Author.books).selectinload(Book.reviews)
        )
        .where(Author.country == country)
        .order_by(Author.name)
        .limit(50)
    )
    return (await session.scalars(stmt)).all()

Safety justification:

  1. Functionally equivalent: both load Author.books and Book.reviews eagerly. The result in Python is identical — the same Author object with the same list of books with the same reviews.

  2. Performance: selectinload is almost always faster. For this query (50 authors × N books × M reviews), the change reduces complex queries with subqueries to simple queries with WHERE id IN (...).

  3. Order preservation: the .order_by(Author.name) on the parent is preserved. The books and reviews get assigned correctly to each author.

  4. Post-migration validation: capture with echo=True and confirm:

    • Before: 3 queries with nested subqueries.
    • After: 3 queries with WHERE id IN (...).
    • Same number of rows returned, same Python objects built.

When NOT to migrate immediately:

  • If the endpoint has rigorous tests: run the tests first, make sure they pass after the change.
  • If the endpoint is critical (financial, auth, etc.): migrate in an isolated PR with before/after metrics.
  • If your codebase has 200 uses of subqueryload: don't migrate them all in one PR. Do it gradually.

Recommended test post-migration:

@pytest.mark.asyncio
async def test_get_authors_uses_selectin(client):
    # Capture queries with an event listener
    queries = []
    @event.listens_for(engine.sync_engine, "before_cursor_execute")
    def capture(conn, cursor, statement, *args):
        queries.append(statement)

    await get_authors_with_books("AR", session)

    # Validate that there's NO nested subquery
    for q in queries:
        assert "FROM (SELECT" not in q, f"Nested subquery detected: {q}"
    # Validate that there IS an IN list
    assert any("IN (" in q for q in queries), "selectinload should use WHERE IN"

Exercise 4: identify an edge case where subqueryload can win

Think of a case (it can be hypothetical) where subqueryload would produce a more efficient plan than selectinload. Justify it with reasoning about cardinality and/or the complexity of the parent's WHERE.

See solution

Hypothetical case: a parent with an expensive WHERE using a non-indexed function, but the result is very selective.

stmt = (
    select(Order)
    .where(func.expensive_calculation(Order.metadata) == "rare_value")
    .options(subqueryload(Order.items))
)

Reasoning:

  • expensive_calculation is a heavy Python or SQL function (doesn't use an index).
  • Only 5 orders match (high selectivity).
  • selectinload would run the filter, collect the 5 IDs, and do WHERE order_id IN (id1, ..., id5) in query 2.
  • subqueryload would run the filter TWICE (once in query 1, once in query 2's subquery), but the final JOIN is direct.

When could subqueryload win here? Almost never. The heavy function runs twice, which is worse.

A more realistic "edge case" for subqueryload:

If your app can't pass IN lists with many parameters (a very old driver, a restriction from your DB proxy, etc.) and you have 50,000 parents:

# selectinload generates: WHERE id IN (50,000 values) → may fail in some setups
# subqueryload generates: WHERE id IN (SELECT ... LIMIT 50000) → friendlier to some parsers

But this is fixing a symptom, not a cause. If you need to load 50k parents, paginate. subqueryload isn't the right solution — the endpoint's design is what should change.

Honest conclusion:

In SQLAlchemy 2.0 with PostgreSQL 16+ and FastAPI + asyncpg, there's no real case where subqueryload clearly wins over selectinload. If you find one, measure it carefully — it's probably a coincidence or a particular detail of your setup.

Exercise 5: detect subqueryload in code review

You're reviewing a PR. You see this in review_models.py:

@app.get("/articles/{article_id}/full")
async def article_full(article_id: int, session: AsyncSession = Depends(get_db)):
    stmt = (
        select(Article)
        .options(
            subqueryload(Article.comments).joinedload(Comment.author),
            subqueryload(Article.tags),
        )
        .where(Article.id == article_id)
    )
    article = await session.scalar(stmt)
    return article

What do you comment on the PR review? Justify your suggestions.

See solution

Suggested comment:

Hey, two suggestions about eager loading:

  1. subqueryload → selectinload in both cases (Article.comments and Article.tags). In SQLAlchemy 2.0, selectinload is the recommended default and almost always generates simpler and faster plans. subqueryload is legacy.

  2. joinedload(Comment.author) is fine — it's 1:1 (each comment has one author), joinedload is optimal for 1:1.

Suggested change:

stmt = (
    select(Article)
    .options(
        selectinload(Article.comments).joinedload(Comment.author),
        selectinload(Article.tags),
    )
    .where(Article.id == article_id)
)

No functional risk (same result in Python). Probable 10-20% improvement in query 2 latency.

If you want to see the difference, capture the SQL with echo=True before and after — you'll see that subqueryload generates FROM (SELECT ... LIMIT) JOIN ... and selectinload generates WHERE id IN (...).

Why this comment is senior:

  • Identifies the subqueryload as legacy.
  • Correctly distinguishes joinedload (1:1, optimal) from selectinload (1:N, optimal in this case).
  • Gives the exact code for the change.
  • Suggests validation with echo=True.
  • Justifies with data (a reference to SQLAlchemy 2.0 best practice).
  • No scolding tone — it's a constructive suggestion.

Exercise 6: special case with dynamic loading

You have a case where, depending on a parameter, you want to load or not load the relationship:

async def get_author(
    author_id: int,
    include_books: bool = False,
    session: AsyncSession = Depends(get_db),
):
    options = []
    if include_books:
        options.append(selectinload(Author.books))

    stmt = select(Author).options(*options).where(Author.id == author_id)
    return await session.scalar(stmt)

Is it valid to apply options conditionally? Any caveat?

See solution

Yes, it's completely valid. It's a clean pattern for flexible endpoints.

How it works:

  • If include_books=False: options is empty → simple query without eager loading.
  • If include_books=True: options=[selectinload(Author.books)] → query with eager loading.

SQLAlchemy processes .options(*[]) (empty) as "no extra options". It works perfectly.

Caveats:

  1. Careful with lazy relationships + async. If the client does NOT ask for books but your serializer (a Pydantic response model) tries to access author.books, you'll have problems. In async, lazy loading isn't straightforward (capsule 06). Make sure the response model adjusts dynamically.

    Typical pattern:

    from pydantic import BaseModel

class AuthorResponse(BaseModel):
    id: int
    name: str

class AuthorWithBooksResponse(AuthorResponse):
    books: list[BookResponse]

@app.get("/authors/{author_id}")
async def get_author(
    author_id: int,
    include_books: bool = False,
    session: AsyncSession = Depends(get_db),
) -> AuthorResponse | AuthorWithBooksResponse:
    options = []
    if include_books:
        options.append(selectinload(Author.books))
    stmt = select(Author).options(*options).where(Author.id == author_id)
    author = await session.scalar(stmt)
    if include_books:
        return AuthorWithBooksResponse.model_validate(author)
    return AuthorResponse.model_validate(author)

  2. nplusone can get confused. If you enable nplusone with raise mode and a test exercises the include_books=False case that still ends up accessing author.books in some conditional serializer, you'll have false positives. Solution: whitelist or make sure the serializer respects the flag.

  3. Query caching. If you have query caching and the set of options changes between requests, each combination has its own cached plan. It's not a problem in general, but something to keep in mind for plan cache analysis.

Applicability to subqueryload: this pattern applies identically to subqueryload (change the function name). But as we already saw, selectinload is preferable.

Summary and next step

In this capsule you learned:

  • subqueryload runs 2 queries: parent + a correlated subquery that repeats the parent's WHERE and JOINs with the children table.
  • vs selectinload: selectinload uses WHERE id IN (list), simpler and faster. subqueryload re-runs the WHERE in the subquery, more complex.
  • Typical plan: selectinload ~25% faster than subqueryload in common cases; a bigger gap with an expensive WHERE.
  • Cases where subqueryload still has a niche: very few in SQLAlchemy 2.0 — giant IN lists (a symptom of missing pagination), old drivers, compatibility with legacy code.
  • Migration to selectinload: trivial (change the function name). Validate with echo=True and EXPLAIN. Do it gradually, not as a big-bang.
  • subqueryload is NOT deprecated, just not recommended for new code.

Before moving on you should be able to:

  • Identify subqueryload in code and recognize the SQL it generates.
  • Migrate to selectinload with confidence, validating with echo=True.
  • Argue in a code review why subqueryload isn't the right choice for new code.
  • Recognize the few edge cases where subqueryload could hold up (and understand that there's almost always a better solution).

Next capsule — Async relationships and asyncio. So far you've handled loading without qualifying the "async context". But AsyncSession changes the rules: direct lazy loading (author.books) raises an exception instead of firing a query. You need awaitable_attrs or explicit eager loading from the start. Capsule 06 shows you the canonical patterns of FastAPI + AsyncSession + relationships, the gotchas (DetachedInstanceError, MissingGreenlet), and why selectinload becomes even more important in async.

Resources

  1. SQLAlchemy 2.0 — Subquery Eager Loading — the official reference for subqueryload. Explains the mechanism and limitations.
  2. SQLAlchemy 2.0 — Relationship Loading Techniques (summary) — a comparison table between the three strategies.
  3. SQLAlchemy Migration 1.x → 2.0 — the "selectin loading is the default eager loader" section where the change in recommendation is justified.
  4. Mike Bayer — "What's the deal with subqueryload" (mailing list) — historical discussions about why subqueryload is legacy.
  5. PostgreSQL Documentation — Subqueries — subquery fundamentals in PostgreSQL to understand what subqueryload does at the SQL level.
  6. Use The Index, Luke — "Subqueries" — the performance of subqueries vs JOINs in general.

Module 4 — Database Performance & Query Tuning Guide

← Previous`joinedload` vs `selectinload`: when each one winsNext →Relationships and `AsyncSession`: why async changes the rules