The N+1 Query: How Your ORM Turns 1 Request Into 201

RedBite’s admin dashboard has an order list page: fetch the latest 200 orders, show each one with its customer’s name. In dev, with a handful of seed orders, it loads instantly. In production, with real volume, the same page takes 4 seconds and shows up in the slow-query dashboard every single time it’s loaded. Nobody changed the code between dev and prod — the data just got big enough to expose what was always there.

The code that looks completely reasonable

const orders = await Order.findAll({ limit: 200, order: [['createdAt', 'DESC']] });

const rows = [];
for (const order of orders) {
  const customer = await order.getCustomer(); // ORM lazy-load
  rows.push({ id: order.id, total: order.total, customerName: customer.name });
}

Nothing here looks wrong reading it top to bottom. order.getCustomer() is a normal, idiomatic ORM call — exactly how the docs show you to access a related record. The problem is invisible at the code level and only shows up in the query log:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 200;
SELECT * FROM customers WHERE id = 4821;
SELECT * FROM customers WHERE id = 7733;
SELECT * FROM customers WHERE id = 1092;
... (197 more, one per order)

One query to fetch 200 orders, then 200 more queries — one per order — to fetch each one’s customer. 201 round trips to Postgres for a page that should need one or two. This is the N+1 query problem: 1 query for the list, N queries for each item’s related data, where N is however many rows happened to come back. At 5 rows in dev, N+1 is 6 queries — not even noticeable. At 200 rows in production, it’s 201 — each one a full network round trip to the database, and that’s the entire 4 seconds.

This is an ORM lazy-loading trap, specifically: order.getCustomer() is lazy by design — it fetches the related row only when you actually call it, rather than fetching every order’s customer up front whether you need it or not. Lazy loading is the right default for a single record view. Inside a loop over 200 records, the same default fires 200 times.

Fix 1: eager loading — tell the ORM up front what you’ll need

Most ORMs let you declare the related data you want at query time, so it comes back in one join instead of N follow-up queries:

const orders = await Order.findAll({
  limit: 200,
  order: [['createdAt', 'DESC']],
  include: [{ model: Customer, as: 'customer' }], // eager load
});

const rows = orders.map((order) => ({
  id: order.id,
  total: order.total,
  customerName: order.customer.name,
}));

This generates one query with a JOIN:

SELECT orders.*, customers.*
FROM orders
LEFT JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.created_at DESC
LIMIT 200;

200 orders, 1 query, every customer already attached. The for loop’s await order.getCustomer() is gone entirely — order.customer is already populated from the join, no further round trip needed. This is the right fix whenever the related data is a single row per parent (order -> customer, one each) and you already know you need it for every row on the page.

Fix 2: batching — when a join would blow up the result set

Eager loading via JOIN works cleanly for one-to-one or one-to-few relations. It gets worse, not better, when the related data is one-to-many — say, showing each order with all of its line items. A JOIN there multiplies rows: 200 orders with an average of 4 items each comes back as 800 duplicated-order rows that the application then has to regroup. For that shape, batch the related lookups into a single WHERE ... IN instead of a join:

const orders = await Order.findAll({ limit: 200, order: [['createdAt', 'DESC']] });

const orderIds = orders.map((o) => o.id);
const items = await OrderItem.findAll({
  where: { orderId: { [Op.in]: orderIds } },
});

const itemsByOrder = new Map();
for (const item of items) {
  if (!itemsByOrder.has(item.orderId)) itemsByOrder.set(item.orderId, []);
  itemsByOrder.get(item.orderId).push(item);
}

const rows = orders.map((order) => ({
  id: order.id,
  items: itemsByOrder.get(order.id) ?? [],
}));

Still 2 queries total — 1 for orders, 1 for all their items via a single WHERE order_id IN (...) — regardless of whether there are 200 orders or 2,000. The grouping happens in application memory afterward, which is cheap compared to a database round trip. This is the general shape behind most “batch loader” / DataLoader-style libraries: collect the IDs you need across a whole page, fetch them in one query, then distribute the results back to each parent.

How to actually catch this before production

The reason RedBite’s dev environment never caught this: 5 seed orders means N+1 is 6 queries, invisible in a local query log nobody’s watching. The fix isn’t “write better code from memory” — it’s making N+1 visible automatically, before it ships:

• Turn on query logging in a staging environment seeded with production-scale data (hundreds, not single digits), and watch for any endpoint whose query count scales with its result count instead of staying flat.
• Most ORMs have a strict mode that throws on lazy-load access instead of silently firing a query (Sequelize’s rejectOnEmpty is adjacent; some ORMs, e.g. Rails’ Bullet gem or Django’s nplusone package, flag the pattern directly) — turn it on in dev and CI, not just production.
• A quick manual check that catches most cases: if a loop calls an ORM method per iteration, ask “could this be one query outside the loop instead?” — N+1 is almost always a query sitting inside a loop that didn’t need to be there.

The underlying rule for RedBite going forward: any endpoint returning a list should fetch its related data in a fixed number of queries, independent of how many items are in the list. If adding more rows to the result set adds more queries too, that’s the N+1 shape, whether or not it’s shown up in a slow-query alert yet.

Found an N+1 your ORM’s eager-loading syntax couldn’t express cleanly? Drop it in #scaling on Discord.