N+1 Query Problem

1The Shape of the Bug

04-query_optimization/ covered diagnosing a single slow query. This topic is about a different failure mode entirely: not one slow query, but far too many fast ones, usually hidden behind an innocent-looking loop over ORM objects.

// 1 query to get all posts
const posts = await db.query("SELECT id, title, author_id FROM posts LIMIT 20");

// then N more queries -- one PER post, to get that post's author
for (const post of posts) {
  const author = await db.query("SELECT name FROM users WHERE id = ?", [post.author_id]);
  console.log(`${post.title} by ${author.name}`);
}

For 20 posts, this is 1 query for the post list, plus 20 more for their authors — 21 queries to render one page. This is the "N+1" in the name: 1 query for the parent collection, N queries for each child relation, where a single well-shaped query could have answered the whole thing.

2Why It's Easy to Write by Accident

This bug is almost never written deliberately — it hides behind ORM convenience. An ORM that lazily loads a related object the moment code accesses it (e.g. post.author.name) makes the N+1 shape invisible at the call site:

// looks like plain property access -- but each `.author` triggers a fresh query
// if the ORM is configured to lazily load relations
for (const post of posts) {
  console.log(`${post.title} by ${post.author.name}`);  // hidden query here!
}

The code reads as if author were already in memory. Nothing in the syntax signals "this line hits the database." This is exactly why N+1 bugs tend to survive code review and show up first in production, once the loop runs over hundreds of rows instead of the 3-row fixture used in local testing.

3The Fix: Eager Loading / a Single JOIN

// ONE query: JOIN authors in up front, instead of fetching them one at a time
const postsWithAuthors = await db.query(`
  SELECT posts.id, posts.title, users.name AS author_name
  FROM posts JOIN users ON posts.author_id = users.id
  LIMIT 20
`);

Most ORMs expose this as an explicit "eager load" / "include" option (e.g. .include("author"), populate: ["author"]) — the point is making the join/prefetch explicit at the call site, turning what was an invisible per-row cost into a single query decided once, up front.

4Batching: WHERE id IN (...) as a Middle Ground

Sometimes a full JOIN isn't the right shape (e.g. the child data comes from a different data source entirely, like a separate microservice or a different database). A batched load collects all the needed IDs first, then makes exactly one follow-up query for all of them:

const posts = await db.query("SELECT id, title, author_id FROM posts LIMIT 20");
const authorIds = [...new Set(posts.map(p => p.author_id))];

// ONE query for ALL authors needed, using their collected ids
const authors = await db.query("SELECT id, name FROM users WHERE id IN (?)", [authorIds]);
const authorById = new Map(authors.map(a => [a.id, a]));

for (const post of posts) {
  console.log(`${post.title} by ${authorById.get(post.author_id).name}`);
}

This goes from N+1 queries down to exactly 2, regardless of how many posts there are — and it works even when the parent and child data live in entirely different stores that can't be joined with SQL at all.

5DataLoader-Style Batching: Deferred, Automatic Collection

The pattern in section 4 requires manually collecting IDs before the loop. DataLoader (popularized by GraphQL resolvers, where N+1 is an especially common trap — see backend/api-design/12-graphql/ for the resolver-level version of this problem) automates that collection: instead of firing a query the instant .load(id) is called, it batches every .load() call made within the same event-loop tick into one combined request, transparently:

const userLoader = new DataLoader(async (ids: number[]) => {
  const users = await db.query("SELECT * FROM users WHERE id IN (?)", [ids]);
  const byId = new Map(users.map(u => [u.id, u]));
  return ids.map(id => byId.get(id));   // DataLoader requires results in the SAME order as ids
});

// each call LOOKS like a separate request, but DataLoader collects them
// into one batched query behind the scenes
for (const post of posts) {
  const author = await userLoader.load(post.author_id);
}

This preserves the ergonomic per-item .load(id) call site (no manual ID-collection loop needed) while still producing exactly one underlying query per batch — the batching happens transparently, deferred until the current synchronous tick finishes.

⚠️ Common Pitfalls

1. Not noticing N+1 in local development — with 3 test rows, N+1 queries vs 1 query is invisible in wall-clock time; it only becomes a real problem at production scale, which is why query-count assertions (not just correctness assertions) matter in tests for list-rendering code paths.
2. Fixing N+1 by removing the loop's abstraction entirely — e.g. writing one giant hand-rolled JOIN for every possible access pattern is overkill; batching (section 4) or DataLoader (section 5) fix the query count without sacrificing per-item code clarity.
3. Batching without deduplicating IDs — if 20 posts share only 5 distinct authors, WHERE id IN (...) should be called with 5 unique IDs, not 20 (with duplicates); forgetting new Set(...) wastes nothing correctness-wise but does waste query payload size.
4. Assuming eager-loading everything is always better — a JOIN that pulls in a relation the page never actually uses wastes bandwidth on every request; eager-load the specific relations a given code path needs, not reflexively all of them.

📋 Quick Reference

N+1 = 1 query for a list + N queries (one per row) for each row's related data
Usually hidden behind lazy-loaded ORM property access (post.author.name)

Fix 1: eager load / JOIN up front       -> 1 query total
Fix 2: batch with WHERE id IN (...)      -> 2 queries total, works across data sources
Fix 3: DataLoader (deferred auto-batch)  -> keeps per-item .load(id) call sites, 1 query per batch

Detect it: count queries per request in tests/logging, not just correctness

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbidx_n_plus_1_query_problem_theory.md · generated by tools/build_lessons.py