Chapter 13 · Denormalization, Caching, and Derived Data
Summary Tables, Materialized Views, and Aggregates
Use summary tables, materialized views, and aggregates to accelerate repeated analytical and dashboard workloads while controlling freshness and refresh cost.
Learning outcomes
Many expensive queries repeatedly compute the same aggregates. Summary tables and materialized views precompute those results so reads become cheaper. The tradeoff is freshness, refresh cost, and more derived state to operate.
Choose between on-demand aggregation and precomputation.
Define aggregate grain before building summaries.
Compare full, incremental, and event-driven refresh.
Prevent double counting and stale summary data.
Repeated aggregation problem
SELECT date_trunc('day', opened_at) AS day, status_code, COUNT(*)FROM work_orderWHERE opened_at >= current_date - 90GROUP BY 1,2;Running this every few seconds over tens of millions of rows repeats expensive work.
Summary table
DailyWorkOrderSummary( day, status_code, work_order_count, refreshed_at, PRIMARY KEY(day, status_code))Grain: one row per day + status.
Ordinary view versus materialized view
| Structure | Stores rows? | Freshness |
|---|---|---|
| View | No | Queries current base data |
| Materialized view | Yes | As of refresh |
Full refresh
Recompute the complete derived result. This is operationally simple and often easiest to verify, but it can be too expensive for large data or tight freshness SLAs.
Incremental refresh
new WorkOrder on 2026-08-10=> increment 2026-08-10 + status groupEfficient, but changes and reversals become more complex.
Status changes
open -> closeddecrement openincrement closedDuplicate or missed events can drift totals.
Event-driven aggregation
WorkOrderOpenedWorkOrderStatusChangedWorkOrderCancelledEach event updates one or more summary rows asynchronously.
Idempotency matters
If the same event is delivered twice, an unconditional increment corrupts the result. Use processed event IDs, source versions, or replay-safe derivation.
Incremental materialization must be replay-safe or easily rebuildable.
Rebuildability
Being able to recreate the structure is a major safety property.
Freshness SLA
operations dashboard <= 10 secondsexecutive dashboard <= 15 minutesmonthly finance report = daily snapshotPartUsage summary
MonthlyPartConsumption( month, part_id, total_quantity, total_charged_amount)Grain: one month + one Part.
Do not mix grains
A single table containing arbitrary daily, weekly, and monthly rows through nullable “period” columns is difficult to constrain. Prefer explicit structures or a carefully designed period dimension.
Preaggregation reduces flexibility
A monthly summary is excellent for monthly reporting but cannot answer arbitrary per-hour analysis. Keep the detailed fact table available.
Materialized structures need indexes too
If a summary or materialized view is physically stored, it has its own access patterns. Index it according to the queries that read the materialized result.
WorkshopHub aggregate candidates
| Report | Candidate |
|---|---|
| Work orders by day/status | DailyWorkOrderSummary |
| Part consumption by month | MonthlyPartConsumption |
| Technician utilization | WeeklyTechnicianUtilization |
| Customer repair spend | CustomerMonthlySpend |
Refresh strategy decision
- full refresh for simple/small structures;
- incremental refresh for large structures with identifiable deltas;
- event-driven projection for low-latency freshness;
- scheduled rebuild for batch reporting.
Practice: choose refresh strategy
Dashboard SLA
A dashboard needs totals within 30 seconds of source changes. Full refresh takes 20 minutes. What strategy fits?
Review answer
Use incremental/event-driven refresh or a continuously maintained summary, with idempotent updates and periodic reconciliation/rebuild. A 20-minute full refresh cannot satisfy the freshness contract.
Summary and next lesson
Summary tables and materialized views trade refresh complexity for much cheaper repeated reads. Their correctness depends on clear grain, deterministic ownership, freshness SLAs, and replay-safe updates. The next lesson broadens the discussion into caches and source-of-truth design.
References
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.
- Martin Kleppmann, Designing Data-Intensive Applications.
- DBMS documentation for materialized-view refresh semantics.