Materialized View

A materialized view is a precomputed, stored copy of a query's result. Unlike a regular (virtual) view, which runs its query every time it is accessed, a materialized view physically saves the output, so subsequent reads are fast lookups rather than expensive recomputations.

How It Works

When a materialized view is created, the database executes the defining query — often a complex join or aggregation — and persists the result as if it were a table. It can be indexed for even faster access.

Because the stored data goes stale as the underlying tables change, the database must refresh it. Strategies include:

• Full refresh: recompute the entire view on a schedule or on demand.
• Incremental (fast) refresh: apply only the changes since the last refresh, often using change logs.
• Real-time/streaming materialized views: continuously update as new data arrives (offered by systems like ClickHouse, Materialize, and some streaming engines).

Why It Matters

Materialized views make expensive, frequently run queries cheap. They are a powerful tool for dashboards, reporting, and aggregations in OLAP and analytical workloads, effectively caching the result of heavy computation. They are a form of controlled denormalization managed by the database itself.

The trade-off is freshness versus cost: more frequent refreshes mean more up-to-date data but more overhead, while infrequent refreshes serve stale results. Choosing the right refresh strategy is the key design decision.

Related Terms

A materialized view is a managed form of denormalization, benefits from indexing, suits OLAP analytics, and can be refreshed via change data capture.