Part 4 of 5 · 2 min read
Trading storage for speed
A materialized view costs extra storage and staleness in exchange for skipping expensive recomputation.
Trading storage for speed
Think of a company pre-printing thousands of copies of a complex report once a day. It trades the paper and ink cost, plus the fact that the report is only as current as this morning's printing, for the ability to hand anyone an instant copy on demand.
In plain words: a materialized view's stored result occupies real storage space, and that result can be stale between refreshes, as covered above. In exchange, a query that reads from the materialized view avoids the cost of re-running a potentially very expensive underlying query, such as one with several large joins or aggregations across millions of rows. This trade is worthwhile specifically when the underlying query is both expensive to run and queried frequently enough that recomputing it every single time would be wasteful.
A worked example. customer_revenue, computed from a large join and aggregation, is read by a dashboard hundreds of times per day; materializing it once nightly is far cheaper overall than recomputing that join and aggregation on every single dashboard view.
The key lines. No new SQL; this is a judgment call about the specific query's cost and read frequency, not a syntax decision.
Storage-for-speed trade-off
The storage-for-speed trade-off of a materialized view is paying extra storage space and accepting some staleness between refreshes, in exchange for skipping the cost of re-running an expensive query on every access.
Example: pre-printing thousands of report copies once a day, accepting the paper cost and the report's morning-old staleness, in exchange for instant handouts, mirrors this trade-off.
Remember this
A materialized view is the right tool specifically when a query is both expensive and read often, and some staleness between refreshes is acceptable. When a query needs to reflect every change instantly, or is cheap to begin with, an ordinary view or a direct query usually remains the simpler, better fit.
Quick notes
- An ordinary view re-runs its query every time; a materialized view stores the result physically.
- A materialized view must be refreshed, fully or incrementally, to stay current with the underlying data.
- A full refresh recomputes everything; an incremental refresh updates only the affected rows, where supported.
- A materialized view trades storage space and staleness for skipping expensive recomputation on every read.
- The trade is worthwhile when a query is both expensive to run and read frequently enough to justify it.