Context
You inherit a listing endpoint over a single table. It was written against a seed of a thousand rows and it was fine. Production has a million, and the page now takes seconds.
Nobody needs a new database here. What is missing is the habit of asking the database what it is actually doing, instead of guessing — and of proving the fix rather than declaring it.
Seed a table with a million rows, serve it, and make it fast. Then break your own fix on purpose and watch the plan change.
Requirements
What must be true — the how is yours.
-
R1
A filtered, sorted listing endpoint returns correct results over at least a million rows.
-
R2
The hot query uses an index, and you can show the plan that proves it rather than asserting it.
rehearsed by D1 · D2
-
R3
Paging deep into the result set does not get slower the further in you go.
rehearsed by D3
Technical guidance
- ▸Measure before you change anything; a fix with no before is a story.
- ▸EXPLAIN tells you the plan, EXPLAIN ANALYZE tells you what happened.
- ▸An index the planner refuses to use is the same as no index.
Definition of done
All of them, or it isn't shipped.
-
○
The plan falls back to a sequential scan and the latency jumps by an order you can state out loud.
— not recorded yet
D1
-
○
The index is ignored and the plan says so, even though the index still exists.
— not recorded yet
D2
-
○
OFFSET degrades with depth; your scheme returns the deep page in about the same time as the first.
— not recorded yet
D3
-
○
The seed script runs from a clean clone and the numbers reproduce.
— not recorded yet
nothing recorded yet — running the drills fills these in
spec v1.0 · drafted with AI, human-reviewed