The N+1 Query Storm

Medium

· Database

Incident

SEV-3

Time to fix: 20 min

Failing checks on the shipped build

  • one page load is one query

    batch it!: expected 1 but got 4

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Inspired by

Every ORM

Inspired by the outage pattern behind a thousand postmortems: a page renders 50 posts, and the ORM quietly fires 51 queries. Works fine in staging with 5 rows; melts the primary at scale.

The mock data layer counts queries. Load every author in one batched query.

Code map

var db = require("./db");

// Resolves the author of every post.
exports.loadAuthors = function (posts) {
  var out = [];
  for (var i = 0; i < posts.length; i++) {
    var user = db.findUserById(posts[i].authorId);
    out.push({ postId: posts[i].id, authorName: user.name });
  }
  return out;
};

Sign in to edit the file, run the tests and submit a fix

A free account — no card, no trial clock. You come straight back here.

The N+1 Query Storm — JavaScript Bug Hunt

Inspired by the outage pattern behind a thousand postmortems: a page renders 50 posts, and the ORM quietly fires 51 queries.

  • Language: JavaScript
  • Layer: Database
  • Difficulty: Medium
  • Concepts: ORM, Performance
  • Modelled on: Every ORM
  • Visible tests: authors resolve correctly and in order; one page load is one query
  • Reward: 50 XP for a complete fix

Incident

  • Severity: SEV-3
  • Time to fix: 20 minutes, the target for a medium incident

Failing checks on the shipped build

  • one page load is one query — batch it!: expected 1 but got 4

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Briefing

Inspired by the outage pattern behind a thousand postmortems: a page renders 50 posts, and the ORM quietly fires 51 queries. Works fine in staging with 5 rows; melts the primary at scale.

The mock data layer counts queries. Load every author in one batched query.

Bug report

BUG-N+1 · Priority: High · Reported by: DBA (again)

loadAuthors(posts) must resolve each post's author using AT MOST one query (db.findUsersByIds). Returned shape: [{ postId, authorName }] in post order.

Observed: one db.findUserById call per post — 51 queries per page render.

Logs

[db] SELECT * FROM users WHERE id = ? (x51 in 40ms)
[alert] primary saturated during feed render

The code as shipped

src/orm/authors.js (editable)

var db = require("./db");

// Resolves the author of every post.
exports.loadAuthors = function (posts) {
  var out = [];
  for (var i = 0; i < posts.length; i++) {
    var user = db.findUserById(posts[i].authorId);
    out.push({ postId: posts[i].id, authorName: user.name });
  }
  return out;
};

Read-only context: src/orm/db.js.

Open the hunt to start the incident clock, edit the files, run the visible tests and submit against the hidden ones, then write the root cause down. More JavaScript bug hunts.