Denormalization Best Practices

Q: Is denormalization ever required?

Historical snapshots (unit_price, tax_rate_at_sale) are business requirements, not optional performance tricks.

Q: How do I list all denorm columns?

Maintain a denorm_registry table or grep COMMENT ON COLUMN for SNAPSHOT / MIRROR tags in migration files.

Q: Can CHECK constraints enforce denorm?

Only for derivable rules (line_total = quantity * unit_price). Cross-table mirrors need triggers or jobs.

Q: When should I remove denorm?

When EXPLAIN shows normalized path meets SLO after index or hardware improvements for two consecutive quarters.

Document invariants denorm columns must preserve. Denormalize only with evidence, ownership, and drift detection - not because joins feel slow in development.

How to Use This List

Default to 3NF for OLTP; use this list only when proposing redundant columns or materialized views.
Every checked item should appear in the migration PR description or ADR.
Schedule drift queries in monitoring; page when invariants break.
Re-evaluate denorm after index tuning and hardware changes - joins may become cheap again.

A - Evidence and Scope

Prove the pain with pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS). No denorm without measured hot queries.
Try covering indexes on normalized schema first. FK indexes plus include columns often remove the need to denorm.
Define acceptable staleness for read copies. Materialized views need a documented max_staleness SLA.
Keep normalized tables as source of truth. Denorm is derived; you must be able to recompute from base tables.
Limit denorm to specific access paths. One dashboard query is not a reason to denorm the core fact table globally.

B - Invariants and Documentation

Classify each redundant column: snapshot vs live mirror. Snapshots never update; mirrors need refresh jobs.
Add COMMENT ON COLUMN for every denorm field. State the invariant in plain language and owner team.
Record refresh mechanism. Trigger, batch job, or REFRESH MATERIALIZED VIEW schedule with on-call owner.
Version JSONB snapshots when shape changes. Include schema_version inside snapshot blobs.
Add drift detection SQL to the runbook. Compare denorm column to source; alert on mismatch for mirrors.

C - Implementation Safety

Prefer triggers or generated columns over scattered app dual-writes. Fewer code paths mean fewer bugs.
Use NOT NULL on snapshot columns filled at insert. Partial NULL snapshots break finance and support tooling.
Create unique indexes before REFRESH CONCURRENTLY. Concurrent MV refresh fails without a unique index.
Refresh heavy MVs off-peak or on replicas. IO spikes on primary hurt OLTP latency.
Plan contract migration to remove denorm. Denorm columns without sunset dates become permanent debt.

D - Governance

Require second reviewer for denorm PRs. DBA or staff engineer signs off on invariant and rollback.
Track denorm columns in a schema catalog. New hires should find all redundant fields in one doc query.
Re-test after major version upgrades. Planner changes can make normalized joins fast enough to drop denorm.
Do not denorm across tenant boundaries without tenant_id in the invariant. Cross-tenant leakage risk rises.
Post-mortem every drift incident. Turn broken invariants into lint rules or CI checks.

FAQs

What is the difference between snapshot and mirror denorm?

Snapshots freeze values at an event (price at purchase). Mirrors try to track live dimension data and need refresh when the source changes.

What should a COMMENT ON COLUMN look like?

COMMENT ON COLUMN order_items.unit_price IS
  'SNAPSHOT invariant: unit price at insert; source products.price';

How often should I run drift detection?

Mirrors: nightly or hourly depending on SLA. Snapshots: only when business rules retroactively change source data.

Is denormalization ever required?

Historical snapshots (unit_price, tax_rate_at_sale) are business requirements, not optional performance tricks.

What is the biggest denorm mistake?

Treating mirror columns as snapshots - reports show stale customer emails forever with no refresh job.

Should materialized views replace column denorm?

Prefer MVs when many queries share the same aggregation. Prefer column denorm when a single row needs embedded snapshot fields in OLTP writes.

How do I list all denorm columns?

Maintain a denorm_registry table or grep COMMENT ON COLUMN for SNAPSHOT / MIRROR tags in migration files.

Can CHECK constraints enforce denorm?

Only for derivable rules (line_total = quantity * unit_price). Cross-table mirrors need triggers or jobs.

When should I remove denorm?

When EXPLAIN shows normalized path meets SLO after index or hardware improvements for two consecutive quarters.

How does this relate to data warehouses?

Warehouses denormalize by design. OLTP denorm still needs invariants because apps write the same rows transactions depend on.

Normalization Basics - baseline normalized design
Intentional Denormalization - implementation patterns
Materialized Views - refresh mechanics
Data Modeling Best Practices - modeling review list

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Denormalization Best Practices

Document invariants denorm columns must preserve. Denormalize only with evidence, ownership, and drift detection - not because joins feel slow in development.

How to Use This List

Default to 3NF for OLTP; use this list only when proposing redundant columns or materialized views.
Every checked item should appear in the migration PR description or ADR.
Schedule drift queries in monitoring; page when invariants break.
Re-evaluate denorm after index tuning and hardware changes - joins may become cheap again.

A - Evidence and Scope

Prove the pain with pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS). No denorm without measured hot queries.
Try covering indexes on normalized schema first. FK indexes plus include columns often remove the need to denorm.
Define acceptable staleness for read copies. Materialized views need a documented max_staleness SLA.
Keep normalized tables as source of truth. Denorm is derived; you must be able to recompute from base tables.
Limit denorm to specific access paths. One dashboard query is not a reason to denorm the core fact table globally.

B - Invariants and Documentation

Classify each redundant column: snapshot vs live mirror. Snapshots never update; mirrors need refresh jobs.
Add COMMENT ON COLUMN for every denorm field. State the invariant in plain language and owner team.
Record refresh mechanism. Trigger, batch job, or REFRESH MATERIALIZED VIEW schedule with on-call owner.
Version JSONB snapshots when shape changes. Include schema_version inside snapshot blobs.
Add drift detection SQL to the runbook. Compare denorm column to source; alert on mismatch for mirrors.

C - Implementation Safety

Prefer triggers or generated columns over scattered app dual-writes. Fewer code paths mean fewer bugs.
Use NOT NULL on snapshot columns filled at insert. Partial NULL snapshots break finance and support tooling.
Create unique indexes before REFRESH CONCURRENTLY. Concurrent MV refresh fails without a unique index.
Refresh heavy MVs off-peak or on replicas. IO spikes on primary hurt OLTP latency.
Plan contract migration to remove denorm. Denorm columns without sunset dates become permanent debt.

D - Governance

Require second reviewer for denorm PRs. DBA or staff engineer signs off on invariant and rollback.
Track denorm columns in a schema catalog. New hires should find all redundant fields in one doc query.
Re-test after major version upgrades. Planner changes can make normalized joins fast enough to drop denorm.
Do not denorm across tenant boundaries without tenant_id in the invariant. Cross-tenant leakage risk rises.
Post-mortem every drift incident. Turn broken invariants into lint rules or CI checks.

FAQs

What is the difference between snapshot and mirror denorm?

Snapshots freeze values at an event (price at purchase). Mirrors try to track live dimension data and need refresh when the source changes.

What should a COMMENT ON COLUMN look like?

COMMENT ON COLUMN order_items.unit_price IS
  'SNAPSHOT invariant: unit price at insert; source products.price';

How often should I run drift detection?

Mirrors: nightly or hourly depending on SLA. Snapshots: only when business rules retroactively change source data.

Is denormalization ever required?

Historical snapshots (unit_price, tax_rate_at_sale) are business requirements, not optional performance tricks.

What is the biggest denorm mistake?

Treating mirror columns as snapshots - reports show stale customer emails forever with no refresh job.

Should materialized views replace column denorm?

Prefer MVs when many queries share the same aggregation. Prefer column denorm when a single row needs embedded snapshot fields in OLTP writes.

How do I list all denorm columns?

Maintain a denorm_registry table or grep COMMENT ON COLUMN for SNAPSHOT / MIRROR tags in migration files.

Can CHECK constraints enforce denorm?

Only for derivable rules (line_total = quantity * unit_price). Cross-table mirrors need triggers or jobs.

When should I remove denorm?

When EXPLAIN shows normalized path meets SLO after index or hardware improvements for two consecutive quarters.

How does this relate to data warehouses?

Warehouses denormalize by design. OLTP denorm still needs invariants because apps write the same rows transactions depend on.

Normalization Basics - baseline normalized design
Intentional Denormalization - implementation patterns
Materialized Views - refresh mechanics
Data Modeling Best Practices - modeling review list

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Denormalization Best Practices

Search Documentation

Denormalization Best Practices