Denormalization: Counters, Summaries and Snapshots

In one line

Denormalization stores data redundantly to make specific reads cheaper: duplicated columns, precomputed counters, summary tables, materialized views and snapshots. Every copy needs an owner and a mechanism (same transaction, trigger, CDC, or periodic rebuild) that keeps it correct, and a stated tolerance for staleness.

0/5 · 0%

Think of it like this

A shop that writes today's total sales on a whiteboard instead of adding up every receipt each time someone asks. It's much faster, but someone must update the board with every sale, and if they forget, the board lies.

Key ideas

01
Duplicate columns: copy customer_name onto orders to avoid a join on a hot listing. It's only safe if the copy means "name at order time" or is updated whenever the source changes.
02
Precomputed counters: post.like_count. Updating it in the same transaction as the like insert creates a hot row under viral load; alternatives are sharded counters, async aggregation from an event stream, or Redis counters flushed periodically.
03
Summary tables: daily_sales(day, product_id, units, revenue) rebuilt or incrementally updated, which turns an hour-long scan into a point lookup. Materialized views (Phase 12.2) are the database-managed form.
04
Snapshot tables: capture state at a moment (monthly_balance_snapshot), so history queries don't replay every transaction.
05
Decision rule: normalize by default; denormalize when a measured, frequent read is too slow or too costly, the write overhead is acceptable, and you can name the sync mechanism and the staleness budget.

Code & diagrams

counter-options.sqlsql

-- 1) same-transaction counter (simple, but a hot row for viral posts)
BEGIN;
INSERT INTO post_like (post_id, user_id) VALUES (42, 7);
UPDATE post SET like_count = like_count + 1 WHERE id = 42;
COMMIT;

-- 2) sharded counter: spread increments across N rows, sum on read
CREATE TABLE post_like_counter (post_id bigint, shard smallint, n bigint NOT NULL DEFAULT 0,
                                PRIMARY KEY (post_id, shard));
UPDATE post_like_counter SET n = n + 1
WHERE post_id = 42 AND shard = floor(random() * 16)::int;
SELECT sum(n) FROM post_like_counter WHERE post_id = 42;

-- 3) summary table rebuilt incrementally each hour
INSERT INTO daily_sales (day, product_id, units, revenue)
SELECT created_at::date, product_id, sum(qty), sum(qty * unit_price)
FROM order_item JOIN orders o ON o.id = order_id
WHERE created_at >= date_trunc('hour', now()) - interval '1 hour'
  AND created_at <  date_trunc('hour', now())
GROUP BY 1, 2
ON CONFLICT (day, product_id) DO UPDATE
  SET units = daily_sales.units + EXCLUDED.units,
      revenue = daily_sales.revenue + EXCLUDED.revenue;

Interview problem

The problem

Like counts for a viral social post

Posts show like counts. A celebrity post receives 50K likes/sec. The current design updates post.like_count in the like transaction and the database is melting. Redesign it.

When it breaks

A denormalized column without a sync owner

What you see

orders.customer_email stays old after a user changes email; receipts go to the wrong address and support can't tell which is correct.

Fix & prevent

Document each copy's meaning ("at order time" vs "current"), its update path, and a reconciliation job that detects drift.

Explain it without notes

When is denormalization justified?

Practice

Design a summary table for "revenue per merchant per day" and say how it stays correct when an order is refunded next week.

Trade-offs

↔
Faster reads and simpler queries in exchange for write amplification, storage, and consistency work.

Done when you can

I can choose a denormalization technique and name its sync mechanism and staleness budget.

Command Palette