The N+1 query trap

One query becomes a hundred

You load a page of 50 orders, then for each order you fetch its customer. That’s 1 query for the list + N queries for the children = N+1 queries. Each is individually fast and indexed, so it passes review — until production, where the page makes 51 sequential database round trips and takes half a second. The cost isn’t rows scanned; it’s round trips. At ~0.5 ms of network latency each, 50 extra queries is 25 ms of pure waiting, and it grows linearly with the list.

Why it sneaks in

The trap is almost always hidden behind an abstraction:

• ORM lazy loading — order.customer looks like a field access but secretly issues a query the first time it’s touched. Put it in a loop and you’ve made N of them without writing a single visible query.
• A loop over a list — fetch ids, then call get(id) per id.
• Service fan-out — the microservice version: list 50 items, then call another service once per item. Same shape, now over the network at 1–10 ms each.
• GraphQL resolvers — a naive per-field resolver runs once per parent.

# the trap
orders = db.query("SELECT * FROM orders LIMIT 50")   # 1
for o in orders:
    o.customer = db.query("SELECT * FROM customers WHERE id = ?", o.cust_id)  # N

Collapsing it

Turn N+1 into 1+1 (or just 1) by fetching the children in bulk:

• Batch with IN — collect all the ids, then SELECT * FROM customers WHERE id IN (...) once, and stitch in memory. Two queries total, regardless of N.
• Join — SELECT ... FROM orders JOIN customers ... returns everything in one round trip when you need both together.
• Eager loading — ORMs expose this directly (includes, selectinload, JOIN FETCH): tell it to preload the association so it issues one extra query, not N.
• A request-scoped batcher — DataLoader-style: collect the per-item lookups fired during one request, coalesce them into a single batched query, and hand each caller back its slice. This is the standard fix for GraphQL fan-out.

ids = [o.cust_id for o in orders]
customers = db.query("SELECT * FROM customers WHERE id IN (?)", ids)  # 1 more, total 2

Where it shows up

Anywhere a list-then-detail pattern meets an ORM or a service boundary: rendering a feed with each author, a dashboard with per-row aggregates, a GraphQL query that walks an object graph. It’s one of the most common causes of “the page got slow as we added data” — and it doesn’t show up in single-record testing.

The interview cue

When a design fetches a collection and then enriches each element, say it out loud: “I’d watch for the N+1 pattern here — fetching the list and then a query per row. I’d batch the child lookups into one IN query or a join, or use a request-scoped DataLoader so the fan-out coalesces.” Recognizing that the bottleneck is round trips, not row count — and that the fix is batching — is the point.