Views vs Materialized Views: When to Use Each

What is a view

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

-- Querying it looks just like querying a table
SELECT * FROM active_customers WHERE name LIKE 'A%';

A view stores nothing but the query definition itself. Every time it's queried, the database substitutes the saved query in place of the view name and runs it against the current data — meaning a view is by definition always up to date, but never faster than running the underlying query directly.

What is a materialized view

-- PostgreSQL / Oracle
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

Unlike a plain view, this actually executes the query once at creation time and stores the resulting rows physically, the same way a regular table would. Subsequent reads hit that stored data directly — no re-aggregation of every order, no re-join — which is why materialized views are the standard fix for dashboards and reports built on expensive aggregate queries that get read far more often than the underlying data changes.

Syntax across databases

-- SQL Server has no CREATE MATERIALIZED VIEW.
-- Its equivalent is an "indexed view":
CREATE VIEW monthly_revenue
WITH SCHEMABINDING AS
SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mo,
       SUM_BIG(total) AS revenue, COUNT_BIG(*) AS cnt
FROM dbo.orders
GROUP BY YEAR(order_date), MONTH(order_date);

CREATE UNIQUE CLUSTERED INDEX ix_monthly_revenue
ON monthly_revenue (yr, mo);

Adding a unique clustered index to a WITH SCHEMABINDING view forces SQL Server to physically materialize and store it — functionally the same outcome as Postgres's or Oracle's materialized view, reached through different syntax entirely.

MySQL has no native materialized view feature at all. The standard workaround is a plain summary table kept in sync by a scheduled EVENT or an external job that periodically re-runs the aggregation and replaces the table's contents — effectively a hand-built materialized view with manually written refresh logic.

Refresh strategies

-- PostgreSQL: manual refresh, blocks reads unless CONCURRENTLY is used
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Oracle: ON DEMAND (manual) or ON COMMIT (auto, limited query shapes)
CREATE MATERIALIZED VIEW monthly_revenue
REFRESH FAST ON COMMIT AS ...;

Every refresh strategy is really a tradeoff between staleness and refresh cost: ON DEMAND (or a scheduled job) gives full control over when the refresh cost is paid but leaves data stale in between; Oracle's ON COMMIT keeps data current automatically but only supports a limited set of "fast refreshable" query shapes and adds overhead to every write on the base tables; SQL Server's indexed views are always current because the engine updates them transactionally alongside the base table, at the cost of slowing down writes to that table.

Comparison table