Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

Normalization vs Denormalization

Tradeoffs for reads, writes, and consistency; denormalize with constraints, not hope.
On this page

Normalization vs Denormalization: Design for Workload, Not Purity

Normalization reduces redundancy and improves integrity. Denormalization improves read performance and reduces join cost. At scale, the question is not which is correct, but which tradeoff fits your workload.

Production modeling must consider:

  • Read/write ratio
  • Query shape and fanout
  • Consistency requirements
  • Operational complexity

Normalization: Integrity First

Normalized schemas (3NF and beyond) separate entities and remove redundancy.

Advantages:

  • Strong data integrity
  • Single source of truth
  • Smaller updates (write amplification reduced)

Costs:

  • More joins
  • Complex read queries
  • Higher CPU and memory under heavy read load

Denormalization: Read Efficiency First

Denormalization duplicates data to avoid joins and speed up reads.

Advantages:

  • Faster reads (especially for hot endpoints)
  • Simpler query plans
  • Lower join fanout risk

Costs:

  • Write complexity (multiple tables to update)
  • Risk of inconsistency
  • Backfill and repair complexity

When Normalization Works Well

  • Write-heavy systems
  • Strong integrity constraints (financial systems)
  • Frequent updates to individual attributes
  • Low join complexity or low QPS

When Denormalization Is Justified

  • Read-heavy systems with high QPS
  • Expensive joins in hot paths
  • Strict latency SLOs
  • Precomputed aggregates and feeds

Denormalization Patterns

  • Copy frequently-read attributes into child table
  • Materialized summary tables
  • Event-driven read models
  • Embedded JSON snapshots

Production rule: every denormalized field must have a defined update strategy.

Write Amplification Tradeoff

Denormalization shifts cost from read path to write path.

  • More writes per logical update
  • More index updates
  • Higher lock contention risk

Under heavy write load, excessive denormalization can become the bottleneck.

Consistency Models

Denormalization introduces consistency questions:

  • Synchronous update (strong consistency, higher latency)
  • Async/event-driven update (eventual consistency)

Production rule: explicitly define acceptable staleness window.

Backfills and Rebuilds

Denormalized data must be rebuildable.

  • Keep canonical source of truth
  • Design backfill jobs
  • Test repair procedures

If you cannot rebuild safely, your denormalization is a liability.

Failure Modes in Production

  • Drift: denormalized data not updated correctly.
  • Fanout explosion: single update touches thousands of rows.
  • Write bottleneck: denormalization overloads primary DB.
  • Stale read models: async update lag grows.
  • Complex migrations: duplicated data complicates schema changes.

Operational Checklist

  • Model normalized first; denormalize only where metrics justify.
  • Measure read latency and join cost before denormalizing.
  • For each duplicated field, define update path and owner.
  • Define staleness tolerance if using async updates.
  • Ensure denormalized tables are rebuildable from canonical source.
  • Monitor write amplification and lock contention.
  • Load test heavy update scenarios.
  • Document which table is source of truth.
  • Plan backfill strategy before shipping.
  • Review schema periodically for unnecessary duplication.

Summary

Normalization optimizes integrity and write efficiency. Denormalization optimizes read performance and latency. At scale, you often combine both: normalized source-of-truth tables plus denormalized read models for hot paths. The key is explicit tradeoff management and operational discipline.

Soft Delete Patterns →