Spotting and fixing N+1

Picture the most ordinary screen in the invoicing app: a list of invoices, and next to each one a small summary of its line items. You wrote it last chapter, it passed review, and on your dev database of twenty seeded invoices it renders the instant you click. Then a customer with eight hundred invoices opens the same page, and it takes six seconds. Or worse, it doesn’t render at all, because the connection pool ran out of connections and the request errored.

Nothing about your code changed; the data changed. The bug was there the whole time, just invisible at twenty rows.

This is the N+1 query problem, and it is the most common performance bug in this stack. You already have the cure, because last chapter you wrote both tools that fix it: the relational query API with with, and joins with db.select. This lesson isn’t about learning a new tool. It’s about training one habit: scan a chunk of data-access code and ask whether the query runs once, or once per row. Once you can see that shape, the fix is the API you already know.

The shape of the bug

Let’s write the bug on purpose, in its clearest form. The page fetches the invoices, then for each invoice fetches its line items.

const invoiceList = await db.select().from(invoices);

for (const invoice of invoiceList) {
  const items = await db
    .select()
    .from(lineItems)
    .where(eq(lineItems.invoiceId, invoice.id));
  // render the invoice with its items…
}

Count the queries. The first select is one query, and it returns N invoices. Then the loop runs once per invoice, and each iteration fires its own select for that invoice’s line items, which is N more queries. One for the parents and N for the children gives you N+1. For a twenty-invoice page, that’s twenty-one queries. For eight hundred invoices, eight hundred and one.

Why does that hurt so much? Because every one of those statements is a separate round-trip to the database. Each statement gets parsed, planned, and executed by Postgres, and the expensive part is that each one travels across the network to the database and back. Even if a single line-items query takes only two milliseconds, eight hundred of them running one after another add up to well over a second of pure waiting. The database isn’t even busy for most of that time; it’s sitting idle while the request crosses the wire, again and again.

The arithmetic is the whole bug, so the diagnostic question is the whole skill:

Tip

Is this query running once, or once per row? That single question is the N+1 reflex. Once per row is the bug; once is the fix. Ask it of every data-access call you read.

It helps to see the round-trips rather than reason about them. Scrub through this sequence, with the app on the left, the database on the right, and one trip across the wire per step.

round-trips so far 1

🖥️ App your page

select … from invoices

20 rows

select … from line_items where invoice_id = $1

items

select … from line_items where invoice_id = $2

items

…line_items where invoice_id = $4 … $20 … ×17 more

🗄️ Database Postgres

One round-trip: the app asks for the invoice list, and the database returns 20 rows. This is the single query you'd expect.

round-trips so far 2

🖥️ App your page

select … from invoices

20 rows

select … from line_items where invoice_id = $1

items

select … from line_items where invoice_id = $2

items

…line_items where invoice_id = $4 … $20 … ×17 more

🗄️ Database Postgres

Round-trip 2: now the loop starts. It fires a line-items query for invoice #1, a brand-new trip across the wire, with the parent query already behind it (dimmed).

round-trips so far 3

🖥️ App your page

select … from invoices

20 rows

select … from line_items where invoice_id = $1

items

select … from line_items where invoice_id = $2

items

…line_items where invoice_id = $4 … $20 … ×17 more

🗄️ Database Postgres

Round-trip 3: and again, for invoice #2. It's the same query shape with a different bound id ($2). Every invoice is going to cost one of these.

round-trips so far 20

🖥️ App your page

select … from invoices

20 rows

select … from line_items where invoice_id = $1

items

select … from line_items where invoice_id = $2

items

…line_items where invoice_id = $4 … $20 … ×17 more

🗄️ Database Postgres

Round-trips 4 through 20, one per remaining invoice. The loop keeps firing the same query with the next id, 17 more crossings of the wire, collapsed here.

round-trips so far 21

🖥️ App your page

select … from invoices

20 rows

select … from line_items where invoice_id = $1

items

select … from line_items where invoice_id = $2

items

…line_items where invoice_id = $4 … $20 … ×17 more

🗄️ Database Postgres

21 round-trips to render one 20-invoice page — one parent query, then one child query per row. That's the N+1.

21 round-trips to render one page: one for the parent list, twenty for the children. Each row paid its own trip to the database, and that's the N+1.

Prev

Next

That picture is the shape: one parent query, then one child query per returned row. The rest of this lesson is about what to do once you recognize it.

Why Promise.all doesn’t fix it

Here’s where almost everyone takes a wrong turn, so let’s take it together and then find our way back.

You just saw the cost of the sequential loop: twenty queries, one after another, each waiting for the last to finish. The obvious instinct is to make them parallel, firing all the line-items queries at once instead of waiting in line. Promise.all does exactly that, and the page genuinely gets faster. So the problem is solved?

It is not. Look at the two side by side, the same wrong idea at two speeds.

Sequential loop

const invoiceList = await db.select().from(invoices);

for (const invoice of invoiceList) {
  const items = await db
    .select()
    .from(lineItems)
    .where(eq(lineItems.invoiceId, invoice.id));
}

N+1, run one at a time. Each child query waits for the previous one to come back. This is the slowest possible shape, because the round-trips happen in single file.

Promise.all

const invoiceList = await db.select().from(invoices);
const itemsByInvoice = await Promise.all(
  invoiceList.map((invoice) =>
    db.select().from(lineItems).where(eq(lineItems.invoiceId, invoice.id)),
  ),
);

Faster, and still N+1. The database does the exact same N+1 work, now all at once instead of in a line. Fewer seconds of waiting, but the same number of queries.

The mark is red in both tabs on purpose: both are the same bug. The distinction that matters is this. Promise.all parallelizes JavaScript awaits. It does not collapse database queries. Those are two different layers, and Promise.all only touches the top one.

Think about what’s still happening underneath. The database parses, plans, and executes N separate statements just as before. It has no idea they were launched together; it just sees N queries arrive. The connection pool still serves N round-trips, except now it serves them concurrently, which means this one page can grab N connections from the pool at the same moment. A pool only has so many connections. Fire enough of these in parallel on a busy server and you starve the pool: other requests wait for a connection that’s tied up running your eight hundredth line-items query. That pool-contention failure is its own topic a couple of lessons from now. For now, just notice that making it parallel can trade a slow page for a pool incident.

So Promise.all is a real win over the sequential loop, because parallel network traffic beats serial. But it’s a win within a broken shape. You went from sequential N+1 to parallel N+1, and the N+1 itself is untouched. The floor on how fast the page can be is no longer one query’s worth of time; it’s the slowest query in the batch, plus whatever contention the pool adds.

Here is that layer confusion drawn out:

JavaScript awaits — what Promise.all changes rearranged

Sequential

await 1

await 2

await 3

await 4

await 5

await 6

await 7

await 8

Promise.all

all 8 awaits launched together

The awaits now overlap — the lane gets shorter.

Database round-trips — what Promise.all does NOT change identical

Sequential

SQL

SQL

SQL

SQL

SQL

SQL

SQL

SQL

8 × line_items where invoice_id = …

Promise.all

SQL

SQL

SQL

SQL

SQL

SQL

SQL

SQL

8 × line_items where invoice_id = … — same count, same trips

Still N statements, N round-trips — the count never moved.

time →

Promise.all rearranges the top lane and leaves the bottom lane, the one that actually costs, completely identical.

One guardrail before you leave this section, so you don’t over-correct into avoiding Promise.all entirely. It is a perfectly good tool, and the problem isn’t the function but what you map over it. Running several different queries at once is exactly what it’s for. Fetching the invoice list, the current organization, and the user’s notifications concurrently for one page is correct and fast, because those are independent reads with no relationship to each other. The bug is specifically Promise.all over the same query shape parameterized by an id, like lineItems where invoiceId = $1, $2, $3, and so on. That isn’t parallel work, it’s an N+1 fired all at once. So before you add a Promise.all, ask whether these are different queries or the same query repeated. Different queries are fine; the same query once per row is the bug.

The four shapes N+1 hides in

You’ve now seen two forms of the bug, the loop and the Promise.all. But N+1 is a shape, not a particular piece of syntax, and it shows up in disguises. Here are the four you’ll meet, so you can recognize it however it appears.

1. A loop with await inside. This is the one from the top of the lesson. It’s the sequential N+1, the worst-performing form and, mercifully, the easiest to spot, because the await sitting inside the for is a visible tell that it fires one query on every iteration.

for (const invoice of invoiceList) {
  const items = await getLineItems(invoice.id);
}

2. Promise.all over a parameterized map. This is the parallel N+1. It’s faster than the loop, and the hardest to spot, because it looks like good, modern, concurrent code. You read it as “nice, this is parallelized” and move on. The tell isn’t the Promise.all itself; it’s that the map issues the same query with a different id each time.

const items = await Promise.all(
  invoiceList.map((invoice) => getLineItems(invoice.id)),
);

3. A component tree where each card fetches its own data. This is the sneaky one, because there’s no loop in sight. The list renders an <InvoiceCard> per invoice, and each card is a Server Component that awaits its own line-items query inside it. The N+1 is spread across the component tree instead of sitting in one function, so it’s invisible in any single file you open.

{invoiceList.map((invoice) => (
  <InvoiceCard key={invoice.id} invoiceId={invoice.id} />
))}

This component-tree form has its own depth. Deciding parallel data needs at one layer instead of at each leaf is a Server Component skill the course returns to much later. Here it’s just one more disguise to recognize: a list of cards where each leaf fetches its own data is an N+1.

4. A findMany followed by a findFirst per row. This is the relational-API version of the same bug. Each invoice does its own findFirst to look up the one organization it belongs to, a many-to-one lookup back up to the parent. The irony is that the very tool that prevents N+1, the relational query API, is here being used in the shape that causes it.

const invoiceList = await db.query.invoices.findMany();
for (const invoice of invoiceList) {
  const org = await db.query.organizations.findFirst({
    where: { id: invoice.organizationId },
  });
}

All four are the same shape: one list read, then one more query per returned row, whether that query reaches down to the row’s children or up to its parent. That gives you the code-review heuristic, the thing to actually scan for when reading a PR:

Tip

Scan for Promise.all, for (const … of …), and .map( sitting near a db. call, and for component trees where a leaf fetches its own data. Each is a place N+1 hides. When you find one, ask the question: once, or once per row?

Try sorting these. Each chip is a small data-access snippet. Decide whether it’s N+1 (one query per row) or fine (one query, or several genuinely independent queries run in parallel).

Sort each snippet by whether it issues one query per row (N+1) or not (fine). Drag each item into the bucket it belongs to, then press Check.

N+1 One query per row

Fine One query, or independent queries

for (const inv of invoices) { await getLineItems(inv.id) }

Promise.all(invoices.map((inv) => getLineItems(inv.id)))

A list of <InvoiceCard invoiceId={inv.id} /> where each card awaits its own line-items query

for (const inv of invoices) { await db.query.organizations.findFirst({ where: { id: inv.organizationId } }) }

db.query.invoices.findMany({ with: { lineItems: true } })

db.select(...).from(invoices).innerJoin(lineItems, eq(lineItems.invoiceId, invoices.id))

Promise.all([listInvoices(), getCurrentOrg(), getNotifications()])

Check Reset

One query instead of N

Now the fix, and the point is that it’s an anticlimax. The cure is the API you already wrote last chapter, so there’s nothing new to learn here. When the data access is shaped right, N+1 never arises in the first place.

The cure is always the same move: collapse the N child queries into the one parent query. Two tools do it, and which you pick is decided by the shape you want back, not by performance.

The relational query API, the default for tree-shaped reads. When you want each invoice with its line items nested inside it, db.query.invoices.findMany({ with: { lineItems: true } }) emits one SQL statement that aggregates the children into a JSON array per parent. That’s one round-trip, and the result comes back already shaped as (Invoice & { lineItems: LineItem[] })[]: typed, nested, and ready to render. You learned the mechanics of with last chapter; here you’re just reaching for it as the cure.

A join with db.select, when the shape is flat or aggregates enter. When you don’t want a nested tree but a flat projection, such as columns from both tables in one row or a count/sum rolled up, you hand-write the innerJoin or leftJoin. If you still need the parent-with-children grouping, you fold the flat rows in app code. Either way, it’s one statement.

The decision is shape-driven: a nested tree calls for the relational API, while flat or aggregated calls for a join. Both are one round-trip, and both are fully typed. There is no version of the fix that is “loop, but smarter.”

Here’s the fix in place, on the running example. The first tab is the bug, and the next two are the two cures.

N+1 (Promise.all)

const invoiceList = await db.select().from(invoices);
const itemsByInvoice = await Promise.all(
  invoiceList.map((invoice) =>
    db.select().from(lineItems).where(eq(lineItems.invoiceId, invoice.id)),
  ),
);

21 statements for a 20-invoice page. One parent query plus one child query per row, fired in parallel: fast, still N+1.

Fixed (relational API)

const invoicesWithItems = await db.query.invoices.findMany({
  with: { lineItems: true },
});

1 statement. Each invoice comes back with its lineItems array nested inside. There’s no hand-written InvoiceWithItems type, because the nested shape is inferred straight from the query.

Fixed (join)

const rows = await db
  .select({
    invoiceId: invoices.id,
    total: invoices.amountDue,
    itemId: lineItems.id,
    description: lineItems.description,
  })
  .from(invoices)
  .innerJoin(lineItems, eq(lineItems.invoiceId, invoices.id));

1 statement, flat rows. This is the shape you reach for when you want a flat projection or an aggregate. Group the rows by invoice in app code if you need the nested shape.

Two things are won here, and both are worth naming.

First, the obvious one: round-trips drop from 21 to 1. The same data and the same page, with one trip across the wire instead of twenty-one. That gap doesn’t widen gently with data; it’s the difference between a page that stays fast at eight hundred invoices and one that falls over.

Second, the quieter one: the type comes for free. The relational result is typed (Invoice & { lineItems: LineItem[] })[] with no interface written by hand. You can pass it straight into a Server Component as a prop, and if you rename a column or change a with key, the type updates itself. That’s the inferred-types thread from last chapter paying off again: the query owns its own result type, so the fix doesn’t saddle you with a hand-maintained InvoiceWithItems to keep in sync.

Now try the fix yourself. The sandbox below has the N+1 already written as a loop. Rewrite it as a single query that returns each invoice with its line items.

Note

This sandbox runs the SQL builder, so db.query and its relations graph aren’t wired up here. Finish the equivalent join instead. The result it returns is flat, with each invoice’s columns repeating once per line item. That’s exactly the trade-off from the section above: the relational API would hand you the same data already folded into { ...invoice, lineItems: [...] }. The point of the drill is the one-statement shape, whichever builder you use.

This page fetches each invoice's line items in a separate query — a classic N+1. Rewrite it as a single query: one innerJoin that pairs every invoice with its line items. The select is started for you — finish the .from(...).innerJoin(...) so each line item comes back next to its invoice's id and total.

View schema & seed rows

Schema (Drizzle)

export const invoices = pgTable('invoices', {
  id: integer('id').primaryKey(),
  organizationId: integer('organization_id').notNull(),
  amountDue: numeric('amount_due').notNull(),
});

export const lineItems = pgTable('line_items', {
  id: integer('id').primaryKey(),
  invoiceId: integer('invoice_id').notNull().references(() => invoices.id),
  description: text('description').notNull(),
  amount: numeric('amount').notNull(),
});

Seed rows (SQL)

INSERT INTO invoices (id, organization_id, amount_due) VALUES
  (1, 1, '300.00'),
  (2, 1, '150.00'),
  (3, 1, '80.00');

INSERT INTO line_items (id, invoice_id, description, amount) VALUES
  (1, 1, 'Design retainer', '200.00'),
  (2, 1, 'Hosting',         '100.00'),
  (3, 2, 'Consulting',      '120.00'),
  (4, 2, 'Travel',          '30.00'),
  (5, 3, 'Support hours',   '80.00');

Run query Feedback Reset

Proving it with query logging

So far you’ve reasoned your way to N+1, picturing the round-trips and counting them in your head. But you should be able to catch it, not just suspect it. The fastest way to make N+1 reveal itself is to watch the SQL the database actually receives.

Drizzle gives you that for one line of config. Setting logger: true on the Drizzle client makes it print every SQL statement it emits to the console. Turn it on in dev, load the page, and the N+1 shows itself at once: a single invoices query, then a burst of near-identical line-items queries scrolling past back to back:

Query: select … from "invoices"
Query: select … from "line_items" where "invoice_id" = $1
Query: select … from "line_items" where "invoice_id" = $2
Query: select … from "line_items" where "invoice_id" = $3
Query: select … from "line_items" where "invoice_id" = $4
Query: select … from "line_items" where "invoice_id" = $5

That stack of identical statements, differing only by the bound $1, $2, $3, is the visual signature of N+1. Once you’ve seen it, you spot it across the room. After the fix, the same page logs exactly one statement.

One thing in that output trips people up: the logged SQL says line_items and invoice_id (snake_case), while your TypeScript says lineItems and invoiceId (camelCase). That’s not a bug. The Drizzle client is configured to map camelCase TS properties to snake_case columns automatically, so the SQL you read in the log is the database’s spelling of the same thing. Expect the gap: it’s the casing translation doing its job.

The metric to watch is statements per render, not statements per user. One render of the page should emit a bounded, predictable number of statements, and if that number grows with the number of rows on the page, you have an N+1. Hold onto “per render,” because the next section shows how a cache can quietly hide repeat statements within one render and make the count lie.

There’s one boundary worth drawing here, because the next lesson teaches a different diagnostic and it’s easy to reach for the wrong one. You might think I have a slow query problem, let me run EXPLAIN ANALYZE on it. For N+1, that’s the wrong tool, and understanding why sharpens what N+1 actually is.

EXPLAIN ANALYZE shows you the execution plan of one statement. But each individual statement in an N+1 batch has a perfectly fine plan: select … from line_items where invoice_id = $1 does a fast index scan on a single id and returns in a millisecond. Run EXPLAIN ANALYZE on it and the plan looks great, because it is great. The cost of N+1 isn’t in any one plan; it’s the round-trip latency and connection overhead summed across all N statements. That cost lives at the call site, in the code shape that fires the query once per row, not in the SQL of any single query.

Note

N+1 is invisible to plan inspection. EXPLAIN ANALYZE (the next lesson’s tool) diagnoses one slow query, while counting statements in the query log diagnoses many queries firing. Different bug, different tool: reach for the log when statements-per-render grows with the data.

Why caching, batching, and DataLoader aren’t the answer here

You know the fix now: reshape the data access into one query. But there are three tempting non-fixes that look like they solve N+1 and don’t. Knowing why each one is the wrong move is what makes your mental model precise, so it’s worth ruling them out one at a time.

A cache is not the fix. The instinct might be the queries are slow, so cache the results. A request-scoped cache (React’s cache, Next’s 'use cache') will hide the N+1 from a user who hits a warm cache, but it doesn’t fix anything. The database still ran all N queries the first time. The cache still expires, and the next cold request pays the full N+1 again. The structural shape is still wrong; you’ve just put a curtain in front of it. Worse, a cache can mask the bug in dev. If the same component renders twice and only the first render pays, your query log shows fewer statements than production will actually run, which is exactly why the metric is statements per render, not per user. A cache is for reusing the same query across components, not for collapsing N parameterized queries into one. Caching proper is a whole topic the course covers elsewhere; here it’s only ruled out as the N+1 fix.

DataLoader belongs to a different world, so name it but don’t reach for it. If you’ve come from GraphQL, you may know the DataLoader pattern: collect all the .load(id) calls fired within one tick and batch them into a single query. It’s the resolver world’s answer to N+1, and it earns its weight there, in GraphQL servers or deeply nested tRPC routers, where the call sites genuinely can’t see each other and there’s no single place to write one query. But in a Server Component and Server Action codebase with Drizzle, you do have that single place. The relational query API solves the same problem at the query layer, with full typing and zero extra machinery. Recognize DataLoader when you see it; you won’t need it here.

Don’t regress the fix. One last trap, and the easiest to miss, because it looks like optimization. Once a with query works, someone may notice it pulls child columns the page doesn’t use and “optimize” by dropping with and re-fetching the children by hand, quietly re-introducing the N+1 you just removed. That’s a regression, not a tuning. If a with tree over-fetches, the fix is to narrow the one query with a columns projection on the relation, not to split it back into N. Re-shape the single query; never go back to the loop.

Here is the one line to carry out the door: an N+1 across twenty dev rows stays invisible right up until production scales it to five thousand. Design for the load, not for the dev sample. The bug ships green, passes review, and surfaces months later as a slow page or a pool-exhaustion incident, unless a reviewer trained to ask “once, or once per row?” catches the shape before it merges. Becoming that reviewer is the point of this lesson.

External resources

Drizzle — Relational queries (v2)

orm.drizzle.team

The with traversal that collapses a tree read into one statement — the primary N+1 cure in this stack.

Drizzle — Query logging

orm.drizzle.team

The logger: true option that prints every emitted statement — the diagnostic that surfaces N+1 visually.

What is the N+1 problem?

planetscale.com

A framework-agnostic walkthrough of the same shape and fix, useful for seeing it outside Drizzle.

Promise.all is too much of a good thing

mattburke.dev

A real-world account of how Promise.all over DB queries starves the connection pool — the pool-contention trap this lesson warns about.