Denormalization

Denormalization deliberately duplicates or precomputes data to speed reads, simplify reporting, or span distributed boundaries where joins are expensive or impossible. It trades storage and consistency work for query latency.

Common patterns:

| Pattern | Example | Consistency cost | |---------|---------|------------------| | Redundant column | `orders.customer_name` copied from `users` | Update user name → backfill orders | | Aggregate cache | `product.order_count` | Increment on each order line | | Materialized view | nightly revenue rollup | Refresh lag acceptable | | JSON blob snapshot | cart line with price at add time | Historical truth, not live catalog |

					CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day, SUM(total_cents) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY 1;
-- REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
				

Document the source of truth and invalidation: triggers, async workers, or periodic refresh. Eventual consistency is fine for dashboards; it is not for ledger balances without explicit rules.

On interviews: when asked to speed a read-heavy feed, propose denormalization with a concrete sync strategy and name what can be stale.

Common pitfalls: denormalizing before measuring join cost; no backfill plan when source changes; duplicate caches with no ownership; using denormalization to paper over missing indexes.

The trade-off is balancing read performance and query simplicity against update complexity and stale data risk — denormalize only when normalized joins or aggregates fail your latency or availability budget with evidence.

Checklist:

• Identify the slow read path and target latency.
• Name the canonical source of truth.
• Define sync: trigger, job, or refresh schedule.
• State acceptable staleness for the product.
• Plan migration/backfill before cutting over reads.