Module 4: The N+1 problem with SQLAlchemy

Module 4: The N+1 problem with SQLAlchemy

Next →

In module 3 you got dangerous with indexes: composite, covering, partial, expression. You already know how to design the index the planner will actually use and validate it with EXPLAIN. If your endpoint was slow because of an inefficient query, you already have the tool to fix it.

But there's a class of slowness that isn't fixed with indexes. A class where every EXPLAIN looks perfect, where every individual query is fast, and yet the endpoint takes 800ms in production. Where the bug isn't in SQL or in PostgreSQL — it's in your ORM.

Welcome to the N+1 problem: the #1 "silent killer" of Python APIs with SQLAlchemy. The bug your APM doesn't detect, that your DBA team doesn't see, and that your boss doesn't understand. In this module you learn to detect it, measure it, and eliminate it.

Where are we? Where are we going?

You already know (modules 1-3):

  • Measure latency with wrk, pgbench, and locust (module 1).
  • Read PostgreSQL plans: cost, rows, buffers, scan types, joins (module 2).
  • Design advanced indexes: composite with the right order, covering with INCLUDE, partial with WHERE, expression (module 3).

In this module you'll learn to:

  • Recognize the N+1 problem in any FastAPI endpoint with SQLAlchemy.
  • Detect it manually with echo=True and automatically with the nplusone library.
  • Differentiate lazy loading (default, dangerous in list endpoints) from eager loading (explicit, controlled).
  • Choose between joinedload, selectinload, and subqueryload depending on the case.
  • Apply raiseload to enforce discipline in production.
  • Eliminate real N+1s from the bookstore project with measurable numbers.

Afterward (module 5) you'll take this skill to production: how to detect N+1s with pg_stat_statements when you no longer have access to echo=True. The clue is simple — a query that appears 5,000 times per minute in pg_stat_statements is almost always a hidden N+1. But that recognition only works if in this module you internalize the pattern.

Professional objective

By the end of this module you'll be able to:

  • Diagnose N+1 in SQLAlchemy code just by reading it, predicting how many queries it fires before running it.
  • Detect N+1s automatically in CI with nplusone configured to fail in pytest when a new one appears.
  • Choose the right eager loading strategy (joinedload vs selectinload vs subqueryload), justifying it with cardinality and the response's shape.
  • Refactor an endpoint with N+1, reducing from 50+ queries to 1-2, measuring the improvement with numbers (queries before/after, p95 latency before/after).
  • Configure raiseload on sensitive relationships so an accidental lazy load fails in tests instead of blowing up in production.

This is the skill that separates a junior backend dev from a senior in any serious technical interview. "How would you handle N+1 in SQLAlchemy?" is a standard mid-to-senior backend Python question.

Why does this module matter?

N+1 is invisible where you look and obvious where nobody looks.

Three reasons why this is the most expensive bug of your career if you don't master it:

1. It's invisible to EXPLAIN. Each individual query of the N+1 is perfectly optimized. EXPLAIN ANALYZE tells you "Index Scan, 0.5ms, all in cache." The plan is ideal. The problem isn't the plan — it's that this plan runs 50 times in a row. And EXPLAIN only shows you one query at a time.

2. It's invisible in development. With 10 authors and 10 books each, the endpoint takes 50ms and nobody notices anything. With 5,000 authors and 200 books each in production, the same endpoint takes 8 seconds. The bug only manifests when the data grows, and by then it's already in production in front of real users.

3. Your APM (Datadog, New Relic, Sentry) reports it as "slow DB" without saying why. The aggregate span is high, but no individual span stands out. The trace shows 50 short queries in series, and nobody wants to look at them one by one. It's the definition of "silent killer".

What mastering it does for you:

  • In your daily work: your endpoints will scale from 10 to 100k users without collapsing.
  • In PR reviews: you'll be the dev who detects N+1s in code that looks clean.
  • In technical interviews: you'll answer "how do you eliminate N+1?" with a concrete decision matrix, not the generic answer of "use eager loading".
  • In postmortems: when your team discovers that endpoint X fires 500 queries per request, you'll be the one who diagnoses and fixes it in an hour instead of a week.

A scenario that illustrates the module

Imagine you join an e-commerce company as a senior backend dev. Your first week, the SRE team shows you this chart: the /orders/{user_id}/details endpoint has a p95 of 4.2 seconds in production. In local development with 100 orders it runs in 80ms. Nobody understands what changes.

You open the code:

@app.get("/orders/{user_id}/details")
async def order_details(user_id: int, session: AsyncSession = Depends(get_db)):
    user = await session.get(User, user_id)
    orders = user.orders  # ← lazy load: 1 query
    result = []
    for order in orders:
        items = order.items  # ← lazy load: 1 query per order
        for item in items:
            product = item.product  # ← lazy load: 1 query per item
        result.append({...})
    return result

If the user has 50 orders and each order 4 items: 1 + 50 + 200 = 251 queries per request.

You enable echo=True, hit the endpoint with a real user, and watch the 251 SQL logs go by. Aha. It's not PostgreSQL — it's your ORM loading relationships one by one.

In this module you learn:

  • To predict that number (251) before running it (capsule 02).
  • To detect it automatically with nplusone so the bug never reaches production again (capsule 03).
  • To choose between selectinload (which would bring it down to 4 queries) and joinedload (which would bring it down to 1 but with caveats) depending on the case (capsules 04-05).
  • To handle the FastAPI async case without tripping over lazy loading (capsule 06).
  • To recognize when eager loading becomes an anti-pattern (capsule 07).
  • To apply all of the above to the real bookstore, measuring before/after (capsule 08).

By the end of the module, that endpoint goes from 251 queries and 4.2s p95 to 4 queries and 200ms p95. Same business code, different way of loading relationships.

Module map

CapsuleTopicWhat you'll learn
01Module introductionContext, objectives, map (this capsule)
02What is N+1 and why it happensThe strict definition, lazy loading default, manual counting with echo=True
03Detecting N+1 with nplusoneSetup in pytest (raise) and FastAPI middleware (warn), CI integration
04joinedload vs selectinloadWhen each one wins, decision matrix, generated SQL, cartesian explosion
05subqueryload and when to use itWhy it's almost never the right choice in SQLAlchemy 2.0
06Async relationships and asyncioLoading patterns with AsyncSession, lazy gotchas in async, awaitable_attrs
07Eager loading anti-patternsOver-fetching, unnecessary eager, raiseload, dangerous * patterns
08Project: eliminating N+1 from the bookstoreAn integrative mini-project with measurable before/after

Narrative flow:

  1. Diagnosis (02-03): first you understand the problem and learn to detect it without libraries and with libraries.
  2. Solution (04-06): then you build the toolkit — the three eager loading strategies and how to apply them in the FastAPI async case.
  3. Discipline (07): you learn not to fall into the opposite extreme (eager loading everything).
  4. Application (08): you consolidate everything in a mini-project that measures the real impact.

Connection with the capstone project

The final project of module 8 includes a canonical N+1 endpoint you already met in module 1: GET /books-with-author?author_name=tolkien. When you captured it with echo=True in module 1, you saw the log with 50+ queries per request. At that moment you only identified it as a problem. In this module you fix it.

The module's mini-project (capsule 08) takes exactly that endpoint, applies the techniques you learned in capsules 02-07, and measures the improvement with wrk and pg_stat_statements. The result is a "before/after" commit you'll be able to quote verbatim in an interview: "I reduced from 51 queries to 2, p95 from 850ms to 95ms".

When you get to module 8, this optimized endpoint gets integrated into the guide's final benchmark along with the other 4 problems (large OFFSET, COUNT(*), missing GIN, untuned pool). By then the N+1 will already be resolved because you resolve it here.

What is NOT covered in this module

The following is important but is not in scope for this module:

  • ❌ Advanced indexing — covered in module 3. Here we assume your indexes are already well designed. N+1 is a problem on top of SQL, not below it.
  • ❌ Profiling with pg_stat_statements — covered in module 5. Here you detect N+1 in dev (with echo=True and nplusone); in production it's module 5.
  • ❌ Connection pooling — covered in module 6. N+1 saturates the pool, yes — but the solution to N+1 is eliminating the extra queries, not enlarging the pool.
  • ❌ Caching with Redis — belongs to another guide in the path. Caching papers over N+1 without fixing it. Learn first not to generate it.
  • ❌ ORM patterns in Django — this guide is FastAPI + SQLAlchemy. If you come from Django, the concepts transfer (select_related, prefetch_related are the equivalents), but the syntax is different.
  • ❌ Sync lazy loading with traditional sessions — this whole module assumes AsyncSession with SQLAlchemy 2.0. If your code is sync, the concepts apply but the details change (you don't need awaitable_attrs, for example).

Traps to avoid while taking it

1. Skipping capsule 02 because "you already know what N+1 is".

Many people can recite the definition but can't look at code and predict how many queries it fires. Capsule 02 trains you in exactly that: reading and counting. Without that training, the solutions in capsules 04-05 are magic recipes instead of informed choices.

2. Treating joinedload as "the universal solution".

It's tempting because "it joins everything in one query" sounds ideal. But for a large 1:N it causes a cartesian explosion (1 author × 100 books × 50 reviews = 5,000 duplicated rows in memory) and ends up being slower than the original N+1. Capsule 04 shows you the explicit case.

3. Ignoring nplusone "because it's easy without the lib".

In your learning app with one endpoint, yes, it's easy to count queries by hand. In a real app with 80 endpoints it's impossible. Capsule 03 integrates nplusone into pytest so that any N+1 that slips into a PR breaks CI before merging. Without that automation, N+1s accumulate.

4. Not showing the generated SQL when choosing a strategy.

Each strategy (joinedload, selectinload, subqueryload) produces different SQL. If you don't see the SQL, you choose blindly. In capsules 04-05 you'll capture the real SQL with echo=True for each case. Do it even if it seems redundant — it's what internalizes the difference.

5. Not connecting with FastAPI specifically.

N+1 typically occurs when the response_model accesses unloaded relationships. Capsule 06 covers the canonical pattern: Depends(get_db), AsyncSession, a response model with relationships. If you skip that context, the techniques become abstract.

Self-evaluation question

Before starting this module, can you answer?

  • What is a "lazy relationship" in SQLAlchemy and when does the associated query fire?
  • How do you enable the SQL log in SQLAlchemy 2.0 (create_async_engine(..., echo=...))?
  • What does select(Author).options(...) do in SQLAlchemy 2.0 (what's the basic syntax of options)?
  • How do you define a Mapped[list["Book"]] relationship in a model class?
  • What's the difference between await session.execute(stmt) and await session.scalars(stmt) in SQLAlchemy 2.0?

If you're unsure about any, review guide #8 (PostgreSQL & SQLAlchemy), specifically the chapters on relationships and SQLAlchemy 2.0 syntax. This module assumes comfort with that foundation.

Evidence of success

By the end of the module, you'll know you succeeded if:

  • ✅ You can look at an endpoint with loops over relationships and predict the exact number of queries it fires.
  • ✅ You have nplusone configured in at least one project (yours or the bookstore) and CI breaks when a new N+1 appears.
  • ✅ You can argue in a PR review why selectinload is the right choice for case X and joinedload for case Y, citing the expected cardinality.
  • ✅ You took the /books-with-author endpoint from the bookstore and measured a real reduction: queries (51 → 2) and p95 latency (from X ms to Y ms).
  • ✅ You can explain why raiseload('*') is production discipline, not an advanced feature.

We start in the next capsule

The next capsule (02) attacks the heart of the problem: what exactly is N+1 and why does SQLAlchemy cause it by default?. You'll take a bookstore endpoint you saw in module 1, enable echo=True on it, count the queries it fires, and understand why the pattern appears without you programming it explicitly.

Before moving on, make sure you have:

  • The module 1 bookstore running locally with seeded data (5,000 authors, 100,000 books, reviews).
  • psql connected to the DB.
  • The SQLAlchemy log fresh in your memory — you'll read a lot of raw SQL.

If your module 1 setup is gone, go back to capsule 08 of module 1 and run the seed before moving on.

Resources for the module

  1. SQLAlchemy 2.0 — Relationship Loading Techniques — the official reference. Read at least the "Lazy Loading", "Joined Eager Loading", and "Select IN Loading" sections before starting.
  2. jmcarp/nplusone (GitHub) — the library you'll use in capsule 03. README + integration examples.
  3. Mike Bayer — "Asynchronous I/O in SQLAlchemy" (PyCon talk) — the fundamentals of async loading explained by the ORM's creator.
  4. Asif Muhammad — "Solving the N+1 problem in FastAPI" — a case applied to the path's stack.
  5. Use The Index, Luke — "Slow Indexes Part II: The Application Side" — N+1 is the "application-side" version of the problems module 3 solved on the SQL side.
  6. PostgreSQL Documentation — pg_stat_statements — for the curious who want to see in advance how N+1 manifests in production (module 5).

Module 4 — Database Performance & Query Tuning Guide

← PreviousModule project: Indexing the BookstoreNext →What is N+1 and why it happens