All topics
11 / 66
schema design

Denormalization for Reads

Trade write complexity for read speed — duplicate or pre-aggregate data when the read path is hot and the freshness window is acceptable.

Denormalization is putting duplication back on purpose, to make a specific read faster.

Pre-printing flyers vs typesetting one each time a customer asks. Faster delivery, but you have to reprint when the offer changes.

Key Concepts

1
It is not the opposite of normalization so much as a targeted exception to it. The word that matters is "specific" — this is an optimisation aimed at one measured problem, not a style of schema design.
2
The three forms you will be asked about.Copy a column to avoid a join. Store author_name on each post. Listing 50 posts then touches one table instead of joining to users.
author_nameusers
3
Keep a running count. A comment_count column on each post, updated as comments arrive, replaces a COUNT(*) over the comments table on every page load.
comment_countCOUNT(*)
4
Materialise a whole result. A materialized view or summary table holds the output of an expensive multi-table query, refreshed on a schedule. Dashboards run on these.
5
What you are actually buying and paying. Reads get cheaper by exactly the work you moved. Writes get more expensive and, more importantly, more fragile — because now two places hold the same fact and something has to keep them together.
6
That something is a trigger, application code, or a background job, and it can fail. When it does, the copy is not just stale, it is wrong, and nothing in the schema will tell you.
7
So make the copy rebuildable. Keep the normalized tables as the source of truth. Treat the denormalized copy as a cache you can throw away and recompute.
8
That one rule turns a data-corruption incident into a job you re-run. Without it, a failed trigger three months ago means nobody knows which comment_count values are right.
comment_count
9
When the trade is worth it. All three should hold:
10
  - the read is hot and you have measured that it is slow
  - the data changes far less often than it is read
  - the application can tolerate the staleness window you choose
11
A comment_count that is a few seconds behind is fine. An account balance a few seconds behind is not.
comment_count
12
The counter the interviewer is listening for. "We denormalized because joins are slow" is a guess. "This endpoint ran 40,000 times an hour and the plan showed the join to users dominating, so we copied the name and the P99 dropped from 300 ms to 40 ms" is an engineering decision. Normalise, measure, then denormalise the narrow hot spot.
users
13
What the interviewer is probing.1. "When is denormalisation justified?" Probing: the trigger. Stalls: "When queries are slow." Moves up: when a measured read pattern needs it and the write path can keep the copies consistent — not as a starting point.
14
2. "What does it cost?" Probing: the trade. Stalls: "Storage." Moves up: every duplicate must be updated together, so writes get more complex and inconsistency becomes possible.
15
3. "How do you keep denormalised data in step?" Probing: the mechanisms. Stalls: "Manually." Moves up: the same transaction, a trigger, or an event-driven update from a change feed — each with different consistency guarantees.
16
4. "What is a materialised view in this context?" Probing: the managed option. Stalls: "A view." Moves up: a stored precomputed result the database refreshes, which gives denormalised reads without hand-maintained copies.
Use cases:DenormalizationMaterialized ViewTriggers