The N+1 Query Problem

~14 min · operations, queries

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The most common ORM bug

You query 100 users. For each user, you ask for their orders — that's 1 + 100 = 101 queries. Each query is fast in isolation; the round-trip overhead destroys the page. This is the N+1 query problem and it shows up in every ORM, every framework, every team.

How to spot it

Turn on driver-level query logging and watch the count. A page that should make 5 queries making 500 is N+1. Tools like Django Debug Toolbar, Rails Bullet, and SQLAlchemy's events system can flag the pattern automatically.

Three fixes

Eager loading — fetch related rows in a single JOIN or a single IN-clause. Every ORM has a name for this: selectinload (SQLAlchemy), prefetch_related (Django), includes (Rails).
Manual JOIN in raw SQL — when ORM eager-loading is awkward, the SQL JOIN is always available.
Aggregate in SQL, not Python — instead of "for each user, count their orders", use COUNT(*) ... GROUP BY user_id.

Code

N+1 in disguise·python

# N+1: 1 query for users, then N queries for orders
users = session.query(User).all()
for u in users:
    orders = u.orders  # ← each access fires a query
    print(u.name, len(orders))

Fix: eager loading·python

from sqlalchemy.orm import selectinload

users = session.query(User).options(selectinload(User.orders)).all()
for u in users:
    print(u.name, len(u.orders))  # zero extra queries

Fix: aggregate in SQL·python

# Single query: user + order count
rows = session.execute(
    text("""
      SELECT u.id, u.name, COUNT(o.id) AS order_count
      FROM users u
      LEFT JOIN orders o ON o.user_id = u.id
      GROUP BY u.id, u.name
    """)
).all()

External links

Bullet (Rails N+1 detector)

Exercise

Find a list-view endpoint in your project. Turn on query logging. Count how many queries fire per request. If it's more than 5, find the N+1 and fix it with eager loading or a manual JOIN.