When to denormalise on purpose

Understanding When to denormalise on purpose

Normalising to third normal form gives every fact exactly one home, which is why a single-row UPDATE can never leave two copies disagreeing. Denormalising on purpose gives that up in exchange for something you can name: a read that no longer joins, an aggregate that no longer scans thousands of child rows, or a value that must stay put while its source changes. The trade is not really speed against disk space; it is read cost against a synchronisation duty that you now own and the database no longer performs for you.

Two very different things get called denormalisation. Derived data - a reply count, a running balance, a pre-joined reporting row - is fully reconstructible from base tables, so the worst outcome is staleness and the repair is a recompute. A copied source value such as the unit price on an invoice line is not duplication at all: the price a customer was charged is a different fact from today's catalogue price, and joining to product to fetch it would rewrite history every time someone edits a number. The dangerous middle case is copying a mutable attribute that still lives authoritatively somewhere else with no rule about which copy wins.

Before adding the column, prove the join is the problem; a missing index on the foreign key produces exactly the same symptoms and costs nothing to fix. Then decide where maintenance lives: triggers and generated columns sit in the database where every writer is subject to them, while application code only holds if there is genuinely one write path, which stops being true the first time somebody runs a backfill script. Whatever you choose, keep a reconciliation query that recomputes the value from base tables and compares, because that is the only thing turning "should stay in sync" into something you can check. Then price the write side: every child insert now also updates a parent row, so a popular parent becomes a contention point.

In short, a redundant column is a debt with an interest rate paid on every write, and the enforcement mechanism is what stops that debt from turning into wrong answers.

CREATE TABLE thread (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  reply_count INTEGER NOT NULL DEFAULT 0   -- derived from post, stored on purpose
);

CREATE TABLE post (
  id        INTEGER PRIMARY KEY,
  thread_id INTEGER NOT NULL REFERENCES thread(id),
  body      TEXT NOT NULL
);

CREATE TRIGGER post_ai AFTER INSERT ON post BEGIN
  UPDATE thread SET reply_count = reply_count + 1 WHERE id = NEW.thread_id;
END;

CREATE TRIGGER post_ad AFTER DELETE ON post BEGIN
  UPDATE thread SET reply_count = reply_count - 1 WHERE id = OLD.thread_id;
END;

INSERT INTO thread (id, title) VALUES (1, 'Index bloat'), (2, 'Vacuum settings');

INSERT INTO post (id, thread_id, body) VALUES
  (1, 1, 'Try REINDEX'),
  (2, 1, 'Check fillfactor'),
  (3, 2, 'Autovacuum handles it');

DELETE FROM post WHERE id = 2;

-- the read the column exists for: no join, no aggregate
SELECT id AS thread_id, title, reply_count FROM thread ORDER BY id;

-- the invariant that keeps it honest
SELECT COUNT(*) AS drifting_threads
FROM thread
WHERE reply_count <> (SELECT COUNT(*) FROM post WHERE post.thread_id = thread.id);

Worked examples

CREATE TABLE product (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE order_item (
  order_id         INTEGER NOT NULL,
  product_id       INTEGER NOT NULL REFERENCES product(id),
  qty              INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,   -- what was charged, frozen at sale time
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO product VALUES (1, 'Keyboard', 4000);
INSERT INTO order_item VALUES (100, 1, 2, 4000);

UPDATE product SET price_cents = 5500 WHERE id = 1;

SELECT oi.order_id AS order_id,
       oi.qty * oi.unit_price_cents AS invoiced_cents,
       oi.qty * p.price_cents       AS if_we_joined_instead
FROM order_item oi
JOIN product p ON p.id = oi.product_id;

CREATE TABLE customer (
  id     INTEGER PRIMARY KEY,
  region TEXT NOT NULL
);

CREATE TABLE sale (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customer(id),
  region      TEXT NOT NULL,   -- copy of customer.region, maintained by nobody
  amount      INTEGER NOT NULL
);

INSERT INTO customer VALUES (1, 'EU'), (2, 'US');
INSERT INTO sale VALUES (1, 1, 'EU', 100), (2, 2, 'US', 250);

UPDATE customer SET region = 'US' WHERE id = 1;

SELECT 'from sale.region' AS source, region, SUM(amount) AS total
FROM sale
GROUP BY region
UNION ALL
SELECT 'from customer.region', c.region, SUM(s.amount)
FROM sale s JOIN customer c ON c.id = s.customer_id
GROUP BY c.region
ORDER BY source, region;

Important notes

Common mistakes

Try it yourself