Learn the key differences between views and materialized views in SQL. Visual comparison, code examples, decision guide, and when to use each.
Jump into the SQLNoir case files and put these tips to work.
Views and materialized views both let you save a query and reuse it like a table. The difference: one runs fresh every time, the other caches results on disk. Pick wrong and you get stale dashboards or sluggish reports.
| Feature | View | Materialized View |
|---|---|---|
| Storage | No physical storage (virtual) | Stored on disk (physical) |
| Query Speed | Re-executes every time | Reads from cache (fast) |
| Data Freshness | Always current | Stale until refreshed |
| Disk Space | Zero | Uses storage |
| Updatable? | Sometimes (simple views) | No (read-only snapshot) |
The core tradeoff: freshness vs speed
A view is a saved SQL query that you can reference like a table. It stores no data. Every time you SELECT from it, the database re-runs the underlying query and returns fresh results.
CREATE VIEW active_suspects AS
SELECT
s.suspect_id,
s.name,
s.last_known_location,
c.case_title
FROM suspects s
JOIN cases c ON s.case_id = c.case_id
WHERE c.status = 'open';Now you can query SELECT * FROM active_suspects and it re-runs this JOIN every time.
Every query against a view triggers a full execution of the stored SQL
A materialized view runs the query once and stores the results on disk like a real table. Subsequent reads hit the cache instead of re-executing the query. The tradeoff: you must refresh it to see updated data.
CREATE MATERIALIZED VIEW case_summary AS
SELECT
district,
COUNT(*) AS total_cases,
COUNT(*) FILTER (WHERE status = 'open') AS open_cases,
COUNT(*) FILTER (WHERE status = 'closed') AS closed_cases
FROM cases
GROUP BY district;
-- Refresh when you need updated data
REFRESH MATERIALIZED VIEW case_summary;Queries against case_summary read from the stored snapshot. No aggregation re-runs until you refresh.
Materialized views skip query execution entirely, reading pre-computed results from disk
If you want to practice the JOINs, aggregations, and subqueries that power views and materialized views, SQLNoir's detective cases let you write real queries to solve crimes.
| Feature | View | Materialized View |
|---|---|---|
| Storage | Virtual (no disk) | Physical (on disk) |
| Query Performance | Slower (re-executes) | Faster (cached) |
| Data Freshness | Always current | Stale until refreshed |
| Can Create Indexes? | No | Yes |
| INSERT/UPDATE/DELETE? | Sometimes (simple views) | No |
| Refresh Needed? | No | Yes (manual or scheduled) |
| Disk Space | Zero | Proportional to result set |
| Best For | Security, simplification | Dashboards, reporting |
| Write Overhead | None | Refresh cost |
| Database Support | All databases | PostgreSQL, Oracle, Snowflake, SQL Server (indexed views) |
Full comparison: 10 key differences
INSERT...SELECT, then refresh via cron or triggers.The biggest operational question with materialized views: how and when do you refresh them? Here are your options.
case_summaryREFRESH MATERIALIZED VIEW CONCURRENTLY lets users query the old snapshot while the refresh runs
-- Full refresh (locks the view during refresh)
REFRESH MATERIALIZED VIEW case_summary;
-- Concurrent refresh (no lock, requires unique index)
CREATE UNIQUE INDEX idx_case_summary_district
ON case_summary (district);
REFRESH MATERIALIZED VIEW CONCURRENTLY case_summary;-- Refresh every hour using pg_cron
SELECT cron.schedule(
'refresh_case_summary',
'0 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY case_summary'
);Oracle also supports ON COMMIT refresh, which automatically updates the materialized view whenever the base tables change. PostgreSQL and SQL Server do not have this built in.
If data must be real-time OR the query is cheap, use a regular view. Materialize only expensive, non-real-time queries.
Use a view when:
Use a materialized view when:
The same SQL patterns you'd use in views (JOINs, subqueries, aggregations) are exactly what you need to analyze surveillance records and hotel checkins to catch a killer.
Start InvestigationCREATE VIEW v1 AS SELECT ... FROM big_table; CREATE VIEW v2 AS SELECT ... FROM v1 JOIN other; CREATE VIEW v3 AS SELECT ... FROM v2 JOIN more; -- Querying v3 triggers ALL three queries
•Each layer re-executes the one below it
•Performance degrades exponentially
•Nearly impossible to debug slow queries
CREATE MATERIALIZED VIEW summary AS SELECT ... FROM big_table JOIN other ON ... JOIN more ON ...; CREATE INDEX idx_summary_id ON summary (id);
•One query, pre-computed and cached
•Indexed for fast lookups
•Clear refresh schedule
If you're stacking 3+ views deep, it's time to materialize
A materialized view created once and never refreshed serves weeks-old data. Always set up a refresh schedule (cron, pg_cron, or application-level triggers) when you create one.
If your base data changes constantly and users expect real-time results, a materialized view just adds complexity. You'd be refreshing so often that you lose the performance benefit. Use a regular view instead, or optimize the underlying query with proper indexes.
A materialized view without indexes is just a table you forgot to optimize. The whole point of materializing is fast reads. Create indexes on the columns you filter and join on.
Q1.Your dashboard shows monthly crime statistics, refreshed every hour. Which should you use?
Q2.You need to hide sensitive suspect columns (SSN, home address) from junior analysts. Which should you use?
Q3.A complex 5-table JOIN query runs 100+ times per day for the same report. Which should you use?
Q4.An audit log must always show the latest entries immediately after insertion. Which should you use?
Ready to practice JOINs, subqueries, and aggregations?
The same SQL patterns behind views and materialized views power every detective case in SQLNoir. Write real queries to solve crimes.
Start Your Investigation →No. Materialized views are read-only snapshots. To change the underlying data, modify the base tables directly and then refresh the materialized view.
Not natively. The common workaround is creating a regular table, populating it with INSERT...SELECT, and refreshing it on a schedule using cron or triggers.
It depends on your staleness tolerance. Real-time dashboards: every few minutes. Daily reports: once overnight. Monthly summaries: weekly. Match the refresh frequency to how often users need updated data.
No. Views are virtual and store no data, so there's nothing to index. You can index the underlying tables, or switch to a materialized view if you need indexed results.
SQL Server's equivalent of a materialized view. You create a regular view, then add a unique clustered index on it. This physically stores the results on disk, just like a materialized view in PostgreSQL or Oracle.