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 and editor_id.

Each of the three 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.
• The assigned editor is the actor_id of the latest row an editor wrote.

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 the rule against this. Third normal form says that no column depends on another column that is not the primary key. state depends on the rows in post_state_changes that carry the same post_id, not on post_id itself. When a new row is added to post_state_changes, state has to be updated to match. That is the violation.

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 normalized design would remove the three columns 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, and it needs the current state of every post. Without state on posts, that means: for each post, find its change rows, pick the latest, and check whether its to_state is published. That is a lookup into post_state_changes for every post, and that table grows with every transition ever made. Opening a post requires the same lookup, and a second lookup to find the publication time.

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 three 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.