backendgym backendgym · projects Sign in with GitHubSign in

projects /02-the-slow-tuesday

# The reports that slowed Tuesday

A report filter shipped on Monday, and on Tuesday morning finance reports started timing out. Find out why, fix it, and show the p99 coming back down.

Warmup ~3h PythonPostgreSQL

practises sql, indexes, query-planning, postgresql, percentiles, load-testing

prepare with SQL · Querying, SQL · Designing and writing, SQL · Viewing

Sign in with GitHub
the-slow-tuesday / incident.md v1.0
Symptoms

09:14, Tuesday

The pager goes off for Northstar Ledger's reporting API. Customers download their daily finance reports from GET /reports, and its p99 has gone from 34ms to 812ms. The error rate hasn't moved from 0.2%, but 23% of report downloads are timing out in the web app, and support has 146 complaints before lunch.

Late on Monday someone added a report filter to the endpoint. This morning's deploy got the blame first. The database is taking connections and CPU sits at 41%.

You have 45 minutes to reproduce it, look at what the database is doing, make the smallest safe fix, and check the number customers feel. Don't drop or rebuild the reports table.

What's running

The starter has the API and a PostgreSQL table seeded with 5,000,000 reports, a stand-in for the much bigger production table. make load sends Tuesday's report traffic at it. How you'd roll an index out safely in production, and how you'd watch it, go in the postmortem.

starter Tserewara/starter-the-slow-tuesday · you generate your copy when you start

Requirements

What has to be true when you're done. How you get there is up to you.

  1. R1
    The filtered report query returns the same reports as before, and under `make load` its p99 falls back to what the page had before Tuesday's deploy, for a reason you can show in the plan.
    ask the stakeholder

    start the project to ask

Runbook

Reproduce, diagnose, fix and verify, in that order.

  • D1 reproduce , not recorded yet

    Reproduce Tuesday: start the stack with make up, then run make load.

    expectThe summary line shows successful=40 for 40 requests and a p99_ms above 250.

  • D2 diagnose , not recorded yet

    Run make explain and look at the node that reads the reports table.

    expectEXPLAIN ANALYZE shows a sequential scan for the filtered query, with a Rows Removed by Filter count.

  • D3 diagnose , not recorded yet

    Check the deploy: read this morning's diff in deploys/ (it matches the label GET /version returns), restart only the API (COMPOSE_PROJECT_NAME=slow_tuesday docker compose restart api), and run make load again.

    expectThe deploy's diff only changes a response header, and after the restart the load is still slow and make explain shows the same sequential scan.

  • D4 fix R1 , not recorded yet

    Make the smallest safe change the plan points to (a schema change goes in with COMPOSE_PROJECT_NAME=slow_tuesday docker compose exec db psql -U reports -d reports), then run make explain again.

    expectThe plan uses the new index, directly or through a bitmap scan, and the query returns the same reports. The table was not rebuilt.

  • D5 verify R1 , not recorded yet

    Run make load after the fix and compare its p99 with D1's.

    expectAll 40 requests still succeed and p99_ms has fallen. Say which plan node changed and why that explains the drop.

Technical guidance

  • ▸Go in order: reproduce what the customer saw, read the query plan, then change one thing.
  • ▸Change one thing at a time, and keep the query plan in front of you.
  • ▸Write down the p99 before and after, and which plan node changed.

Done when

Every drill above passes, and:

  • The fix can roll out without deleting or rebuilding the reports table.
  • The postmortem names the check that was missing, the one that would have caught this before Tuesday.
Solutions

No solutions published yet. Ship the project and yours will be the first.