My LibraryBuy PDF
Part 5/Data and Databases

The N+1 Problem

The endpoint passed review. Two engineers looked at it, both wrote “clean”, and it shipped. Here is the whole thing, more or less:

const authors = await db.query("SELECT * FROM authors");

for (const author of authors) {
  author.books = await db.query(
    "SELECT * FROM books WHERE author_id = $1",
    [author.id],
  );
}

res.json(authors);

Read it again. There is nothing wrong with any single line. The list query is fine. The inner query is parameterized, uses the primary key, returns in a fraction of a millisecond. In dev, seeded with a dozen authors, the whole route comes back in under 100 ms and nobody thinks twice.

Then it meets production, where the authors table has 500 rows. The same route now takes four seconds. No code changed. No index got dropped. The query plan is identical. It just runs the fast little query 500 times instead of 12, and the endpoint that felt instant is now the slowest thing in your app.

That is the N+1 problem, and it is the most well-camouflaged performance bug there is. It hides in a for loop that looks like ordinary code, it never shows up on small data, and every piece of it is individually reasonable. This whole article is about seeing it, understanding why it costs what it costs, and the three ways out.

What “N+1” actually counts

The name is the shape. You run 1 query to fetch a list of things, then N more queries, one for each thing in that list, to fetch something related. 1 + N. For 500 authors that is 1 + 500 = 501 queries where you wanted 1 or 2.

Handling one request that returns 500 rowsN+1 patternlist query… ×5001 + 500 round trips, in series, ≈ 4000 msOne query insteadone JOIN query≈ 12 msSame rows. The cost is the number of round trips, not the database work.
One list query, then one round trip per row in series. The wall-clock cost is dominated by the count of round trips, not by any query being slow.

Here is the part that surprises people the first time. The database is barely working. Each SELECT ... WHERE author_id = $1 on an indexed column is maybe 0.2 ms of real query time. If the cost were the queries themselves, 500 of them would add about 100 ms and nobody would notice.

The cost is the round trip. Every query is a little conversation: your process serializes the SQL, writes it to a socket, the packet crosses the network to the database, the database does its 0.2 ms of work, it writes the result back, the packet crosses the network again, your process reads and parses it. On a good day inside one cloud region that round trip is around 8 ms. Put your app on a serverless platform talking to a managed database a couple of network hops away and it is easily 15 to 40 ms.

Multiply. 500 round trips at 8 ms each is 4 seconds where your database sat idle almost the entire time. You did not build a slow query. You built a program that spends four seconds saying “hello? … you there?” across a wire, five hundred times, one after another.

Fix 1: pull it into a JOIN

The database was built to combine related rows for you. That is what a JOIN is. Instead of asking for authors and then asking about each author’s books separately, you ask once and let the database stitch them together server-side, sending back a single result:

SELECT
  a.id, a.name,
  b.id AS book_id, b.title
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
WHERE a.deleted_at IS NULL;

One statement. One round trip. The database walks the join with an index on books.author_id and hands you every author paired with every one of their books. Your job on the app side shrinks to reshaping flat rows back into nested objects.

There is a catch, and it is worth understanding rather than memorizing. A LEFT JOIN against a one-to-many relationship multiplies rows. An author with 40 books comes back as 40 rows, with that author’s id and name repeated in every single one. Join in two such relationships at once (books and awards, say) and you get a Cartesian product: 40 books times 5 awards is 200 rows for one author, most of them redundant. That is the “join explosion”, and on wide tables it can move more bytes over the wire than the N+1 did.

Path A: one JOIN, rows multiplySELECT a.name, b.titleFROM authors a JOIN books bresult rowsAdap1Adap2Adap3Linp9Ada repeats once per bookPath B: two queries + groupSELECT * FROM authors → Ada, LinSELECT * FROM booksWHERE author_id IN (1, 2)group in app by author_idAda → p1 p2 p3Lin → p92 round trips, each author stored once
A JOIN returns everything in one round trip, but a one-to-many join repeats the parent's columns on every child row. Two queries plus in-memory grouping keeps each parent stored once.

A JOIN is the right first reach when the relationship is narrow (one-to-one, or one-to-few) and you actually want the combined shape. When it multiplies rows badly, you want the second fix.

Fix 2: one more query, grouped in memory

Keep the two queries, but make the second one cover all the ids at once instead of one per loop iteration. Fetch the list, collect every id, then issue a single query with WHERE ... IN (...) (in Postgres, the parameterized = ANY($1) with an array binds the whole set safely):

const authors = await db.query("SELECT * FROM authors");
const ids = authors.map((a) => a.id);

const books = await db.query(
  "SELECT * FROM books WHERE author_id = ANY($1)",
  [ids],
);

// group the flat book rows by author_id, in memory
const byAuthor = new Map(ids.map((id) => [id, []]));
for (const book of books) {
  byAuthor.get(book.author_id).push(book);
}

for (const author of authors) {
  author.books = byAuthor.get(author.id) ?? [];
}

Two queries total, forever. Not two per author. Two, whether there are 12 authors or 12 million. The grouping loop is pure in-memory work, Map lookups measured in microseconds, so it does not count against you the way a round trip does.

This is the sweet spot for one-to-many relationships. You avoid the join explosion (each author row comes back exactly once) and you keep the round-trip count flat. The price is a little app-side plumbing to regroup the children, which is trivial and shown above.

Fix 3: a batching loader that collects ids in a tick

The two fixes above assume you can see the whole list up front and rewrite the loop. Sometimes you cannot. The classic case is GraphQL, where the shape of the query is decided by the client at request time and each field has its own resolver. The engine calls your author.books resolver once per author node, independently, with no single place for you to hand-write a JOIN. Structurally, you are handed the N+1.

The fix is a batching loader. Instead of running a query the instant someone asks for books(authorId), you record the id, hand back a promise, and wait. Every request for a book that arrives within the same tick of the event loop gets collected into one list. Then, on the next tick, you fire a single WHERE id IN (...) query for the whole batch and resolve everyone’s promise from the result.

One tick of the event loopload(1)load(4)load(7)load(9)batch queue1 4 7 9tick ends, flushSELECT * FROM booksWHERE id IN (1, 4, 7, 9)one round trip answersall four load() calls
Every load call made during the current tick drops its id into one queue. When the tick ends, the loader flushes a single query for all the collected ids and hands each caller back its row.

The reference implementation of this idea is the dataloader package (stable at 2.x, and effectively the standard in the Node GraphQL world). You give it one batch function whose entire contract is: “here is an array of keys, give me back an array of values in the same order.” It handles the collect-within-a-tick scheduling for you.

import DataLoader from "dataloader";

const bookLoader = new DataLoader(async (authorIds) => {
  const rows = await db.query(
    "SELECT * FROM books WHERE author_id = ANY($1)",
    [authorIds],
  );

  // MUST return one entry per key, in the same order as authorIds
  const byAuthor = new Map(authorIds.map((id) => [id, []]));
  for (const row of rows) byAuthor.get(row.author_id).push(row);
  return authorIds.map((id) => byAuthor.get(id));
});

// anywhere, any number of times, in the same tick:
const booksForAda = await bookLoader.load(1);
const booksForLin = await bookLoader.load(2);
// ...both resolved by ONE query

Under the hood, .load() does not run anything immediately. It queues the key and returns a promise. DataLoader schedules the actual batch dispatch onto the microtask queue, so every .load() call that happens before the process next drains gets folded into a single batch. It also caches within the request, so .load(1) twice hits the database once. That caching is why you create a fresh loader per request and never share one across users. A long-lived loader would serve one user another user’s stale rows.

How ORMs hand you N+1 for free

Most people do not meet N+1 through hand-written SQL. They meet it through an ORM, because ORMs make the fatal pattern feel like plain property access.

The mechanism is lazy loading. A relation like author.books is not fetched when you load the author. It is fetched the moment you touch the property, quietly, with its own query. One access, one query. Perfectly convenient, and lethal inside a loop:

const authors = await Author.findAll();

for (const author of authors) {
  // each .getBooks() is a separate SELECT, this is the N+1
  const books = await author.getBooks();
  console.log(author.name, books.length);
}

Nothing here mentions a query. It reads like you are looping over data you already have. You are not. You are firing one round trip per iteration, and the loop is the N.

The cure is eager loading: tell the ORM up front that you want the relation, and it fetches everything in a bounded number of queries. The two big Node ORMs do this a little differently, and the difference maps exactly onto the two fixes above.

Sequelize eager-loads with include, and by default it emits a JOIN (fix 1):

const authors = await Author.findAll({
  include: [{ model: Book }],   // one JOIN
});

When that JOIN would multiply rows badly, you switch a single relation to separate: true, and Sequelize runs a second WHERE id IN (...) query and groups in memory for you (fix 2):

const authors = await Author.findAll({
  include: [{ model: Book, separate: true }], // second query + group
});

Prisma goes the other way. Its include runs one query per relation level and stitches the results together in the client by default. That is fix 2, done for you, and it is not N+1. It is a constant two queries no matter how many authors:

const authors = await prisma.author.findMany({
  include: { books: true },   // 2 queries total, merged in the client
});

If you want the single-JOIN shape instead, Prisma exposes relationLoadStrategy: "join", which pushes the merge into the database using a lateral join. As of mid-2026 that option still sits behind the relationJoins preview flag, so treat the exact status as fast-moving and check your version. The default include is already safe from N+1 either way.

The mirror bug: over-eager loading

Here is the trap on the other side, and people fall into it right after they learn about N+1, overcorrecting. Once “eager load your relations” becomes a reflex, you start pulling the entire object graph on every request, most of which you throw away.

Picture an endpoint whose job is to return a list of author names for a dropdown. Somewhere in the codebase there is a reusable getAuthors() that eager-loads books, and each book’s comments, and each comment’s replies, because some other screen needed all that. You call it, use author.name, and ignore the rest. You just made the database assemble and serialize a megabyte of nested data so you could read one string from it.

Endpoint needs: author.nameauthorname (used)books ×50loaded, unusedprofileloaded, unusedsettingsloaded, unusedcomments ×20per bookreactionsloaded, unusedcomment authorone field needed,the whole graph fetchedand discarded
The endpoint needs one field, the author's name, but a blanket eager-load pulls the entire graph of books, comments, authors and reactions, then discards nearly all of it.

N+1 and over-fetching are the same mistake wearing opposite coats: a mismatch between the data you ask for and the data you use. The fix for both is the same discipline. Ask for exactly the columns and relations this endpoint needs, no more. Use select to name columns instead of SELECT *, and only include a relation when you are going to read it. A shared “load everything” function is a convenience that quietly taxes every caller.

Detecting it before your users do

You will not out-discipline this bug. It slips into a code review because the code looks clean, exactly as it did in the opening. So you instrument for it instead of trusting your eyes.

Count queries per request in development. Wrap your database client once so every query bumps a counter, tie the counter to the current request, and log a warning past a threshold. In Node, AsyncLocalStorage attributes the count to the right request even under concurrency:

import { AsyncLocalStorage } from "node:async_hooks";

const als = new AsyncLocalStorage();

// wrap the driver a single time
const raw = pool.query.bind(pool);
pool.query = (...args) => {
  const store = als.getStore();
  if (store) store.count++;
  return raw(...args);
};

// per-request middleware
app.use((req, res, next) => {
  als.run({ count: 0 }, () => {
    res.on("finish", () => {
      const { count } = als.getStore();
      if (count > 20) {
        console.warn(`[n+1?] ${req.method} ${req.path} ran ${count} queries`);
      }
    });
    next();
  });
});

The first time a route that should run 2 queries prints ran 340 queries, you have found an N+1 in seconds instead of chasing a vague “the page is slow” ticket for an afternoon.

Assert on the count in a test. A query count is a fact you can pin down. Write a test that exercises the endpoint against a seed with several parent rows and asserts the query count stays under a budget. It fails the moment someone reintroduces a per-row query, and it fails in CI, not in production:

test("GET /api/authors stays within its query budget", async () => {
  const before = getQueryCount();
  await request(app).get("/api/authors");
  const used = getQueryCount() - before;
  expect(used).toBeLessThanOrEqual(2); // list + batched books
});

Seed with 3 authors, not 1. An N+1 that runs “1 + N” queries looks identical to the correct “2” when N is 1. With three parents, 2 versus 4 is unmissable.

In production, distributed tracing tells the same story: a request span fanning out into hundreds of tiny, near-identical database spans is the N+1 signature, and it is worth an alert. Once you have consolidated to a single query, make sure the foreign-key column it filters on is actually indexed, or your one clean query does a sequential scan and you have traded a latency problem for a throughput one.

Feel the difference

Here is the whole thing in miniature. The list has 8 rows. Each simulated database round trip costs 45 ms. Run it one query per row, then run it batched, and watch the call count and the wall clock:

interactiveN+1 vs batched: same data, very different clocks

Nine round trips versus two. On this toy the gap is a third of a second against ninety milliseconds. Swap 8 rows for 500 and 45 ms for a real cross-network latency, and that third of a second becomes the four-second outage from the opening.

One more nuance: concurrency is not a fix

Someone always asks: what if I fire the per-row queries in parallel with Promise.all instead of awaiting them one at a time? The async/await loop in the opening is serial, so surely running them together fixes it?

It fixes the wall clock, not the problem. You are still sending N+1 queries. All you changed is whether they wait in line or stampede at once:

Two spellings of the same N+1for…of + await (serial)… ×500one at a time, ≈ 4000 ms, gentle on the poolPromise.all (concurrent)500 in flight at oncevs a pool of 10 connectionsfast when it works, but the pool queues and calls time out under load
Serial awaits send N round trips back to back over four seconds. Promise.all sends them at once, so the wall clock drops, but 500 queries now slam a connection pool of ten and queue behind each other anyway.

Your database is not an infinite parallel machine. It talks to your app through a connection pool, typically 10 to 20 connections. Throw 500 concurrent queries at a pool of 10 and 490 of them queue up waiting for a free connection, you have spiked database load for no reason, and under real traffic you start handing out connection-acquisition timeouts. Promise.all turns a slow-but-stable endpoint into a fast-until-it-isn’t one. The count of queries is the disease. Serial versus parallel only changes the symptom.

Summary

  • N+1 is one query to fetch a list, then one more per item in a loop. 1 + N. It reviews as clean code and only hurts once N gets large.
  • The cost is round-trip latency, not database work. Each trip is a few milliseconds of network wait, and the count of trips, not the speed of any query, sets the wall clock.
  • Fix 1, JOIN: let the database combine rows in one round trip. Best for one-to-one and one-to-few; watch out for row multiplication on one-to-many.
  • Fix 2, one IN-list query plus in-memory grouping: collect all the ids, run a single WHERE id = ANY($1), regroup the children with a Map. Constant two queries, no duplication. The sweet spot for one-to-many.
  • Fix 3, a batching loader (DataLoader): collect every id requested within one event-loop tick and flush a single query. The structural fix for GraphQL, where per-field resolvers give you no place to write a JOIN. Return values positionally matched to keys, and use one loader per request.
  • ORMs cause N+1 through lazy relation access in a loop. Eager-load with include. Prisma’s default include is already a bounded two queries; Sequelize joins by default and offers separate: true for the IN-list shape.
  • Over-fetching is the mirror bug: eager-loading a huge object graph to read one field. Same root cause as N+1, opposite direction. Ask for exactly what the endpoint uses.
  • Detect it: count queries per request in dev with a wrapped client and AsyncLocalStorage, assert a query budget in tests seeded with several parent rows, and index the foreign key you filter on.