What is a materialized view?
A materialized view stores the precomputed result of a query as a real table, so reads are instant — at the cost of keeping it refreshed as the source changes.
A materialized view is a query whose result is physically stored, not recomputed on every read. A normal view is just a saved query — each time you select from it, the database runs the underlying query again. A materialized view runs the query once, writes the answer to disk like a real table, and serves subsequent reads straight from that stored copy. You trade storage and freshness for speed.
View vs materialized view
The distinction is when the work happens.
- A view is a name for a query. Reading it re-executes that query against live data, so it is always current but pays the full cost every time. Good for convenience and abstraction; useless for making an expensive query fast.
- A materialized view is the query's result, stored. Reading it is a cheap table scan. The expensive joins and aggregations already ran. The catch: the stored result is a snapshot, so it goes stale the moment the source data changes, and something has to refresh it.
Why they exist
The whole point is precomputation. If a dashboard runs the same heavy aggregation — daily revenue by region across a billion rows — every time someone loads it, that work is wasted repetition. Materialize it once, refresh it on a schedule, and every load becomes instant. This is a workhorse pattern in analytics and OLAP1 serving, where the same expensive rollups are read far more often than the underlying data changes.
🔗 Learn more — 1 OLTP vs OLAP: two opposite jobs
Refresh comes in two shapes. A full refresh recomputes the whole result — simple, but expensive if the query is heavy. An incremental refresh updates only the rows affected by what changed in the source — much cheaper, but only some engines support it and only for some query shapes. Choosing between them is the central operational decision.
The honest tradeoff
A materialized view is a cache, and caches have one hard problem: staleness. Between refreshes, the view disagrees with reality. How much that matters depends entirely on the consumer — a daily executive report tolerates hours of lag; a fraud check does not. You are choosing a point on the freshness-versus-cost line: refresh more often for fresher data and higher compute cost, or less often for cheaper, staler data.
Reach for one when an expensive query is read repeatedly and a bounded amount of staleness is acceptable. Don't reach for one when the read is cheap (a plain view is simpler), when the data must be exactly current (query the source), or when the refresh ends up costing more than the reads it saves. It is precomputation, and precomputation is only a win when you read far more than you write.