Backend / Databases / SQL / 07_n_plus_1.md

N+1 query problem

Updated 2 min read source
On this page7
  1. Minimal repro
  2. Why it happens
  3. How to detect
  4. How to fix
  5. When JOIN beats IN and vice versa
  6. Subtler N+1s
  7. Interview angle

N+1 query problem

The classic ORM trap: one query loads N parent rows, then N more queries load each parent’s children. Total: 1 + N queries instead of 1 or 2.

Minimal repro

python 
# SQLAlchemy ORM with lazy loading (the default)
users = session.query(User).all()       # 1 query
for user in users:
    # N queries — one per user
    print(user.posts)

If User.posts is a relationship(lazy="select"), accessing .posts triggers a SELECT * FROM posts WHERE user_id = ? per user. Loading 100 users means 101 queries.

Why it happens

ORMs default to lazy loading: relationships fetch on attribute access, not when the parent loads. That’s a sane default for one-off access but catastrophic in loops.

How to detect

Tool How
Django from django.db import connection; print(len(connection.queries)) after the request
SQLAlchemy create_engine(..., echo=True) logs every SQL statement
Django Debug Toolbar Shows query count + duplicates in dev
Production APM tools (Sentry, Datadog) flag endpoints with abnormal query counts

Rule of thumb: if your endpoint issues > ~5 queries for a single request, investigate.

How to fix

SQLAlchemy

python 
from sqlalchemy.orm import joinedload, selectinload

# JOIN — single query, but cartesian-product risk on multiple collections
users = session.query(User).options(joinedload(User.posts)).all()

# IN-clause — two queries (1 for users, 1 for posts WHERE user_id IN (...))
users = session.query(User).options(selectinload(User.posts)).all()
  • joinedload: one query with LEFT OUTER JOIN. Best for to-one relationships.
  • selectinload: two queries. Best for to-many — avoids row duplication from JOINs.
  • subqueryload: one query with subquery. Mostly superseded by selectinload in 1.4+.

Django

python 
# select_related — JOIN, for ForeignKey / OneToOne
User.objects.select_related("profile").all()

# prefetch_related — separate query, for ManyToMany / reverse FK
User.objects.prefetch_related("posts").all()

select_related follows ForeignKeys via JOIN. prefetch_related issues a second query and joins in Python — needed for many-to-many and reverse one-to-many.

When JOIN beats IN and vice versa

  • JOIN (joinedload / select_related): best for one-to-one, many-to-one. Single round trip. Bad for one-to-many with large children — duplicates parent columns per child row.
  • IN (selectinload / prefetch_related): best for one-to-many, many-to-many. Two round trips, no duplication.

Subtler N+1s

  • Polymorphic attributes: obj.related where related is computed via a property that hits the DB.
  • Permission checks in serializers: each field calls user.has_perm(...) which queries.
  • Repr / str that touches relationships: print(user) triggers loads if __repr__ references children.
  • Template rendering (Django): {{ user.profile.avatar }} in a loop without select_related.

Interview angle 4

  • Q: “What’s the N+1 problem and how would you find it in production?”
  • Q: “Difference between select_related and prefetch_related?” — JOIN vs separate query, FK vs M2M.
  • Follow-up: “When would joinedload make things slower?” — to-many relationships: parent rows duplicated per child, network bandwidth wasted.
  • Follow-up: “How do you fix N+1 without an ORM?” — write the JOIN yourself, or batch IDs and issue one WHERE id IN (...) query.

See SQLAlchemy for SQLAlchemy fundamentals.

Previous
SQLAlchemy