What is denormalization and when should you use it?

Q: What is denormalization and when should you use it?

**Target Audience:** Developers who know what normalization is and now face slow reads, plus candidates who need to discuss read/write trade-offs and sync mechanisms precisely. **Difficulty:** Medium **Engines:** The SQLite examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine). Triggers and materialized views are shown on PostgreSQL 16.15, run in a scratch database for this page. SQLite has triggers but no materialized views. > **How to read this doc:** Each idea is first explained in plain language, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A library keeps one master catalogue: every book, every author, every loan, each fact written once. To answer "how many books has Asha borrowed?" a librarian walks to the loans ledger and counts. That is correct, but slow whe

The problem

Question presented to candidate: "You have a nicely normalized schema, but the order list page and the revenue dashboard are slow because every request joins and sums. What is denormalization, when would you do it, and how do you keep the copied data correct?"

What a strong answer should cover:

Denormalization is deliberately storing redundant or precomputed data (a copied column, a cached total or counter, a summary table) to make reads cheaper, starting from a normalized design rather than skipping it
When it pays off: read-heavy paths measured to be slow, expensive aggregates on every request, analytics (star schemas), and values that must be frozen at a point in time (price or address on the order)
The cost: every write must update the copies, so there is a sync mechanism (same transaction, trigger, materialized view refresh, or async job) and a defined staleness
The tools on PostgreSQL: triggers, materialized views and REFRESH MATERIALIZED VIEW [CONCURRENTLY], generated columns; plus a reconciliation query that detects drift
Alternatives to try first: indexes, covering indexes, query rewrites, caching outside the database

Clarifying questions expected:

What is the read/write ratio, and which query is actually slow (with a plan)?
How fresh must the denormalized value be: exact at commit time, or minutes behind?
Is the copy a cache of current data or a historical snapshot that must not change?

Code / implementation expected: Yes — a cached column kept in sync (transaction or trigger), a summary via materialized view, and a drift check.

Schema used by the examples:

design
denormalization

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know what normalization is and now face slow reads, plus candidates who need to discuss read/write trade-offs and sync mechanisms precisely. Difficulty: Medium Engines: The SQLite examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine). Triggers and materialized views are shown on PostgreSQL 16.15, run in a scratch database for this page. SQLite has triggers but no materialized views.

How to read this doc: Each idea is first explained in plain language, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A library keeps one master catalogue: every book, every author, every loan, each fact written once. To answer "how many books

The solution is hidden so you can try first

13 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLReconciliation query: find orders whose cached total no longer matches the line items

SQLKeep a cached counter correct by updating it in the same transaction as the insert

PostgreSQL: a trigger recomputes orders.total on every insert, update or delete of a line

CREATE FUNCTION sync_order_total() RETURNS trigger AS $$
BEGIN
  UPDATE orders o
  SET total = (SELECT COALESCE(SUM(qty * unit_price), 0)
               FROM order_items i WHERE i.order_id = o.order_id)
  WHERE o.order_id = COALESCE(NEW.order_id, OLD.order_id);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_items_sync
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION sync_order_total();

INSERT INTO order_items VALUES (105, 'MS-02', 1, 18);
UPDATE order_items SET qty = 4 WHERE order_id = 104 AND sku = 'MS-02';
DELETE FROM order_items WHERE order_id = 101 AND sku = 'MS-02';

SELECT order_id, total FROM orders ORDER BY order_id;

PostgreSQL: a materialized view is a denormalized summary that is stale until refreshed

CREATE MATERIALIZED VIEW city_revenue AS
SELECT c.city, COUNT(DISTINCT o.order_id) AS orders, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.city;

INSERT INTO orders VALUES (106, 3, 'Austin', '2025-03-20', 80);
INSERT INTO order_items VALUES (106, 'KB-01', 2, 40);
CREATE TEMP TABLE before_refresh AS SELECT * FROM city_revenue;

REFRESH MATERIALIZED VIEW city_revenue;

SELECT 'before refresh' AS snapshot, city, revenue FROM before_refresh WHERE city = 'Austin'
UNION ALL
SELECT 'after refresh', city, revenue FROM city_revenue WHERE city = 'Austin'
ORDER BY snapshot DESC;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.