intermediate~3h

Views — Simple View & Materialized View

Views let you name a complex query once so everyone reuses the same definition; materialized views take that a step further by physically storing the result, trading freshness for speed.

Learning objectives

Explain what actually happens, internally, when a query runs against a view versus a materialized view.
Decide correctly when a materialized view is worth the staleness it introduces, and when a simple view is enough.
Set up a materialized view so it can be refreshed without blocking concurrent readers.

Once a query gets complex enough (a five-table join with several filters) to matter that everyone runs the same version of it, you need a way to name and share that definition once — that's what makes views a distinct topic worth learning, not just "a SELECT with extra steps."

Without a view, every analyst or application writing that same complex query independently risks a subtle divergence — one adds an extra filter, another forgets a join condition — and now "active EU customer" quietly means something different depending on who wrote the query.

View — a named, saved query with no storage of its own; always re-executes against current data. Materialized view — a named query whose result is physically stored on disk, refreshed on demand rather than recomputed on every read.

📖 Story

Imagine a report your manager asks for every single Monday: "active customers in the EU who've spent over $500 this year, joined with their latest order." The underlying query is a five-table join with three filters — nobody wants to retype it, or worse, have five different analysts each write a slightly different version that quietly disagrees with the others. A view is naming that query once, permanently, so everyone runs the exact same definition of "active EU customer" by just querying it like a table.

A view is a saved query, not stored data

A simple (regular) view is just a name attached to a SELECT statement, stored in the database catalog. Every time you query the view, PostgreSQL substitutes the underlying query and runs it fresh, against current data — the view itself holds zero rows of its own. This means a view is always up to date, by construction, but pays the full cost of the underlying query on every single access; it does no work you couldn't do by hand-writing the same SELECT.

A materialized view is a saved query's result, physically stored

A materialized view runs the query once and physically stores the result set on disk, exactly like a table. Querying it afterward reads the stored rows directly, no join or filter recomputation involved. The tradeoff is that the stored data goes stale the moment the underlying tables change — a materialized view only reflects reality as of whenever it was last refreshed, via REFRESH MATERIALIZED VIEW.

	Simple View	Materialized View
Storage	None — just a saved query	Full result set, stored on disk
Data freshness	Always current	Current as of last refresh
Query cost	Full underlying query, every time	Just reads stored rows
Best for	Simplifying/hiding complex or sensitive queries	Expensive aggregations queried often, where slight staleness is fine

What a simple view actually is in the catalog

Creating a view with CREATE VIEW v AS SELECT ... does not execute anything — it stores the query text (as a parsed rule) in pg_rewrite/pg_views. When a query references the view, PostgreSQL's rewrite system literally splices the view's stored query into the outer query in place of the view name, then plans and executes the combined query as if you'd written the join yourself. This is why a view can be indexed on its underlying tables but never has its own index — there's no data of its own to index.

What a materialized view is physically

CREATE MATERIALIZED VIEW mv AS SELECT ... runs the query immediately and writes the output rows into a physical heap, exactly like a regular table's storage — it even gets its own file on disk and can have indexes created on it directly, because it genuinely holds rows.

Refreshing: full rebuild vs. concurrent refresh

REFRESH MATERIALIZED VIEW mv truncates the existing storage and reruns the defining query from scratch, taking an ACCESS EXCLUSIVE lock — any query touching the view blocks until the refresh finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY mv instead computes the new result into a temporary copy, then swaps it in row-by-row using a diff, allowing concurrent reads throughout — but it requires a unique index on the materialized view first, and is slower than a plain refresh for large result sets.

Identify a query that's either repeated often across the codebase, or complex enough that hiding it behind a simple name reduces mistakes — that's your candidate for a simple view.
CREATE VIEW active_eu_customers AS SELECT ... FROM customers JOIN orders ... WHERE country IN (...) AND spend > 500; — the view now behaves like a read-only table for that query.
If that same query is expensive (large aggregation, several joins) and queried very frequently, but doesn't need up-to-the-second freshness, promote it to a materialized view instead: CREATE MATERIALIZED VIEW mv_active_eu_customers AS SELECT ...;.
Add a unique index on the materialized view (CREATE UNIQUE INDEX ON mv_active_eu_customers (customer_id);) so REFRESH ... CONCURRENTLY becomes available.
Schedule a periodic REFRESH MATERIALIZED VIEW CONCURRENTLY mv_active_eu_customers; (cron, or a scheduled job) matching how stale the data is allowed to get — hourly, nightly, whatever the business tolerates.

Use a simple view purely to name and hide complexity — never expect it to make a slow query fast; it runs the exact same underlying plan every time.
Reach for a materialized view specifically when the underlying query is expensive AND queried often AND slight staleness is acceptable — all three conditions, not just one.
Always add a unique index to a materialized view you intend to REFRESH ... CONCURRENTLY, before you need the concurrent refresh — it can't be added as an afterthought during an incident.
Grant access through views to expose only specific columns/rows to a role, instead of granting broad table access — a view is a legitimate access-control tool, not just a convenience.
Name materialized views distinctly (a mv_ prefix is common) so anyone reading a query immediately knows the data might be stale.

⚠️ Why this keeps happening

Views and materialized views look identical from a SELECT statement's perspective — the syntax to query either is exactly the same SELECT * FROM v — so it's easy to forget which kind you're touching, until staleness or performance surprises you in production.

Expecting a simple view to speed up a slow query. A view is not a cache — it re-runs the full underlying query every single time; if the query was slow before, it's exactly as slow through the view.
Forgetting to schedule refreshes for a materialized view. Without an ongoing refresh job, a materialized view silently freezes at whatever data existed when it was created, and nobody notices until a report looks obviously wrong.
Using a plain REFRESH MATERIALIZED VIEW on a view that's queried constantly. The ACCESS EXCLUSIVE lock it takes blocks every concurrent reader for the full duration of the rebuild — on a large view, this can mean a very visible outage.
Creating a materialized view without ever adding a unique index, then discovering during an incident that REFRESH ... CONCURRENTLY simply isn't available and you're stuck with the blocking version.
Chaining views on top of views on top of views. Each layer adds its own rewrite step; a deeply nested view stack can produce a surprisingly bad final query plan that's hard to reason about from any single view's definition.

Prefer a materialized view over a simple view specifically when the same expensive aggregation is queried repeatedly and a refresh cadence (hourly, nightly) is acceptable — this is the entire performance case for materializing anything.
Index a materialized view exactly as you would a real table, based on the actual query patterns run against it — it gets none of its indexes for free.
Use REFRESH MATERIALIZED VIEW CONCURRENTLY for anything read during business hours — the extra time it takes over a plain refresh is almost always worth avoiding a full read-blocking outage.
Don't materialize a view just because a query is complex — complexity alone isn't a performance problem; only materialize when the query is genuinely expensive to compute.
Watch the refresh duration over time as underlying tables grow — a materialized view that refreshed in seconds at launch can silently grow into a multi-minute job a year later.

Views are a legitimate row/column-level access-control tool — grant a role access to a view exposing only permitted columns/rows, instead of broader table access, so the underlying table's full schema and sensitive columns stay hidden from that role entirely.

Track "last refreshed at" for every materialized view and alert if a scheduled refresh silently stops running — a materialized view that stops refreshing doesn't error, it just quietly serves stale data forever until someone notices a report looks wrong.

Track "last refreshed at" for every materialized view (many teams add a small metadata table or check pg_stat_user_tables) and alert if a refresh hasn't run within its expected window.
Document, next to each materialized view's definition, exactly how stale it's allowed to be and who depends on it — this is the difference this view is making a tradeoff on, and it should be explicit, not tribal knowledge.
Version-control view and materialized view definitions in your migration history like any other schema object — a view silently redefined outside of migrations is a common source of "it used to return different columns" bugs.
Set up the refresh job (cron, scheduled worker) with retry-and-alert on failure — a materialized view that silently stops refreshing due to a failed job is worse than one that was never created, because everyone assumes it's current.
Before dropping or renaming a view, grep the codebase and any BI/reporting tools for references — views are an easy thing to break silently for a downstream consumer nobody remembered.

Create a simple view over a 2-table join with a WHERE filter, then confirm (EXPLAIN) that querying the view produces the identical plan as running the join manually.
Create a materialized view over an expensive aggregation (e.g., COUNT/SUM grouped across a large joined dataset), then compare query time against the simple-view version of the same query.
Modify the underlying table's data, then query both views — confirm the simple view reflects the change immediately while the materialized view does not, until refreshed.
Add a unique index to the materialized view and run REFRESH MATERIALIZED VIEW CONCURRENTLY, confirming the view remains queryable by a second concurrent session throughout the refresh.

✓ Quick recap

A simple view is a saved query with zero storage of its own — always current, always pays the full underlying query cost.
A materialized view physically stores its result set — fast to query, but only as fresh as its last REFRESH.
REFRESH MATERIALIZED VIEW blocks concurrent readers; REFRESH ... CONCURRENTLY doesn't, but requires a unique index first.
Materialize specifically when a query is expensive, frequent, and tolerant of some staleness — not just because it's complex.
Views (both kinds) are also a legitimate access-control tool for exposing a restricted subset of columns/rows to a role.

Want a visual for this concept?

Generate a diagram tailored to “Views — Simple View & Materialized View” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Set Operators — UNION, INTERSECT, EXCEPT/MINUS →← Back to all SQL chapters