Step 2 of 15 · 1 min

One fact, one place

The reason relational databases are designed the way they are (lesson 3.1) is an idea called normalization: each fact is stored in exactly one place, and everything else refers to it by ID.

A user's display name lives in one row of users. Posts don't store the author's name; they store author_id. When you need to show a post with its author's name, you look the name up.

This protects you from update anomalies: situations where the same fact is stored in several places, and an update changes some of them but not others. That's exactly what happened to Meera. Once a fact is copied, every change has to reach every copy, and the database can't do that for you unless it knows where the copies are.

There are formal rules for normalization (first, second, third normal form, and so on), but the working idea is simple: don't store the same fact twice unless you have a good reason.

Step 3 of 15 · 1 min

Why copy anything, then?

Because looking things up has a cost, and sometimes that cost is too high.

Chirp's profile header shows "18,204 followers." Without a copy, that's:

SELECT count(*) FROM follows WHERE followee_id = 4821;

With the index on (followee_id, follower_id) from lesson 3.1, Postgres can count entries in the index rather than reading the table, but it still has to count every entry. For Meera, that's 18,000. For the cricket commentator from lesson 2.1, it was 2.3 million, which took about half a second. Profile headers are viewed around 3,000 times a second at peak. Counting from scratch on every view was never going to work.

So Chirp keeps a copy: a follower_count column on users, updated whenever someone follows or unfollows. Reading it is one row. That's denormalization: deliberately storing derived or copied data to make reads cheaper.

The trade is always the same: reads get cheaper, writes get more expensive, and you take on the job of keeping the copy correct. Whether that's worth it depends on two things: how much more often the data is read than written, and how bad it is if the copy is briefly (or permanently) wrong.

Step 4 of 15 · 1 min

The kinds of copies

Most denormalization falls into a few patterns.

Counters. Follower counts, like counts, post counts, unread notification counts. Reads vastly outnumber changes, and counting from scratch is expensive. Almost always worth it.

Copied attributes. Storing the author's name and avatar alongside each post, or a product's price on each order line. Saves a lookup on read. The cost depends entirely on how often the original changes, and whether old copies should change at all (more on that below).

Precomputed results. A user's home feed (lesson 4.4 is entirely about this), a leaderboard, "trending this hour," a dashboard's numbers. Expensive to compute, read often, and usually fine to be a little stale.

Summary tables. Daily totals per user, posts per city per week. Used for reports and analytics, where scanning raw rows every time would be slow.

Embedded data in a document database (lesson 3.1): copying related data inside a document for locality.

Search indexes and caches. These are copies too, kept in a different system. Lessons 2.4 and 3.4 cover the ways they go stale.

Step 5 of 15 · 1 min

What copies cost

Each copy adds costs. It's worth naming them, because they're easy to underestimate when the read speedup is staring you in the face.

More work on every write. A follow now writes the follows row and updates two counters (one user's followers, the other's following). A name change, if names were copied into posts, would rewrite every post.

Write fan-out. Sometimes "every copy" is enormous. If each post embedded its author's name, a user with 40,000 posts would need 40,000 updates to change their name. Lesson 4.4's feed fan-out is this problem at its most extreme.

Contention. A counter on a popular row is updated by many requests at once, and each update locks the row for a moment (lesson 1.4). The commentator's follower count, during the week they joined, was updated hundreds of times a second. Lesson 4.5 is about this.

Drift. If any code path forgets to update the copy, or a crash happens between updating the original and the copy, they disagree, and nothing notices. Meera's follower count was 13 too high because of exactly this.

Harder changes later. Every copy is something future features have to know about. Kabir's next feature (letting users merge two accounts) had to find every place a user ID or name had been copied.

Step 6 of 15 · 3 min

Keeping copies correct

There are three main ways, and good systems use more than one.

1. Update the copy in the same transaction

If the original and the copy live in the same database, update both in one transaction (lesson 1.4):

BEGIN;
INSERT INTO follows (follower_id, followee_id) VALUES (12, 4821);
UPDATE users SET follower_count = follower_count + 1 WHERE id = 4821;
UPDATE users SET following_count = following_count + 1 WHERE id = 12;
COMMIT;

Either all three happen or none do, so crashes can't leave them out of step. This is the strongest guarantee, and it's the right default whenever the copy is in the same database and the write fan-out is small.

You can also have the database do it automatically with a trigger, a function that runs on every insert or delete in follows. That way, no code path can forget. The cost is that the logic is now hidden in the database, where developers reading the application code won't see it.

So why was Meera's count wrong? Because one code path didn't do this. The "remove follower" button, added months earlier, deleted the follows row and then updated the counter in a separate transaction. Deploys sometimes stopped a server between the two (lesson 2.1's graceful shutdown wasn't in place yet), leaving the follow deleted but the count not decremented. Thirteen times, over several months.

2. Update the copy in the background

When the copy lives somewhere else (a cache, a search index, another database), or the fan-out is too big for one transaction, you can't update it atomically. Instead, the original change is recorded, and something else updates the copies afterwards. The copies are eventually consistent: briefly out of date, but they catch up.

The danger is the gap between "the original changed" and "something knows to update the copies." If the app updates the database and then tries to tell the search index, and crashes in between, the search index never hears about it.

Two patterns close that gap, and they're coming up properly in the next lessons:

• The outbox pattern (lesson 4.2): in the same transaction as the change, also write a row saying "this needs propagating." A separate process reads those rows and does the propagating, retrying until it succeeds.
• Change data capture (lesson 3.4): read the database's own log of changes (the write-ahead log from lesson 1.4) and propagate from there. Nothing in the application can forget, because every change goes through the log.

3. Repair regularly

Even with both of the above, copies drift: bugs, manual fixes in the database, incidents, data imported from somewhere else. So for anything that matters, also run a reconciliation job that recomputes the copy from the original and fixes any differences:

UPDATE users u
SET follower_count = c.n
FROM (SELECT followee_id, count(*) AS n FROM follows GROUP BY followee_id) c
WHERE u.id = c.followee_id AND u.follower_count <> c.n;

On a big table you'd do this in batches, perhaps a slice of users each night, so it doesn't overload the database. And it's worth logging what it fixes. If reconciliation corrects hundreds of counts a night, that's a bug somewhere, and the log tells you where to look.

Chirp's reconciliation job found 6,400 wrong follower counts the first time it ran. That's how they found the "remove follower" bug.

Let the database maintain it

Postgres has a built-in kind of copy: a materialized view, a query whose results are stored as a table. You define it once:

CREATE MATERIALIZED VIEW posts_per_city_per_day AS
SELECT u.city, date(p.created_at) AS day, count(*) AS posts
FROM posts p JOIN users u ON u.id = p.author_id
GROUP BY u.city, date(p.created_at);

and refresh it when you choose (REFRESH MATERIALIZED VIEW CONCURRENTLY ..., which needs a unique index on the view, lets reads continue during the refresh). Postgres recomputes the whole result each time, so it suits summaries that can be a few minutes or hours stale, not counters that must be current.

Step 7 of 15 · 1 min

Snapshot or reference?

Back to Meera's notifications. The real mistake wasn't that the name was copied. It was that nobody had decided what the notification was supposed to mean.

There are two kinds of copied data, and they look identical in a table:

• A reference is a copy that's supposed to track the original. A post shows its author's current name. If the name changes, the post should show the new one.
• A snapshot is a record of what something was at a particular moment, and should never change. An invoice records the address it was sent to. If the customer moves, last year's invoice must still show last year's address. A message in a chat log records the text that was sent.

Chirp's notifications stored the finished sentence: "Meera K liked your post". That made the name a snapshot by accident, when everyone expected a reference.

The fix was to store notifications as IDs, not text:

{ kind: "like", actor_id: 4821, post_id: 9001, created_at: ... }

and build the sentence when displaying it, looking up the actor's current name (from the cache). Old notifications now say "Meera Kapoor." As a bonus, the app can now show notifications in the reader's language, which a stored English sentence couldn't.

The digest email was the opposite case, and Chirp left it alone. Once an email is sent, it's a snapshot by nature, since nobody can change what's already in someone's inbox. The fix there was just to generate the digest closer to sending time.

Before copying any piece of data, ask: if the original changes, should this copy change too? If yes, it's a reference, and you need one of the sync approaches above. If no, it's a snapshot, and you should make sure nothing ever "helpfully" updates it.

Step 8 of 15 · 1 min

When to denormalize

A simple way to decide, for any piece of data you're tempted to copy:

And before copying anything, try the cheaper fixes first: an index (lesson 1.4) and a cache (lesson 2.4). A cache is also a copy, but a disposable one: if it's wrong, it expires.

Step 9 of 15 · 1 min

From the real world: Facebook's two-sided edges

Facebook's social graph store, TAO, is a large, long-running example of denormalization by design, described in a 2013 paper.

TAO stores objects (users, posts, comments) and associations between them ("Alice follows Bob," "Alice liked post 7"). Data is sharded by object, and an association is stored with its source object, so "everything Alice follows" is a read from Alice's shard. But the app also needs "everyone who follows Bob," quickly. So many association types have an inverse: writing "Alice follows Bob" also writes "Bob is followed by Alice," on Bob's shard.

That's a copy, and the two halves live on different machines. TAO does not make the pair atomic: it writes the forward edge, then the inverse, as two separate writes.

If something fails in between, you get a "hanging" association: Alice follows Bob, but Bob's follower list doesn't include her. TAO accepts that, and runs a background job that finds hanging associations and repairs them. Facebook chose fast, single-shard writes for an enormous, read-heavy graph, and paid for it with copies that are occasionally, briefly inconsistent.

This lesson's rule, "every copy needs an owner and a repair path," is what that background job is: the repair path, run continuously. When you denormalize across shards or services, plan the repair job from the start, not after the first support ticket.

Step 10 of 15 · 1 min

Going deeper: copies that go backwards

The keeping-copies-correct section updates copies from events. There's a subtle failure there that shows up only under load: events arriving out of order.

Meera edits her post's title twice, a second apart. Two events go out: "title is now v1," then "title is now v2." Somewhere between the database and the copy, the order flips: a retry of the first event, two consumers processing in parallel, a partitioning change. The copy applies v2, then v1. Now the copy shows the older title.

Notice why this is worse than ordinary lag: nothing will ever fix it. No newer event is coming, because Meera has finished editing. The copy is permanently wrong until something rebuilds it.

The defence is to make every update carry its own position in history, and to apply it only if it's newer than what the copy already has:

• A version number on the row, incremented on every write, carried in the event. The copy does UPDATE … SET … , version = :v WHERE id = :id AND version < :v.
• The source's log position (Postgres LSN, MySQL GTID, Kafka offset) if you're using change data capture. It's naturally increasing for a given row.
• Built-in support where it exists. Elasticsearch accepts an external version (version_type=external) and rejects older ones.

Two related rules come with it. Deletes need versions too: keep a tombstone with the version, or a late "update" can resurrect a deleted row. And events that carry the whole new state (not "add 1" or "set field X") are much easier to make idempotent and order-safe. A late full snapshot is simply ignored if its version is older.

Step 11 of 15 · 1 min

What Chirp changed

• Moved every counter update into the same transaction as the change it counts, including the "remove follower" path.
• Added a nightly reconciliation job for follower, following and post counts, which logs every correction.
• Changed notifications to store IDs, with text built when displayed. A one-off job converted the existing 300 million.
• Generated the weekly digest an hour before sending, rather than the night before.
• Fed name changes to the search index through the database's change log (lesson 3.4), instead of from the app.
• Wrote a short rule into the team's design guide: for every copied field, write down whether it's a reference or a snapshot, and how it's kept correct.

Step 12 of 15 · 1 min

In the simulation

Stage 2's name-change feature is where your Stage 0 choices show up. If your data copies author details into posts (a common document-model design), the simulation counts how many writes one name change causes and how long old names linger. If you kept names in one place, name changes are cheap, but watch the read side: feeds now need a name lookup for every post, which is where the cache from Part 2 earns its keep.

Open the simulation →

Step 13 of 15 · 1 min

If you're here for an interview

Denormalization comes up whenever a design shows counts, feeds or anything precomputed. Good answers:

• Say what you're copying and why: "I'll store the follower count on the user row, because counting on every profile view is too expensive."
• Say how it stays correct: "Updated in the same transaction as the follow, with a nightly recount to catch drift."
• Separate references from snapshots when it's relevant: "Orders store the price at purchase time, as a snapshot. Product pages read the current price."
• Know the hot-counter problem for very popular items, and that lesson 4.5's fixes exist.

Step 14 of 15 · 2 min

Try these yourself

Step 15 of 15 · 1 min

Further reading

• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 2 (normalization and the document model) and Chapter 11 (derived data, keeping copies in sync).
• PostgreSQL documentation on materialized views and triggers.
• Pat Helland, "Immutability Changes Everything," Communications of the ACM, 2016. On snapshots, versions and why data that never changes is easier to reason about.
• Nathan Bronson et al., "TAO: Facebook's Distributed Data Store for the Social Graph," USENIX ATC 2013. See the sections on associations, inverse associations and consistency.
• Elasticsearch reference, "Index API: versioning" (external versions).