PythonDatabases18 min read

The N+1 query problem

The performance bug that hides behind clean-looking ORM code: how one loop becomes a thousand queries, how to spot it, and how to fix it.

Day 136 of the 488-day pathway. Published in full — nothing held back.

Relationships and the N+1 problem — the performance bug that hides behind clean code

The sentence to own: an N+1 is what happens when an ORM makes a database round trip look like an attribute access — the code is short, obvious and correct, and it issues one query per row, which is why this is the most common serious performance bug in every ORM ever written.

Day 100A showed you how to find one (a surprising ncalls). Today is why they happen, the four ways to fix them, and how to make them impossible to ship.

Part 1 — The bug

orders = session.scalars(select(Order).limit(50)).all()          #   1 query
for order in orders:
    print(order.user.email)                                       # ⚠️⚠️ 50 MORE queries

   51 QUERIES FOR ONE PAGE. AND LOOK AT THE CODE — IT IS CLEAN, READABLE AND
    WRONG, WHICH IS EXACTLY WHY IT SURVIVES REVIEW.
    ⇒    `order.user` LOOKS LIKE AN ATTRIBUTE. IT IS A NETWORK ROUND TRIP.
           The ORM's central convenience is also its central hazard: it makes
         the expensive thing invisible.
    ⇒   each query is 1 ms locally ⇒ 51 ms, unnoticed.    Each is 5 ms across
         a network ⇒ 255 ms, and it scales with the page size, so it degrades
         exactly as you grow.
    ⇒    AND THE TELL FROM DAY 100A: A SURPRISING `ncalls`. 24,000 customer
         lookups for 1,000 orders is not a slow function —   it is a function
         called 24,000 times, and no micro-optimisation fixes it.

class Order(Base):
    user: Mapped["User"] = relationship(back_populates="orders")        #   many-to-one
    items: Mapped[list["OrderItem"]] = relationship(back_populates="order",
                                                    cascade="all, delete-orphan")

class User(Base):
    orders: Mapped[list["Order"]] = relationship(back_populates="user",
                                                 lazy="raise")          #    see Part 4

back_populates keeps both sides in sync in memory — append to user.orders and order.user is set without a round trip. cascade="all, delete-orphan" means deleting an order deletes its items in the ORM; it does not create a database-level ON DELETE CASCADE, so a delete issued outside SQLAlchemy leaves orphans. Declare both.

Part 2 — The four loading strategies

from sqlalchemy.orm import selectinload, joinedload, subqueryload

#    1. selectinload — TWO queries: the parents, then all children by parent id
orders = session.scalars(
    select(Order).options(selectinload(Order.items)).limit(50)
).all()
# ⇒ SELECT * FROM orders LIMIT 50
# ⇒ SELECT * FROM order_items WHERE order_id IN (1,2,…,50)        ONE extra query

#   2. joinedload — ONE query, with a LEFT OUTER JOIN
orders = session.scalars(
    select(Order).options(joinedload(Order.user)).limit(50)
).unique().all()                                      #    .unique() REQUIRED for collections

Strategy	Queries	Best for
`lazy` (default)	⚠ 1 + N	nothing, in a loop
`selectinload`	2	collections (one-to-many) — the default choice
`joinedload`	1	many-to-one / one-to-one
`subqueryload`	2	legacy; `selectinload` superseded it
`raiseload`	0 — it errors	enforcement (Part 4)

   WHY THE SPLIT — AND IT IS NOT ARBITRARY:
    ·    `joinedload` ON A COLLECTION MULTIPLIES ROWS. 50 orders × 20 items =
         1,000 rows returned, each repeating the whole order —   that is
         Day 100's FAN-OUT, arriving as bandwidth and memory rather than as a
         wrong SUM.    And it is why `.unique()` is mandatory: SQLAlchemy has
         to de-duplicate the parents itself.
    ·   `selectinload` sends `WHERE id IN (...)` ⇒    NO row multiplication,
         one extra round trip, and it batches (500 ids per query by default).
    ·   for a MANY-TO-ONE there is nothing to multiply — one order has one user
         —    so `joinedload` is strictly better there: one query, no fan-out.
    ⇒    THE RULE: **`joinedload` FOR TO-ONE, `selectinload` FOR TO-MANY.**
           That single sentence covers ~90% of real cases.

Nesting works: selectinload(Order.items).selectinload(OrderItem.product) loads three levels in three queries. Eager-load exactly what you will use, and no more — over-eager loading is its own performance bug, quieter and harder to spot than N+1.

Part 3 — Where the fix belongs

# ⚠️    THE FIX IN THE WRONG PLACE — the route knows about loading strategies:
@app.get("/orders")
async def list_orders(db: DB):
    return db.scalars(select(Order).options(selectinload(Order.items))).all()

#    THE FIX IN THE RIGHT PLACE — the repository decides (Days 090, 135):
class OrderRepository:
    def list_for_user(self, user_id: int, *, with_items: bool = False) -> list[Order]:
        stmt = select(Order).where(Order.user_id == user_id)
        if with_items:
            stmt = stmt.options(selectinload(Order.items))
        return self.session.scalars(stmt).all()

   WHY THE REPOSITORY: THE CALLER KNOWS WHAT IT NEEDS; THE REPOSITORY KNOWS HOW
    TO FETCH IT.
    ⇒   a route asking for `with_items=True` is expressing an intent, not a
         strategy —    so when `selectinload` turns out to be wrong you change
         one file, not forty call sites.
    ⇒    AND IT PUTS THE PERFORMANCE DECISION NEXT TO THE SCHEMA KNOWLEDGE,
         which is where someone can reason about indexes and cardinality
         (Stage 5) rather than guessing from a handler.
    ⇒ ⚠️   THE COST: an explosion of boolean flags if you are not careful.
            When that happens, the honest answer is separate methods —
         `list_for_user` and `list_for_user_with_items` — because they really
         are different queries with different costs.

⚠ Eager loading does not survive .limit() on the child side, and it interacts badly with pagination (Day 121): joinedload plus LIMIT 20 limits rows, not parents, so you get fewer than twenty orders. selectinload is immune, which is a third reason to prefer it for collections.

Part 4 — Making it impossible to ship

class User(Base):
    orders: Mapped[list["Order"]] = relationship(lazy="raise")   #    lazy load ⇒ EXCEPTION

   `lazy="raise"` IS THE SINGLE BEST SETTING IN THIS DAY, AND ALMOST NOBODY
    USES IT: ANY ATTEMPT TO LAZY-LOAD RAISES `InvalidRequestError` INSTEAD OF
    SILENTLY ISSUING A QUERY.
    ⇒    SO AN N+1 BECOMES A **LOUD FAILURE IN DEVELOPMENT AND IN TESTS**
         rather than a quiet 250 ms in production.   You are forced to declare
         what you need, at the point you write the query.
    ⇒   it also kills `DetachedInstanceError` (Day 135) as a side effect: an
         attribute that cannot lazy-load cannot fail after the session closes.
    ⇒    AND IT IS DAY 118'S PRINCIPLE AGAIN — **MAKE THE WRONG THING HARD TO
         WRITE**, rather than relying on every author remembering.
    ⇒ ⚠️   adopt it incrementally: `lazy="raise_on_sql"` allows already-loaded
         access, which is what you want when retrofitting.

#    THE TEST THAT CATCHES REGRESSIONS — assert the QUERY COUNT:
def test_list_orders_is_two_queries(client, db_engine):
    with count_queries(db_engine) as counter:
        r = client.get("/orders?limit=50")
    assert r.status_code == 200
    assert counter.count <= 2            #    1 for orders + 1 for items

   A QUERY-COUNT ASSERTION IS THE ONLY THING THAT CATCHES AN N+1 REINTRODUCED
    BY A REFACTOR — and they are reintroduced constantly, because adding one
    field to a serialiser can do it.
    ⇒   implement `count_queries` with a SQLAlchemy `before_cursor_execute`
         event listener — about ten lines.
    ⇒    ASSERT `<= 2`, NOT `== 2`: an exact count is brittle and gets
         "updated" reflexively (Day 125's snapshot warning).   A ceiling
         expresses the actual requirement, which is "not per-row".
    ⇒   put it on your two or three hottest endpoints, not everywhere.

Part 5 — Finding one in an existing system

   FOUR DETECTORS, CHEAPEST FIRST:
   1.    `echo=True` IN DEVELOPMENT AND **READ THE LOG FOR ONE REQUEST**.   A
        repeated identical `SELECT` with a different id is an N+1, visible in
        five seconds.    This is the highest-value five seconds in the day.
   2.   THE QUERY COUNT PER REQUEST as a metric or a log field (Day 099A)
        ⇒    then it is graphable, and a deploy that doubles it is visible.
   3.   `cProfile` / `py-spy` ⇒ Day 100A's surprising `ncalls`.
   4.   the database's own slow-query log — ⚠️    WHICH WILL NOT SHOW IT, and
        that is the point worth knowing: each of the 50 queries is FAST.    AN
        N+1 IS INVISIBLE TO SLOW-QUERY LOGGING, which is why it survives so long
        in systems that have monitoring.

   AND THE COUSINS, WHICH ARE THE SAME MISTAKE IN OTHER CLOTHES:
    ·   N+1 ON WRITE: `for row in rows: session.add(Thing(...))` then commit —
            one INSERT per row. Use a bulk insert (Day 135).
    ·    N+1 ACROSS SERVICES: a loop calling an HTTP API per item.   Same
         shape, worse latency, and no ORM to blame.    The fix is a batch
         endpoint, and it is why Day 126 said an API should offer one.
    ·   N+1 IN A SERIALISER: a Pydantic `computed_field` (Day 131) that touches
         a lazy relationship ⇒    one query PER OBJECT SERIALISED, and it is
         invisible in the repository code.
    ⇒    THE GENERAL FORM: **A ROUND TRIP INSIDE A LOOP**.   Once you see it
         that way, you find it in queues, caches, S3 calls and file reads too.

Common mistakes

Mistake	Correction
Accessing a relationship in a loop	One round trip per row. The code looks perfect.
`joinedload` on a collection	Row multiplication — 50 × 20 = 1,000 rows. Use `selectinload`.
Forgetting `.unique()` after `joinedload`	SQLAlchemy raises; it cannot de-duplicate silently.
`selectinload` for a many-to-one	An unnecessary round trip. `joinedload` is one query.
Eager-loading everything	Its own performance bug, quieter than N+1.
Loading strategies in the route	Forty call sites to change. Put them in the repository.
`joinedload` plus `LIMIT`	Limits rows, not parents. `selectinload` is immune.
Relying on ORM `cascade` alone	A delete outside SQLAlchemy leaves orphans. Declare `ON DELETE` too.
No `lazy="raise"`	N+1 stays silent until production.
An exact query-count assertion	Brittle; gets updated reflexively. Assert a ceiling.
Expecting the slow-query log to find it	Every individual query is fast. It is invisible there.
Fixing the ORM N+1 and not the HTTP one	Same shape, worse latency.

Interview questions

Q: What is an N+1 query?

One query to fetch a list, then one more per row because something accesses a relationship. Fifty orders becomes fifty-one queries. What makes it the most common serious ORM bug is that the code is clean and obvious — order.user.email looks like an attribute access and is actually a network round trip. The ORM's central convenience is that it hides the expensive thing, and that's also its central hazard. It scales with page size, so it degrades exactly as you grow, and it's invisible to a slow-query log because every individual query is fast.

Q: How do you fix one, and which strategy?

Eager loading, and the choice depends on cardinality. joinedload for many-to-one or one-to-one — one query, and there's nothing to multiply because one order has one user. selectinload for collections, because a join against a one-to-many multiplies rows: fifty orders with twenty items each returns a thousand rows, each repeating the whole order. That's the fan-out problem arriving as bandwidth rather than as a wrong SUM, and it's why .unique() is required with joinedload. selectinload issues a second query with WHERE id IN (...), so no multiplication, and it's also immune to the pagination interaction where a join plus LIMIT limits rows rather than parents.

Q: Where does the fix belong?

The repository, not the route. The caller expresses an intent — "I need the items" — and the repository decides how to fetch it. So when selectinload turns out to be the wrong strategy you change one file rather than forty call sites, and the performance decision sits next to the schema knowledge where someone can reason about indexes and cardinality. The failure mode is an explosion of boolean flags, and when that happens I'd split into separate methods, because they really are different queries with different costs.

Q: How do you stop N+1s being reintroduced?

Two things. lazy="raise" on relationships, which turns any accidental lazy load into an exception in development and tests instead of a silent query in production — it forces you to declare what you need at the point you write the query, and it kills DetachedInstanceError as a side effect. And a query-count assertion on the hottest endpoints, implemented with a before_cursor_execute listener. I'd assert a ceiling rather than an exact number, because an exact count is brittle and gets updated reflexively; the requirement is "not per-row", and a ceiling says that.

Q: Where else does the same bug appear?

Anywhere there's a round trip inside a loop. On the write side, adding objects one at a time gives one INSERT per row where a bulk insert is one statement. Across services, a loop calling an HTTP API per item is the same shape with worse latency and no ORM to blame — the fix is a batch endpoint, which is why an API should offer one. And in serialisers: a Pydantic computed field that touches a lazy relationship issues one query per object serialised, and it's completely invisible in the repository code. Once you see it as "a round trip inside a loop" you start finding it in caches, S3 calls and file reads too.

Mini task

Build the N+1 with 50 orders. Turn on echo=True and count the queries in the log.
Add 5 ms of latency (a network, or a proxy) and time it again. Compare with local.
Fix it with selectinload and count again.
Use joinedload on the collection and count the returned rows. Explain the multiplication.
Forget .unique() and read the error.
Use joinedload on a collection with LIMIT 20 and confirm you get fewer than 20 parents.
Use joinedload for the many-to-one and confirm it is one query.
Nest selectinload(Order.items).selectinload(OrderItem.product) and count the queries.
Move the loading strategy from the route into the repository. Count the call sites you would have had to change.
Set lazy="raise" on one relationship and run your test suite. Fix every failure — each one was an N+1.
Write count_queries with an event listener and assert <= 2 on your list endpoint. Then add a field that reintroduces the N+1 and watch it fail.
Find an N+1 across HTTP or in a serialiser in your own code.

Exit questions

Answer aloud, no notes.

What is an N+1, and why does the code look fine?
Why does it degrade as you grow?
Why is it invisible to a slow-query log?
joinedload vs selectinload — the rule, and the reason behind it.
Why does joinedload on a collection need .unique()?
What happens when joinedload meets LIMIT?
Why does the loading strategy belong in the repository?
What is the cost of over-eager loading?
What does lazy="raise" do, and what else does it fix?
Why assert a ceiling rather than an exact query count?
Name three places the same bug appears outside the ORM.
What is the general form of the mistake?

Articulation drill

Record two minutes: "Tell me about the N+1 problem."

Define it and immediately say why it survives: "one query for the list, then one more per row because something touches a relationship — fifty orders becomes fifty-one queries. What makes it the most common serious ORM bug isn't that it's subtle, it's that the code looks perfect. order.user.email reads as an attribute access and is a network round trip. The ORM hides the expensive thing, which is its main convenience and its main hazard."

Then the property that makes it dangerous rather than merely wasteful: "it's invisible to a slow-query log, because every one of those fifty queries is fast — so a system with monitoring can carry an N+1 for years. And it scales with page size, so it's fine at ten rows in development and degrades exactly as you grow. The tell is a surprising call count: twenty-four thousand user lookups for a thousand orders isn't a slow function, it's a function called twenty-four thousand times, and no micro-optimisation touches it."

Then the fix, with the cardinality rule: "eager load, and which strategy depends on cardinality. joinedload for to-one, because there's nothing to multiply. selectinload for collections, because joining a one-to-many multiplies rows — fifty orders with twenty items each is a thousand rows, each repeating the whole order — and it also breaks pagination, since LIMIT then limits rows rather than parents. And the strategy belongs in the repository, so when it turns out to be wrong you change one file rather than forty call sites."

Close on prevention, which is what actually stops it recurring: "the two things I'd insist on are lazy="raise", which turns an accidental lazy load into a loud failure in tests instead of a quiet 250 milliseconds in production, and a query-count assertion on the hottest endpoints — a ceiling, not an exact number. Because these get reintroduced constantly: adding one field to a serialiser is enough."

Previous: Day 135 · Tomorrow: Day 137 — async SQLAlchemy: the greenlet bridge, and why lazy loading stops working entirely