Why and When to Denormalize

Learning outcomes

Denormalization means intentionally storing redundant, derived, or prejoined data to improve a measured workload. It is not the opposite of good modeling. A disciplined system can keep a normalized source of truth while adding carefully governed denormalized structures for hot reads, analytics, search, or distributed projections.

Normalization remains the baseline

Normalization gives clear fact ownership, reduced anomalies, and enforceable dependencies. Denormalization should normally begin only after that structure is understood.

Valid denormalization motivations

• repeated expensive joins on a high-frequency read path;
• dashboard aggregation that repeatedly scans the same data;
• search-oriented documents with very different access requirements;
• low-latency APIs that need a small prejoined projection;
• analytics isolated from OLTP;
• distributed services that need local read copies.

Bad motivation: “joins are slow”

Joins are not inherently a design failure. Before duplicating data, verify query shape, indexes, statistics, row grain, execution plan, and measured latency. A missing index or accidental Cartesian multiplication should not be “fixed” by copying columns everywhere.

WorkshopHub example

WorkOrder(work_order_id, asset_id, status_code, ...)Asset(asset_id, customer_id, ...)Customer(customer_id, legal_name, ...)WorkOrderStatus(status_code, display_name)

A hot list API repeatedly needs work-order number, asset serial number, customer name, status display name, and opened_at. A read projection can prejoin those values without making WorkOrder itself the owner of Customer or Status facts.

Forms of denormalization

• duplicated columns;
• materialized views;
• summary tables;
• cache entries;
• search indexes;
• warehouse dimensions/facts;
• event-driven read models.

The hidden cost: synchronization

Each copy needs a rule. Some should update; historical snapshots may intentionally remain unchanged.

Historical snapshots are different

Invoice recipient name or charged unit price can be intentionally preserved as “what was true for this transaction.” Those values are not performance caches of current master data. They are historical facts with their own determinant and time semantics.

Measure the benefit

Before:p95 latency = 420 msCPU/query = highjoin reads = 50k pagesAfter projection:p95 latency = 45 msprojection lag = 2 swrite amplification = +8%

This makes the tradeoff reviewable rather than ideological.

Freshness becomes a contract

A derived structure can be transactionally synchronous, eventually consistent within seconds, refreshed every minute, or rebuilt nightly. Consumers need to know the guarantee so they do not use a stale projection for a correctness-critical command.

Write amplification

If one source change must update five copies, read optimization has moved cost to writes. That can be appropriate for read-heavy systems but harmful for write-heavy workloads.

Complexity budget

Denormalization adds refresh code, failure handling, reconciliation, monitoring, migrations, and developer cognitive load. These operational costs should be included in the performance decision.

Denormalize for a named access pattern

Structure: WorkOrderListProjectionSupports: Q2, Q5Source of truth: WorkOrder + Asset + Customer + StatusFreshness: <= 5 secondsRebuildable: yesOwner: Service Operations

WorkshopHub candidates

Decision checklist

1. Is the logical source model correct?
2. Is the workload problem measured?
3. Have query/index fixes been tested?
4. What exact data will be duplicated?
5. Who owns the truth?
6. How stale may the copy be?
7. How is it rebuilt?
8. How is drift detected?

Practice: should you denormalize?

Summary and next lesson

Denormalization is justified by measured access patterns, not by fear of joins. Every duplicate needs ownership, freshness, synchronization, and a rebuild story. The next lesson focuses on duplicated attributes and how to prevent them from becoming competing sources of truth.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• Martin Fowler, Patterns of Enterprise Application Architecture.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.