Denormalization

Normalization is the textbook relational design: split data into many tables, each holding one fact, eliminate redundancy, and rely on JOINs at read time. The goals are write simplicity (update a fact once) and consistency (there's one source of truth).

Denormalization inverts this: deliberately duplicate data so that common reads can be answered from a single table (or document, or cache entry), without JOINs or fan-out. The goals are read performance and query simplicity.

The fundamental trade-off:

• Normalized: 1 write per fact, N reads per query (JOIN cost).
• Denormalized: M writes per fact (one per copy), 1 read per query.

You denormalize when reads outnumber writes by a wide margin — which, for most web apps, is 100:1 or more. A product page is read a thousand times for every price update; embedding the category name into the product record (instead of JOINing to a categories table) saves a thousand JOINs at the cost of one extra write when a category is renamed.

Denormalize when:

1. Reads vastly outnumber writes — a product page viewed 1000× per price update. The arithmetic favors denormalization.
2. JOINs are the bottleneck — EXPLAIN ANALYZE shows most time in joins, not in scans.
3. Latency targets can't be met with JOINs — e.g., p99 < 50ms on a user feed that needs 5-table joins.
4. Geographic distribution — JOINs across regions are prohibitively slow; denormalize locally.
5. You're using a NoSQL database — MongoDB, Cassandra, DynamoDB don't have JOINs, so denormalization is the model, not an optimization.
6. You're building a read model in CQRS — the read side is explicitly a denormalized projection optimized for queries.

Don't denormalize when:

1. Writes outnumber reads (logs, audit trails).
2. The data changes constantly (real-time inventory on a flash-sale site).
3. Consistency is critical and you can't tolerate stale copies.
4. The denormalized copy is hard to keep in sync (complex derivations, multi-source).
5. You haven't measured — premature denormalization adds complexity for nothing.

The single biggest mistake is denormalizing without a measurement. 'It feels like JOINs will be slow' is not a reason. EXPLAIN ANALYZE is.

The hard part of denormalization isn't the initial duplication — it's keeping the copies in sync. Strategies, in order of complexity:

1. Synchronous update in the same transaction — UPDATE products SET price=? WHERE id=?; UPDATE products_denormalized SET price=? WHERE product_id=?; within one transaction. Simple, consistent, but adds write latency and only works within one database.

2. Application-level dual-write — service writes to both the source and the denormalized copy. Risk: if the second write fails, you have inconsistency. Mitigated by idempotent retries, but never fully safe without the outbox pattern.

3. Outbox pattern — write the source data and an 'update event' row in the same transaction; a separate process reads the outbox and updates denormalized copies (and publishes to Kafka, etc.). Guarantees the event is emitted iff the source is committed. The standard answer for reliable cross-system denormalization.

4. Change Data Capture (CDC) — read the database's transaction log (PostgreSQL WAL, MySQL binlog) and stream changes to downstream denormalized stores. Debezium is the canonical tool. Decouples the source from consumers entirely.

5. Periodic rebuild — materialized views refreshed on a schedule (REFRESH MATERIALIZED VIEW). Simple, but data is stale between refreshes. Fine for analytics, dangerous for user-facing data.

6. Accept eventual consistency — many denormalized copies don't need to be real-time. A search index updated 'within a minute' is fine; a price feed might not be.

Common denormalization patterns:

• Counter fields — user.post_count, video.view_count, product.review_avg. Updated on every write to the underlying data, read as a single field. Counter caches in Redis are the same idea applied across a service boundary.

• Materialized views — SELECT … GROUP BY results precomputed and stored. PostgreSQL's MATERIALIZED VIEW is the textbook example.

• Embedded documents — MongoDB's pattern: store the user's address inside the user document, not in a separate addresses table.

• Read models in CQRS — the read side is a denormalized projection; the write side is the normalized source. Updates flow via events.

• Lookup denormalization — copy category_name into the products row so reads don't JOIN to categories.

• Time-series rollups — precompute hourly/daily aggregates from raw events so dashboard queries are fast.

• Precomputed user feeds — Twitter-style fanout-on-write: when a user posts, write the post ID into every follower's precomputed feed list. The feed read is O(1); the write cost is O(followers).

Each of these trades write amplification for read simplicity. The right choice depends on the read/write ratio and how stale the denormalized copy can be.

Mental model. Think of Denormalization as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Denormalization mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Denormalization, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.