TopolearnReal systems denormalise; doing it on purpose with a sync strategy is what separates a design from a mess.
Denormalisation stores redundant or derived data to make reads cheaper: a comment_count on posts, a customer's name copied onto invoices, a precomputed daily totals table. It trades write complexity and consistency risk for fewer joins and less computation at read time.
Denormalise when you have a measured read problem that indexes and query tuning didn't solve, or when the copy is semantically a snapshot rather than a duplicate. That second case is important: an invoice should keep the billing address as it was when issued, so copying it isn't redundancy at all — it is a different fact.
Common techniques:
- Counters and aggregates kept on the parent row (
posts.comment_count). - Summary or rollup tables refreshed periodically, or materialized views.
- Embedding a few columns from a related table to avoid a hot join.
- Star schemas in analytics, where dimension data is deliberately flattened.
Every copy needs a plan to stay correct: update it in the same transaction as the source, use a trigger, or accept staleness and recompute on a schedule. Also have a way to rebuild it from the source of truth, because drift will happen. Counters updated by many concurrent writers can become lock hot spots; batching or periodic recomputation can be better than incrementing on every write.
Example
CREATE TABLE post (id int PRIMARY KEY, title text,
comment_count int NOT NULL DEFAULT 0); -- denormalised
CREATE TABLE comment (id int PRIMARY KEY, post_id int REFERENCES post(id), body text);
INSERT INTO post (id, title) VALUES (1, 'Hello');
-- keep the copy in sync in the same transaction as the source write
BEGIN;
INSERT INTO comment VALUES (100, 1, 'First!');
UPDATE post SET comment_count = comment_count + 1 WHERE id = 1;
COMMIT;
-- and keep a way to rebuild it from the source of truth
UPDATE post p SET comment_count = (SELECT count(*) FROM comment c WHERE c.post_id = p.id);
SELECT id, comment_count FROM post; -- 1 | 1Remember
- Denormalise for a measured read problem, not by default
- Snapshots (invoice addresses) are facts, not redundancy
- Every copy needs a sync strategy and a rebuild path
Watch out for
- Adding a cached column with no code path that keeps it updated on every write.