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 |