backend / databases / sql / 07_n_plus_1.md

N+1 query problem

2 min read source

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

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

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

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

# 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

  • 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 06_sqlalchemy.md for SQLAlchemy fundamentals.

Previous
SQLAlchemy