What is a Materialized View?
Learn what a materialized view is, how it differs from a regular view, and when to use one for faster query performance.
Expected Interview Answer
A materialized view is a database object that stores the physical result of a query on disk, unlike a regular view which recomputes its result every time it is queried.
A standard view is just a saved SQL query — every time you select from it, the underlying query runs fresh against the base tables. A materialized view instead executes the query once and persists the output as an actual table-like structure, so subsequent reads are fast lookups instead of full recomputation. Because the underlying base tables can change after the materialized view is built, it becomes stale and must be refreshed periodically or on demand, either fully or incrementally. This trades storage space and data freshness for significantly faster read performance, making materialized views ideal for expensive aggregate reports and dashboards.
- Speeds up expensive aggregate queries
- Reduces repeated computation load
- Supports fast dashboard and reporting reads
- Allows indexing on precomputed results
AI Mentor Explanation
A regular view is like asking a statistician to recalculate a batter’s career average from every single scorecard ever recorded, every time a commentator asks. A materialized view is like printing that average onto a physical stat sheet once, so the commentator just glances at the sheet instantly instead of waiting for a fresh recalculation. The catch is that the printed sheet goes out of date after the batter plays a new innings, so someone must reprint it periodically to keep it current.
Step-by-Step Explanation
Step 1
Define the query
Write the SQL query that computes the expensive aggregate or join you want to speed up.
Step 2
Create the materialized view
Persist the query result physically using CREATE MATERIALIZED VIEW instead of CREATE VIEW.
Step 3
Index the materialized view
Optionally add indexes on the stored result for even faster subsequent lookups.
Step 4
Refresh on schedule or demand
Periodically run REFRESH MATERIALIZED VIEW to sync it with the latest base table changes.
What Interviewer Expects
- Clear contrast with a regular (non-materialized) view
- Understanding of staleness and refresh strategies
- Recognition of the storage-versus-speed trade-off
- A realistic use case such as dashboards or reports
Common Mistakes
- Saying a materialized view always auto-updates in real time
- Confusing materialized views with regular views
- Not mentioning the need to periodically refresh the data
- Failing to identify a good use case for materialized views
Best Answer (HR Friendly)
“A materialized view stores the actual result of a query on disk, rather than recalculating it every time like a regular view does. This makes reads much faster for expensive queries, but it means the data can become slightly outdated until the view is refreshed, so it works best for reports and dashboards where near-real-time data is acceptable.”
Code Example
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY region, DATE_TRUNC('month', order_date);
-- Fast reads afterward
SELECT * FROM monthly_sales_summary WHERE region = 'APAC';
-- Refresh when base data changes
REFRESH MATERIALIZED VIEW monthly_sales_summary;Follow-up Questions
- How is a materialized view different from a regular view?
- What is the difference between a full refresh and an incremental refresh?
- When would you choose a materialized view over caching in the application layer?
- Can you index a materialized view?
MCQ Practice
1. What distinguishes a materialized view from a regular view?
A materialized view persists the computed result to disk, unlike a regular view which recalculates on every query.
2. What is the main trade-off of using a materialized view?
Since the result is precomputed and stored, it can drift out of sync with the base tables until refreshed.
3. Which SQL command updates a materialized view with the latest data?
REFRESH MATERIALIZED VIEW re-executes the underlying query and updates the stored result.
Flash Cards
What is a materialized view? — A database object that stores the physical result of a query on disk for fast repeated reads.
How does it differ from a regular view? — A regular view recomputes its query every time; a materialized view persists the result and must be refreshed.
What is the main downside? — The stored data can become stale until the view is refreshed.
Name a good use case. — Dashboards and reports needing fast reads on expensive aggregate queries.