Denormalization

The schema now stores some values twice. The state column on posts says a post is published. The post_state_changes table says the same thing: the latest row for that post has to_state equal to published. The same is true of published_at.

Each can be derived from post_state_changes:

• The current state of a post is the to_state of its latest change row.
• The publication time is the occurred_at of the row where to_state is published.

A column whose value can be computed from other rows is a duplicate. The database already has the value.

Why duplicates are a problem

Chapter 2 gave us a rule of thumb: store each value in one place. The state on posts is also recorded by the rows in post_state_changes. When a new change row is added, state must be updated to match. Keeping both copies is redundant, and if one changes without the other, they drift apart.

There are three costs. The first is space. A duplicated value is stored twice, so the database is larger than it needs to be. The second is extra writes. Every transition must write a row to post_state_changes and also update posts. That is two writes instead of one. The third is drift. Suppose the application does both writes correctly. The database still does not know that the two writes describe one event. If one happens and the other does not, the post’s current state and its history disagree, and nothing in the schema catches it. A normalized schema has none of these costs, because the value is stored in one place.

A design without the redundant copies would remove state and published_at from posts. Each transition would then be a single insert into post_state_changes.

What normalization costs on the read side

Reads get more expensive. Listing published posts is the most frequent operation HopPress performs. Without state and published_at on posts, the application must get that information from post_state_changes and combine it with the post’s title and other details. Opening a published post also requires consulting the history for its publication time. That is extra work involving another table, and that table grows with every transition ever made.

With state on posts, listing published posts is one condition on one column of one table. Opening a post reads one row.

Why we choose cheaper reads

The choice depends on how HopPress is used. A post is created once, submitted a few times, and published once. After that it is read many times, by many readers. The Chapter 1 targets are about those readers: a post must open in under two seconds, with up to 500 readers at once.

So we keep the two redundant columns. That means we accept the extra writes, the wasted space, and the risk of drift. Preventing the drift becomes the application’s job: every code path that changes a post’s state must write both posts and post_state_changes.

Keeping a derivable column on purpose, to make reads cheaper, is called denormalization. It is a design decision, and it is made on the same evidence every time: how often the value is read, how often it is written, how expensive it is to derive, and whether the application can be trusted to keep the two copies consistent. Post state meets all four conditions.