Normalization vs denormalization

Normalization keeps one copy of each fact and assembles answers with joins when you read. Denormalization stores extra copies, or precomputed results, so a read can skip that work. Neither is “better”: a normalized design pays on reads, a denormalized one pays on every write and in the work of keeping copies correct. The skill is knowing which bill is smaller, and if you copy, naming exactly what keeps the copy in sync.

Picture it first: a kirana shop’s price list

A kirana owner keeps prices in one ledger. Customers ask for prices all day, so he also writes them on a board by the counter, and his delivery boy carries a printed list on his bike.

When the price of a 1 kg sugar packet changes, the ledger is one line, but the board and the boy’s list must change too. If he updates the ledger and the board together, the board is never wrong. If he phones the boy afterwards and the line is busy, the boy quotes the old price all week and nobody notices. A careful owner writes each change with a carbon sheet: the carbon slip drops into a tray, and the boy collects the tray every morning, so no change is missed even when the phone is down. On Sundays he compares the board and the list against the ledger and fixes whatever drifted.

Map it back:

• The ledger is the normalized source of truth.
• The board is a denormalized copy in the same database, updated in the same transaction.
• The delivery list is a copy in another system: a search index, cache or read model.
• Phoning after writing is a dual write: two separate writes with no guarantee both happen.
• The carbon slip and tray are a transactional outbox, relayed by change data capture.
• Sunday’s check is the reconciliation job every copy needs.

The core trade

Normalized means one place to write each fact. A rename is one row. Reads that need the fact from another table join for it, using an index to find the matching row.

Denormalized means the fact, or something computed from it, also lives where the read needs it. The read is one lookup. You pay in three ways:

1. More writes. One logical change becomes 1 + N physical writes, where N is the fan-out: how many copies hold the fact.
2. A window of disagreement. Between the source write and the last copy write, readers can see two different answers. With a same-transaction update the window is zero; with anything asynchronous it is seconds or more.
3. Repair code. A code path that forgets the copy, a failed job or a bug leaves copies wrong, so you need a way to detect drift and rebuild.

Back-of-envelope: when a copy pays

A food-delivery app at peak takes 1,200 orders a second with 2.5 dishes each, so 3,000 order lines a second. Three candidate copies:

1. “Ordered 12k times” badges on menu pages. Menus get 30,000 views a second and show 40 dishes each. Counting on the fly, with an index on order_lines(dish_id), reads about 12,000 index entries per dish: 12,000 × 40 = 480,000 per page, × 30,000 pages = 14.4 billion index entries a second. No database does that. A times_ordered column on dishes costs one increment per order line, 3,000 small updates a second, and the menu read gets the number from rows it already fetches. Copy, without hesitation.

2. The restaurant’s name inside each dish’s search document. A search index cannot cheaply join back to restaurants at query time, so the copy is not optional. Fan-out is the number of dishes per restaurant, about 150, and renames are rare, so a rename costs 150 document updates. Copy, fed by CDC.

3. The reviewer’s name on each review. Review lists get 5,000 views a second with 10 reviews each: 50,000 name lookups a second. Through the primary-key index on users, each is a handful of cached page reads (database indexes). Without that index, each lookup scans 50 million users: 50,000 × 50,000,000 = 2.5 trillion rows a second. The problem in that case is the missing index, not the join. With it, the join is cheap, and copying the name would add a rewrite path and stale names for no measurable gain. Keep it normalized.

The rule underneath:

Copy when (reads that need the fact × work saved per read) is far larger than (writes to the source × fan-out × cost per copy write), and the staleness of your sync mechanism is acceptable to the reader.

Try the cheaper fixes first

Every copy is permanent maintenance. Before adding one, rule out fixes that leave a single source of truth:

• An index that matches the query. Most “joins are slow” reports are a missing index on the join or filter column; read the plan before redesigning (query execution plans).
• A covering index. If the index holds every column the query needs, the database can answer from the index alone, which captures much of a copy’s benefit with nothing to sync (composite index design).
• A query rewrite. Fetch 20 orders with one join, not 20 separate lookups (the N+1 query problem), and paginate by key, not by offset.
• Read replicas. If the database is busy rather than the query slow, spread reads across replicas, accepting replication lag.
• A cache. A cache is also a copy, but one with a TTL and no schema change; it suits hot keys that tolerate staleness (caching 101).

Denormalize when the read needs the copied value to be queryable: sorted by (ORDER BY times_ordered), filtered on, or indexed. That is what a cache cannot give you.

The denormalization ladder

Techniques range from “the database keeps it exact” to “a pipeline you run keeps it roughly right”. Climb only as far as you must.

Generated columns: the database does it, within one row

ALTER TABLE order_lines
  ADD COLUMN line_total numeric(10,2)
  GENERATED ALWAYS AS (qty * unit_price) STORED;

PostgreSQL recomputes the value whenever the row changes, and nobody can write it directly. A stored column is computed on write and takes space; a virtual one (the default in current PostgreSQL) is computed on read. The limit is strict: the expression may only use immutable functions and the current row, never another table. So this removes repeated arithmetic, not joins.

Materialized views: a saved query, refreshed when you say

CREATE MATERIALIZED VIEW restaurant_daily_sales AS
SELECT o.restaurant_id,
       (o.placed_at AT TIME ZONE 'Asia/Kolkata')::date AS day,
       count(DISTINCT o.order_id)                      AS orders,
       sum(l.qty * l.unit_price)                       AS revenue
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON restaurant_daily_sales (restaurant_id, day);

-- every 10 minutes, from a scheduler
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant_daily_sales;

The view stores the result like a table, so the dashboard reads a few rows instead of aggregating millions. It cannot be updated directly, and it is only as current as its last refresh. A refresh re-runs the whole query and replaces the contents. A plain REFRESH is usually faster when much has changed but can block readers; CONCURRENTLY keeps readers working, and PostgreSQL only allows it when the view is already populated and has a UNIQUE index on plain columns with no WHERE clause, which is why that index is there. Only one refresh can run on a view at a time, so a refresh that takes 12 minutes cannot run every 10.

Aggregate columns in the same transaction

BEGIN;
INSERT INTO order_lines (order_id, dish_id, qty, unit_price) VALUES (5005, 91, 2, 95.00);
UPDATE dishes SET times_ordered = times_ordered + 2 WHERE dish_id = 91;
COMMIT;

The copy is exact at commit, because both writes succeed or neither does (ACID transactions). The risk is a second code path, such as an admin tool or a bulk import, that inserts lines and forgets the counter. The cost is that every order now also locks a dish row, which matters for popular dishes (see counters below).

Triggers: the same, enforced for every writer

CREATE FUNCTION bump_times_ordered() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE dishes SET times_ordered = times_ordered + NEW.qty
    WHERE dish_id = NEW.dish_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE dishes SET times_ordered = times_ordered - OLD.qty
    WHERE dish_id = OLD.dish_id;
  END IF;
  RETURN NULL;   -- ignored for AFTER row triggers
END $$;

CREATE TRIGGER order_lines_times_ordered
AFTER INSERT OR DELETE ON order_lines
FOR EACH ROW EXECUTE FUNCTION bump_times_ordered();

A trigger runs inside the writer’s transaction, so it is as exact as the hand-written version and no code path can skip it. PostgreSQL’s own docs show this pattern for maintaining a summary table. You pay in visibility: the work is hidden from whoever writes the INSERT, a bulk load of 10 million lines fires 10 million row triggers (statement-level triggers with transition tables are the batch-friendly variant), and a change of qty would need a third branch.

Read models fed by CDC or an outbox

When the copy lives in another store (a search index, a Redis leaderboard, another service’s database), no local transaction can cover it. The source database publishes changes, and a consumer applies them to the copy: change data capture reads the database’s log, or a transactional outbox records an explicit event in the same transaction. Separating the write model from read models this way is the read side of CQRS. Martin Fowler warns that for most systems CQRS adds risky complexity, so apply it to the one part that needs it, not the whole system.

Document embedding

A MongoDB document that embeds a copy of shared data (the restaurant’s name inside every order document) is a denormalized copy. MongoDB’s guidance sorts duplicated data into four kinds: immutable data (safe to copy), historical values such as the address at order time (snapshots, never updated), staleness-sensitive data, which needs transactions or triggers to update every occurrence, and staleness-tolerant data, which a background job can refresh. It also notes that if duplicated data changes often, a reference may be the better choice.

Warehouse star schemas

The furthest copy of all: operational data loaded in batches into a separate analytical store and reshaped into fact and dimension tables. It is as fresh as the last load and is rebuilt by the ETL job; the OLTP vs OLAP section below covers why.

Keeping copies in sync

There are four ways to keep a copy in step with its source, from strongest to weakest:

1. Same transaction. Atomic and exact, but only when source and copy live in one database, and it puts the copy’s cost and locks on the write path.
2. Trigger. Same guarantees, enforced for every writer, but hidden and still limited to one database.
3. CDC or outbox. Asynchronous and eventually consistent, but able to reach any store, and it survives the copy being down.
4. Dual write from the application. Write the database, then write the copy. This is the classic bug.

The dual-write bug

Two writes to two systems cannot share one transaction. The Debezium team’s write-up of the outbox pattern explains why for the common case: Kafka cannot join a distributed (XA) transaction with your database. Any failure between the two writes (a timeout, a deploy, an out-of-memory kill) leaves one done and one not, and nothing remembers to finish the job.

The outbox fix moves the second write inside the first transaction:

BEGIN;
UPDATE restaurants SET name = 'Dosa Point' WHERE restaurant_id = 9;
INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload)
VALUES (gen_random_uuid(), 'restaurant', '9', 'RestaurantRenamed',
        '{"restaurant_id": 9, "name": "Dosa Point"}');
COMMIT;

Either both rows commit or neither does. A relay, usually log-based CDC such as Debezium, reads committed outbox rows from the write-ahead log and publishes them; the search indexer consumes them. Three rules come with it:

• Delivery is at least once. A relay or consumer that crashes before acknowledging will see some events twice, so consumers must be idempotent, for example by recording processed event IDs (effectively-once consumers).
• Order per key. Key events by aggregate_id so a rename and a later rename of the same restaurant arrive in order.
• Readers of the copy see the past. Show “updated a few seconds ago” where it matters, or read from the source for the user who just made the change.

CDC directly on the restaurants table works too and needs no application change; the outbox gives you a deliberate event shape instead of raw row changes.

Counters under concurrency

Denormalized counters are the most common copy and the most common source of concurrency bugs.

Lost updates. If the application reads times_ordered = 1200, adds 1 and writes 1201, two concurrent orders both write 1201 and one increment is gone. Let the database do the arithmetic (SET n = n + 1), lock the row first, or compare-and-set on a version (lost update problem).

Hot rows. The in-place increment is correct, but every transaction that updates a row holds its lock until it commits, so increments to one row run one at a time. If each holds the lock for about 2 ms until its commit is flushed (an assumption; measure yours), one row tops out near 1 ÷ 0.002 s = 500 increments a second, however many cores you have. A one-rupee biryani offer that draws 3,000 orders a second will queue on that row.

Striped counters. Split the hot row into slots and add up on read:

CREATE TABLE dish_order_counts (
  dish_id bigint   NOT NULL,
  slot    smallint NOT NULL,          -- 0 to 15
  n       bigint   NOT NULL DEFAULT 0,
  PRIMARY KEY (dish_id, slot)
);

-- write: pick a random slot
INSERT INTO dish_order_counts (dish_id, slot, n)
VALUES (91, floor(random() * 16)::int, 2)
ON CONFLICT (dish_id, slot) DO UPDATE SET n = dish_order_counts.n + EXCLUDED.n;

-- read: sum 16 small rows
SELECT sum(n) FROM dish_order_counts WHERE dish_id = 91;

Sixteen slots raise the ceiling to roughly 16 × 500 = 8,000 increments a second under the same assumption, and a read sums 16 rows instead of one. If the badge can be a minute stale, go further: append order lines as usual and let a job add up the last minute’s counts into dishes in one statement. More patterns are in scaling writes.

NoSQL: denormalization is the default

In DynamoDB and Cassandra there are no joins, so the copy is the design rather than an optimization. AWS describes eliminating joins, and encouraging denormalization, as the heart of NoSQL data modeling, with the aim of answering each query in a single request. The cost moves to writes. A DynamoDB transaction is a single call that touches at most 100 items, so a rename copied into thousands of items cannot be one transaction; it becomes an asynchronous fan-out with retries. DynamoDB keeps a global secondary index in sync for you, asynchronously, normally within a fraction of a second, which makes a GSI the safest copy in that world: the database owns the sync. Copies you make yourself are yours to repair. See Cassandra and DynamoDB.

MongoDB sits in between. Its guidance is to embed what is read and updated together, contained by the parent and bounded, and to reference what is high-cardinality, unbounded, written at different times or able to exist on its own. Data that is embedded as a copy follows the four duplication kinds above. The SQL vs NoSQL article compares the families.

OLTP vs OLAP: normalized at the source, denormalized for analysis

Operational databases stay normalized because they take many small writes and need each fact in one place. Warehouses are the opposite: data arrives in bulk loads and is read by large scans and group-bys. Dimensional modeling keeps a narrow fact table of measurements (one row per order line, with keys and amounts) surrounded by wide dimension tables (restaurant, dish, customer, date) whose attributes are flattened. The Kimball Group advises against normalizing dimension hierarchies into “snowflakes”: a flattened dimension holds exactly the same information, is easier for analysts to navigate, and avoids joins that can hurt query performance. The OLTP database stays the source of truth, and the ETL job is the sync mechanism (OLTP vs OLAP).

Repair and reconciliation

Every copy will drift eventually: a bug, a skipped code path, a failed backfill. Plan three things up front.

A drift detector. Compare the copy with the source of truth on a schedule, in batches, off the primary:

SELECT d.dish_id, d.times_ordered, coalesce(s.actual, 0) AS actual
FROM dishes d
LEFT JOIN (SELECT dish_id, sum(qty) AS actual
           FROM order_lines GROUP BY dish_id) s USING (dish_id)
WHERE d.times_ordered <> coalesce(s.actual, 0);

A rebuild path. Any copy must be recomputable from the source: re-run the aggregate, re-snapshot the table into the read model, or replay the event stream from the start. If you cannot rebuild a copy, it is not a copy; it has quietly become a second source of truth.

Ownership and metrics. Name the source of truth in the schema docs, make copies read-only for everyone but their sync path, and alert on CDC consumer lag and on the drift count.

A decision checklist

Before you add a copy, answer these in writing:

1. Which read, at what rate, and what does it cost today (with the plan to prove it)?
2. Which cheaper fix did you try: index, covering index, rewrite, replica, cache?
3. What is the fan-out of a change to the source, and how often does the source change?
4. How stale may the copy be, and does the mechanism you chose meet that?
5. Who keeps it in sync, and what happens when that mechanism is down?
6. How do you detect drift and rebuild the copy from the source?

Cheat sheet

How to say it in the interview

“I keep the operational schema normalized, so each fact has one writer, and I only denormalize for a measured read that indexes, rewrites, replicas or a cache can’t fix. For the ‘ordered 12k times’ badge, counting on read would be billions of index entries a second against 3,000 order lines a second, so I keep a times_ordered column updated in the same transaction. If one dish gets hot I stripe the counter or batch it every minute. The restaurant name in search documents is copied by CDC from an outbox row written in the same transaction as the rename, with idempotent consumers. I never write the database and then publish separately. Every copy has a named sync path, a drift check and a rebuild job.”

Isn’t denormalization just caching? Both are copies. A cache is keyed lookup with a TTL and no schema change, and it is fine to lose. A denormalized column or read model is part of the schema: you can sort, filter and index on it, and it must be kept correct rather than merely expired.

What’s wrong with updating the database and then publishing to Kafka? It is a dual write. The two systems can’t share a transaction, so a crash between the calls leaves the database changed and the event unsent, or the reverse, with nothing to repair it. Write an outbox row in the same transaction and let CDC or a poller publish it, with consumers that tolerate duplicates.

Materialized view or trigger-maintained table? A materialized view is simplest when minutes of staleness are fine and the result is one SQL query; each refresh re-runs the whole query. A trigger-maintained summary is exact at commit and updates incrementally, but adds work to every write and can create hot rows.

How do you keep a denormalized counter correct under load? Increment in place (SET n = n + 1) so concurrent updates can’t be lost. If one row gets hot, stripe it across slots and sum on read, or append events and aggregate in batches. Run a reconciliation query that recomputes the count from the source and fixes drift.

Where to go next

• Database normalization: the normal forms and the anomalies that copies bring back.
• Data modeling and denormalization: where copies fit in the modelling process, with snapshots and per-query tables.
• Change data capture: reading the database log to feed copies.
• Transactional outbox: publishing events atomically with the write.
• Event sourcing and CQRS: separate read models as an architecture.
• Lost update problem: the race behind broken counters, and its fixes.
• Scaling writes: batching, sharding and spreading hot keys.

References and further reading

1. PostgreSQL documentation: Materialized viewspostgresql.orgA materialized view persists a query’s result, cannot be updated directly, and is only as current as its last REFRESH; the nightly summary example.
2. PostgreSQL documentation: REFRESH MATERIALIZED VIEWpostgresql.orgRefresh replaces the contents; CONCURRENTLY avoids blocking readers but needs a populated view and a plain, non-partial UNIQUE index, and only one refresh runs at a time.
3. PostgreSQL documentation: Generated columnspostgresql.orgStored versus virtual (the default), and the rule that a generation expression may only use the current row and immutable functions.
4. PostgreSQL documentation: Trigger functions in PL/pgSQLpostgresql.orgTG_OP, NEW and OLD, AFTER-trigger return values, the summary-table maintenance example and statement-level transition tables.
5. MongoDB Manual: Data modeling best practicesmongodb.comWhen to embed and when to reference, and the four kinds of duplicated data (immutable, historical, staleness-sensitive, staleness-tolerant) with how to keep each consistent.
6. Amazon DynamoDB Developer Guide: Best practices for modeling relational datadocs.aws.amazon.comEliminating joins and encouraging denormalization as the core of NoSQL modeling, and transactions bounded to 100 items in one call.
7. Amazon DynamoDB Developer Guide: Global secondary indexesdocs.aws.amazon.comDynamoDB keeps each GSI in sync asynchronously, eventually consistent, normally within a fraction of a second.
8. Reliable Microservices Data Exchange With the Outbox Pattern (Gunnar Morling, Debezium blog, 2019)debezium.ioWhy a database write plus a Kafka send cannot share a transaction, the outbox table relayed by log-based CDC, and deduplicating at-least-once events by ID.
9. CQRS (Martin Fowler, bliki)martinfowler.comSeparate models for updates and reads, often eventually consistent, and the warning that for most systems CQRS adds risky complexity.
10. Kimball Group: Snowflaked dimensions (dimensional modeling techniques)kimballgroup.comWhy warehouse dimensions are kept as flattened, denormalized tables rather than normalized snowflakes.