They added an index and the query got slower.
Then they doubled the instance size and bought back 400 milliseconds.
So they put Redis in front of it. The hit rate came back at 4 percent. Then they added a read replica, and it fell 45 seconds behind.
Four fixes. All reasonable. All wrong.
None of it was bad luck. Every one of those numbers was predictable from what the engine was doing underneath the query.
The index lost because the planner's row estimate was off — and an index scan is two hops, find the entry, then go fetch the row. Past enough rows, reading the pages straight through just wins.
The 4 percent wasn't a Redis problem. It was the diagnosis. A cache is a second buffer pool. If the working set doesn't fit in the first one, it isn't going to fit in the second one either.
And the replica lag came from the same place. Replication is the write-ahead log, shipped. An UPDATE in Postgres never overwrites a row — it writes a new version. More versions, more WAL, more lag.
Same layer, three times. Storage.
Most of us learned databases top-down: write the query, add an index when it's slow, add a replica when the index stops helping. That's backwards. Indexes make no sense without pages. Joins make no sense without indexes. And nobody explains the WAL until the replica is already 45 seconds behind.
So I built the bottom-up version I wish I'd had.
2 hours 22 minutes. Seven layers — storage, indexes, pagination, execution, transactions, read scaling, schema. One query, one 200 million row table, all the way up. Seven checkpoint quizzes with real countdowns, so you answer before you hear the answer.
If you only watch part of it, watch the bottom four.
Top comments (0)