What is a materialized view, and how does it differ from a regular view?
Quick Answer
A regular `VIEW` is just a saved, named query — it has no storage of its own and re-executes its underlying query every time it's referenced. A `MATERIALIZED VIEW` actually stores the query's result set physically on disk, so reading it is as fast as reading a regular table, but the stored data goes stale until explicitly (or, on some engines, automatically) refreshed. The tradeoff is always the same: a materialized view trades read speed for staleness and refresh cost, while a regular view trades neither but pays the full underlying query cost on every read.
Detailed Answer
Regular view — a saved query, no storage
CREATE VIEW active_customer_totals AS
SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;
SELECT * FROM active_customer_totals WHERE lifetime_total > 1000;
Querying the view re-runs the underlying JOIN/GROUP BY every single time — it's purely a naming/abstraction convenience (hiding query complexity, centralizing a business definition, or restricting column access for security) with zero performance benefit over just writing the underlying query directly. It's always exactly as current as the base tables, because there's no cached/stored copy at all.
Materialized view — stored, physical results
CREATE MATERIALIZED VIEW active_customer_totals_mv AS
SELECT c.id, c.name, SUM(o.total) AS lifetime_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name;
-- Reading this is now a simple table scan/index lookup -- no join/aggregation cost
SELECT * FROM active_customer_totals_mv WHERE lifetime_total > 1000;
-- But it's now a snapshot -- must be explicitly refreshed to reflect new data
REFRESH MATERIALIZED VIEW active_customer_totals_mv;
By default, REFRESH in PostgreSQL takes a lock that blocks concurrent reads during the refresh; REFRESH MATERIALIZED VIEW CONCURRENTLY (which requires a unique index on the view) avoids that at the cost of a slower refresh.
Key tradeoffs
| Regular view | Materialized view | |
|---|---|---|
| Storage | None (just a saved query) | Full physical copy of the result set |
| Read speed | Same as running the underlying query | Fast — like reading a table |
| Data freshness | Always current | Stale until refreshed |
| Write/refresh cost | None | Refresh re-runs the whole query (or does incremental refresh, if supported) |
| Can be indexed | No, but the underlying query can use base-table indexes | Yes — indexes can be created directly on the materialized view itself |
When to use each
- Regular view: encapsulating a complex or security-sensitive query behind a simple name, with no performance goal — freshness matters more than read speed, or the underlying query is cheap enough that re-running it is a non-issue.
- Materialized view: an expensive aggregation/join that's read far more often than the underlying data changes — dashboards, reporting rollups, expensive analytics queries — where a controlled amount of staleness (refreshed hourly, nightly, or on a trigger) is an acceptable tradeoff for dramatically faster reads.
Refresh strategies
Scheduled (cron + REFRESH MATERIALIZED VIEW), triggered (refresh after a batch ETL job completes), or, in engines/extensions that support it, incremental refresh (only recomputing the delta rather than the whole view) — full refresh is the simplest but scales poorly if the underlying query is expensive and the view is large.