Designing a Table to Live With
Copy the Value Only If You Are Also Building the Thing That Repairs It
Last timeThe Trouble With Missing
Deliberate duplication is a trade: reads get cheaper, writes get harder, and something has to keep the copy true. Here is the arithmetic and the two cases that are not really duplication.
Everything so far has pushed toward each fact living in one place. This lesson
argues the other side, which is a real argument with a real answer, and the
answer has a number in it.
Measure the read first
Start with the thing that is almost always skipped. Somebody says the orders
page is slow because it joins four tables, and proposes copying the customer
name onto the order. Before agreeing, find out what the join costs.
A join on an indexed key is among the cheapest operations a database performs.
It is a lookup in a tree, a few page reads, usually under a millisecond. Four
of them is still well under the time the page spends doing other things.
| step | what was measured | milliseconds | share of the page | what happened |
|---|---|---|---|---|
| 1 | the four joins everybody blamed | 3 | 2 per cent | Three milliseconds in total, all of it indexed lookups. Removing every one of them would make the page two per cent faster, which nobody would notice. |
| 2 | one query run once per row of the list | 94 | 63 per cent | Fifty rows, fifty separate queries, each one fast. This is the real problem and it is a different problem: the fix is to fetch the rows together, which is a change to the code rather than to the schema. |
| 3 | sorting a column with no index | 38 | 25 per cent | Also a real problem, also unrelated to duplication, and fixed by one index. |
| 4 | everything else | 15 | 10 per cent | Rendering, serialisation, the network. The ordinary remainder. |
If the measurement shows the join is genuinely the cost, the conversation is
worth having. Usually it shows something else, and the duplication would have
been permanent while the benefit was two per cent.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents