View vs Materialized View: What's the Difference? (With Examples)

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.

Quick Answer: View vs Materialized View

What Is a View in SQL?

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';

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.

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;

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

Refresh Strategies for Materialized Views

The biggest operational question with materialized views: how and when do you refresh them? Here are your options.

-- 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'
);

When to Use Each: Decision Guide

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

Common Mistakes

1. Stacking views on top of views

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

CREATE MATERIALIZED VIEW summary AS
SELECT ...
FROM big_table
JOIN other ON ...
JOIN more ON ...;

CREATE INDEX idx_summary_id ON summary (id);

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

Frequently Asked Questions