Skip to content
←Data & Databases

// Data & Databases · 06

Denormalization

Same joins on every readleads toData stored read-ready

Store data redundantly so reads stop paying for joins.

How it fits together

Denormalization structureBeforeAfter«client»Product listGET /productsNormalized tablesproductspricesstockreviewsProduct writesproductssource of truth«client»Product listGET /productsproduct_listone row per product1 4-table join1 write2 same tx3 one lookup
uses / callscreatesreplicates / copies· numbered steps replay in order
  • Product list uses / calls Normalized tables (1 4-table join)
  • Product writes creates products (1 write)
  • products replicates / copies product_list (2 same tx)
  • Product list uses / calls product_list (3 one lookup)

Spot it

  • A hot query joins six tables
  • Dashboards recompute the same totals per view
  • Reads vastly outnumber writes

It costs

Faster reads, but each write updates more places and copies can drift.

In code

CREATE MATERIALIZED VIEW product_list ASSELECT p.id AS product_id,       p.name,       pr.amount AS price,       s.quantity AS stock,       COUNT(r.id) AS review_count,       AVG(r.rating) AS avg_ratingFROM products pJOIN prices pr ON pr.product_id = p.idJOIN stock s ON s.product_id = p.idLEFT JOIN reviews r ON r.product_id = p.idGROUP BY p.id, p.name, pr.amount, s.quantity;​-- Refreshed on a schedule: readers see data up to one refresh interval oldREFRESH MATERIALIZED VIEW product_list;​SELECT product_id, name, price, stock, review_count, avg_ratingFROM product_listWHERE stock > 0;
Go deeperThe full explanation and what to ask for · 1 min readExpand

Normalization stores each fact once, so writes are simple and consistent. Denormalization deliberately copies facts, such as an order total or a product name on the order line, so the common read becomes a single lookup.

The classic mistake is copying data without deciding who owns the truth and how copies refresh. The copies drift and nobody can say which value is right. Name the source and the sync mechanism (same transaction, trigger, event, scheduled refresh) up front.

Measure first: a missing index often explains the slow join. At scale, denormalized read models are what CQRS formalises, and in event-driven systems they are projections rebuilt from events.

Ask for it

  • source of truth
  • read model
  • materialized view
  • computed column
  • projection
  • staleness window

The product list page joins products, prices, stock and reviews on every request. Propose a denormalized read model (a materialized view or a table updated on write), state which table stays the source of truth, how it stays in sync, and how stale it may be.

Connects to