What Is a Materialized View? The Saved Answer That Stays Up to Date
Databases let you save queries so you don't have to write them out every time. A saved query like this is called a view, and it's a convenience: you define a complicated query once, give it a name, and afterward you can use that name as if it were a table. It's a useful feature, but it hides a cost that becomes important at scale. A regular view doesn't save the answer to the query. It saves the question.
Every time you use a regular view, the database runs the underlying query all over again, from scratch, against the current data. If that query is expensive, crunching through millions of rows to produce its result, then you pay that full expense every single time you touch the view. A materialized view is the alternative: instead of saving the question and re-asking it constantly, it computes the answer once and stores it, so that using it is as cheap as reading from a table.
The distinction is worth dwelling on, because the two things share a name but behave very differently. A regular view is essentially a stored definition, a shorthand for a query. When you query the view, the database expands it into the full underlying query and runs that against the live data, every time. The upside is that the result is always current, because it's computed fresh from whatever the data says right now. The downside is that you pay the full computational cost on every use, and if the query is heavy and you use the view often, that cost adds up relentlessly.
A materialized view turns this around. When it's created, the database runs the expensive query once and physically stores the result, the actual rows of the answer, as though it were a real table sitting on disk. After that, querying the materialized view doesn't re-run the underlying computation at all. It just reads the stored result, which is fast, often dramatically faster, because reading a precomputed answer is trivial compared to recalculating it from millions of underlying rows. The word "materialized" captures this: the answer has been made material, given concrete stored form, rather than existing only as a question waiting to be re-answered.
This makes materialized views a powerful tool for exactly the situation regular views handle poorly: an expensive query whose result is needed often. A complex aggregation that summarizes a vast transaction table, a report that joins and crunches data from many sources, anything where the computation is heavy and the same result gets requested repeatedly, these are ideal candidates. Compute the result once, store it, and serve it cheaply to everyone who asks, rather than making each request pay the full cost of recalculation. The savings can be enormous when many users or dashboards lean on the same heavy query.
But storing the answer creates the problem that defines everything interesting about materialized views: the stored answer can go out of date. A regular view is always current because it's recomputed every time. A materialized view holds a result computed at a particular moment, and the moment the underlying data changes, the stored answer no longer reflects reality. The materialized view is now stale, showing yesterday's answer to today's data. This is the fundamental tradeoff, the same one that runs through so much of data systems: you bought speed by precomputing, and the price is that the precomputed result drifts away from the truth as the underlying data moves on.
So the central question with any materialized view is how to keep it current, and this is called refreshing. Refreshing re-runs the underlying query and updates the stored result to reflect the current data. The question is when and how often to do it, and the answer is a balancing act. Refresh too rarely and the view shows stale data for long stretches, which may or may not matter depending on what it's for. Refresh too often and you're paying the expensive computation again and again, eroding the very savings that justified the materialized view in the first place. The right refresh cadence depends on how fresh the data needs to be against how costly the recomputation is.
Different refresh strategies suit different needs, and they reveal the range of the tradeoff. Some materialized views are refreshed on a schedule, rebuilt entirely every night, say, which works well when the data feeding them updates in batches and a day-old answer is perfectly acceptable, as it often is for reporting. Some are refreshed on demand, rebuilt when someone decides the result is stale enough to warrant it. And some systems support incremental refresh, where instead of recomputing the entire result from scratch, the view is updated to account only for what changed since the last refresh, which is far cheaper when only a small fraction of the underlying data moved. The more sophisticated the refresh, the better you can keep the view current without paying the full recomputation cost each time.
Deciding whether a materialized view is the right tool comes back to the nature of the data and the use. It shines when the underlying query is expensive, the same result is needed repeatedly, and some staleness is tolerable, the data either changes slowly or doesn't need to be perfectly current for the use at hand. A daily sales summary that thousands of people view, computed from a massive transaction history, fits perfectly: the computation is heavy, the demand is high, and the figures being a few hours old is fine. It's a poor fit when the result must always reflect the absolute latest data, in which case the staleness is unacceptable and a regular view, despite its cost, is the honest choice.
The broader idea is one that recurs throughout data systems and beyond: when the same expensive work is needed over and over, doing it once and reusing the result is often far better than redoing it each time, as long as you can tolerate the result aging between updates. A materialized view is one specific, well-supported application of that principle, aimed at expensive database queries. It trades freshness for speed, and the skill in using it lies in knowing how much freshness you can give up, and arranging the refresh so you give up no more than you must. The saved answer is fast, but only the saved question is always right, and choosing between them is the whole art of it.