I have watched the same query go from instant to unusable without a single line of code changing. The data grew, the planner changed its mind, and nobody noticed until the dashboard started timing out. This lesson follows one read query through a database, first with an index and then without one, so you can see what an index is actually paying for, and what it costs you on every write.
Difficulty: Intermediate · Know this first: Lesson 09, Lesson 10
Every hop charges rent in milliseconds. The bar below is a worked example for this lesson's scenario — not a measurement from a live system — but the shape is honest: in the normal case the whole trip costs less than 40 milliseconds and no single hop dominates, while in the bad scenarios one or two hops eat almost the whole budget. Step through the lesson or switch the scenario and watch the bar change. The lesson is not that indexes are fast. It is that a missing index, or an index built in the wrong order, makes one hop pay for the whole mistake.
The simulator models the same support dashboard query from the steps. All timings are a teaching model, not live measurements. Drag a slider and the step timings above, the budget bar, and the trouble highlights update with it.
More rows barely move an indexed lookup — a B-tree adds one comparison per doubling. A full scan grows in direct proportion, because it reads every row.
Every index is a second copy that every insert must update. At zero, the lookup and composite hops become full scans.
Past 5,000 returned rows, fetching one by one costs more than reading the whole table — and the planner flips to a full scan.
Three questions. No score is saved anywhere — this is just you against the material.
I have covered the happy path and the two classic mistakes. Here is what the steps leave out, and what production taught me the hard way.
An index you build wrong can lock the table while you build it. A plain CREATE INDEX on a big table takes a write lock while it sorts. On a table the checkout writes to, that lock is an outage with a polite name. I build indexes concurrently now, and I run them in a low-traffic window even then. The build takes longer. The site stays up. That trade is always worth it.
Indexes rot. An index is not a thing you create once and forget. Rows get updated and deleted, pages get half empty, and the index gets bigger than the data it points to. I check index bloat when a table has heavy updates, and I rebuild the worst ones in a maintenance window. A bloated index still answers, it just answers slower, which makes it easy to miss until the dashboard graphs bend.
A partial index is the cheapest index you will ever build. If nine out of ten queries filter on active orders, I do not index all orders. I index the active ones. The index is smaller, the writes are cheaper, and the planner still picks it for the queries that matter. The catch is that the query has to match the partial condition exactly, or the planner ignores the index. I have been bitten by that mismatch more than once.
Covering indexes get wide, and wide is a cost. Every column you add to an index makes the index bigger and every write slower. I add a column to a covering index only when a real query asks for it, and I check the index size after. An index that holds half the table is not a shortcut anymore. It is a second copy of the table with a fancy name.
At 10x scale, the write tax is the story. At one million rows, two indexes cost 4 ms a write and nobody notices. At ten million rows with ten times the write rate, the same two indexes cost more per write and there are ten times as many writes. That is when I start asking which indexes earn their keep. I pull the usage stats, I find the ones with zero reads, and I drop them one at a time, watching the write latency after each drop. Never drop two at once. If latency jumps, you will not know which one mattered.
At 100x scale, one table stops being one table. A hundred million rows in one orders table means the index alone is bigger than the memory I have for it, and every lookup starts touching disk. That is when I reach for the lessons ahead: partitioning the table by date, or sharding it, or moving the hot recent data somewhere the index fits in memory. The index did not fail. The shape of the data outgrew it, and the fix is a different shape, not a bigger index.
The check I run before I add any index: I look at the real queries first, not the schema. I pull the slowest queries from the logs, I run EXPLAIN on the top five, and I add the index that fixes the slowest one. Then I measure again. An index I added because it felt right is an index I will be dropping in six months.
-- The index from this lesson, built the safe way on a live table. -- CONCURRENTLY avoids the write lock. It takes longer. The site stays up. CREATE INDEX CONCURRENTLY idx_orders_customer_email ON orders (customer_email) INCLUDE (order_id, total, status); -- The composite, customer first, date second. Order is the decision. CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC); -- What I check after: is the planner actually using it? -- Index Scan good. Seq Scan on a big table means the plan did not change. EXPLAIN ANALYZE SELECT order_id, total, status FROM orders WHERE customer_email = 'a@example.com';
A support dashboard search that had been instant for a year started taking four to six seconds. No deploy had gone out that week. No traffic spike. The page just got slow, and it stayed slow. Support agents started keeping the order list open in a second tab and refreshing it, which doubled the load on the same slow query.
Three things. First, ANALYZE went on a weekly schedule for the big tables, not "when someone remembers." Stale statistics had caused the incident, and a schedule is cheaper than another morning like that one. Second, I added an alert on sequential scans against tables over a million rows, so the next plan flip pages me before the users notice it. Third, the bulk import runbook got one new line: run ANALYZE on any table you just loaded, before you hand it back to the app. The index was never broken. The planner was working with old information, and nobody had told it the table had changed.
You own the orders table for a shop that is growing fast. It holds 2 million orders today and adds about 200,000 a month. Three queries run against it all day: (1) look up orders by customer email, for support; (2) list a customer's recent orders, newest first, for the account page; (3) count orders by status for the ops dashboard, refreshed every minute. Writes are steady: about 50 new orders a minute at peak. Design the indexing for this table. Name the indexes, the column order, and what you would measure after.
How many rows does each query return? The email lookup returns a handful. The recent-orders list returns the newest twenty. The status count returns one row per status, but it reads the whole table to get there. Which query is slow today, and what does EXPLAIN say about it? Is the status count allowed to be a minute stale, or does it need live numbers? How big is the table in a year, at 200,000 rows a month? That is 2.4 million new rows a year, so the table doubles in about ten months.
Start with two indexes, not five. First, a covering index on (customer_email) including order id, total, and status. That serves query 1 without touching the table. Second, a composite on (customer_id, created_at DESC) for query 2, filter first, sort second, so the sort is free. Do not index status for query 3. A count by status reads most of the table either way, and an index will not save it. Instead, ask whether the dashboard can read from a small summary table refreshed once a minute. That turns a full scan into a twelve-row read.
At 2 million rows, the email lookup costs about 5 ms with the index and about 700 ms as a scan, using this lesson's numbers. At 4.4 million rows a year from now, the scan doubles to about 1,400 ms and the lookup stays near 6 ms. Two indexes cost about 4 ms per write. At 50 writes a minute, that is nothing. At ten times the write rate, recheck the usage stats and drop anything with zero reads.
Every index you add taxes every write, so two indexes is a deliberate choice, not a starting point for more. The summary table for the status count trades freshness for speed: the dashboard is up to a minute stale, and someone has to own the refresh job. The composite index only serves queries that filter on customer id first. If a new query filters on date alone, it will scan, and that is a new conversation, not a reason to add a third index today.