Part 2 of 5 · 1 min read
A materialized view stores its result; an ordinary view does not
An ordinary view re-runs its query every time; a materialized view stores the result physically.
Think of two kinds of restaurant menus. One recalculates today's specials fresh every single time a customer asks. The other printed today's specials once this morning and simply hands out the same printed page all day.
In plain words: an ordinary view is a saved query definition. Querying it re-runs the underlying query every single time, paying its full cost on every access. A materialized view, by contrast, physically stores the result of that query on disk, like a regular table, and querying it reads the stored result directly, without re-running the underlying query at all, until that stored result is explicitly refreshed.
A worked example. A view summarizing total revenue per customer, defined as a SUM over all orders, recalculates that sum from scratch on every single query if it is an ordinary view, but reads an already-computed, stored number instantly if it is a materialized view.
The key lines.
CREATE MATERIALIZED VIEW customer_revenue AS
SELECT customer_id, SUM(price * quantity) AS total_revenue
FROM order_items JOIN books USING (book_id)
GROUP BY customer_id;
-- stores this result physically; querying customer_revenue reads it directly
Materialized view
A materialized view physically stores the result of a query on disk, like a table, so querying it reads the stored result directly instead of re-running the underlying query each time.
Example: a printed menu of today's specials, handed out all day rather than recalculated per customer, mirrors a materialized view's stored result.