System Design HLD-07.07
Denormalization, materialized views and CQRS
Storing data in the shape reads need: denormalized copies, materialized views, and CQRS with separate write and read models kept in sync by events.
The big idea
Normalization keeps each fact in one place, which makes writes simple and correct. But reads pay for it: a product page that joins seven tables on every view, or a dashboard that aggregates a billion rows every time someone opens it, is slow no matter how good the indexes are. The fix is to do the work once, at write time, and store the result in exactly the shape reads need.
DenormalizationDeliberately storing duplicated or precomputed data so reads need fewer joins or calculations, at the cost of more work on writes. copies data so reads avoid joins. A materialized viewThe stored result of a query, kept up to date as the underlying data changes, so reading it is as cheap as reading a table. stores a query's result and keeps it updated. CQRSCommand Query Responsibility Segregation: using one model for writes (commands) and one or more separate models, optimized per query, for reads. goes furthest, splitting the system into a write model that validates and records changes and one or more read models, each shaped for a particular screen or query, kept in sync by events.
Think of a restaurant. The kitchen records every order in its own detailed format (write model). The front of house keeps a simple board of "table 4: mains ready", and the manager keeps a nightly sales summary. Each is a view built for one reader, updated as orders flow through.
How it works
Step 1: Denormalize and precompute
- Copy fields: store the author's display name on each comment so the comment list needs no join to users. When the name changes, update the copies, often asynchronously.
- Precompute aggregates: keep
comment_counton the post instead of counting comments on every view. - Materialized views: let the database maintain a stored query, refreshed on a schedule or incrementally, such as daily revenue per product.
Every copy is a promise you must keep: whenever the source changes, the copies must follow, or they disagree.
Step 2: CQRS with events
from collections import defaultdict
# Write side: commands append events to a log (the source of truth).
events = []
def place_order(order_id, customer, items):
events.append({"type": "OrderPlaced", "order": order_id, "customer": customer,
"total": sum(qty * price for _, qty, price in items)})
def ship_order(order_id):
events.append({"type": "OrderShipped", "order": order_id})
# Read side: projections consume events and keep query-shaped views up to date.
orders_by_customer = defaultdict(list) # for "my orders" page: one lookup, no joins
revenue_by_customer = defaultdict(float) # for a dashboard
status = {}
def project(event):
if event["type"] == "OrderPlaced":
orders_by_customer[event["customer"]].append(event["order"])
revenue_by_customer[event["customer"]] += event["total"]
status[event["order"]] = "placed"
elif event["type"] == "OrderShipped":
status[event["order"]] = "shipped"
place_order(1, "ada", [("keyboard", 1, 49.0), ("mouse", 2, 19.0)])
place_order(2, "grace", [("monitor", 1, 199.0)])
place_order(3, "ada", [("mouse", 1, 19.0)])
ship_order(1)
applied = 0
for e in events[applied:]: # in production: a consumer reading a stream
project(e)
applied += 1
print("events in the log:", len(events))
print("ada's orders:", orders_by_customer["ada"], "statuses:", [status[o] for o in orders_by_customer["ada"]])
print("revenue:", dict(revenue_by_customer))Output
events in the log: 4
ada's orders: [1, 3] statuses: ['shipped', 'placed']
revenue: {'ada': 106.0, 'grace': 199.0} Commands only append events. A projection reads the events in order and updates two read models: a customer's order list for their account page, and revenue per customer for a dashboard. Each read is now a single lookup. Adding a new screen means adding a new projection and replaying the existing events into it, without touching the write side. Storing the events themselves as the source of truth is called event sourcing, covered in Event-driven architecture and event sourcing .
Step 3: The cost: eventual consistency
Read models are updated after the write, through a stream, so they lag a little. A user who places an order and is immediately redirected to "my orders" may not see it yet. The usual mitigations are the read-your-writes tricks from Replication lag and read consistency guarantees : show the result of the command directly, read from the write model for that user briefly, or wait for the projection to reach the event's position.
Going deeper
When not to
CQRS adds moving parts: an event pipeline, projections, replay tooling and monitoring for lag. For a simple CRUD application it is overkill; a few denormalized columns or a materialized view usually suffice. It earns its keep when reads and writes have very different shapes or scales, or when many different views of the same data are needed.
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Normalized only | Simple CRUD, strong consistency, modest read load | Read-heavy pages that need many joins or aggregates |
| Denormalized fields and materialized views | Specific hot queries, inside one database | Write complexity and possible staleness of copies |
| Full CQRS | Very different read and write shapes, many views, large scale | Operational complexity and eventual consistency between models |
Common mistakes
- Denormalizing without a reliable path to update every copy.
- Introducing CQRS for a small CRUD app.
- Forgetting that read models lag, then showing users a confirmation page that does not include what they just did.
- Building projections that cannot be rebuilt from the event history.
Check yourself
-
Why store comment_count on a post?
Show answer
B. So showing the count does not require counting comments on every read Precomputing at write time makes the frequent read cheap.
-
What does CQRS separate?
Show answer
A. Reads and writes into different models A write model handles commands; read models are shaped for queries.
-
How do read models stay in sync with the write model in CQRS?
Show answer
B. By consuming events emitted by the write side Projections apply the event stream to each read model.
-
What is the main consistency cost of CQRS?
Show answer
B. Read models lag behind writes, so they are eventually consistent Projections update after the event is published, so reads may briefly miss recent writes.
Recap
- Denormalization stores data in the shape reads need, trading write work for read speed.
- Materialized views keep precomputed query results up to date.
- CQRS splits a write model from read models synchronized by events.
- Read models are eventually consistent; handle read-your-writes explicitly.
- Use the lightest tool that solves the read problem.
Next up
- Crack the system design round learning path