Architect's notebook: when to denormalise

Denormalising trades write complexity for read speed, and the bill arrives later. Four questions decide when a copy is worth it, and how to keep it honest.

Published

Updated

Reading time

11 min

Denormalise only a value you can rebuild from its source, and write the query that checks the copy against the source before you write the code that maintains it. Everything else in this entry, the four questions, the patterns, the code, is a way of making that rule cheap to follow. A copy that is sometimes wrong is worse than a join that is sometimes slow, because the slow query announces itself and the wrong number does not.

Normalised data keeps each fact in one place. Writes are simple and correct, and reads pay with joins, lookups or extra queries. Denormalising stores a fact in more than one place, so reads get cheaper and the cost moves to the write side and to correctness. Something has to keep the copies aligned, and that something fails: a trigger disabled during a data fix, a worker that crashes halfway, a code path nobody updated. So the question is never "joins are slow, therefore copy". It is a trade between a read cost you can measure and a consistency cost you have to own.

The best argument against any copy#

The strongest objection is that you should not do it at all. Read the data with a join, put a cache in front if it is hot, and accept the latency. A cache has an explicit expiry, which is an honest statement about staleness, and when it is wrong the damage is bounded by a time limit. A denormalised column is wrong until someone notices.

That argument is correct more often than not, and it is why the first step below is to measure and the second is to try cheaper things. Where it stops holding is when the read is hot enough, and the value cheap enough to maintain, that a cache's expiry is the wrong tool: a counter that must change the moment a user acts, or a field that a query has to filter or sort on, which a cache in front of the application cannot help with. Then the copy is the right answer, provided it comes with its own check.

Four questions before the first column#

I ask these in order and stop at the first no.

1. Is the read actually the problem, and has it been measured? Run the query with EXPLAIN (ANALYZE) on production-sized data, or read the real latency percentile for the endpoint. Many slow joins are missing indexes or fetch columns nobody uses. Fix those first, since they cost nothing in consistency.

2. Do reads far outnumber writes for this fact? A copy pays off when the fact is read many times for each time it changes. A follower count shown on every profile view and changed a few times a day is a good candidate. A field that changes on every request and is read once is not: you pay the maintenance on every write and rarely collect.

3. Can the reader tolerate a stale value, and for how long? This question picks the pattern. A comment count that is off by one for a second is fine. An account balance at checkout is not. If the answer is never stale, the copy has to update in the same transaction as the source, which leaves triggers and transactional writes.

4. Can the copy be rebuilt from the source at any time? If yes, the worst failure is a temporary wrong value a job can repair. If no, because the copy has become the only record of something, you have not denormalised; you have created a second source of truth. Stop and redesign.

If question 3 or 4 comes out badly, look at caching, which gives the same read benefit with an explicit expiry, as in refresh-on-read instead of a cron job.

Write the repair query first#

Every derived value ships with a query that recomputes it from the source and compares. This is the part that gets skipped, and the part that makes the rest safe. For a comment counter on posts:

repair/comment_count_drift.sqlsql
SELECT p.id, p.comment_count AS stored, count(c.id) AS actual
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
HAVING p.comment_count <> count(c.id);

Run it on a schedule against a replica, or at a quiet hour, and alert on a non-empty result. A few rows of drift mean a code path bypasses the maintenance. A flood means a deploy broke it. Either way you learn from a query and not from a user. If the numbers must be right, the same job can fix them, but I alert first and repair by hand until the cause is understood, because an auto-repair job hides the bug that is producing the drift.

Writing it first has a second benefit: it forces you to say what the source of truth is. If you cannot write the query, the answer to question 4 was no.

Match the mechanism to the tolerable staleness#

PatternFreshnessWho maintains itWrite costTypical use
Compute at read timeAlways currentNobodyNoneThe default; keep it until measurements say otherwise
Trigger-maintained columnSame transactionThe databaseExtra update per write, row lock on the parentCounters, denormalised totals
Materialised viewAs of the last refreshA scheduled refreshOne refresh covering all rowsReports, dashboards, rankings
Application-maintained copyEventually consistentYour code or a workerFan-out writesDocument stores, copied display fields

Strict freshness points at the first two rows. Tolerable staleness opens the other two, which are usually cheaper to run.

The copy is the last step, not the first, and it never replaces the source. The repair job is part of the design from day one.

A counter that survives updates and deletes#

A post page shows its comment count. Counting comments on every view scans an index range per post, and users expect the number to move the moment they comment, which points at a trigger. The easy version handles inserts and deletes and misses a third way a comment can leave a post: an UPDATE that changes post_id, for example when a moderator moves a comment to another thread. The old post keeps a count that is too high, the new one too low, and the drift is permanent. The trigger has to cover all three operations:

migrations/0007_comment_count.sqlsql
ALTER TABLE posts ADD COLUMN comment_count integer NOT NULL DEFAULT 0;
 
CREATE FUNCTION maintain_comment_count() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  ELSIF TG_OP = 'UPDATE' AND OLD.post_id IS DISTINCT FROM NEW.post_id THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  END IF;
  RETURN NULL; -- the return value of an AFTER row trigger is ignored
END
$$;
 
CREATE TRIGGER comments_maintain_count
AFTER INSERT OR DELETE OR UPDATE OF post_id ON comments
FOR EACH ROW EXECUTE FUNCTION maintain_comment_count();
 
-- Backfill from the source of truth.
UPDATE posts p
SET comment_count = (SELECT count(*) FROM comments c WHERE c.post_id = p.id);

UPDATE OF post_id makes the trigger fire only when that column appears in the statement's SET list, and the IS DISTINCT FROM test handles the case where the value is rewritten unchanged. The syntax is in the CREATE TRIGGER reference. Row triggers do not fire on TRUNCATE, so emptying the comments table leaves stale counts; the repair query is what catches that.

The trigger runs in the same transaction as the write, so the count and the comment commit together or not at all. Two costs come with it. The first is a row lock: every comment on one post updates the same posts row, and the lock is held until the transaction ends, so a burst of comments on a viral post serialises on that row. The answer then is a separate counter table with several rows per post, summed on read, or a store built for counters. The second is the backfill. On a large table the single UPDATE rewrites every post and holds locks throughout. Add the trigger first, so new writes are counted, then backfill in batches by id range outside the migration. The backfill assigns absolute counts, so it is safe to overlap with the trigger, and the repair query confirms the result.

A report that is allowed to be five minutes old#

When a figure can lag and is expensive to compute, a materialised view moves the cost out of the read path.

migrations/0008_author_stats.sqlsql
CREATE MATERIALIZED VIEW author_stats AS
SELECT author_id,
       count(*)        AS post_count,
       max(created_at) AS last_post_at
FROM posts
GROUP BY author_id;
 
-- Required for REFRESH ... CONCURRENTLY.
CREATE UNIQUE INDEX author_stats_author_id_idx ON author_stats (author_id);
refresh.sqlsql
REFRESH MATERIALIZED VIEW CONCURRENTLY author_stats;

The REFRESH MATERIALIZED VIEW reference documents what matters. CONCURRENTLY refreshes without blocking concurrent selects, but needs at least one unique index on plain columns with no WHERE clause, and the view must already be populated. Only one refresh can run against a view at a time. A plain refresh is cheaper but blocks readers while it runs, so choose by whether anyone reads the view during the refresh window.

A refresh is a full recompute, so its cost grows with the data, not with the number of changes. When that hurts you have outgrown the pattern, and a trigger-maintained summary or an incremental job is next. The view needs no separate repair job: the next refresh is the repair.

A copied field in Firestore#

Document stores push you toward copies because there are no joins. A post document shows its author's display name, so the name is copied into each post at write time, and a feed read costs one query instead of one plus a lookup per author, as discussed in Firestore data modelling for a million users. The bill arrives when the source changes: rename an author and every post carrying the old name needs an update.

src/profile/propagate-display-name.tsts
import { Firestore, type QueryDocumentSnapshot } from "@google-cloud/firestore";
 
const db = new Firestore();
 
export async function propagateDisplayName(authorId: string, displayName: string) {
  const writer = db.bulkWriter();
  const failed: string[] = [];
  const posts = db.collection("posts").where("authorId", "==", authorId);
 
  for await (const doc of posts.stream() as AsyncIterable<QueryDocumentSnapshot>) {
    if (doc.get("authorName") === displayName) continue; // idempotent: skip finished work
    writer
      .update(doc.ref, { authorName: displayName })
      .catch(() => {
        failed.push(doc.ref.path);
      });
  }
 
  await writer.close(); // flushes and waits for every queued write
 
  if (failed.length > 0) {
    // Let the queue redeliver: the skip check above makes a rerun cheap.
    throw new Error(`display name propagation failed for ${failed.length} posts`);
  }
}

Each update() returns a promise that rejects if the write ultimately fails, and a rejection nobody handles can take the process down or, worse, vanish. Catching each one and throwing after close() turns a partial failure into a retried job. The skip check makes the retry safe, which matters because this runs from a queue and queues deliver more than once, as covered in idempotent Pub/Sub consumers.

The copy lags the source by however long the job takes, so the interface has to accept an old name on a post for a short while. If that is unacceptable for a field, do not copy it. Each propagated update is also a billed write, so estimate the write amplification before you ship: the number of copies one change touches, times how often it changes, against the Firestore pricing. The repair query here is a scan that compares each post's authorName with the author's current profile, run in batches.

Where the position stops holding#

Copies multiply. The first is easy to reason about; by the fifth nobody can say which fields derive from which. Keep a short list in the repository of every derived field, its source and its repair query, and review it when the schema changes. Put maintenance as close to the data as you can, in a trigger or one write function that nothing may call around, since a bulk import, an admin script or a data fix each bypass application logic. Triggers hide work from people who read only application code, so document them beside the table definition and keep them boring. Anything that reads a copy, such as a search index or an export, inherits its lag.

Deletes are harder than inserts. Counters have to go down, copied names have to be cleared when an account is erased, and privacy obligations extend to copies, which is the subject of GDPR deletion as a distributed-systems problem.

And some things should never be copied. Money, permissions and legal state should have exactly one place to live, read through a join or a transaction and cached if you must. Do not denormalise while the data is small: joins on a table that fits in memory are fast, and the consistency problems you would add are permanent. Do not denormalise to fix a missing index. When the answer is "not yet", write down the measurement that would change it and move on.

Share
All articles →

An agent does what the spec says, not what you meant. Scope, named files and an executable check turn a long unattended session into a reviewable diff.

12 min