What is N+1 and why it happens

Capsule overview

In module 3 you became an expert at making each individual query fast. Composite indexes, covering, partial: all the tools to make EXPLAIN show Index Scan with minimal buffers.

But there's a form of slowness those indexes don't solve. A form where the problem isn't the query but how many queries. Where the endpoint fires 51 queries in series, each in 0.5ms — 25ms total in pure SQL, but 250ms in network round-trips because each query is a trip to the server.

This is called the N+1 problem, and it's endemic to ORMs. SQLAlchemy causes it by default if you don't know about it and don't confront it. A for author in authors: print(author.books) looks innocent and fires N additional queries without you writing a single line of SQL.

This capsule trains you to:

• Define N+1 precisely (not every query explosion is an N+1).
• See the pattern in SQLAlchemy code before running it.
• Count queries with echo=True and validate your prediction.
• Distinguish lazy loading from eager loading conceptually.

By the end, you'll be able to look at 10 lines of SQLAlchemy code and predict how many queries it fires with an error of ±1.

Mental model: the ORM doesn't warn you when it hits the database

SQLAlchemy gives you an abstraction where author.books looks like accessing a normal Python attribute. But it isn't a normal attribute — it's a proxy that, when you access it, fires a SQL query against PostgreSQL.

Let's think about this with an analogy:

Your fridge (the SQLAlchemy session) has a magic panel that shows "apples". In code you write fridge.apples. The first time you access it, someone runs off to the supermarket to buy apples. They come back, put them in the fridge, and show them to you. You didn't see the trip to the supermarket — you only saw apples.

Now imagine that instead of one fridge you have 50 fridges, one per author. And each one has its own supermarket. The code for fridge in fridges: print(fridge.apples) triggers 50 trips to the supermarket in series, one per fridge. You wrote a simple loop. The system made 50 trips.

Each "trip to the supermarket" is a query to PostgreSQL. The N+1 is exactly that: 1 initial query to bring the authors, N additional queries (one per author) to bring each one's books.

Why does SQLAlchemy do this by default?

Because the ORM can't know in advance whether you'll use author.books or not. If SQLAlchemy preloaded all the relationships of every query, it would bring megabytes of data you might never read. The "load on access" choice is the most conservative one — but it forces you to be explicit when you know you will use the relationship.

That choice is called lazy loading, and it's the default. The explicit alternative is called eager loading, and it's activated with joinedload, selectinload, or subqueryload (capsules 04-05).

The strict definition of N+1

An N+1 problem occurs when an endpoint executes:

1. 1 initial query that returns N rows (the "parent list").
2. N additional queries, one for each parent row, to bring related data (the "child list").

Total: 1 + N queries (hence the name).

Canonical example:

# 1 query: SELECT * FROM authors WHERE name = 'tolkien' LIMIT 10
authors = (await session.scalars(select(Author).where(...).limit(10))).all()

for author in authors:
    # N queries: SELECT * FROM books WHERE author_id = ? (one per author)
    print(author.books)

If authors has 10 rows, 11 queries execute (1 + 10).

Nested N+1: 1 + N + N×M

When the N+1 has an additional level, it scales worse:

# 1 query: SELECT * FROM authors WHERE name = 'tolkien' LIMIT 10
authors = (await session.scalars(select(Author).where(...).limit(10))).all()

for author in authors:
    # N queries: SELECT * FROM books WHERE author_id = ?
    for book in author.books:
        # N×M queries: SELECT * FROM reviews WHERE book_id = ?
        print(book.reviews)

If you have 10 authors, each with 20 books, each book with 5 reviews:

• 1 query for authors
• 10 queries for books (one per author)
• 200 queries for reviews (one per book: 10 × 20)

Total: 211 queries. They run in series. If each query is 0.5ms in pure SQL and 2ms including the network round-trip, the endpoint takes 422ms — for data that would fit in 3 well-designed queries.

What is NOT N+1

Not every query explosion is an N+1. Distinguish:

Case 1 — Loop with variable queries (not N+1, just badly written code):

# This fires N queries but is NOT structural N+1
for user_id in id_list:
    user = await session.get(User, user_id)  # N direct queries
    print(user.name)

Here the problem isn't lazy loading — it's that you're doing individual lookups instead of a single WHERE id IN (...). It's fixed with a single query, not with eager loading.

Case 2 — Multiple queries per endpoint without a loop (not N+1):

# 3 queries but not N+1
user = await session.get(User, 42)
orders = (await session.scalars(select(Order).where(Order.user_id == 42))).all()
items = (await session.scalars(select(Item).where(Item.order_id.in_([o.id for o in orders])))).all()

Three planned queries, unrelated to lazy loading. It's code you can maybe optimize, but it isn't the N+1 pattern.

Strict N+1 requires:

• An initial query (the "1").
• A loop over the result.
• Access inside the loop to a relationship (not an arbitrary property).
• That relationship fires an additional query automatically (lazy loading).

That last condition is the key. It's what makes N+1 invisible — you didn't write the additional query, the ORM fires it for you.

The pattern seen in real code

Let's take the /books-with-author endpoint from the bookstore (module 1) and trace exactly how many queries it fires.

Model setup (recap from module 1)

# models.py
from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)

    # Lazy relationship by default (lazy="select")
    books: Mapped[list["Book"]] = relationship(back_populates="author")


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))

    author: Mapped["Author"] = relationship(back_populates="books")
    reviews: Mapped[list["Review"]] = relationship(back_populates="book")


class Review(Base):
    __tablename__ = "reviews"

    id: Mapped[int] = mapped_column(primary_key=True)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"))
    rating: Mapped[int]
    comment: Mapped[str]

    book: Mapped["Book"] = relationship(back_populates="reviews")

Key point: relationship(back_populates="...") without a lazy= parameter uses lazy="select" (default), which is lazy loading. Each access to the relationship fires a query.

Endpoint with N+1

# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from models import Author, Book, Review

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    echo=True,  # ← key for seeing the SQL
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

app = FastAPI()


async def get_db():
    async with SessionLocal() as session:
        yield session


@app.get("/books-with-author")
async def list_books_with_author(
    author_name: str,
    session: AsyncSession = Depends(get_db),
):
    # Query 1: bring the author
    author_q = select(Author).where(Author.name == author_name)
    author = (await session.execute(author_q)).scalar_one_or_none()
    if not author:
        raise HTTPException(404, "Author not found")

    # Query 2: bring all the author's books
    books_q = select(Book).where(Book.author_id == author.id)
    books = (await session.scalars(books_q)).all()

    # N queries: for each book, one query to bring its reviews
    result = []
    for book in books:
        # Accessing book.reviews fires: SELECT * FROM reviews WHERE book_id = ?
        # But since this is async, it does NOT work with default lazy loading
        # (capsule 06 explains why). To make the N+1 happen as a demo,
        # we force the load manually here.
        reviews_q = select(Review).where(Review.book_id == book.id)
        reviews = (await session.scalars(reviews_q)).all()
        result.append({
            "title": book.title,
            "review_count": len(reviews),
        })

    return {"author": author.name, "books": result}

Important note: in pure async code, accessing book.reviews directly does not fire a lazy load — it raises an exception (capsule 06 explains). The N+1 equivalent in async looks like explicit queries inside a loop, equally inefficient. For this demo we use the pattern with explicit queries because it visualizes the problem better.

Query count for tolkien (with 50 books, 5 reviews per book)

If tolkien has 50 books:

• Query 1: SELECT * FROM authors WHERE name = 'tolkien' → 1
• Query 2: SELECT * FROM books WHERE author_id = 41 → 1
• Queries 3 to 52: SELECT * FROM reviews WHERE book_id = ? → 50 queries

Total: 52 queries per request.

Each one takes maybe 0.5ms in pure PostgreSQL, but adding the network round-trip they're ~3ms each. Total time: ~156ms just from the queries in series. Plus the Python overhead of processing each result.

If the database grew to 500 books per author, that would be 502 queries and ~1.5 seconds per request. That's N+1 scaling.

Enabling echo=True to see the SQL

echo=True in create_async_engine(...) prints each executed SQL query to stdout (or to the logger). It's the simplest tool for detecting N+1 in development.

Minimal setup

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    echo=True,
)

When you hit the endpoint, in your terminal you see something like:

2026-05-02 10:23:14,210 INFO sqlalchemy.engine.Engine SELECT authors.id, authors.name FROM authors WHERE authors.name = $1
2026-05-02 10:23:14,210 INFO sqlalchemy.engine.Engine [generated in 0.00012s] ('tolkien',)
2026-05-02 10:23:14,213 INFO sqlalchemy.engine.Engine SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id = $1
2026-05-02 10:23:14,213 INFO sqlalchemy.engine.Engine [generated in 0.00009s] (41,)
2026-05-02 10:23:14,216 INFO sqlalchemy.engine.Engine SELECT reviews.id, reviews.book_id, reviews.rating, reviews.comment FROM reviews WHERE reviews.book_id = $1
2026-05-02 10:23:14,216 INFO sqlalchemy.engine.Engine [generated in 0.00008s] (1,)
2026-05-02 10:23:14,218 INFO sqlalchemy.engine.Engine SELECT reviews.id, reviews.book_id, reviews.rating, reviews.comment FROM reviews WHERE reviews.book_id = $1
2026-05-02 10:23:14,218 INFO sqlalchemy.engine.Engine [generated in 0.00009s] (2,)
... (48 more identical ones with a different book_id)

Pattern to look for: the same query repeated, changing only the parameter. That's N+1 written in neon lights.

Counting queries with a script

To confirm the count, use a script that hits the endpoint and counts the SELECT lines in the log. Simple version with curl and grep:

# In one terminal: run uvicorn with echo on and redirect stderr to a file
uvicorn main:app --reload 2> echo.log

# In another terminal: hit the endpoint
curl "http://localhost:8000/books-with-author?author_name=tolkien" > /dev/null

# Count SELECTs in the log
grep -c "INFO sqlalchemy.engine.Engine SELECT" echo.log
# Expected output: 52 (1 author + 1 books + 50 reviews)

This is manual but useful for confirming that your count prediction is correct. Capsule 03 automates this with nplusone so it fails in CI.

When echo=True gets annoying: use logging

echo=True prints to stdout, which pollutes production logs. The professional alternative is to configure SQLAlchemy's logger:

import logging

logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)

# In the engine, without echo:
engine = create_async_engine("postgresql+asyncpg://...", echo=False)

With this you control levels, formats, and outputs (file, syslog, etc.) without the crude echo boolean. In production you typically leave it at WARNING or ERROR; in dev you raise it to INFO only when you're hunting an N+1.

Lazy vs eager loading: the two philosophies

SQLAlchemy offers two general strategies for loading relationships:

Lazy loading (default)

• When it loads: the first time you access the relationship.
• How many queries: 1 per relationship per parent instance.
• Pro: brings only what you use. If you never access author.books, that query never runs.
• Con: in loops, it fires N+1.

Eager loading (explicit)

• When it loads: together with the parent query, in the same or an adjacent execution.
• How many queries: 1 (with joinedload) or 2 (with selectinload or subqueryload).
• Pro: eliminates N+1.
• Con: brings data even if you don't use it (over-fetching). If you list 1000 authors and only read name, loading books for all 1000 is waste.

The practical rule:

Lazy loading is safe for queries that return 1 entity. It's dangerous for queries that return lists where your code will iterate over relationships.

Typical cases per endpoint:

Capsules 04 and 05 give you the detailed decision matrix. For now it's enough to understand: lazy is the default, and the default doesn't scale for list endpoints.

Why this matters in real work

1. N+1 is invisible in unit tests with mocks.

If your tests mock the SQLAlchemy session (common in Python projects), N+1s don't appear. They only manifest with a real session pointing at a real DB. That's why nplusone (capsule 03) is designed to run with a real DB in integration tests.

2. N+1 scales with data, not with code.

Your code passes code review because it's readable. Your tests pass because they work with 5 rows. It goes to production, the 5,000 users with 50 orders each arrive, and your endpoint falls over. Detecting it in code review requires a trained eye — the eye you're building in this capsule.

3. Your APM doesn't tell you "you have an N+1".

Datadog/New Relic show "DB time: 850ms". Which of the 50 spans is the problem, that's what you discover yourself. If you recognize the pattern "the same query repeated 50 times with a different parameter", you know it's N+1. If you don't recognize it, you open "slow DB" tickets with no diagnosis.

4. The classic senior interview question.

"You have this endpoint, it fires 51 queries per request, what do you do?". If your answer is "I add an index" you're talking about SQL. If it's "I use selectinload on the relationship", you're talking about the ORM. Only the second is the correct answer for this case.

Traps and common mistakes

Mistake 1 (conceptual): thinking the SQL "shows" in the code

Symptom: "My code has no SELECT, where does the query come from?"

Why it happens: used to hand-written SQL, you assume every query is visible in the source. With an ORM, queries are generated when you access special attributes (relationships).

How to distinguish: every Mapped[list[X]] or Mapped["X"] defined as relationship(...) is a proxy that can fire SQL on access. Identify those attributes as mental "query points".

How to avoid it: enable echo=True in dev. Once you see the SQL come out on its own, you internalize which accesses fire queries.

Mistake 2 (conceptual): confusing N+1 with "many queries"

Symptom: "My endpoint fires 5 queries, is it N+1?"

Why it confuses: the count doesn't define N+1. Five planned queries is verbose code but not N+1. The structure of N+1 is 1 parent query + N queries in a loop over the relationship, where N depends on the size of the parent result.

How to distinguish: ask yourself: does the number of queries depend on the number of rows returned by a previous query? If yes (for x in query_1_list: lazy_load(x.relationship)), it's N+1. If not, they're planned queries, possibly optimizable, but not N+1.

Mistake 3 (practical): assuming expire_on_commit=False solves N+1

Symptom: "I set expire_on_commit=False in my async_sessionmaker and the N+1 persists."

Why it happens: expire_on_commit controls whether objects are invalidated after a commit (which forces a re-read). It has nothing to do with how relationships are loaded initially. Lazy loading is still lazy.

How to fix it: expire_on_commit=False is good practice in async (avoids "DetachedInstanceError" bugs), but it doesn't solve N+1. For that you need joinedload or selectinload (capsules 04-05).

Mistake 4 (conceptual): thinking lazy="dynamic" is the solution

Symptom: "I changed lazy="select" to lazy="dynamic" and the N+1 got worse."

Why it happens: lazy="dynamic" returns a Query object (not a loaded list). Each time you iterate, it runs a new query. Designed for relationships with thousands of rows where you want to filter/paginate before loading. For common list endpoints, it's worse than the default — on top of N+1, it doesn't cache between accesses.

How to distinguish: lazy="dynamic" is only useful when the set of child rows is enormous (100k+) and you want to apply additional filters (author.books.filter(Book.year > 2000).limit(10)). For everything else, avoid it in SQLAlchemy 2.0 — use selectinload with an explicit WHERE.

Mistake 5 (practical): mixing echo=True with tests

Symptom: "I enabled echo=True and my tests print 50,000 lines, I can't see anything."

Why it happens: in tests with many cases, the log becomes noise. You need a more surgical mechanism.

How to fix it: in tests use nplusone (capsule 03) or a context manager that counts queries only during the block you care about. Basic pattern:

from sqlalchemy import event

queries_count = 0

def count_queries(conn, cursor, statement, parameters, context, executemany):
    global queries_count
    queries_count += 1

event.listen(engine.sync_engine, "before_cursor_execute", count_queries)
# ... run the code under test ...
print(f"Total queries: {queries_count}")

echo=True is for manual exploration; nplusone and event listeners are for automated tests.

Exercises

Exercise 1: predict the query count

Given this code, predict how many queries it fires when authors returns 4 rows and each author has 10 books:

authors_q = select(Author).where(Author.country == 'AR').limit(4)
authors = (await session.scalars(authors_q)).all()

for author in authors:
    books_q = select(Book).where(Book.author_id == author.id)
    books = (await session.scalars(books_q)).all()
    for book in books:
        print(f"{author.name} - {book.title}")

for book in books:
    print(book.author.name)  # ← access to the author relationship

Exercise 2: identify the N+1 in async code

Read this endpoint. Is there an N+1? If so, how big?

@app.get("/users-with-orders")
async def users_with_orders(session: AsyncSession = Depends(get_db)):
    users_q = select(User).where(User.active == True)
    users = (await session.scalars(users_q)).all()

    result = []
    for user in users:
        orders_q = select(Order).where(Order.user_id == user.id)
        orders = (await session.scalars(orders_q)).all()
        total = sum(o.amount for o in orders)
        result.append({"user": user.name, "total": total})

    return result

If active users are 200, how many queries does this endpoint fire?

# A single query with eager loading
users_q = (
    select(User)
    .options(selectinload(User.orders))
    .where(User.active == True)
)
users = (await session.scalars(users_q)).all()

for user in users:
    total = sum(o.amount for o in user.orders)  # already loaded
    result.append({"user": user.name, "total": total})

Exercise 3: count queries with echo=True

Configure the module 1 bookstore with echo=True, start the server with stderr redirected to a file, and hit the endpoint:

uvicorn main:app --reload 2> echo.log &
curl "http://localhost:8000/books-with-author?author_name=tolkien" > /dev/null
grep -c "INFO sqlalchemy.engine.Engine SELECT" echo.log

Does the number you see match your prediction of "1 author + 1 books + N reviews where N is tolkien's number of books"?

Exercise 4: distinguish N+1 from "inefficient queries"

For each snippet, decide whether the problem is N+1 or "many planned queries":

Snippet A:

total = 0
for i in range(100):
    user = await session.get(User, i)
    total += user.score

Snippet B:

authors = (await session.scalars(select(Author).limit(10))).all()
for author in authors:
    books = (await session.scalars(select(Book).where(Book.author_id == author.id))).all()
    for book in books:
        print(book.title)

Snippet C:

user = await session.get(User, 42)
profile = await session.get(Profile, user.profile_id)
settings = await session.get(Settings, user.settings_id)
preferences = await session.get(Preferences, user.preferences_id)

users = (await session.scalars(select(User).where(User.id.in_(range(100))))).all()
total = sum(u.score for u in users)

Exercise 5: nested prediction (two-level N+1)

Predict the number of queries for this code, assuming:

• 5 categories
• 8 products per category
• 3 reviews per product

categories_q = select(Category)
categories = (await session.scalars(categories_q)).all()

for cat in categories:
    products_q = select(Product).where(Product.category_id == cat.id)
    products = (await session.scalars(products_q)).all()
    for product in products:
        reviews_q = select(Review).where(Review.product_id == product.id)
        reviews = (await session.scalars(reviews_q)).all()
        avg = sum(r.rating for r in reviews) / len(reviews) if reviews else 0

categories_q = (
    select(Category)
    .options(selectinload(Category.products).selectinload(Product.reviews))
)

Exercise 6: diagnosis from a SQLAlchemy log

You have this extract from the echo=True log for an endpoint that returns "list of projects with their owner and members":

SELECT * FROM projects WHERE workspace_id = $1
SELECT * FROM users WHERE id = $1
SELECT * FROM project_members WHERE project_id = $1
SELECT * FROM users WHERE id = $1
SELECT * FROM project_members WHERE project_id = $1
SELECT * FROM users WHERE id = $1
SELECT * FROM project_members WHERE project_id = $1
... (the pattern continues)

How many N+1s do you see? How would you describe the problem in a PR review?

Summary and next step

In this capsule you learned:

• Strict N+1: 1 initial query + N additional queries in a loop over relationships. It is NOT the same as "many queries".
• Lazy loading is the default in SQLAlchemy. Each access to a relationship fires a query if the relationship wasn't loaded eagerly.
• The pattern shows in simple code: a for x in list: x.relationship is a guaranteed N+1.
• echo=True is the basic tool for manual detection: it shows each executed SQL query.
• Nested N+1 scales as 1 + N + N×M: 5 categories × 8 products × 3 reviews = 46 queries.
• Not every query explosion is N+1. There are badly written loops (lookups by ID), planned queries, and structural N+1 — different diagnoses.

Before moving on you should be able to:

• Look at 10 lines of SQLAlchemy code and predict how many queries it fires.
• Enable echo=True and count SELECTs in the log.
• Distinguish structural N+1 from "badly written code".
• Explain why SQLAlchemy uses lazy loading by default and when it's safe.

Next capsule — Detecting N+1 with nplusone. echo=True is great for an educational capsule, but in an app with 80 endpoints and 200 tests it's unmanageable noise. Capsule 03 integrates the nplusone library: configured in pytest, it raises when an N+1 appears (CI breaks); configured as FastAPI middleware, it warns in dev. After capsule 03 your process will be: if the N+1 enters the PR, CI stops it before the reviewer has to look at it. Automated detection instead of manual inspection.

Resources

1. SQLAlchemy 2.0 — Relationship Loading Techniques — the official reference. Read the "Lazy Loading" section to understand the default.
2. SQLAlchemy 2.0 — Configuring Loader Strategies at Mapping Time — all the possible values of lazy= in relationship(...).
3. Mike Bayer — "Asynchronous I/O in SQLAlchemy" (PyCon 2020) — the creator of SQLAlchemy explaining async loading. Essential if you're going to do FastAPI with SQLAlchemy seriously.
4. Markus Winand — "Slow Indexes Part II: The Application Side" — N+1 seen from the query planning perspective. Useful for understanding why indexes don't fix it.
5. PostgreSQL Documentation — EXPLAIN ANALYZE with loops — a module 2 refresher: how loops=N in a plan relates conceptually to N+1.
6. Asif Muhammad — "Solving the N+1 problem in FastAPI with SQLAlchemy eager loading" — a case applied to the path's stack with complete code.
7. Real Python — "SQLAlchemy 2.0 Tutorial" — to review fundamentals if the relationship details feel rusty.

Module 4 — Database Performance & Query Tuning Guide