Data & Storage

The N+1 Query Problem

How lazy loading turns one list into hundreds of round trips, the three ways to fix it, and how to catch it in tests before it reaches production.

Intro·9 min read·Updated Sep 27, 2026

You fetch a list of N rows with one query, then touch a related record for each row, and the ORM quietly runs one more query per row: 1 + N round trips instead of 2. Each query is fast, so nothing looks slow in isolation. The cost is the count of round trips, which grows with the data, and the fix is always the same shape: load the related rows for the whole list in one go.

Why it matters

N+1 is the most common performance bug in ORM-backed applications, and the most deceptive: the page with 10 test records is instant, the page with 500 production records takes seconds and pins a database connection the whole time. It hides behind innocent-looking property access, it survives caching, and it comes back every time someone adds a field to a template. Recognising the shape is worth more than any single fix.

What it looks like

posts.ts (Prisma)

const posts = await prisma.post.findMany({take: 50});     // 1 query

for (const post of posts) {
  const author = await prisma.user.findUnique({           // 50 queries
    where: {id: post.authorId},
  });
  console.log(post.title, 'by', author.name);
}
// 51 round trips to render one list

In ORMs with lazy loading (ActiveRecord, Django, Hibernate, TypeORM relations) the loop is even less visible: post.author.name looks like a property read and is a query. The database log is where the pattern becomes obvious.

Same list, two strategies. Each round trip costs network latency plus connection time regardless of how little data it returns, so 51 sequential trips take roughly 51 × latency.

The three fixes

Fix	Queries	Best when	Watch out for
JOIN / eager load	1	One-to-one or small one-to-many; you need most columns	Row explosion when joining several has-many relations
Batch with IN (…)	2 per relation	One-to-many, many-to-many, nested graphs, GraphQL	Very long IN lists — chunk them
Denormalise / precompute	1	Read-heavy fields like author_name or comment_count	Keeping the copy in sync on writes

1. Eager load with a join or include

N+1

const posts = await prisma.post.findMany({
  take: 50,
});
for (const p of posts) {
  const a = await prisma.user.findUnique({
    where: {id: p.authorId},
  });
}

eager load

const posts = await prisma.post.findMany({
  take: 50,
  include: {author: true},
});
// Prisma issues 2 queries (posts, then
// users WHERE id IN (...)) and stitches
// them; other ORMs use a JOIN.

The ORM equivalents: includes(:author) in ActiveRecord, select_related (JOIN, for foreign keys) and prefetch_related (IN batch, for many-to-many) in Django, JOIN FETCH or @EntityGraph in Hibernate. A JOIN is one round trip but repeats the parent columns on every child row; with two has-many relations the result set multiplies, which is why most ORMs batch instead for collections.

2. Batch by hand with IN

batch.ts (any SQL client)

const posts = await db.query('SELECT * FROM posts LIMIT 50');
const ids = [...new Set(posts.map(p => p.author_id))];

const users = await db.query(
  'SELECT * FROM users WHERE id = ANY($1)',   // one query for all authors
  [ids],
);
const byId = new Map(users.map(u => [u.id, u]));

for (const p of posts) {
  console.log(p.title, 'by', byId.get(p.author_id)?.name);
}

This is what every eager-loading feature does under the hood. Knowing it by hand matters when the ORM cannot see the loop, for example when the related lookup lives in a serializer, a template helper, or a GraphQL resolver.

3. Batch automatically with DataLoader (GraphQL)

GraphQL resolvers are N+1 by construction: Post.author runs once per post, and the resolver has no idea how many siblings it has. DataLoader collects every load(id) made during one tick of the event loop, issues a single batched fetch, and hands each caller its own result. One loader instance per request, so caches never leak across users.

loaders.ts

import DataLoader from 'dataloader';

export const makeLoaders = () => ({
  userById: new DataLoader(async (ids: readonly string[]) => {
    const rows = await db.query(
      'SELECT * FROM users WHERE id = ANY($1)', [ids],
    );
    const byId = new Map(rows.map(u => [u.id, u]));
    return ids.map(id => byId.get(id) ?? null); // same order as ids
  }),
});

// resolver
const Post = {
  author: (post, _args, ctx) => ctx.loaders.userById.load(post.authorId),
};

Catching it before production

EXPLAIN will not help: every individual query is already optimal. You need to count queries per request.

1
Turn on query logging in development (log: ['query'] in Prisma, DEBUG SQL logging in Django, show_sql in Hibernate) and look for the same statement repeating with different parameters.
2
Assert query counts in tests. Django ships assertNumQueries; in Node, wrap the client in a counter for the duration of a test. A list endpoint should issue the same number of queries for 2 rows as for 200.
3
Watch the ratio in production. APM tools flag "N similar queries in one trace"; a request whose SQL span count grows with the response size is the signature.

posts.test.ts

test('list endpoint does not scale queries with rows', async () => {
  await seedPosts(2);
  const small = await countQueries(() => api.get('/posts'));

  await seedPosts(200);
  const large = await countQueries(() => api.get('/posts'));

  expect(large).toBe(small);   // constant, whatever the row count
});

Pitfalls

Eager-loading everything, everywhere
Adding include for every relation to be safe over-fetches columns nobody renders and, with several has-many relations, multiplies the joined result set. Load what the view needs; the point is one query per relation, not one giant query.
N+1 on writes
A loop of UPDATE … WHERE id = ? or one INSERT per row is the same problem in the other direction. Use multi-row INSERT, UPDATE … FROM (VALUES …), or the ORM's createMany / bulk_update.
Unbounded IN lists
Batching 50 000 ids into one IN (…) hits parameter limits (65 535 in Postgres) and produces a slow plan. Chunk the ids into batches of a few hundred to a few thousand.
Hiding it behind a cache
A per-row cache turns 51 database queries into 51 cache lookups: still 51 round trips, now with a warm-up cliff every time the cache empties. Fix the access pattern, then cache the batched result if it is still needed.
Loops that live outside the data layer
Serializers, template partials and GraphQL field resolvers all iterate over rows without the ORM knowing. The query that runs per item was written far from the loop, which is why query-count tests find these when code review does not.

Interview questions

Q1What is the N+1 query problem?

Fetching a list with one query and then running one additional query per item to load a related record, so a page of N rows costs N+1 round trips. It usually comes from lazy-loaded relations accessed inside a loop, each query is individually fast, and the total grows linearly with the data, which is why it only shows up at production sizes.

Q2How do you fix it, and what is the trade-off between the approaches?

Either eager-load with a JOIN, which is one round trip but duplicates parent columns and explodes with multiple has-many relations, or batch the related lookups into one WHERE id IN (…) query per relation, which is two round trips and scales to nested graphs. For fields read far more often than written, denormalising the value onto the parent row avoids the second query entirely.

Q3Why is N+1 so common in GraphQL, and what is DataLoader?

Each field resolver runs independently per parent object, so Post.author executes once per post with no knowledge of its siblings. DataLoader queues every load(id) call made during one tick of the event loop, issues a single batched query, and resolves each caller with its own row. It is created per request so its cache is scoped to that user.

Q4How would you detect N+1 in a codebase you have never seen?

Enable SQL logging and hit a list endpoint with a handful of rows: repeated statements differing only in parameters are the signature. Then make it permanent with a test asserting that the query count does not change between 2 and 200 seeded rows, and use APM traces that flag many similar spans per request in production.

Q5Is a JOIN always better than two queries?

No. A JOIN of a parent to two has-many relations returns parent × children₁ × children₂ rows, so the ORM ends up transferring and deduplicating far more data than two IN-batched queries would. JOIN wins for one-to-one and small one-to-many; batched queries win for collections and nested graphs.

Q6What happens when a batched IN list gets very large?

The query hits driver or server parameter limits and the planner degrades, often to a sequential scan with a hash of the list. Chunk into batches of a few hundred ids, or write the ids to a temporary table and join against it for very large sets.

Key takeaways

N+1 is one query for the list plus one per item for a relation; the cost is round trips, not rows, so it hides at small sizes.
Fix it by loading the relation for the whole list at once: a JOIN, an IN-batched query, or a denormalised column.
JOINs suit one-to-one; IN batches suit collections and nested graphs; DataLoader automates batching in GraphQL.
EXPLAIN cannot see it; count queries per request and assert the count is constant in tests.
Writes in a loop are the same bug; batch inserts and updates too.
Do not eager-load everything; load what the view renders.

Sources

#Why it matters

#What it looks like

#The three fixes

1. Eager load with a join or include

2. Batch by hand with IN

3. Batch automatically with DataLoader (GraphQL)

#Catching it before production

#Pitfalls

#Interview questions