When to denormalise on purpose

Denormalisation is storing a fact in more than one place on purpose, to make a read faster.

It is a trade, and the currency is correctness. Every copy you make is a copy that can go out of sync. So the rule is: denormalise when you have measured a problem, and prefer a mechanism the database keeps in step for you over one you maintain by hand.

That last clause is the real content of this lesson. PostgreSQL gives you four ways to denormalise safely, and most people reach for the unsafe one.

When it is justified

Four situations, and they all start with a measurement.

A count or sum you read constantly. The library's "books currently on loan" appears on every page. count(*) over 180 loans is nothing; over 50 million it is not.

An aggregate over a large history. "Total borrowed this year per member" scanning millions of rows per page load.

A join that is genuinely expensive. Measured, in the plan, not assumed. Most joins on indexed foreign keys are cheap.

A value that must be frozen in history. This one is different and important — see below.

And when it is not justified: because the schema "feels" slow; because someone said joins are slow; before you have run EXPLAIN; on a table with 10,000 rows.

The special case: history must not change

CREATE TABLE order_lines (
  order_id bigint NOT NULL REFERENCES orders(id),
  product_id bigint NOT NULL REFERENCES products(id),
  quantity int NOT NULL,
  unit_price_paise int NOT NULL,      -- a copy of products.price_paise
  product_name text NOT NULL          -- a copy of products.name
);

That looks like a 3NF violation, and formally it is. It is correct anyway.

The price on an order line is not "the product's price". It is the price this customer paid on that day, which is a different fact that happens to have started as a copy. Join to products for it and last month's invoices change when you raise a price — which is a reporting bug, an accounting problem, and in many jurisdictions illegal.

The test: is this a copy of a current fact, or a record of a past one? A copy of a current fact is denormalisation with a synchronisation problem. A record of a past one is just a fact, and normalisation has nothing to say about it.

Same reasoning: the delivery address on a shipped order, the tax rate applied, the name on an issued certificate.

Four safe mechanisms, worst to best

Counter columns kept by triggers

The classic, and the one people write first:

ALTER TABLE books ADD COLUMN active_loans int NOT NULL DEFAULT 0;

CREATE FUNCTION sync_active_loans() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE books SET active_loans = active_loans + 1 WHERE id = NEW.book_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE books SET active_loans = active_loans - 1 WHERE id = OLD.book_id;
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.returned_on IS NULL AND NEW.returned_on IS NOT NULL THEN
      UPDATE books SET active_loans = active_loans - 1 WHERE id = NEW.book_id;
    ELSIF OLD.returned_on IS NOT NULL AND NEW.returned_on IS NULL THEN
      UPDATE books SET active_loans = active_loans + 1 WHERE id = NEW.book_id;
    END IF;
    IF OLD.book_id <> NEW.book_id THEN
      -- and now you are writing the fiddly part
      UPDATE books SET active_loans = active_loans - 1 WHERE id = OLD.book_id;
      UPDATE books SET active_loans = active_loans + 1 WHERE id = NEW.book_id;
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_active_loans
AFTER INSERT OR UPDATE OR DELETE ON loans
FOR EACH ROW EXECUTE FUNCTION sync_active_loans();

Look at how much of that is edge cases — and it is still not complete. It does not handle TRUNCATE. And there are two more problems:

Write contention. Every loan on a popular book updates the same books row, so concurrent borrowers serialise on it. On a hot row this becomes your bottleneck, and it is the lost-update territory from module 6.

It will drift. A bulk load with triggers disabled, a bug in a branch, a restore. You need a reconciliation job:

UPDATE books b SET active_loans = c.n
FROM (SELECT book_id, count(*) AS n FROM loans WHERE returned_on IS NULL GROUP BY book_id) c
WHERE b.id = c.book_id AND b.active_loans <> c.n;

If you write a counter column, write the reconciliation query at the same time and schedule it. Not later.

Generated columns, for anything derivable from the same row

ALTER TABLE loans ADD COLUMN days_out int
  GENERATED ALWAYS AS (returned_on - borrowed_on) STORED;

The database computes it and you cannot write to it. It cannot drift, because there is nothing to keep in step — it is derived at write time from the row itself:

ERROR:  column "days_out" can only be updated to DEFAULT
DETAIL:  Column "days_out" is a generated column.

Only works for the same row, and the expression must be IMMUTABLE, so no now():

ERROR:  generation expression is not immutable

That second error is worth meeting deliberately, because "days overdue" is the obvious thing to want and it is exactly what you cannot have — the answer changes every midnight without the row changing. Compute it in the query, or in a view. PostgreSQL 18 added VIRTUAL generated columns, computed on read, for when you would rather not pay the storage.

Use this whenever it applies. It is denormalisation with the risk removed.

Materialised views, for whole aggregates

CREATE MATERIALIZED VIEW member_loan_stats AS
SELECT m.id AS member_id, m.name, count(l.id) AS total_loans,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS open_loans,
       max(l.borrowed_on) AS last_borrowed
FROM members m LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

CREATE UNIQUE INDEX ON member_loan_stats (member_id);     -- required for the next line

REFRESH MATERIALIZED VIEW CONCURRENTLY member_loan_stats;

A real table holding a query's result, which you can index. The data is as old as the last refresh — that is the whole trade-off, so it suits dashboards and reports and does not suit a balance you must not overdraw.

Skip that index and the refresh refuses, helpfully:

ERROR:  cannot refresh materialized view "public.member_loan_stats" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

Two things worth knowing: CONCURRENTLY requires a unique index and is the difference between a refresh that locks readers out and one that does not. And a plain REFRESH on a big view can take minutes, during which the view is unreadable.

An ordinary VIEW, by contrast, is just a stored query — no copy, no staleness, no denormalisation at all. Try a plain view first; it often reads as nicely as the denormalised thing while being free.

Rollup tables, for large time-series aggregates

CREATE TABLE daily_loan_counts (
  day date PRIMARY KEY,
  loans_started int NOT NULL,
  loans_returned int NOT NULL
);

Filled by a nightly job. Once a day is over its numbers never change, so there is nothing to keep in sync — the same property that makes generated columns safe.

For anything time-based at scale this beats a materialised view, because you only ever compute the new day rather than re-aggregating history. It is what the time-series lesson in module 12 generalises.

The one to avoid

-- in application code
def borrow_book(book_id, member_id):
    db.execute("INSERT INTO loans (...) VALUES (...)")
    db.execute("UPDATE books SET active_loans = active_loans + 1 WHERE id = %s", book_id)

Denormalisation maintained by application code is the version that breaks, because there is always a second code path — an admin script, a data migration, a bulk import, another service, a psql session at 2am. Any one of them writes to loans without knowing about the counter.

If you must, at least put both statements in one transaction so they cannot half-succeed. But prefer a trigger, which every writer gets for free.

Choosing

Check your work

What denormalisation trades. Read speed for correctness risk.

The four justifications. A constantly-read count, a large aggregate, a measured expensive join, a value frozen in history.

Why an order line stores the price. It is the price paid then, a different fact — joining would rewrite history.

The test that distinguishes the two cases. Is it a copy of a current fact, or a record of a past one?

Two problems with a trigger-maintained counter. Write contention on a hot row, and drift.

What to write at the same time as a counter column. The reconciliation query, scheduled.

Why a generated column cannot drift. It is derived from the same row at write time and cannot be written to.

Its limitation. Same row only, and the expression must be IMMUTABLE — so "days overdue" cannot be one.

What a materialised view costs. Staleness, and a locking refresh unless CONCURRENTLY.

What REFRESH ... CONCURRENTLY requires. A unique index on the view.

The difference between a view and a materialised view. A view is a stored query with no copy and no staleness.

Why a rollup table is safe. A finished day's numbers never change.

Why application-maintained denormalisation fails. There is always another writer that does not know about it.

What to do before denormalising at all. Measure, and try a plain view.

Practice

1. Add an active_loans counter with the trigger above and verify it against count(*).
2. Break it: disable the trigger, insert loans, re-enable it. Run the reconciliation query.
3. Have two sessions borrow the same book simultaneously and observe the contention on the books row.
4. Change a loan's book_id and check whether your trigger handled it.
5. Add a days_out generated column and try to UPDATE it. Read the error.
6. Try to create a generated column using now(). Read the error and explain it.
7. Build member_loan_stats as a plain view and time a query against it.
8. Build it as a materialised view and time the same query. Compare.
9. Refresh it without CONCURRENTLY while another session reads it. Observe the block.
10. Add the unique index and refresh CONCURRENTLY. Confirm the reader is not blocked.
11. Insert a loan and query the materialised view without refreshing. Note the stale answer.
12. Build daily_loan_counts and the job that fills yesterday's row.
13. Design an order_lines table and argue, in writing, for each copied column.
14. Find a denormalised column in a real project and work out which mechanism maintains it — and whether anything reconciles it.

Official documentation

• PostgreSQL — Generated columns — Stored and virtual, and the immutability requirement.
• PostgreSQL — Materialized views — How they differ from ordinary views.
• PostgreSQL — REFRESH MATERIALIZED VIEW — The CONCURRENTLY requirements and locking behaviour.
• PostgreSQL — Triggers — When each kind fires, which decides whether your counter is correct.
• PostgreSQL — CREATE VIEW — Including updatable views, which sometimes remove the need for any of this.

Next: modelling a real domain from nothing.