What is the N+1 query problem and how do you fix it?
The N+1 problem occurs when an application fetches a list of N records with one query, then executes an additional query for each record to load associated data — resulting in N+1 total database round trips instead of one or two.
A classic example in an ORM like SQLAlchemy or Django ORM: you query all users, then inside a loop access `user.posts` for each user. Each `.posts` access fires a separate SELECT. With 100 users, you execute 101 queries. The problem scales linearly and is often invisible in development with small datasets, but catastrophic in production.
The primary fix is eager loading. Most ORMs provide this: Django's `select_related()` (SQL JOIN for FK/one-to-one) and `prefetch_related()` (separate optimized query for many-to-many/reverse FK), SQLAlchemy's `joinedload()` or `selectinload()`. These collapse N queries into 1 or 2. Choose `selectinload` over `joinedload` for one-to-many to avoid row multiplication in the result set.
At the SQL level, a well-constructed JOIN or a subquery can retrieve all needed data in one round trip. For GraphQL APIs, the dataloader pattern (batching + caching) solves the equivalent problem: instead of resolving each parent's children immediately, resolvers collect all requested IDs and issue a single batched query.
Detection strategies matter as much as fixes. In development, enable query logging and count statements per request. Django Debug Toolbar highlights N+1 visually. New Relic, Datadog, and similar APM tools surface slow endpoints with high query counts in production. nplusone is a Python library that raises exceptions on detected N+1 patterns during testing.
A subtler variant is the N+1 write problem: updating N records with N individual UPDATE statements instead of a bulk update. Fix with `bulk_update()` or a single UPDATE with a CASE expression.
The tradeoff to acknowledge: eager loading can over-fetch. If you only need a user's post count, joining all post rows is wasteful — use a COUNT subquery or annotation instead. Profile before and after any fix.
Correctly defines the problem with a concrete example, names at least one ORM-level fix (eager loading), and explains the query count reduction.
All of the above plus: distinguishes joinedload vs selectinload or prefetch vs select_related, mentions detection tooling, acknowledges the over-fetching tradeoff, and covers the dataloader/batching pattern for non-ORM contexts.
Reading the answer is step one. Explaining it unprompted — under interview pressure — is what actually matters. Get AI-graded feedback on your answer with follow-up probes on your weak points.
Get Graded — Free Assessment