Materialized views store the result of a query as actual rows on disk, computed once and reused, rather than recalculating that result every time someone asks for it. A regular view is just a saved query — a name you can select from that expands back into the underlying SQL on every single call. A materialized view runs that expensive query once, saves the output as if it were a table, and serves every subsequent read from that saved copy until you explicitly refresh it. The entire feature is a trade of freshness for speed, and understanding that trade is most of what you need to use it well.
What changed in 2026
- Incremental refresh moved from Materialize.com's core pitch into wider awareness. Streaming-first databases now maintain materialized views continuously as new rows arrive, instead of recomputing the whole thing on a schedule.
REFRESH ... CONCURRENTLY became the default recommendation everywhere, not just an advanced tip, once teams got burned by the locking behavior of a plain refresh in production.- Managed Postgres providers added scheduled-refresh UI, turning what used to require a cron job and a script into a dashboard setting.
Materialized view vs regular view vs table
|
Regular view |
Materialized view |
Plain table |
| Storage |
None — just a saved query |
Actual rows, computed once |
Actual rows, written directly |
| Freshness |
Always current |
As of last refresh |
As of last write |
| Read speed |
Same as running the query |
Fast — a simple row read |
Fast — a simple row read |
| Write path |
N/A |
Refresh recomputes it |
Normal inserts/updates |
| Best for |
Simplifying a repeated query, no perf cost accepted |
Expensive, repeated reads that can tolerate some lag |
Data your app writes directly |
How refresh works
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month,
SUM(total_cents) AS revenue_cents
FROM orders
GROUP BY 1;
-- Rebuilds the data; blocks reads against the view while it runs
REFRESH MATERIALIZED VIEW monthly_revenue;
-- Rebuilds without blocking readers; requires a unique index first
CREATE UNIQUE INDEX ON monthly_revenue (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
The plain REFRESH takes an exclusive lock and swaps the data in one step, which is simple but means queries against the view wait until it finishes. CONCURRENTLY builds the new version alongside the old one and swaps atomically, at the cost of needing a unique index and roughly double the temporary storage during refresh.
When a materialized view is the right tool
The classic case is a dashboard or report built on a heavy aggregation — a revenue-by-month rollup, a leaderboard, a search-facet count — that gets queried constantly but only needs to reflect data from a few minutes or hours ago. Instead of every dashboard load re-running a multi-table join and aggregation, it reads a plain, fast table, and a scheduled job refreshes that table on whatever cadence the business actually needs. This is a form of manual query optimization: you are not making the underlying query faster, you are making it run less often.
Staleness and other tradeoffs
The data is exactly as fresh as the last refresh and not a moment fresher — if the refresh runs every hour and someone asks "what changed in the last five minutes," the materialized view has no answer. Refreshing is also not free: a full refresh recomputes the entire query, which can be expensive on a large source table, and running it too often can cost more than the query it is supposed to save you from running directly. A covering index on the underlying tables can speed up the refresh itself, since the refresh is really just running the original query again under the hood.
FAQ
Can I index a materialized view?
Yes — it behaves like a table for indexing purposes, and in fact a unique index is required if you want to use REFRESH ... CONCURRENTLY.
Does a materialized view update automatically when the source data changes?
No, not in standard Postgres or MySQL — you must refresh it manually or on a schedule. Some newer streaming databases offer incremental, near-real-time materialized views as a distinct feature.
Is a materialized view the same as a cache?
Conceptually similar — both trade freshness for speed — but a materialized view lives inside the database, can be indexed and queried with normal SQL, and is refreshed as a database operation rather than invalidated by application code.
How do I decide the refresh interval?
Match it to how stale a business is willing to tolerate the data being, then check whether a full refresh actually completes comfortably within that interval on real data volume.
Where to go next