When Does Denormalization Win?

It wins when a specific read is the bottleneck and you have measured that it is. The pattern that reliably works is to add a column or a summary table that serves one expensive query, rather than to denormalise a whole schema.

-- The normalised source of truth.
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Ada Lovelace', 'London'),
  (2, 'Grace Hopper', 'New York');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- The denormalised read model: the city is copied in.
CREATE TABLE orders_read_model (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_city VARCHAR(80) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders_read_model (id, customer_id, customer_name, customer_city, amount)
SELECT o.id, o.customer_id, c.name, c.city, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

SELECT * FROM orders_read_model ORDER BY id;

Three rows come back, each with the customer name and city already on the order row. The read query no longer has to join anything.

Denormalisation wins in four recognisable situations. The read is far more frequent than the write, and the join is expensive — a feed, a search index, a report that runs every few minutes. The table is huge and the join is over billions of rows, so even a small per-row cost dominates. The read crosses a network or shard boundary, so assembling the answer requires a remote call per row and the duplicated copy makes it local. And the read has to be served from a cache anyway, in which case the shape of the cached row is what you should optimise for.

What it costs is consistency, and the cost depends entirely on how you keep the copy in step. Update it in the same transaction as the source and the write path gets slower but the copy is never stale. Update it asynchronously and the copy can lag by however long the job takes, so you have to decide whether that matters and how anyone finds out. Never update it and it becomes a snapshot, which is fine for a historical report and wrong for anything current.

One table with the duplicated value, and the query that shows what the duplication buys you.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders_denormalised (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_city VARCHAR(80) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open'
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders_denormalised (id, customer_id, customer_name, customer_city, amount) VALUES
  (1, 1, 'Ada Lovelace', 'London',   120.00),
  (2, 1, 'Ada Lovelace', 'London',   340.50),
  (3, 2, 'Grace Hopper', 'New York',  75.25);

-- No join needed for the common read.
SELECT id, customer_name, customer_city, amount
FROM orders_denormalised
WHERE customer_city = 'London'
ORDER BY amount DESC;

Two rows come back: Ada's 340.50 order and her 120.00 order. The filter on the city and the sort both read directly from the table, with no join and no second table to touch.

Note that the denormalised table has lost a constraint. In the normalised version, customer_id is a foreign key and the customer table is the single source of truth for the name. Here, the name and the city are just values on the order row — nothing stops an order from saying 'Grace Hopper' with a customer_id of 1, and nothing updates the copies when Ada moves to Cambridge.

That is the trade in one sentence: you have bought a fast read and sold the guarantee that the data is self-consistent. Whether that is a good trade depends entirely on how the copy is maintained. If you can update it in the same transaction as the source, the copy is never stale and the trade is good. If it is refreshed by a background job, you have introduced a window of inconsistency and you need a way to detect and repair drift.

One design point worth raising: notice that this table keeps customer_id even though the name and city are duplicated. Keeping the key means you can still join back to the source, repair the row, and detect which copy is wrong. Dropping it would leave you with a row that cannot be traced back to its owner, which is exactly the situation where a denormalised copy becomes unrecoverable.

There are three strategies, and the right one depends on how stale the copy is allowed to be.

Synchronous update in the same transaction. The write that changes the source also changes the copy, in the same transaction, so the two can never disagree. This is the strongest guarantee and the most expensive one: every write path that touches a denormalised column has to remember to update it, and the rule gets duplicated across code that does not know about it. It is only safe when the number of places that write the source is small and controlled — for example a single stored procedure — or when the copy is maintained by a database trigger, which is the only way to catch every write path without relying on discipline.

Asynchronous refresh. A job periodically rebuilds or updates the copy from the source. The copy can be stale by up to the job interval, so you have to decide whether that is acceptable and how anyone finds out. This is the standard pattern for a read model serving a dashboard, where a minute of lag is fine. The important parts are making the refresh idempotent, recording the last refresh time so readers can see the freshness, and having a way to detect and repair drift.

Event-driven invalidation. A change to the source emits an event, and a consumer updates the copy. This scales well and keeps the write path fast, but it introduces eventual consistency and the possibility of lost or reordered events. It needs deduplication on the consumer side, because the same event will sometimes arrive twice.

Whichever you choose, two practical safeguards matter. Keep the foreign key in the denormalised row so you can trace it back to the source and repair it. And write a reconciliation query that compares the copy against the source and reports the differences — a count of rows where the duplicated value does not match. That query is your drift detector, and running it on a schedule is what turns an invisible consistency problem into a visible one.