Course contentsShow
← System Design
Lesson 291 of 1,9198. Database Performance OptimizationFree lesson

Materialized Views

Pre-compute and store query results as materialized views to eliminate runtime join overhead.

Materialized Views

You'll learn how to pre-compute and store query results to eliminate expensive join overhead at query time.

What Is a Materialized View?

A materialized view is like a snapshot of a query result stored as a real table. Instead of running a complex query with multiple joins and aggregations every time someone requests data, you run it once, save the results, and serve the pre-computed data directly.

Think of it like meal prepping for the week instead of cooking from scratch every single meal. You do the hard work once, store the result, and enjoy fast access when you need it.

How It Works

When you create a materialized view, the database:

  1. Executes your complex query (joins, aggregations, filters)
  2. Stores the result set as a physical table
  3. Serves queries directly from this stored result

The catch? The data becomes stale over time as the underlying tables change. You must periodically refresh the materialized view to keep it current—either manually, on a schedule, or sometimes automatically.

Why Use Materialized Views

Imagine a dashboard query that joins 5 tables, aggregates millions of rows, and takes 30 seconds to run. If 1,000 users load that dashboard daily, you're burning massive compute resources repeatedly calculating the same result.

With a materialized view, you:

  • Run the expensive query once per refresh cycle (e.g., hourly)
  • Serve 1,000 users from the pre-computed result in milliseconds
  • Eliminate runtime join and aggregation overhead

This is perfect for read-heavy analytical workloads where slight staleness is acceptable (reporting dashboards, metrics, leaderboards).

Key Takeaway: Materialized views trade storage space and data freshness for dramatically faster query performance by pre-computing and caching complex query results.