Denormalization

Tenant labels stopped leaks, but the homepage still joins ten thousand times a second

Multi-tenancy ended with Row Level Security, which is a database feature that silently adds the tenant condition to every query, making a forgotten filter return nothing instead of the world. Labels stopped leaks, but they never removed work. Every order page still needs the buyer's name. The tidy design, which is called normalization and means storing every fact in exactly one place, keeps that name only in the users table, so each order page combines the orders table with the users table at request time. That combining step is called a join. At a hundred checkouts a day nobody notices the join. At ten thousand order views a second, the database runs that same reunion ten thousand times a second, and most of its work becomes fetching a name it already fetched a millisecond ago.

The first instinct is to make the box bigger or to cache the page, and both help for a while. A second tempting fix is a materialized join table maintained by triggers on every write, and it fails on write amplification measured cold: each of the 10 username changes a day fans out fine, but each of the 864 million daily reads still pays trigger-maintained rows that bloat the table by 40 bytes per row, roughly 4 GB per 100 million rows, while every new writer must remember the trigger or the copy drifts silently. A bigger box still runs the same join, just on faster hardware, so the cost per read never drops. A cache helps only when the same page is requested twice before anything changes, which breaks down the moment every user sees their own orders. When the join itself is the work, caching the result of the join still pays the join on every miss, and misses dominate on personalized pages.

Denormalization, which means deliberately storing some facts in two places, copies the name into the orders table too. The join vanishes because the read finds everything in one row. Think of a restaurant that prints the day's soup on each table's menu instead of making every waiter walk to the board: one analogy for the whole idea, and the rest is mechanics. The read gets fast and a new permanent job appears, which is keeping both copies honest with each other.

The same order stored two ways, and what each way costs

A transaction, which is a group of writes that succeed or fail together so the database never holds half an update, is the tool that keeps the two designs honest. In the normalized design the write is a single row insert, and the read pays the join. In the denormalized design the write must update two places inside one transaction, and the read pays nothing extra. That shift of cost from read time to write time is the entire decision.

Copying pays when one field is read thousands of times per write

The decision is a ratio of reads to writes on the copied field, not a general preference for speed. Walk the arithmetic: the order page serves 10,000 reads per second, which is 864 million reads per day, while usernames change perhaps ten times a day. Keeping the join means paying the join cost 864 million times. Copying the name means paying a dual-write, which is updating both tables together, ten times a day plus a few extra bytes on every row. When the ratio is roughly a hundred million to one, the copy wins by so much that the storage cost barely registers.

Four ways to keep the copy honest, ordered by how much machinery they need

The cheapest correct copy is a duplicated column updated in the same transaction as the original, because the database guarantees both changes land together or neither does, so readers never see half an update. When the copy cannot live in the same transaction, for example because it sits in a different table updated by a background worker, the writer records the change in an outbox, which is a durable to-do row written alongside the real update, and a separate worker replays that to-do until the copy converges. That worker must be idempotent, which means running it twice has the same effect as running it once, or retries will double-count.

Every copy charges writes, storage, and attention, so price all three

Storage first: if the denormalized row grows from 200 bytes to 240 bytes, that is 40 extra bytes per row. Across 100 million rows the extra cost is 100,000,000 times 40 bytes, which is 4,000,000,000 bytes or roughly 4 GB. Four gigabytes to eliminate 864 million joins a day is an easy trade. Writes second: every rename now touches the users row plus every order row for that user, so a user with 5,000 orders turns one write into 5,001 writes, which is called write amplification. When the copied field changes rarely the amplification is invisible, and when it changes constantly it dominates the system. Attention third: the copy drifts silently, with no error and two plausible-looking values, whenever any code path updates one side without the other.

Run the read-write ratio before copying, using three concrete patterns

Flip the earlier ratio and the answer flips too. A live account balance copied into every transaction row but recalculated on each deposit means each deposit fans out to thousands of rows, lock contention climbs, and a single slow replica delays every write. A daily revenue rollup sits in the middle: one batch write per day replaces thousands of dashboard scans, and intraday staleness is acceptable because finance closes books daily anyway. The rule stays mechanical rather than stylistic: denormalize the field only when reads outnumber writes on that specific field by orders of magnitude, measured from production logs rather than guessed.

Drift arrives quietly through the write path you forgot

The copy behaves for months, then a deploy adds a new rename path that updates the users table but skips the backfill queue, and order rows carry last year's usernames from that day forward. The counter variant is slower: the creation path increments the count but a rare refund path deletes without decrementing, so the count drifts upward by a few rows a week and both sides look plausible until finance reconciles and the dashboard sits 3% high. The defense is mechanical rather than hopeful: update both copies in one transaction where they share a database, otherwise write through an outbox with idempotent consumers, expose a lag metric showing the age of the oldest unsynced change, and run a nightly reconciler that recounts the raw rows and repairs the copies.

Materialized views trade freshness for freedom from hand-rolled sync

When the precomputed answer joins millions of rows, maintaining it in application code means every new writer must remember the sync, and the first forgotten writer corrupts the copy. A materialized view moves that burden into the engine, which stores the query result and refreshes it on demand or on a schedule. The refresh still runs the full join, but it runs once per refresh interval instead of once per reader, so a dashboard with 50,000 views a day pays one join instead of 50,000. The lag is the contract the team signs: data is minutes or hours old, concurrent refreshes need disk for both the old and new copy, and anything needing this second's truth must read the raw tables instead.

Copies remove joins from the hot read, but the remaining queries still need to find the right rows without scanning millions. Whether a query touches two hundred rows or ten million usually comes down to one missing structure that tells the engine where to look, which is the index.