Course contentsShow
← Data Science
Lesson 1076 of 2,14527. Database Design and OptimizationFree lesson

Materialized Views and Summary Tables

Discover how materialized views and summary tables provide denormalization benefits with automated refresh mechanisms.

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.