Databases
The N+1 query problem: interview questions and how to answer them
One query fetches N rows, then one more query runs per row — so the endpoint issues N+1 queries where one or two would do.
Written and reviewed by Sahil Srivastav
What it actually is
You fetch a list of N entities with one query, then access a related field on each one. The ORM, which loaded the relation lazily, issues a query per entity. Total: N+1 queries. Nothing is wrong with any individual query — each is indexed, each takes a millisecond — and the endpoint is unusable.
The reason this is the most durable performance bug in application development is that the cost is invisible at the call site. for (order of orders) { order.customer.name } contains no visible database access. The query is generated by the property access, several abstraction layers away, and it does not appear in code review as anything unusual.
The cost is dominated by round trips rather than by database work. Four hundred queries at one millisecond each is four hundred milliseconds of network and parsing overhead before the database has done anything expensive. This is why the problem gets dramatically worse when the database is a network hop away and why it is invisible in local development against a socket-connected instance.
Why it matters in production
Because it scales with your data in the worst way. The endpoint is fine with ten rows in development and times out with the customer who has four hundred. It also degrades under concurrency faster than a single slow query would: each request holds its connection for the duration of hundreds of round trips, so a modest traffic increase exhausts the connection pool and takes down endpoints that have nothing to do with the problem.
It is asked in interviews constantly because it is the clearest test of whether a candidate understands what their ORM actually does. An engineer who has never looked at the emitted SQL will describe the ORM as a convenience; one who has will describe it as a query generator whose output needs checking.
How it works
Lazy loading is the mechanism
ORMs default to loading relations on access so that fetching an entity does not drag in its whole object graph. That default is correct for a single entity and pathological in a loop. The query is triggered by attribute access, which is why it has no syntactic footprint.
Eager loading with a join: one query, wider rows
Joining the relation into the original query — select_related in Django, JOIN FETCH in JPA, include in Prisma — collapses N+1 into 1. It works well for to-one relations. For to-many relations it multiplies rows: joining orders to their items returns one row per item, so a hundred orders with ten items each returns a thousand rows carrying duplicated order columns.
Eager loading with a second query: two queries, no fan-out
The alternative is to fetch the parents, collect their ids, and issue one more query with WHERE parent_id = ANY($1), then stitch the results in application memory. Django calls this prefetch_related, SQLAlchemy selectinload. Two queries total regardless of N, and no row multiplication — which is why it is the right default for to-many relations.
Why the join version can be slower
Fan-out means transferring and deduplicating far more data than necessary. A join across two to-many relations multiplies twice — a cartesian explosion that can turn a fix into a worse problem. The rule of thumb that survives: join for to-one, second query for to-many, and never join two to-many relations in one query.
Batching at the boundary, for APIs
In GraphQL and similar resolver-per-field architectures, N+1 is structural rather than accidental. The standard answer is a per-request batching layer — DataLoader and its equivalents — which defers lookups within a tick, collects the keys, and issues one query. It is the same "collect ids, query once" idea moved to where the resolvers live.
Implementing it
Make query count observable before you try to fix anything. Enable SQL logging in development, or a toolbar that counts queries per request. Most N+1s are discovered the moment someone looks at the count rather than the latency.
Choose the fix by relation cardinality: a join for to-one relations, a second batched query for to-many. Verify by reading the emitted SQL, not by trusting the method name.
Assert on query count in tests for important endpoints. A test that fails when the count exceeds a threshold catches the regression that a latency assertion misses on a small fixture — and N+1 regressions are routinely reintroduced by unrelated changes.
Be careful that the fix is bounded. Replacing N+1 with one query that eagerly loads an entire object graph can replace a query-count problem with a memory problem.
# N+1: one query for orders, then one per order for its customer
orders = Order.objects.filter(status='PAID') # 1 query
for o in orders:
print(o.customer.name) # N queries
# to-one relation: join it in. 1 query total.
orders = Order.objects.filter(status='PAID').select_related('customer')
# to-many relation: second batched query. 2 queries total, no row fan-out.
orders = Order.objects.filter(status='PAID').prefetch_related('items')
# Regression guard worth more than a latency assertion
with self.assertNumQueries(2):
self.client.get('/api/orders')Interview questions and how to answer them
An endpoint is slow but every query in the log takes a millisecond. What is happening?
Query count, not query cost. Four hundred one-millisecond queries plus round-trip overhead is a slow endpoint made entirely of fast queries. Count the queries per request; if it scales with the number of rows returned, it is N+1 and the fix is batching rather than indexing.
When would you use a join versus a second query?
Join for to-one relations, where the row count does not change. Second batched query for to-many, because joining multiplies rows — a hundred orders with ten items each becomes a thousand rows with duplicated order columns. Joining two to-many relations at once is a cartesian product and should be avoided entirely.
How would you prevent this from coming back?
Assert query counts in tests for the endpoints that matter. A latency assertion passes on a small fixture, but a count assertion fails the moment someone reintroduces lazy access in a loop. Query logging in development makes the problem visible during authoring rather than in production.
Why is N+1 worse in production than locally?
Round-trip latency. Locally the database is a socket away and a query costs microseconds of overhead; in production it is a network hop with real latency, often across availability zones. The same four hundred queries can go from barely noticeable to several seconds, which is why this bug ships.
How does this apply to GraphQL?
It is the default behaviour rather than an accident: each field resolver runs per parent object, so a list of N items resolving a related field makes N calls. The standard remedy is a per-request batching loader that defers and coalesces lookups into one query — the same collect-ids-and-batch idea, applied at the resolver layer.
Answers that lose the round
- Dismissing it because each individual query is fast
- Using a join for a to-many relation and creating row fan-out instead
- Joining two to-many relations in one query, producing a cartesian product
- Fixing it by adding an index — the queries were already indexed, there are simply too many
- Never reading the SQL the ORM emits, so the fix is unverified
- Replacing N+1 with an unbounded eager load that exhausts memory
- Having no query-count assertion, so the bug returns with the next refactor
FAQ
Is N+1 ever acceptable?
When N is genuinely bounded and small — a detail page loading three related records is not worth batching. The danger is when N is a function of data you do not control, because "small" becomes "four hundred" for one customer without any code change.
Does caching fix it?
It hides it, and only for repeated access. The first request still issues N+1 queries, cold caches after deploys reintroduce the full cost, and now you also own an invalidation problem. Fix the access pattern, then cache if there is still a reason to.
Do raw SQL or query builders avoid this?
They make it visible rather than impossible — you can still write a loop issuing one query per iteration. What changes is that the query is at the call site, so it is obvious in review. Most of the ORM problem is that the access is invisible.
What about a batch endpoint that takes a list of ids?
Same principle at the API layer: one call with N ids beats N calls with one id each, for exactly the same round-trip reason. The implementation behind it still needs to avoid N+1 internally, which is a separate and frequently missed step.