What is a view, and how is a materialized view different?
A view is a saved query that runs each time you select from it and stores no data. A materialized view stores the query result physically, so reads are fast but the data is stale until refreshed.
Explain it simply
A view is a window onto your tables: every time you look, you see the current state. A materialized view is a photograph of that window: quick to look at, but it shows how things were when it was taken.
Example
CREATE VIEW active_students AS
SELECT id, name, city
FROM students
WHERE status = 'active';
SELECT * FROM active_students WHERE city = 'Pune';The second query is expanded into the underlying SELECT on students each time.
-- PostgreSQL, Oracle (MySQL has no native materialized views)
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT month, SUM(amount) AS total FROM sales GROUP BY month;
REFRESH MATERIALIZED VIEW monthly_sales;| View | Materialized view | |
|---|---|---|
| Stores data | No | Yes |
| Always current | Yes | Only after refresh |
| Speed | Same as the query | Fast, reads stored rows |
| Can be indexed | No | Yes |
Views also hide complexity and restrict columns, for example exposing only non-sensitive fields to a team.
What to say in the interview
"A view is a virtual table defined by a query and is always up to date. A materialized view caches the result for speed and needs refreshing. I use materialized views for heavy aggregates that can tolerate some staleness."
Common mistakes
- Thinking a normal view makes queries faster. It does not.