The N+1 That Shipped

orders-api is the backend behind the merchant dashboard. Its busiest endpoint is GET /api/v1/orders?date=…, which merchants open every morning to see yesterday's orders. The page holds 250 rows.

const orders = await db.orders.findMany({
  where: { merchantId: req.merchant.id, placedOn: req.query.date },
  orderBy: { placedAt: "desc" },
  take: 250,
});

return orders.map(toOrderResponse);

A round trip from the application to PostgreSQL costs about 0.35ms.

Last sprint design asked for two additions per row: the customer's loyalty tier as a badge, and the product names as a subtitle. The change was reviewed by two people and shipped on Wednesday.

> The code is in TypeScript and the ORM is deliberately generic. This bug > belongs to no language and no library.

Since Wednesday's deploy, the p95 of GET /api/v1/orders sits at 4.3 seconds, against 210ms before it.

Merchants with a handful of orders a day notice nothing. Every complaint comes from high-volume merchants. Database CPU is at 22%, the same as last week, and no slow-query warning fired — the threshold is 100ms and nothing crosses it.

The change:

 export function toOrderResponse(order: Order) {
   return {
     id: order.id,
     reference: order.reference,
     totalCents: order.totalCents,
+    customerTier: order.customer.tier,
+    itemNames: order.items.map((item) => item.product.name),
   };
 }

order.customer and order.items are associations. The ORM resolves them at the moment they are read, if they are not already loaded.

Query log, one request from a high-volume merchant:

  query  3.1ms  select * from orders where merchant_id = $1 and placed_on = $2 limit $3
  query  0.4ms  select * from customers where id = $1 limit 1
  query  0.5ms  select * from order_items where order_id = $1
  query  0.3ms  select * from products where id = $1 limit 1
  query  0.3ms  select * from products where id = $1 limit 1
  query  0.4ms  select * from customers where id = $1 limit 1
  ... 11,037 more
  200 OK in 4312ms (db 4021.7ms)

for that merchant:

1. 1Two lines produced 11,043 queries. Do the arithmetic on where each one comes from, using the numbers under "the shape of the page".
2. 2Each of those queries takes about 0.3ms and the database is at 22% CPU. So where do the four seconds actually come from?
3. 3How would you fix it, and what would you add so the next change of this kind fails in CI instead of in production?