Case Study · Performance · Rails + PostgreSQL

The N+1 that took down Black Friday

ShopFast's checkout page was fine all year — then Black Friday hit, and it timed out under load while the database pinned at 100% CPU. The cause wasn't traffic alone; it was one hidden N+1 query that turned every checkout into 50 database round-trips. Here's how they found it, fixed it, and made sure it never hides again.

A worked application of the Ruby/Rails, PostgreSQL & System Design playbooks

0The incident

14:00 on Black Friday, traffic 8× normal. Checkout latency climbs from 120ms to over 30 seconds; requests start timing out. The database CPU is at 100%, but no single query looks slow. Revenue is dropping by the minute. The team's instinct — "add more app servers" — does nothing, because the bottleneck is the stateful database, and it's drowning in a flood of tiny queries.

Incident timeline
  14:00  traffic 8× · checkout p95 120ms → 4s
  14:06  checkout p95 > 30s · timeouts begin · DB CPU 100%
  14:09  add app servers → NO effect (the DB is the bottleneck, not the app)
  14:14  APM shows checkout makes 51 queries/request (should be ~3)
  14:20  found: N+1 on line_items → product · ship eager-load fix
  14:26  checkout p95 back to 140ms · DB CPU 35% · recovered
  root cause: 1 hidden N+1 × 8× traffic = a self-inflicted database DDoS

💡 What an N+1 query is (and why it hides)

An N+1 is when loading a list runs 1 query for the list, then N more — one per item — instead of loading everything up front. Render an order with 50 line items and naïvely read each item's product, and you fire 1 + 50 = 51 queries for one page.

It hides because it's invisible at small scale: in dev with 2 line items it's 3 fast queries — imperceptible. In production on Black Friday with big carts and 8× traffic, that same code fires tens of thousands of tiny queries per second and buries the database. The bug didn't change; the data size and traffic did.

1Finding the N+1

The tell of an N+1 is the log: the same query repeated with different ids, back to back. APM (or Rails' log, or the bullet gem) makes it obvious once you look.

The Rails log — the same SELECT, 50 times
Order Load    SELECT * FROM orders WHERE id = 1
LineItem Load SELECT * FROM line_items WHERE order_id = 1
Product Load  SELECT * FROM products WHERE id = 11     -- (+1)
Product Load  SELECT * FROM products WHERE id = 12     -- (+1)
Product Load  SELECT * FROM products WHERE id = 13     -- (+1)
...  (47 more identical-shape queries) ...
Product Load  SELECT * FROM products WHERE id = 60     -- (+1)
==> 51 queries for ONE checkout render
The culprit — a query hiding inside a loop
// ❌ N+1: .product runs a SELECT for EACH line item
@order.line_items.each do |li|
  render_line(li.product.name, li.product.price_cents)   // 1 query per item
end

// ✅ eager-load the association once — 2 queries total, regardless of size
@order = Order.includes(line_items: :product).find(params[:id])
@order.line_items.each do |li|
  render_line(li.product.name, li.product.price_cents)   // no extra queries
end

2Reading the query plan & the shapes involved

Each individual query was fast — that's why nothing "looked slow." The problem was the count. Still, ShopFast checked with EXPLAIN that the per-product lookup used the primary-key index (it did), confirming the issue was volume, not a missing index.

EXPLAIN — each query is fine; there are just 50 of them
EXPLAIN ANALYZE SELECT * FROM products WHERE id = 42;
 Index Scan using products_pkey on products
   Index Cond: (id = 42)
   rows=1  actual time=0.03..0.03  (fast!)
-- verdict: the query is optimal. the BUG is running it 50× per request.
-- 8× traffic × 50 extra queries = tens of thousands of round-trips/sec.
Entity diagram — the shapes behind the N+1
erDiagram USER ||--o{ ORDER : places ORDER ||--|{ LINE_ITEM : contains LINE_ITEM }o--|| PRODUCT : references ORDER { bigint id bigint user_id } LINE_ITEM { bigint id bigint order_id bigint product_id int qty } PRODUCT { bigint id string name int price_cents }
Renders with Mermaid. Each LINE_ITEM points to a PRODUCT — read naïvely, that's the +N.
Sequence diagram — one checkout, 51 round-trips
sequenceDiagram autonumber participant U as User participant App as Rails app participant DB as PostgreSQL U->>App: GET /checkout (cart with 50 items) App->>DB: SELECT order + line_items DB-->>App: 1 order, 50 line_items loop for each of 50 line_items App->>DB: SELECT product WHERE id = X DB-->>App: 1 product end Note over App,DB: 1 + 50 = 51 queries per page — times 8x traffic = meltdown App-->>U: page (slow) or timeout under load
Renders with Mermaid. The eager-load fix collapses this to 2 queries, flat.

3The fix — and the capacity plan

The immediate fix was one line (includes). But the incident was also a capacity-planning failure: the load test never used realistic cart sizes, so the N+1 never showed. The full fix is code plus process.

AImmediate — eager-load the association2 queries, flat▶

Goal: collapse 1+N into a constant number of queries.

  1. Add .includes (or preload/eager_load) for the association read in the loop — Order.includes(line_items: :product). Now it's 2 queries no matter how many line items.
  2. Ship it — this alone took ShopFast's checkout from 30s+ back to ~140ms and DB CPU from 100% to 35%.
  3. Sweep for siblings — N+1s travel in packs. Check other list views (order history, cart, admin) for the same pattern.
BStructural — counts, caching & indexes▶

Goal: remove other per-row database work on the hot path.

  1. counter_cache for counts — store line_items_count on the order instead of a COUNT(*) per render.
  2. Cache the rendered cart/line fragments (Redis) so repeat renders skip the DB entirely.
  3. Verify indexes on the foreign keys used in the loads (line_items.order_id, line_items.product_id) so the eager-load's IN (...) is fast.
CProcess — so it can't hide again▶

Goal: catch N+1s in CI and load-test with realistic data.

  1. Fail the build on N+1s — the bullet gem (or query-count assertions in tests) flags "this action ran N queries" before it merges.
  2. Load-test with production-shaped data — big carts, 8× peak traffic. The N+1 only appears with realistic sizes, so tiny fixtures hide it.
  3. Alert on query-per-request in APM — a checkout that suddenly makes 50 queries should page someone, not wait for Black Friday.
📈
The capacity lessonShopFast's load test passed because it used 2-item carts. Real Black-Friday carts had 30–50 items, and the N+1 scaled with cart size and traffic at once. Capacity planning isn't just "how many requests" — it's "how many requests, with how much data each," tested against production-shaped inputs.

4Scorecard

Eight checks that turn "we got lucky" into "it can't recur."

☑
List views that read associations use eager loading (includes/preload), not per-row queries.
☑
An N+1 detector (bullet / query-count asserts) runs in CI and fails the build.
☑
Hot-path counts use counter_cache, not COUNT(*) per render.
☑
Foreign keys used in loads and filters are indexed; confirmed with EXPLAIN.
☑
Load tests use production-shaped data (realistic cart/list sizes), not tiny fixtures.
☑
APM tracks queries-per-request; a spike alarms before peak season does.
☑
Hot renders are cacheable (fragment cache) so repeats skip the DB.
☑
The team knows the reflex "add app servers" doesn't help a database bottleneck.
The one-line takeaway: an N+1 is invisible in dev and lethal at scale — it multiplies with both data size and traffic, so it detonates exactly when you can least afford it. Eager-load associations, detect query counts in CI, and load-test with real-world data. And remember: when the database is the bottleneck, more app servers just send the flood faster.