backendgym backendgym — projects Sign in with GitHubSign in

projects /02-the-slow-tuesday

# The reports that slowed Tuesday

A new report filter turned Tuesday's p99 into a customer wait. Find the evidence, fix the query path, and prove the number comes back.

Warmup ~3h PythonPostgreSQL
Sign in with GitHub
the-slow-tuesday / incident.md v1.0
Symptoms

The on-call page

At 09:14 on Tuesday, Northstar Ledger's reporting API started paging the on-call. Customers use GET /reports to download their daily finance reports, and p99 rose from 34ms to 812ms. Error rate stayed at 0.2%, but 23% of report downloads timed out in the web app and the support queue received 146 complaints before lunch.

A report filter was added to the endpoint late Monday. The morning deploy was the first thing people blamed, although its release notes mention only response headers. The database is accepting connections, CPU is 41%, and the symptoms return after an API restart.

You have 45 minutes to reproduce the page, use the available query evidence, make the smallest safe fix, and verify the customer-facing number. Do not drop or rebuild the reports table.

Lab boundary

The API and a PostgreSQL table with 600,000 seeded reports are provided. The load generator simulates Tuesday's report traffic, and the container-sized table stands in for the larger production table. Index rollout safety and monitoring for a real migration belong in the write-up.

Requirements

What must be true — the how is yours.

  1. R1 The filtered report query returns the same report rows while keeping measured p99 at or below 250ms in the supplied load shape. rehearsed by D4 · D5

Technical guidance

  • ▸Follow the evidence in order: reproduce the customer symptom, inspect the plan, then change one thing.
  • ▸A restart is a useful experiment, not a fix. Keep the query plan in view.
  • ▸Record the before and after p99 and the plan node that changed.

Definition of done

All of them, or it isn't shipped.

  • The load summary shows the filtered report path with a p99 above the 250ms target and a successful response for each request. — not recorded yet D1
  • EXPLAIN ANALYZE shows a sequential scan for the filtered query and reports the rows removed by its filter. — not recorded yet D2
  • The latest commit changes response metadata rather than the query, and the slowdown remains after restart with the same sequential-scan plan. — not recorded yet D3
  • The plan uses the new index or an index-backed bitmap path, and the query still returns the expected filtered reports without a table rebuild. — not recorded yet D4
  • filtered report p99 under 250 ms at the same load — not recorded yet D5
  • The fix is safe to roll out without deleting or rebuilding the reports table. — not recorded yet
  • The postmortem names the missing guard that would have caught the regression before Tuesday. — not recorded yet

nothing recorded yet — running the drills fills these in

spec v1.0 · drafted with AI, human-reviewed

Solutions

No solutions published yet. Complete the project and yours opens the thread.