Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Scaling Data

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.

Intermediate 12 min Last reviewed

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_count on 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

CQRS: one write model feeding several read models through events Commands go to a write model that validates them and appends events such as OrderPlaced and OrderShipped to an event stream. Three projections consume the stream and maintain read models: a customer's orders view in a key-value store, a search index, and an analytics table. Queries go directly to whichever read model fits. Commands place, ship, cancel Write model rules, transactions Event stream OrderPlaced, OrderShipped My orders view key-value Search index Analytics aggregates Queries Figure 1. Commands change the write model, which publishes events. Each projection keeps its own read model up to date, shaped for one kind of query. Reads never touch the write model.
cqrs.pyruns with python3 (standard library)
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

OptionWins whenLoses 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

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

  2. What does CQRS separate?

    Show answer

    A. Reads and writes into different models A write model handles commands; read models are shaped for queries.

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

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

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy

Next up