Implementing Denormalization Strategies

1. Understanding When to Denormalize

Trigger	Reason
Hot Read Path	Joins dominate latency budget
Reporting / BI	Aggregations on huge tables
Read:Write Ratio > 10:1	Update overhead acceptable
Cross-shard Joins	Network round-trips dominate
Cache Substitution	Pre-computed values vs runtime cache miss

2. Creating Redundant Columns

Pattern	Example	Maintained By
Copy parent column	order.customer_name (from customers)	Triggers / app logic
Generated column	full_name = first \|\| ' ' \|\| last	DB engine
Counter cache	post.comment_count	Triggers / queues

Example: Generated Column (Postgres)

ALTER TABLE users ADD COLUMN full_name TEXT
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

3. Combining Tables

Strategy	When
Merge 1:1 tables	Always joined together, low NULL ratio
Embed 1:N as array/JSON	Small child set, never queried independently
Pre-join fact + dimensions	OLAP wide tables for analytics

4. Using Materialized Views

Feature	Postgres	Oracle
Create	`CREATE MATERIALIZED VIEW`	`CREATE MATERIALIZED VIEW`
Refresh	`REFRESH MATERIALIZED VIEW [CONCURRENTLY]`	`DBMS_MVIEW.REFRESH`
Auto-refresh	External (cron/trigger)	`ON COMMIT` / scheduler
Indexable	Yes	Yes

Example: Materialized Sales Summary

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', placed_at) AS day,
       SUM(total) AS revenue,
       COUNT(*)   AS orders
FROM orders
GROUP BY 1
WITH DATA;

CREATE UNIQUE INDEX ON daily_sales(day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

5. Implementing Summary Tables

Type	Update Strategy
Rollup table	Incremental via trigger or batch job
Pre-aggregated counters	UPDATE on each write
Bucketed snapshots	Hourly/daily snapshot of metrics

6. Handling Data Consistency in Denormalized Schemas

Mechanism	Use
Triggers	Synchronous propagation within DB
Change Data Capture (CDC)	Debezium → downstream sinks
Outbox + Event Bus	Reliable async propagation
Scheduled Reconciliation	Nightly diff + fix drift
Application Transactions	Same tx writes source + copy

Warning: Denormalized data WILL drift. Always have a reconciliation job to detect and repair inconsistencies.

7. Creating Read-Optimized Schemas

Pattern	Description
CQRS	Separate write model + read model
Star Schema	Central fact + denormalized dimensions
Snowflake Schema	Normalized dimensions, joined to fact
Wide Tables	Pre-joined columns to avoid runtime joins
Read Replicas	Eventually consistent copies for queries

8. Balancing Storage vs Query Performance

Decision	Pros	Cons
Denormalize	Fewer joins, faster reads	More storage, update cost
Materialized view	Pre-computed reads	Refresh latency, staleness
Index more	Faster lookups	Slower writes, more disk
Columnar storage	Compression, scan speed	Slow point updates

9. Managing Update Complexity

Risk	Mitigation
Write amplification	Batch updates; use queues
Lost updates	Use transactions or optimistic locking
Cascading writes	Limit scope; async via CDC
Race conditions	Serializable isolation or row locks

10. Documenting Denormalization Decisions

Document	Contents
Decision Record (ADR)	Context, decision, alternatives, consequences
Source of Truth	Which table is canonical for each fact
Propagation Path	How copies stay in sync
Refresh SLA	Max staleness allowed
Reconciliation Job	Frequency, query, alert thresholds

Related Cheat Sheets

Database Fundamentals

Implementing Denormalization Strategies

1. Understanding When to Denormalize

2. Creating Redundant Columns

Example: Generated Column (Postgres)

3. Combining Tables

4. Using Materialized Views

Example: Materialized Sales Summary

5. Implementing Summary Tables

6. Handling Data Consistency in Denormalized Schemas

7. Creating Read-Optimized Schemas

8. Balancing Storage vs Query Performance

9. Managing Update Complexity

10. Documenting Denormalization Decisions

Related Cheat Sheets

Implementing Database Sharding

Using Aggregation Operators

Implementing Database Security

Implementing Search Patterns