Materialized View

A materialized view stores the result of a query physically, rather than recomputing it on every access like a regular (virtual) view. Reads hit the precomputed table directly, turning an expensive join or aggregation into a simple scan or index lookup. The cost moves to maintenance: the view must be refreshed to reflect changes in its source data.

How It Works

A materialized view is defined by a query over base tables. The database executes the query once and persists the rows. Refresh strategies vary:

• Full refresh recomputes the entire view, simple but expensive.
• Incremental (fast) refresh applies only the deltas since the last refresh using change logs, far cheaper for large views.
• On-demand vs. scheduled vs. on-commit controls when refresh runs.

PostgreSQL offers CREATE MATERIALIZED VIEW with manual or concurrent refresh; Oracle, SQL Server (indexed views), Snowflake, and BigQuery provide automatic incremental maintenance. In event-driven systems, a consumer can maintain a materialized read model by applying events as they arrive.

When to Use It

Reach for materialized views when the same expensive query runs repeatedly and the underlying data changes far less often than it is read: dashboards, reporting roll-ups, leaderboards, search projections, and denormalized read models in a CQRS design. They are ideal when slightly stale results are acceptable in exchange for predictable, low read latency.

Trade-offs

The data is as fresh as the last refresh, so materialized views are unsuitable for strict real-time accuracy unless incrementally maintained on commit. They consume storage proportional to the result size and add write-time or scheduled refresh overhead. Incremental refresh has restrictions on which query shapes it supports. Over-using them creates a web of dependent objects that must refresh in the right order, complicating operations.

Related Patterns

Materialized views are the canonical read store for command-query-responsibility-segregation and a natural projection target for event-sourcing. They overlap conceptually with read-through-cache: both serve precomputed reads, but a materialized view lives in the database and is queryable with SQL.

Example

A SaaS analytics dashboard aggregates billions of event rows into per-tenant daily metrics. A materialized view precomputes the daily roll-ups and refreshes incrementally every fifteen minutes. Dashboard queries that once took thirty seconds now return in milliseconds against the compact view, while the raw events remain available for ad-hoc analysis.