Normalizationdenormalizationcounter cachematerialized viewrollupdrift

Denormalization on Purpose

Duplicating a value to make a read cheap is a legitimate design decision as long as you can name the read it serves, the write that maintains it, and the way you will detect drift.

Try queriesInterview question
Progress

When a normalised read is too slow

A user’s follower count is SELECT count(*) FROM follows WHERE followee_id = ?. Correct, indexed, and O(followers). For an account with three million followers that is three million index entries on every profile view. Storing follower_count on the user row makes the read O(1) and creates a second copy of a fact that can now disagree with the first.

That is denormalization: you have chosen read speed over single-source-of-truth for one specific value. It is the right call exactly when the read is far more frequent than the write, the write path is under your control, and you have a way to notice when the copy is wrong.

The forms it takes

Counter cache: posts.like_count, users.follower_count, conversations.message_count. Maintained by UPDATE … SET n = n + 1 in the same transaction as the insert. Snapshot columns: order_items.unit_price, orders.shipping_address — but note these are *history*, not redundancy; the value is *supposed* to differ from the current one. Last-x columns: conversations.last_message_at so the inbox can sort without touching messages. Rollup tables: daily_revenue(day, total) maintained by a nightly job or a trigger. Materialised views: a query result stored as a table, refreshed on a schedule.

The obligations

Every duplicated value comes with three obligations. Write path: every code path that changes the source must update the copy, in the same transaction, or the copy is wrong from that moment. A trigger enforces this regardless of which code path wrote. Drift detection: a query that recomputes the copy from the source and reports mismatches, run on a schedule. Repair: a way to recompute the copy in bulk, because it *will* drift — from a bug, a manual fix, a migration.

Write those three down next to the column. A denormalised value without a documented maintainer is a bug that has not happened yet.

A counter cache with its maintainer and its repair
1-- maintain in the same transaction, every path
2CREATE FUNCTION bump_follower_count() RETURNS trigger AS $$
3BEGIN
4  IF TG_OP = 'INSERT' THEN UPDATE users SET follower_count = follower_count + 1 WHERE id = NEW.followee_id;
5  ELSE                     UPDATE users SET follower_count = follower_count - 1 WHERE id = OLD.followee_id;
6  END IF;
7  RETURN NULL;
8END $$ LANGUAGE plpgsql;
9CREATE TRIGGER follows_count AFTER INSERT OR DELETE ON follows
10  FOR EACH ROW EXECUTE FUNCTION bump_follower_count();
11 
12-- repair (nightly, or on demand)
13UPDATE users u SET follower_count = c.n
14FROM (SELECT followee_id, count(*) AS n FROM follows GROUP BY followee_id) c
15WHERE c.followee_id = u.id AND u.follower_count <> c.n;

Hot counters

A counter cache on a hot row is its own problem: every like on a viral post is an UPDATE posts SET like_count = like_count + 1 on the *same row*, and row locks serialise them. At a few hundred per second the lock queue becomes the bottleneck. Solutions in increasing order of complexity: batch the increments in the application, shard the counter across N rows and sum on read, or move the counter to Redis and flush periodically — see Redis: Data Structures, Not a Cache.

Key points

  • Denormalize a specific value for a specific read; never un-normalise the source of truth.
  • Counter caches, last-x columns, rollups and materialised views are the usual forms. Snapshot columns are history, not redundancy.
  • Every copy needs a write path (trigger or same-transaction update), a drift check, and a repair query.
  • Hot counters serialise on row locks; batch, shard or offload them.

Try it in the playground

SELECT u.id, u.handle, u.follower_count AS cached, count(f.id) AS actual
FROM users u LEFT JOIN follows f ON f.followee_id = u.id
GROUP BY u.id, u.handle, u.follower_count
HAVING u.follower_count <> count(f.id)
ORDER BY abs(u.follower_count - count(f.id)) DESC LIMIT 10;
The drift check every counter cache needs. →

When to use — and when not

Use it when
  • A read that is orders of magnitude more frequent than the writes that would invalidate it.
  • Aggregates over data too large to compute on every request.
Avoid it when
  • When a well-indexed query is already fast enough — measure first.
  • When you cannot control every write path.

Failure modes

  • A code path that updates the source and forgets the copy.
  • No drift check, so the wrong number is trusted for months.
  • A counter on a hot row becoming the write bottleneck.
← Normalization: 1NF to BCNF