Part 4 of 5 · 2 min read
Materialized views, in preview
Storing a view's result physically, and what that trades away.
Materialized views, in preview
Think of printing out a saved search's results once, instead of re-running the search every time. The printout is fast to read, but it stops reflecting new emails the moment it was printed, until it is reprinted.
In plain words: a materialized view stores a view's result physically, rather than recomputing it on every query. Reading it is fast, since no underlying query runs each time. The result becomes stale as soon as the underlying data changes, until it is explicitly refreshed.
A worked example. A materialized view summarizing total revenue per month is fast to read for a dashboard, but will not reflect today's new orders until refreshed.
The key lines.
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', placed_on) AS month, SUM(b.price * oi.quantity) AS revenue
FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN books b ON oi.book_id = b.book_id
GROUP BY DATE_TRUNC('month', placed_on);
REFRESH MATERIALIZED VIEW monthly_revenue;
Materialized view
A materialized view stores a view's result physically on disk, read quickly without recomputing the underlying query, but requiring an explicit refresh to reflect new changes.
Example: monthly_revenue reads instantly for a dashboard, but needs REFRESH MATERIALIZED VIEW run on a schedule to stay current.
For the curious Why this is only a preview
Level 6's module on materialized views covers refresh strategies properly: full refresh versus incremental refresh, and how to decide how stale a materialized view is allowed to get for a given use case.
Quick notes
- A view stores a query definition; querying it re-runs that query against current data.
- A simple, single-table view is usually updatable; a view with a join, aggregate, or set operation usually is not.
- A materialized view stores the result physically, fast to read but stale until explicitly refreshed.
- Views give logical data independence in practice, by hiding the underlying table's real shape behind a stable interface.
- Level 6 covers materialized view refresh strategies in depth.