Lesson 16 / 35

The N+1 Query Problem

Recognise the N+1 pattern in loops and lazy relations, count the queries it causes and fix it with eager loading or batching.

By · 15 years in web development

One query becomes a hundred and one

The N+1 problem happens when code runs one query to fetch a list of N items and then one more query per item to fetch related data. Showing 100 posts with their authors becomes 101 round trips instead of one or two. Each query may be fast on its own, which is why it hides well during development with ten rows, then makes a page slow in production with thousands. It usually appears in three places: an await inside a for loop over results, TypeORM lazy relations accessed inside a loop, and GraphQL resolvers that fetch a parent's children one parent at a time. The fix is to fetch related rows in bulk: use include or relations so the ORM loads them with a join or one IN (...) query, or collect the IDs yourself and run a single findMany({ where: { id: { in: ids } } }). For GraphQL, the DataLoader pattern batches per-request lookups automatically.

Counting queries in a simulation

A stand-in for an ORM that counts every call. Three posts by two authors: the naive loop runs 4 queries, the batched version runs 2, no matter how many posts there are.

const posts = [{ id: 1, authorId: 1 }, { id: 2, authorId: 2 }, { id: 3, authorId: 1 }];
const users = { 1: 'Asha', 2: 'Ravi' };
let queries = 0;
const findPosts = async () => { queries++; return posts; };
const findUser = async (id) => { queries++; return users[id]; };
const findUsers = async (ids) => { queries++; return ids.map((id) => users[id]); };

async function naive() {
  for (const p of await findPosts()) await findUser(p.authorId);
  return queries;
}
async function batched() {
  const list = await findPosts();
  await findUsers([...new Set(list.map((p) => p.authorId))]);
  return queries;
}
naive().then((n) => { console.log('naive:', n); queries = 0; return batched(); })
  .then((n) => console.log('batched:', n));

Output:

naive: 4
batched: 2

Tracing the naive loop

Step through the loop with the same three posts and watch the query counter climb once per post.

let queries = 0;
const list = await findPosts();
for (const p of list) {
  p.author = await findUser(p.authorId);
}
console.log('queries:', queries);
  1. Line 1: The counter starts at zero; nothing has touched the database yet.
  2. Line 2: One query fetches all three posts. This is the '1' in N+1.
  3. Line 4: Inside the loop, post 1 needs its author, so a separate query runs.
  4. Line 4: Post 2 triggers another query, even though the work is identical in shape.
  5. Line 4: Post 3 asks for author 1 again. The naive loop does not remember it was already fetched.
  6. Line 6: Total is 1 + N = 4. With 500 posts it would be 501 round trips.

One trip to the kirana store

If your mother sends you for ten items and you walk to the shop and back for each one, that is N+1. Writing the whole list and making one trip is eager loading. The shop is equally fast either way; the walking is what costs you.

Quick check: Which change fixes an N+1 problem when listing 200 orders with their customers in Prisma?

  • Adding an index on the orders table's primary key
  • Increasing the connection pool size
  • Using include: { customer: true } in the findMany call instead of fetching each customer inside a loop
  • Wrapping the loop in a transaction
Answer

Using include: { customer: true } in the findMany call instead of fetching each customer inside a loop — The cost comes from the number of round trips. Loading customers with include (or one IN query) collapses 201 queries into one or two. More connections or a transaction do not reduce the count.