CSE Roadmap

Common tier · about 2 hours · 5 parts

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.