Materialized View
A precomputed query result stored as a physical table and refreshed periodically. Trades storage for read performance on expensive aggregations.
What is Materialized View?
In short
A materialized view is a database object that stores the result of a query as a physical table on disk, so reads hit precomputed data instead of recomputing the query every time. Unlike a regular view, which runs its query on every access, a materialized view is refreshed on a schedule or on demand, trading storage space and data freshness for much faster reads on expensive joins and aggregations.
What it actually is
A normal database view is just a saved query. When you select from it, the database runs the underlying query right then, every single time. That is fine for a simple filter, but if the view joins five tables and groups millions of rows, every read pays the full cost.
A materialized view stores the answer. The database runs the query once, writes the result rows to disk as a real table, and from then on a read just scans those stored rows. You get the convenience of querying a view with the speed of reading a precomputed table.
The catch is staleness. The stored data reflects the moment the view was last built or refreshed. If the base tables change, the materialized view does not update itself until you refresh it. So you are choosing slightly old but instantly available data over always-fresh but slow-to-compute data.
How refresh works under the hood
When you create a materialized view, the engine executes the defining query and persists the output. Reading it afterward never touches the base tables, so there is no join or aggregation cost at query time.
Refreshing replaces or updates that stored data. A full refresh re-runs the whole query and swaps in the new result. PostgreSQL does exactly this with REFRESH MATERIALIZED VIEW, and the CONCURRENTLY option lets reads keep working against the old copy while the new one is built, at the cost of needing a unique index.
Incremental or fast refresh only applies the rows that changed since the last refresh, which is far cheaper for large views. Oracle supports this with materialized view logs that track base-table changes, and Snowflake and BigQuery maintain their materialized views automatically in the background. The trade-off is that incremental refresh imposes restrictions on what your query can contain.
Refresh is usually triggered one of three ways: on a fixed schedule through a cron job or database scheduler, on demand right after a known bulk load, or automatically by the engine when it detects base-table changes.
When to use it and the trade-offs
Reach for a materialized view when the same expensive query runs often and the result does not need to be exactly current to the second. Dashboard rollups, daily revenue summaries, leaderboard rankings, and search facets are classic cases. A report that aggregates a billion rows can drop from 30 seconds to a few milliseconds because the work was done ahead of time.
The costs are real. You spend extra storage holding a second copy of the data. You spend compute on every refresh. And you accept staleness, so anything that must reflect the latest write, like a bank balance or current inventory, is a poor fit.
Avoid them when the base data changes constantly and reads also demand freshness, because you would be refreshing nearly as often as you read and gaining nothing. In that situation a regular view, a covering index, or an application-side cache like Redis is usually the better tool. Materialized views shine specifically when reads vastly outnumber writes.
A concrete example
Imagine an analytics dashboard for an online store that shows total sales per product category for the last 30 days. The raw orders table has 200 million rows. Computing that aggregation live on each page load would hammer the database and take many seconds.
Instead you define a materialized view that groups orders by category and sums the revenue. The dashboard selects from this view and returns in single-digit milliseconds because it is reading a few hundred precomputed rows.
You schedule a refresh every 15 minutes. Business users see numbers that are at most 15 minutes old, which is completely acceptable for a sales dashboard, and the production order-processing system never feels the analytics load. That separation of fast stale reads from the live write path is the whole point.
Where it is used in production
PostgreSQL
Native CREATE MATERIALIZED VIEW with manual REFRESH, including REFRESH CONCURRENTLY to avoid blocking readers.
Snowflake
Maintains materialized views automatically in the background and transparently rewrites queries to use them when beneficial.
Google BigQuery
Materialized views auto-refresh on base-table changes and the planner silently rewrites matching queries to read them.
Oracle Database
Pioneered fast incremental refresh using materialized view logs that record base-table deltas between refreshes.
Frequently asked questions
- What is the difference between a view and a materialized view?
- A regular view runs its query every time you read it, so the data is always current but reads can be slow. A materialized view stores the query result on disk, so reads are fast but the data is only as fresh as the last refresh.
- How do you keep a materialized view up to date?
- You refresh it. That can be a full refresh that re-runs the whole query, an incremental refresh that applies only changed rows, or automatic maintenance by the engine. Refreshes are triggered on a schedule, on demand after a data load, or by change detection.
- Do materialized views slow down writes?
- Inserts and updates to the base tables are not slowed by a basic materialized view, since refresh happens separately. But incremental refresh schemes like Oracle materialized view logs add some write overhead to track changes, and frequent full refreshes consume compute that competes with other workloads.
- When should I use a cache like Redis instead?
- Use Redis when you need sub-millisecond key lookups, custom eviction, or caching across many services. Use a materialized view when the cached result is itself a SQL query over your own tables and you want the database to own and refresh it. They often coexist.
- Can I index a materialized view?
- Yes. Because the data is physically stored, you can build indexes on a materialized view just like a normal table, which makes filtered or sorted reads against it even faster. PostgreSQL even requires a unique index to allow concurrent refresh.
Learn Materialized View hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
Related lessons
Lessons that touch on Materialized View as part of a larger topic.
Materialized Views
Pre-computed query results stored on disk, the bridge between views and denormalization
foundation · database fundamentals
Data Aggregation
Combine data from multiple sources into a unified view, building the single source of truth
intermediate · data governance compliance
Stream-to-Table Joins
Enriching stream events with reference data, lookup joins, broadcast state, and materialized views
advanced · stream batch processing
See also
Related glossary terms you might want to look up next.
Database View
A virtual table defined by a SQL query. Simplifies complex joins, enforces access control, and presents data in a specific shape without duplicating storage.
Caching
Storing frequently accessed data in a faster storage layer so you don't have to fetch it from the original (slower) source every time.
Denormalization
Intentionally adding redundant data to database tables to speed up read queries by avoiding expensive joins. Trades storage and write complexity for read performance.
Redis
An in-memory data store used as a cache, message broker, and database. Blazing fast because everything lives in RAM.
Memcached
A simple, high-performance distributed memory caching system. Stores key-value pairs in RAM. Simpler than Redis but less feature-rich.
Document Database
A NoSQL database that stores data as flexible JSON-like documents. MongoDB and CouchDB let each document have a different structure.