Denormalization

The first rule anyone learns about database design is not to write the same fact in two places. Put a company's name on every order row and a rename means fixing tens of thousands of rows, and missing one leaves the same company under two names. So normalization stores that name once in a company table and writes only a number on the order row.

The cost shows up on the read side. If the order row carries only a number, displaying the name means going back to the company table every time. Pulling scattered tables together by matching key values is called a join, and it stays invisible with two tables and stops being invisible at six or seven.

In practice it looks like this. Someone reports that a list of 50 documents takes three seconds to render. Open it up and a single document row is being matched against five tables — author, workspace, plan, permissions. The schema follows the rule perfectly and the screen is still slow.

Denormalization breaks that rule at one specific spot. It applies only where keeping a copy in sync is cheaper than matching tables on every read. Nothing about the rule was wrong; the point is that the rule was written to protect writes, and some screens are almost entirely reads.

Leave the original structure alone and copy the value that reads keep fetching from another table into the table doing the reading. That query then reads one table and stops.

The work goes in five steps.

1. Pick one slow query. Take it from the query log rather than from a hunch. If a single list screen accounts for half of total response time, that's the candidate.
2. Among the values that query pulls from other tables, pick one that rarely changes. A workspace name changes once or twice a year while the document list is read tens of thousands of times a day.
3. Add a column for that value to the reading table. Changing the schema also means a one-time pass to fill the column on rows that already exist.
4. Build the path that updates the copy when the original changes. The code that edits a workspace name has to reach into the document table too. Skip this step and old names stay on screen.
5. Add something that checks whether the two values have drifted apart. A nightly job that compares original and copy and counts mismatched rows tells you on the day it happens.

Copied values usually take one of two shapes. One is a straight duplicate of another table's value, such as carrying the workspace name on the document row. The other is a precomputed count or sum: on a document with 20,000 comments, the first shape scans 20,000 rows and the second reads a single number.

Either way the original stays. When a copy drifts, it can be recomputed from the source and written back.

It belongs where reads outnumber writes by a wide margin. Four places are typical.

1. List and dashboard screens. Render dozens of rows and visit another table per row, and the number of queries grows with the number of rows. That's the N+1 problem, where a 50-item list fires 51 queries. Put the needed value on the list table and one query covers it.
2. Counts kept in advance. Numbers that follow every screen — likes, team members — get incremented and decremented on write instead of counted on read. Writes are rare enough that the extra work per write disappears, while the read saves a full scan every time the screen loads.
3. Records that have to hold a moment in place. An order row stores the price and shipping address as they were at checkout. This one is about accuracy rather than speed. If a product goes from $8 to $9, last month's receipt must not move with it.
4. Analytical storage. A data warehouse is built wide and flat on purpose, so that one scan produces the aggregate without anyone memorizing how ten tables relate.

Values that change often, and amounts that have to be exact, stay where they are. Slowness alone isn't a reason either. An index is far cheaper, and caching a result for a while solves plenty of cases. Because this one changes the structure itself, it's the hardest to undo, so it comes last.

"This list query joins seven tables. If the document table carried the workspace name, it would be down to three."

A proposal to change the structure so reads cost less.

"Let's keep the comment count as a number on the document row instead of counting every time. Just don't forget to decrement it when a comment is deleted."

Store the aggregate, but build the update path along with it.

"That duplicate is intentional. It's the price at the time of the order, so it must not follow the original."

A warning that the redundancy is designed, not an oversight to clean up.

At Pagedeck this came up when the admin screen got slow. Theo pulled the logs and found the document list query averaging 2.8 seconds, most of it spent matching the workspace and plan tables. An index brought it to 1.9 seconds and no further.

So Theo proposed adding a workspace name column to the document table. Mia's question was where the name would be updated when it changed. The team decided that workspace settings would write through to the document table, added a nightly comparison of the two values, and then did the work. The list came down to 0.4 seconds, and the code that renames a workspace picked up one more thing to remember.

The most common misreading is treating denormalization as a schema that was never normalized. From the outside both end up with the same value written in several places. But an unnormalized design is one where nobody knows where the duplicates are, while denormalization starts from a normalized structure and duplicates one chosen column. Blur the two and untidy schemas get filed under denormalization, and when values drift there is no record of what should match what.

Reading it as a pure speed win is the other frequent mistake. Read time drops visibly, so it looks like a free gain. The write side gets heavier by the same amount. Renaming one workspace becomes an update across 20,000 documents, and an error partway through leaves half of them changed. On a screen read a hundred times for every write that trade pays off, but skip measuring the ratio and fixing one slow screen makes the save button slow.