Spotting and fixing N+1

Picture the most ordinary screen in the invoicing app: a list of invoices, each with a summary of its line items. You wrote it last chapter, 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 fails outright when the connection pool runs dry.

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, the most common performance bug in this stack. You already have the cure: last chapter you wrote both tools that fix it, the relational query API with with and joins with db.select. So this lesson trains a habit, not a tool: scan a chunk of data-access code and ask whether the query runs once, or once per row.

The shape of the bug

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, returning N invoices. The loop then runs once per invoice, each iteration firing its own select for that invoice’s line items, which is N more. One parent query plus N child queries is N+1: twenty-one queries for a twenty-invoice page, eight hundred and one for eight hundred.

That hurts because every statement is a separate round-trip to the database, and the cost is the trip across the network, not the work Postgres does. Even at two milliseconds each, eight hundred line-items queries run one after another add up to well over a second of pure waiting, most of it with the database sitting idle.

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, 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

The loop starts: a line-items query for invoice #1, a fresh trip across the wire, with the parent query now 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

And again for invoice #2, same query shape with a different bound id ($2). Every invoice will 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

The loop fires the same query for the remaining invoices, 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, and that's the N+1.

Prev

Next

The rest of this lesson is what to do once you recognize that shape.

Why Promise.all doesn’t fix N+1

The sequential loop ran its twenty queries in single file, each awaiting the last. The obvious fix is to fire them in parallel with Promise.all. The page gets faster, but it’s the same bug at a different speed.

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 return. This is the slowest 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 same N+1 work, now all at once instead of in a line. Less waiting, the same number of queries.

Promise.all parallelizes JavaScript awaits, not database queries, and those are different layers. Underneath, the database still parses, plans, and executes N separate statements with no idea they were launched together. The connection pool still serves N round-trips, but now concurrently, so this one page grabs N connections at once. A pool only has so many: fire enough of these in parallel on a busy server and you starve it, leaving other requests waiting for a connection tied up running your eight-hundredth line-items query. Parallelizing trades a slow page for a possible pool incident, a failure we return to later.

So Promise.all beats the sequential loop, but it’s a win inside a broken shape: the floor on page speed is now the slowest query in the batch plus pool contention.

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 one, the one that actually costs, identical.

One guardrail, so you don’t over-correct into avoiding Promise.all. The problem isn’t the function, it’s 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 together is correct and fast, because they’re independent reads. The bug is Promise.all over the same query shape parameterized by an id, like lineItems where invoiceId = $1, $2, $3 — an N+1 fired all at once. So before adding one, ask: different queries, or the same query repeated once per row?

The four shapes N+1 hides in

N+1 is a shape, not a syntax, and it wears disguises. Here are the four you’ll meet.

1. A loop with await inside. The sequential N+1. It performs worst and is the easiest to spot: the await inside the for fires a query on every iteration.

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

2. Promise.all over a parameterized map. The parallel N+1, faster than the loop and the hardest to spot, because it reads like good concurrent code. The tell isn’t the Promise.all; it’s the map issuing 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. No loop in sight. The list renders one <InvoiceCard> per invoice, and each card is a Server Component that awaits its own line-items query. The N+1 is spread across the tree, so no single file reveals it.

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

4. A findMany followed by a findFirst per row. The relational-API version: each invoice runs its own findFirst to look up the organization it belongs to. The query API that prevents N+1 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 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:

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?

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

The fix is the API you wrote last chapter: shape the data access right and N+1 never arises. The move is always the same, collapse the N child queries into the one parent query, and you pick the tool by the shape you want back, not by performance.

The relational query API, for tree-shaped reads. To get each invoice with its line items nested inside, db.query.invoices.findMany({ with: { lineItems: true } }) emits one SQL statement that aggregates the children into a JSON array per parent, typed as (Invoice & { lineItems: LineItem[] })[]: nested and ready to render.

A join with db.select, when the shape is flat or aggregated. For a flat projection instead, such as columns from both tables in one row or a count/sum, hand-write the innerJoin or leftJoin, then fold the rows into parent-with-children groups in app code if you need them. Either way it’s one statement.

Here’s the fix on the running example. The first tab is the bug; the next two are the 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, and the nested shape is inferred from the query, so there’s no hand-written InvoiceWithItems type.

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. Reach for this when you want a flat projection or an aggregate. Group the rows by invoice in app code if you need the nested shape.

The fix wins twice. Round-trips drop from 21 to 1: the same data and page in one trip across the wire instead of twenty-one, the difference between a page that stays fast at eight hundred invoices and one that falls over.

And the type comes for free. Pass the relational result straight into a Server Component as a prop; rename a column or change a with key and the type updates itself, with no InvoiceWithItems interface to keep in sync.

Now try it. The sandbox below has the N+1 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. Finish the equivalent join instead. It returns flat rows, with each invoice’s columns repeating once per line item, exactly the trade-off from 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 N+1 with query logging

You’ve been counting round-trips in your head. To catch N+1 for real, watch the SQL the database actually receives.

Setting logger: true on the Drizzle client prints every statement it emits to the console. Turn it on in dev, load the page, and the N+1 shows itself: one 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. After the fix, the same page logs one statement.

The logged SQL says line_items and invoice_id (snake_case) while your TypeScript says lineItems and invoiceId (camelCase): Drizzle maps the property names to columns automatically, so the log shows the database’s spelling.

The metric to watch is statements per render, not per user. One render should emit a bounded, predictable number of statements; if that number grows with the rows on the page, you have an N+1.

You might reach for EXPLAIN ANALYZE, but it’s the wrong tool here: it shows the plan of one statement, and each statement in an N+1 batch has a fine plan, a fast index scan returning in a millisecond. The cost isn’t in any plan; it’s the round-trip latency summed across all N statements, and it lives at the call site, in the code shape that fires the query once per row.

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.

Three tempting non-fixes for N+1

The fix is to reshape the access into one query. Three other moves look like fixes and aren’t; ruling them out sharpens the model.

A cache is not the fix. A request-scoped cache (React’s cache, Next’s 'use cache') hides the N+1 from a user who hits a warm cache, but the database still ran all N queries to fill it, and the next cold request pays again. A cache reuses the same query across components; it can’t collapse N parameterized queries into one.

DataLoader belongs to a different world. From GraphQL you may know the DataLoader pattern: batch the .load(id) calls fired within one tick into a single query. It fits GraphQL servers and deeply nested tRPC routers, where call sites can’t see each other and there’s no single place to write one query. Server Components and Server Actions with Drizzle have that single place: the relational query API solves it at the query layer, fully typed, with no extra machinery.

Don’t regress the fix. Once a with query works, someone may notice it pulls child columns the page doesn’t use and “optimize” by dropping with to fetch the children by hand, quietly restoring the N+1. If a with tree over-fetches, narrow it with a columns projection on the relation; never split it back into N.

An N+1 across twenty dev rows stays invisible until production scales it to five thousand. Design for the load, not the dev sample.

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.