Views & Materialized Views
A saved query that runs live, versus a saved query that's precomputed and refreshed on a schedule.
Overview
A view is a saved SELECT query that behaves like a virtual table — every time you query it, it runs the underlying query live, against current data. A Materialized ViewA saved query whose result is computed once and stored physically, then served from that stored copy until an explicit refresh runs — fast reads, at the cost of freshness.Learn more is the same underlying idea, but the result is actually computed once and stored physically, then served from that stored copy until you explicitly refresh it. The choice between them is a direct trade-off between always-current data and read speed.
Why It Exists
Views exist to simplify a complex or frequently repeated query behind a simple name, and to control exactly what a caller can see without exposing the underlying tables directly. Materialized views exist for a narrower, more specific reason: some queries — especially large aggregations — are genuinely expensive to compute, and recomputing them from scratch on every single read doesn't scale. A materialized view trades a bit of freshness for a large amount of read speed, by doing the expensive work once and reusing the result.
Real World Example
A `monthly_sales_summary` aggregates millions of rows from `order_items` to produce totals by product and month. As a plain view, that aggregation reruns in full every single time someone opens the dashboard — correct, but slow under real load. As a materialized view refreshed nightly, the dashboard instead reads a small, already-computed table almost instantly — at the cost of showing numbers that are, at worst, a day old. Neither choice is wrong; they're answering different requirements — one prioritizes always-current numbers, the other prioritizes a fast page load.
Example Data
order_items — millions of raw rows underneath
| id | product | quantity | order_date |
|---|---|---|---|
| 1 | Laptop | 1 | 2026-07-03 |
| 2 | Laptop | 1 | 2026-07-18 |
| 3 | Mouse | 3 | 2026-07-05 |
monthly_sales_summary — the view/materialized view result the dashboard actually reads
| month | product | total_units |
|---|---|---|
| 2026-07 | Laptop | 2 |
| 2026-07 | Mouse | 3 |
How It Works
A view stores only the query text, not any data of its own — every read executes that query fresh against the current state of the underlying tables, so it's always accurate but never faster than the query it wraps. A materialized view executes its query once and physically stores the result set as its own data. Subsequent reads hit that stored data directly, which is fast, but the data is frozen at whatever point it was last computed — it only updates when a `REFRESH` runs, which depending on the engine can be triggered manually, on a schedule, or by a trigger. Until that refresh happens, the materialized view is, by design, serving stale data.
Diagram
Same underlying query, two different freshness/speed trade-offs
Query: monthly sales aggregation
View
runs live on every read — always fresh, always slow
Materialized View
computed once, refreshed on schedule — fast, can be stale
Dashboard reads the result
Common Mistakes
Using a materialized view for data that must always be perfectly current, like an account balance
Why: A materialized view is stale by design between refreshes — using it anywhere correctness depends on the latest write silently introduces a window of wrong data.
Fix: Reserve materialized views for reporting and analytics use cases where slightly-stale data is genuinely acceptable, never for the system of record.
Stacking plain views on top of other views, several layers deep
Why: Each layer hides how expensive the underlying combined query actually is — a query plan built on nested views can quietly become far slower than it looks from the top-level SQL.
Fix: Check the real execution plan (EXPLAIN) of a nested view before assuming it's cheap — a view is a convenience for readability, not a guarantee of performance.
Creating a materialized view without automating its refresh
Why: It silently freezes at whatever point it was last refreshed — there's no error, just quietly compounding staleness that's easy to miss until someone notices the numbers look wrong.
Fix: Automate the refresh (a cron job, a trigger, or the engine's native scheduled refresh) and monitor its last-refreshed timestamp.
Interview Questions
What's the core difference between a view and a materialized view?
A view stores only the query itself and runs it live, against current data, every time it's queried. A materialized view stores the actual computed result, which stays fixed until it's explicitly refreshed.
Why would you choose a materialized view over just optimizing the underlying query?
Optimization has a ceiling — some aggregations over large datasets are expensive no matter how well-indexed the underlying tables are. A materialized view sidesteps that ceiling entirely by not recomputing the aggregation on every read, at the cost of the data being only as fresh as the last refresh.
How would you decide the refresh strategy for a materialized view in a production system?
It depends on how stale the data is allowed to be and how expensive the underlying query is. A scheduled refresh (nightly, hourly) is simplest and works when a known staleness window is acceptable. A trigger-based or incremental refresh keeps data fresher but adds real complexity and write-side cost. The deciding question is always the same one that decides materialized-view usage in general: what's the actual business tolerance for stale data on this specific view.
Production Best Practices
Do
✓Use plain views to simplify or restrict access to a complex query, when live accuracy matters.
✓Use materialized views specifically for expensive aggregations where some staleness is acceptable.
✓Automate and monitor materialized view refreshes.
Don't
✗Don't use a materialized view anywhere correctness depends on the absolute latest write.
✗Don't assume a view is free just because it reads like a simple SELECT.
✗Don't create a materialized view without a concrete plan for keeping it refreshed.
Comparison
| View | Materialized View | |
|---|---|---|
| Stores data | No — just the query | Yes — the computed result |
| Freshness | Always current | As of last refresh |
| Read speed | Same as the underlying query | Fast — reads stored data |
| Best for | Simplifying/restricting a live query | Expensive aggregations, reporting |