What Is a Materialized View? Database Concept Explained

A materialized view is a database object that stores the precomputed result of a query as actual physical data, rather than re-running the query every time it’s read. A regular view is just a saved query — every SELECT against it triggers the underlying query fresh. A materialized view runs that query once, writes the result to disk, and serves reads from that stored copy until it’s explicitly refreshed.

Regular views vs materialized views

A standard view is a convenience layer: it gives a complex query a simple name, but every read pays the full cost of that query, including any joins or aggregations it contains. That’s fine for cheap queries, but expensive for anything involving large joins, aggregations, or multiple table scans — the cost is paid on every single read, no matter how often the underlying data actually changes.

A materialized view flips that tradeoff. The query runs once, at creation or refresh time, and the result is stored like a regular table. Reads against it are as fast as reading any other table, because that’s exactly what they are. The cost moves from every read to every refresh.

How refreshing works

Because a materialized view is a snapshot, it goes stale as the underlying tables change. Databases that support them typically offer a few refresh strategies:

• On-demand (manual) refresh — the view is recomputed only when explicitly triggered, useful for data that changes in known batches.
• Scheduled refresh — recomputed on a fixed interval, trading some staleness for predictable load, often driven by the same kind of scheduling logic behind a cron job.
• Incremental refresh — only the rows affected by underlying changes are recomputed, rather than the whole view, where the database supports it.

Whichever strategy you use, staleness is the fundamental cost of a materialized view: the data is only as fresh as the last refresh, and there’s always a window where the stored result and the live query would disagree.

When materialized views earn their keep

Materialized views pay off when a query is read far more often than the underlying data changes, and when that query is expensive enough that recomputing it on every read is wasteful. Classic cases:

• Dashboards and reporting — aggregations over large historical datasets that don’t need to reflect the very latest write.
• Expensive joins or rollups — pre-joining several tables or precomputing a sum/count that would otherwise scan a large table on every request.
• Read-heavy analytical workloads — the kind of pattern typical of OLAP systems, where queries scan large volumes of historical data rather than touching single rows.

They’re a poor fit for data that must always reflect the very latest write — a bank balance or an inventory count that another process depends on immediately shouldn’t be served from a view that might be minutes or hours stale.

Materialized view vs regular view

	View	Materialized view
Storage	None — query only	Physical, stored data
Read cost	Full query cost every time	As cheap as reading a table
Freshness	Always current	As fresh as last refresh
Write/refresh cost	None	Cost paid at refresh time
Best for	Cheap or rarely-read queries	Expensive, frequently-read queries

Materialized views vs other performance tools

Materialized views aren’t the only way to speed up expensive reads. Database indexing speeds up the underlying query itself rather than precomputing its result, and is often the first thing to try before reaching for a materialized view. A cache like Redis can serve a similar purpose to a materialized view — storing a precomputed result for fast reads — but typically lives outside the database and is managed by the application rather than the database engine. Database replication solves a different problem entirely: distributing read load across copies of the same live data, not precomputing an expensive query. Complex aggregations that benefit most from materialization often involve the same window functions that make a query costly to run repeatedly in the first place.

Indexing a materialized view

Because a materialized view is stored as physical data, it can itself be indexed just like a regular table — a materialized view isn’t a substitute for indexing so much as a complement to it. A common pattern is to materialize an expensive aggregation once, then add an index on the materialized view’s own columns so that filtering or sorting the precomputed results is fast too. Skipping this step is a common mistake: teams materialize a heavy query expecting an automatic win, then find reads against the view are still slow because the view itself was never indexed for the access patterns actually used against it.

The takeaway

A materialized view trades freshness for read speed by storing a query’s result as physical data instead of recomputing it every time. It’s the right tool when a query is expensive, read often, and tolerant of some staleness — dashboards and analytical rollups are the classic case. Pick a refresh strategy that matches how quickly the underlying data actually changes, and reach for indexing or caching first if the staleness tradeoff isn’t one you’re willing to make.