N+1 queries: batching, loaders and paging

The orders page shows 50 orders with each customer's name. The obvious code:

const { rows: orders } = await db.query(
  "SELECT id, customer_id, total FROM orders ORDER BY id DESC LIMIT 50",
);

for (const order of orders) {
  const { rows } = await db.query("SELECT name FROM customers WHERE id = $1", [order.customer_id]);
  order.customerName = rows[0]?.name;
}

One query for the list, then one per order: N+1 queries — 51 here. Each one is fast (an index lookup, well under a millisecond inside the database), so nothing looks wrong in the query log. The cost is the round trip: every await db.query sends a message to the database server and waits for the answer. At 1 ms per round trip that's 51 ms; with the database in another region at 20 ms, it's a full second — and it grows with the page size, not with the data's complexity.

The tell: a query inside a loop over the results of another query. Fix it by asking for everything at once.

A join does it in one round trip:

SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id DESC
LIMIT 50;

Or two queries — the list, then every customer it mentions in a single batch:

const ids = [...new Set(orders.map((o) => o.customer_id))];
const { rows: customers } = await db.query(
  "SELECT id, name FROM customers WHERE id = ANY($1)",
  [ids],
);

51 round trips became 1 or 2. The next example shows the part of the batch that's easy to get wrong: putting the answers back on the right rows.

(If customers can be deleted, the join needs to be a LEFT JOIN, or an order whose customer is gone disappears from the page — the keys-and-joins unit's "inner join drops the unmatched".)

A raw db.query inside a for loop is easy to spot in review. Most N+1s don't look like that.

1. An ORM relation loaded inside a loop

The Prisma ORM 7 docs use this example:

const users = await prisma.user.findMany({});
users.forEach(async (usr) => {
  const posts = await prisma.post.findMany({ where: { authorId: usr.id } });  // one query per user
});

and its fix — ask for the relation up front, which Prisma runs as two SQL queries (users, then all their posts):

const usersWithPosts = await prisma.user.findMany({ include: { posts: true } });

ORMs with lazy relations are worse, because the query hides behind a property: order.customer.name in a template looks like a field access and runs a SELECT the first time it's touched, once per order.

2. A helper that hides a query

const rows = await Promise.all(orders.map(async (o) => ({
  ...o,
  customerName: await getCustomerName(o.customerId),   // looks pure; runs a SELECT
})));

Promise.all makes the 50 queries concurrent instead of sequential, which hides the latency on a quiet laptop. It's still 50 queries, 50 round trips and 50 connections' worth of work for the pool — and the pool caps how many run at once, so under load they queue anyway. Concurrency is not batching.

3. Per-item loaders: resolvers and components

GraphQL resolvers, and any architecture where each item fetches its own data, resolve fields one item at a time: Order.customer runs once per order. The code is correct and independent — and it issues N queries. The standard fix keeps the per-item call sites and batches underneath them: a loader collects every load(id) made in the same tick and sends one batch query (the next example builds one). Prisma does this for you in one case: its docs say it automatically batches findUnique() calls made in the same tick — calls that differ only in the key they look up.

Finding them

• Count queries per request. Turn on your ORM's query logging, or wrap your database client in a counter, and print the count with each request in development. A list page whose query count grows with the page size has an N+1.
• Look for repetition in the log: the same statement text, over and over, with only the parameter changing.
• Test the shape, not the speed. Locally, with the database on the same machine, 50 extra queries cost a few milliseconds; in production, across a network, they cost a round trip each. An assertion like "this endpoint makes at most 3 queries" catches the regression where a timing test can't.

Batching fixes "too many queries". Paging fixes "one query that returns too much" — and has two traps of its own.

OFFSET gets slower and drifts

SELECT id, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 10;          -- page 2

• Cost grows with depth. The PostgreSQL manual: rows skipped by OFFSET still have to be computed inside the server. Page 1,000 reads and throws away 9,990 rows to return 10.
• Pages drift under writes. Page 1 returned posts 30…21. A new post arrives before the user clicks "next", everything shifts down one place, and OFFSET 10 returns 21…12 — post 21 shown twice (a delete shifts the other way and one is silently skipped).

Keyset pagination: continue from the last row you saw

SELECT id, created_at FROM posts
WHERE (created_at, id) < ($1, $2)          -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 10;

Same scenario: page 2 starts at 20, whatever was inserted above. With an index on (created_at, id) every page is an index range scan of 10 entries, page 1 or page 1,000. On a 200,000-row table in PostgreSQL 18, OFFSET 100000 LIMIT 10 walked 100,010 index entries (928 pages) to return 10 rows; a keyset page at a similar depth walked 10 (6 pages).

• The row comparison (a, b) < (x, y) compares left to right, stopping at the first unequal pair — exactly "earlier, or same time with a smaller id".
• The ORDER BY must be unique, hence the id tiebreaker. Without it, rows with equal created_at can straddle a page boundary and be skipped or repeated; the manual warns that without a unique order LIMIT returns an unpredictable subset.
• The key columns should be NOT NULL. A NULL that the row comparison reaches makes it unknown, so such rows drop out of later pages — and a NULL in the cursor ends paging altogether.
• You give up "jump to page 37". For feeds, exports and batch jobs that's no loss.

The unpaged read that stops at 1,000 rows — without an error

PostgREST, the API layer under Supabase, has a db-max-rows setting: a hard limit on rows returned per request (unlimited unless configured). Supabase's docs say Supabase projects return a maximum of 1,000 rows by default. A response that hits the limit is not an error — it's just shorter than you think:

const { data: members } = await supabase.from("members").select("id");        // ≤ 1,000 rows
const { data: payments } = await supabase.from("payments").select("member_id"); // ≤ 1,000 rows
const paid = new Set(payments.map((p) => p.member_id));
const unpaid = members.filter((m) => !paid.has(m.id));

With 1,400 payment rows, 400 never arrive, and the members they belong to are reported as unpaid — a false alarm, built from a truncated read, with every call "succeeding". The tell is a count that sits exactly on a round number (1,000) no matter how much data you add.

Fixes, best first:

1. Do set logic in SQL, not in JavaScript over two downloads: an anti-join (WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.member_id = m.id)) returns only the answer — a view or a SQL function exposes it through the API.
2. Page until a short page, with a page size no bigger than the max-rows cap: .range(from, to), or keyset with .order('id').gt('id', last).limit(1000), repeating until a page comes back with fewer rows than you asked for. Ask for 5,000 under a 1,000 cap and the very first page is "short" — the loop stops, and you've rebuilt the bug.
3. Check for truncation. select('id', { count: 'exact' }) also returns the total; if data.length < count, you didn't get everything.