Materialized Views and Summary Tables
What You'll Learn
You'll discover how materialized views and summary tables give you the performance benefits of denormalization while keeping data refresh manageable and semi-automated.
The Challenge of Denormalization
In our previous lesson, you learned that denormalization—storing computed aggregates or duplicating data—speeds up read queries but creates a consistency headache. You have to manually keep those redundant copies in sync. Materialized views and summary tables offer a middle ground: pre-computed results that your database can refresh on a schedule.
What Are Materialized Views?
Think of a materialized view like a snapshot of a query result that's physically stored on disk. Unlike regular views (which are just saved queries that run every time you call them), a materialized view calculates once and saves the answer. When you query it, you get instant results—no joins, no aggregations, just a fast lookup.
Example use case: Instead of joining orders and products and summing totals every time someone checks monthly sales, create a materialized view that stores those monthly totals. Queries hit the pre-computed table instead of crunching raw data.
Summary Tables
Summary tables are similar but manually controlled. You create a regular table, then write INSERT or UPDATE statements (often in scheduled jobs) to populate it with aggregated data. You have full control over refresh timing and logic.
Automated Refresh Mechanisms
Many databases let you configure materialized views to refresh automatically—on a schedule (daily, hourly) or even incrementally when source data changes. This keeps your denormalized data reasonably fresh without manual intervention.
Trade-offs
- Pros: Lightning-fast reads, simplified queries
- Cons: Stale data between refreshes, storage overhead, refresh time on large datasets
Key Takeaway:
Materialized views and summary tables provide denormalized performance with automated or scheduled refresh, balancing speed against data freshness and maintenance complexity.