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.
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.
Creating a view:
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.
🔍SELECT * FROM active_suspects
📋Read view definition
⚙️Execute underlying query
✅Return fresh results
Every query against a view triggers a full execution of the stored SQL
🕵️Think of It This Way
A view is like a saved search filter in your case management system. Every time you open it, the system runs the search fresh. No cached documents, no stale leads.
What Is a Materialized View?
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.
Creating a materialized view (PostgreSQL):
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.
🔍SELECT * FROM case_summary
💾Read stored snapshot
⚡Return cached results instantly
Materialized views skip query execution entirely, reading pre-computed results from disk
🕵️Think of It This Way
A materialized view is like printing your investigation report. Fast to read, but you need to reprint it whenever new evidence comes in.
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.
View vs Materialized View: Key Differences
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
⚠️Database Naming
SQL Server calls them “indexed views,” not “materialized views.” MySQL does NOT support materialized views natively. The workaround: create a regular table and populate it with INSERT...SELECT, then refresh via cron or triggers.
Refresh Strategies for Materialized Views
The biggest operational question with materialized views: how and when do you refresh them? Here are your options.
REFRESH
Command to update the materialized view
MATERIALIZED VIEWcase_summary
The materialized view to refresh
CONCURRENTLY
Optional: refresh without locking reads (requires a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY lets users query the old snapshot while the refresh runs
Manual refresh:
-- 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;
Scheduled refresh (PostgreSQL + cron):
-- 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.
When to Use Each: Decision Guide
❓Do you need real-time data?
👁️YES → Use a View
❓Is the query expensive?
💾YES → Use a Materialized View
👁️NO → Use a View
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:
• Data must always be current (audit logs, live dashboards)
• You need to restrict column access for security
• The underlying query is simple and fast
• Base table data changes frequently
Use a materialized view when:
• The query involves complex aggregations or multi-table JOINs
• The same expensive report runs many times per day
• Slight staleness is acceptable (hourly, daily reports)
• You want to add indexes on the results for faster lookups
Case File #003🔍
The Miami Marina Murder
●●○Intermediate
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.
CREATE 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
✅Single Materialized View
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
2. Forgetting to refresh materialized views
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.
3. Materializing when data changes every minute
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.
4. Skipping indexes on materialized views
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.
Test Your Knowledge
🔍 View vs Materialized View Quiz
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.
No. Materialized views are read-only snapshots. To change the underlying data, modify the base tables directly and then refresh the materialized view.
Does MySQL support materialized views?
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.
How often should I refresh a materialized view?
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.
Can I create indexes on a regular view?
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.
What is an indexed view in SQL Server?
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.