Denormalisation

The problem: an expensive read on every page

Snip's dashboard lists an owner's links with each one's total clicks, and lets them sort by it. In the normalised model, totals live only in clicks_daily: one row per link per day. To sort Ana's 8,000 links by total clicks, the database must add up every daily row for all 8,000 links (millions of rows) on every page load.

The answer is the same every time until the next batch of clicks arrives. Computing it again and again is wasted work. Denormalisation stores the answer:

ALTER TABLE links ADD COLUMN total_clicks bigint NOT NULL DEFAULT 0;
CREATE INDEX links_owner_clicks ON links (owner_id, total_clicks DESC);

Now "Ana's links by clicks" is one index range read. The price: the same fact (how many times x7Kp was clicked) is stored in two places, and they must agree.

Three ways to keep the copy right

1. Update it in the same transaction. Whatever writes the source also updates the copy, atomically:

BEGIN;
INSERT INTO clicks_daily (code, day, clicks) VALUES ('x7Kp', '2026-11-04', 1840)
  ON CONFLICT (code, day) DO UPDATE SET clicks = clicks_daily.clicks + EXCLUDED.clicks;
UPDATE links SET total_clicks = total_clicks + 1840 WHERE code = 'x7Kp';
COMMIT;

Always consistent, which is why Snip's stats workers (02.06 Async workers and queues) do exactly this. It only works when both live in the same database, and every write now does more work.

2. Update it from events, asynchronously. The source publishes "x7Kp got 1,840 clicks"; a consumer updates the copy, maybe in another database or a search index. Works across systems; the copy lags, and the consumer must cope with duplicates (07.03 Delivery guarantees and idempotent consumers).

3. Recompute it periodically. A job (or a materialised view) rebuilds the totals every few minutes. Simplest to get right, staleness up to the interval, and the rebuild itself costs work.

Whichever you choose, add a reconciliation job that recomputes a sample (or all) of the copies from the source now and then and reports differences. Copies drift, through bugs, manual fixes or partial failures, and you want to find out from a report, not from a customer.

When it's worth it

Denormalise when all of these hold:

• The read is frequent or on a hot path, and measurably slow or expensive without the copy.
• The copy can be kept correct with one of the methods above, at a write cost you can afford.
• You know which copy is the source of truth if they ever disagree.

Common, well-worn cases: counters (likes, followers, clicks), the latest item (a conversation's last message for the inbox view), names copied onto records for display, and whole read-optimised tables in partition-keyed stores, where copying data into one table per query is the normal pattern (04.10 NoSQL families).

Snip at the end of Module 04

Snip's data is now modelled (04.01 Relational modeling and normalisation), indexed for its queries (04.02 Indexes and B-trees), protected by transactions and the write-ahead log, replicated with a synchronous standby, and its hottest derived number is denormalised. One Postgres primary handles all of it at today's scale. The next module asks what happens when even the largest single primary isn't enough: splitting the data itself (05.01 Sharding: range, hash, directory).

Recap

Interview questions