We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningIntermediate · Database Types & Storage
A precomputed query result stored as a physical table and refreshed periodically. Trades storage for read performance on expensive aggregations.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Materialized View across a run of lessons, not one page. These 4 alone are about 112 minutes of step-by-step reading, every one with a quiz.
A plain REFRESH made a 7 ms read take 1,231 ms, because it holds an exclusive lock for its whole duration. The concurrent form cost 3 percent more and blocked nobody, and refuses to run without a unique index.
A view and the query it is defined as produced identical plans, character for character. A materialised view answered in 0.017 ms what took 94.929 ms to compute, and is a cache with every problem that implies.
Combine data from multiple sources into a unified view, building the single source of truth.
Enriching stream events with reference data, lookup joins, broadcast state, and materialized views.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Materialized Viewspay once,
yours for life
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Database Views
A view and the query it is defined as produced identical plans, character for character. A materialised view answered in 0.017 ms what took 94.929 ms to compute, and is a cache with every problem that implies
Data Aggregation
Combine data from multiple sources into a unified view, building the single source of truth
Stream-to-Table Joins
Enriching stream events with reference data, lookup joins, broadcast state, and materialized views
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.
This page explains the idea. The "Materialized Views" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Materialized Views lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Materialized View in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
