Catalog My learning Blog
System Design Roadmap: 100-Day Course for Engineers · Phase 3 · Lesson 21 · 4 min read

Normalization vs Denormalization at Scale

On this page
  1. A customer moves house
  2. What is database normalization?
  3. The cost of normalization: joins
  4. Worked example: denormalizing order history
  5. The rule: normalize writes, denormalize reads
  6. When a denormalized counter becomes a bottleneck
  7. Check yourself

A customer moves house

An orders table stores the customer's name and address on every row. A customer moves. Now you must update all 300 of their order rows, and if one update is missed, the database says two different things about where they live. That inconsistency is an anomaly, and preventing it is what normalization is for.

What is database normalization?

Normalization organizes tables so each fact lives in exactly one place. The rules are called normal forms. In practice, third normal form (3NF) is the usual target: every column describes the row's key, the whole key, and nothing but the key. Customer details live in customers, orders reference a customer_id, and products live in products.

It prevents three anomalies:

  • Update anomaly: a fact stored in many rows gets changed in some and not others.
  • Insert anomaly: you can't record a customer until they place an order, because customer data only exists on order rows.
  • Delete anomaly: deleting a customer's last order deletes everything you knew about the customer.

The cost of normalization: joins

Normalized data has to be reassembled for reading. An order history page joins orders, order_items, products and customers. At low traffic that's nothing. At thousands of page views per second, the same joins run over and over and become the database's main load.

Denormalization stores data in the shape it's read, copying fields to avoid joins. Faster reads, at the price of keeping copies in sync.

A normalized schema needs joins across tables; a denormalized read table stores a flat, ready-to-read copy.
A normalized schema needs joins across tables; a denormalized read table stores a flat, ready-to-read copy.

Worked example: denormalizing order history

Keep the normalized tables as the source of truth. Add an order_summaries table written when the order is placed:

SQL
CREATE TABLE order_summaries (
  order_id      bigint PRIMARY KEY,
  customer_id   bigint NOT NULL,
  customer_name text NOT NULL,
  placed_at     timestamptz NOT NULL,
  total         numeric(10,2) NOT NULL,
  items         jsonb NOT NULL      -- [{"name":"Desk lamp","price":39.00,"qty":1}, ...]
);
CREATE INDEX ON order_summaries (customer_id, placed_at DESC);

The history page becomes one indexed query on one table.

Notice that items stores the product name and price as they were at checkout. That isn't just a performance trick; it's correct. If the product is renamed or repriced tomorrow, the order history should still show what the customer bought and paid. This snapshot at write time is the safest kind of denormalization, because the copy is never supposed to change.

The rule: normalize writes, denormalize reads

Keep the tables you write to normalized, so writes are simple and can't create contradictions. Build read-optimized copies on top, and choose how each copy stays in sync:

TechniqueHow it stays in sync
Snapshot at write timeWritten once with the original row; never updated
Counter columns (like comment_count)Updated in the same transaction as the source row
Materialized views (PostgreSQL)Refreshed when you run REFRESH MATERIALIZED VIEW, usually on a schedule
Indexed views (SQL Server)Maintained by the database on every write to the base tables
Separate read modelRebuilt from change events (Day 48, CQRS)

Denormalize because a measured read pattern needs it, not by default. Every copy is one more thing that can drift.

When a denormalized counter becomes a bottleneck

Counter columns have a trap at scale. A viral post gets 2,000 likes a second, and every like runs UPDATE posts SET like_count = like_count + 1 WHERE id = 7. All of those updates need a lock on the same row, so they queue one behind another. The likes table is fine; the single counter row is the bottleneck.

Two fixes. Spread the counter over several rows (post_id, bucket, count, with a random bucket from 0 to 15) and sum them on read. Or record likes as inserts and update the count in the background every few seconds. Both trade an exact real-time number for throughput, which is usually fine for a like count.

The same applies to indexed views in SQL Server: a view that sums sales per product is updated on every insert, so it adds lock contention to the hottest rows.

Check yourself

The order must show what the customer paid. A join would show the current price, which may have changed. The snapshot is both faster and correct.

Update the counter in the same transaction as inserting or deleting the like row, or rebuild it from the source periodically, so it can't drift.

Every update needs the same row lock, so they queue. Split the counter across several bucket rows, or update it in the background from the likes table.

If customer addresses are copied into every order row, changing an address means updating many rows (update anomaly), you can't store a customer without an order (insert anomaly), and deleting their last order loses the address (delete anomaly). Normalization stores the address once to avoid all three.

When reads vastly outnumber writes and a measured query is too slow even with good indexes, or when you need to keep a historical snapshot, like the price at the time of an order. Denormalize deliberately, with a clear owner for keeping the copies in sync.