What is a materialized view, how does it differ from an ordinary non-materialized view, and when would you introduce one?
answer
- view = stored query, MV = stored rows
- reads cheap, data stale
- index it like a table
- read-heavy vs change rate
- try an index on base tables first
basics
~20 sA materialized view stores the actual result rows of its query on disk, like a table. An ordinary view stores only the query text and recomputes it on every read. You trade freshness and storage for cheap reads of expensive queries.
solid answer
~50 sAn ordinary view is a stored query. Reading it expands the definition into your statement and runs the underlying work, so results are always transactionally current and you pay full cost on every read. A **materialized view** stores the computed result set physically. Reads become a scan of precomputed rows, often orders of magnitude cheaper. The price is **staleness** (data is as of the last refresh), extra **storage**, and an ongoing job to refresh it. I reach for one when a query is expensive and read far more often than its base data changes: heavy multi-table aggregations behind a dashboard, rollups over a large fact table, expensive derived computations. I avoid one when readers need transactionally current data, or when the base tables churn so fast that refresh cost approaches simply running the query. A materialized view also carries its own indexes and statistics, which a plain view cannot.
code
sql · 7 linesCREATE MATERIALIZED VIEW daily_sales AS
SELECT store_id, sale_date, SUM(amount) AS total, COUNT(*) AS orders
FROM orders
GROUP BY store_id, sale_date;
CREATE INDEX daily_sales_store_date_idx
ON daily_sales (store_id, sale_date);go deeper
Say it plainly: a view stores the query, a materialized view stores the result rows, so reads are fast but the data is only as fresh as the last refresh.
Add the decision rule (read frequency vs change rate vs query cost) and mention that you can index a materialized view.
Frame it as an operational commitment: refresh scheduling, staleness contract with the consumer, monitoring for refresh lag and failure.
Position it among alternatives — base-table indexes, application caches, stream-maintained read models — and be explicit about the staleness budget the product can absorb.
## Two things called "view" A **view** is a named query stored in the catalog. It holds no data. Selecting from it expands the stored definition into your statement, and the engine executes the underlying joins, filters and aggregations. Consequences: the result is always transactionally current, and you pay the full computation cost on every read. A **materialized view (MV)** stores the defining query *and* a physical copy of its result rows. It is a real segment on disk with its own pages, its own statistics, and usually its own indexes. Reading it does not touch the base tables at all. ## What you buy and what you pay You buy read cost. A dashboard aggregate that scans 500 million fact rows and groups them may take 40 seconds; the materialized result may be 5,000 rows that a client reads in milliseconds. Because the MV is physical, you can index it for the access paths readers actually use, and the optimizer costs it like a table. You pay three things: 1. **Staleness.** The contents reflect the base data as of the last refresh. Between refreshes the MV is provably wrong relative to the base tables. Any use of an MV is an explicit statement that some staleness window is acceptable. 2. **Storage and write amplification.** The rows exist twice, and every refresh is work the database would not otherwise do. 3. **Operational surface.** Something must trigger refreshes, refreshes can fail, they can lag, and readers can be blocked while one runs. ## The decision rule The ratio that matters is *read frequency against base-data change rate*, weighted by the cost of the query. An MV pays when the same expensive result is read many times per change, and when the answer is allowed to be a few minutes or hours old. Good fits: reporting and dashboard aggregates; pre-joined denormalized read models; expensive derived values such as text-search vectors, geospatial simplifications, or scored rankings; a stable summary that many services read. Poor fits: anything a user must see immediately after their own write (a balance, an order status); base tables with churn so high that continuous refresh costs more than the ad-hoc query; results small and cheap enough that an index on the base table solves it. Try the index first — an MV is a heavier tool. ## Related but different mechanisms A plain view is sometimes materialized *implicitly and temporarily* inside a single query plan (an optimizer may spool a subquery result). That is per-statement and invisible; it is not a materialized view, because nothing persists between statements. A hand-rolled **summary table** maintained by application code or triggers is functionally the same idea, minus the database's knowledge that it derives from a query. The MV's advantage is that the engine knows the definition, so it can refresh it correctly (and, in some engines, rewrite queries to use it automatically). ## What interviewers listen for That you name staleness unprompted. Candidates who describe an MV as "a faster view" without saying "it is a snapshot, it is stale, someone must refresh it" have not understood the trade.
- How would a reader of a materialized view know how stale the data is?Publish the refresh timestamp alongside the data: either a column set at refresh time, or a metadata row/table the refresh job updates, or the engine's catalog view of last refresh time. The UI or API then shows "as of HH:MM". Without that, users interpret stale numbers as wrong numbers and lose trust in the report.
- When would you prefer an index on the base tables instead of a materialized view?Whenever an index can turn the query into a cheap access path — selective filters, sorted retrieval, covering index for a narrow projection. Indexes stay transactionally current and cost nothing extra to keep correct beyond write overhead. Materialized views earn their keep when the cost is inherent aggregation or wide joins over many rows, which no index can eliminate.
A view is a recipe; a materialized view is the cooked dish in the fridge. Instant to serve, but only as fresh as the last time you cooked.
saying these in an interview costs you the question
- Calling a materialized view "a view with caching" without mentioning staleness
- Assuming it updates automatically whenever base tables change
- Thinking a plain view stores data or improves performance by itself
- Reaching for a materialized view before trying an index on the base tables
- Believing you can never index a materialized view