36. Views and materialized views

A view is a saved query, nothing more

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customer
WHERE activebool = true;

A view is a named, saved SELECT statement. Querying it — SELECT * FROM active_customers — runs the underlying query fresh, every time, exactly as if you'd typed the full query out yourself. A view stores no data of its own; it's purely a convenience and an abstraction layer.

Why bother — two real reasons

1. Hiding complexity behind a stable name. Recall the film_revenue CTE from Module 6 — a view around it means every future query just says SELECT * FROM film_revenue WHERE ... instead of re-deriving the join every time:

CREATE VIEW film_revenue AS
SELECT f.film_id, f.title, sum(p.amount) AS revenue
FROM film f
JOIN inventory i ON i.film_id = f.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
JOIN payment p ON p.rental_id = r.rental_id
GROUP BY f.film_id, f.title;

SELECT title, revenue FROM film_revenue WHERE revenue > 100 ORDER BY revenue DESC LIMIT 5;

2. Access control (only previewed here — GRANT and database roles are beyond this course's scope): a view can expose a restricted set of columns/rows from a sensitive table, and you grant access to the view instead of the underlying table — a reporting user can query active_customers without ever having direct SELECT privilege on the full customer table (which might have columns you don't want them to see).

A view is always current — because it isn't stored

Since a view re-runs its query every time, it can never be stale — change the underlying customer table, and active_customers immediately reflects it, with zero extra work to keep it in sync. The cost: a view built on an expensive query (many joins, heavy aggregation) pays that expense every single time it's queried — a view is a convenience for writing queries, not a performance optimization.

Materialized views: a view that actually stores its result

CREATE MATERIALIZED VIEW film_revenue_mat AS
SELECT f.film_id, f.title, sum(p.amount) AS revenue
FROM film f
JOIN inventory i ON i.film_id = f.film_id
JOIN rental r ON r.inventory_id = i.inventory_id
JOIN payment p ON p.rental_id = r.rental_id
GROUP BY f.film_id, f.title;

A materialized view runs its query once, at creation time, and stores the result physically — querying it afterward reads the stored result directly, no re-computation, genuinely fast regardless of how expensive the underlying query was. The tradeoff, symmetric to the plain view's tradeoff: it goes stale the moment underlying data changes, and stays stale until explicitly refreshed:

REFRESH MATERIALIZED VIEW film_revenue_mat;

REFRESH re-runs the full query and replaces the stored result entirely — by default, this locks the materialized view against reads for the duration (a real operational concern for a materialized view queried constantly). REFRESH MATERIALIZED VIEW CONCURRENTLY avoids that lock (readers keep working against the old data until the refresh completes) but requires a unique index on the materialized view first, and is somewhat slower than a plain refresh.

Choosing between them

The deciding question: "Is it acceptable for this data to be slightly out of date, in exchange for much faster reads?" A live sales dashboard checked every few seconds needs a plain view (or direct queries) — staleness would be actively misleading. A "revenue by film, updated nightly" report is a textbook materialized view — nobody needs minute-by-minute freshness, and the underlying five-table join is exactly the kind of repeated cost worth paying once instead of on every page load.

This is also a direct, safer instance of the "deliberate denormalization" idea from Module 8's normalization lesson — the canonical, normalized tables remain the single source of truth; the materialized view is a disposable, regenerable cache on top, not a second copy of the truth that could drift and be trusted incorrectly.

Check yourself

1. What does a plain view actually store on disk?
2. After you UPDATE a row that a materialized view's query depends on, what do you see if you immediately query the materialized view? What fixes it?
3. Give one scenario where a plain view is clearly the right choice over a materialized view, and one where the reverse is true.