Materialized Views

Published 5 October 2026

The term shows up across many databases (Postgres, Oracle, Cassandra, ScyllaDB and others), so it's worth pinning down what it means before running into it in each one.

View vs materialized view

A view stores the query; a materialized view stores the query's results.

A regular view is a virtual table. It stores only the query definition, not any data. When you query the view, the database uses that definition to fetch the current data, usually by merging it into the surrounding query. It isn't always recomputed from scratch on every query, because the optimizer can rewrite or optimize the work. Even so, the cost is paid at read time.

A materialized view stores the query's result on disk, much like a table. Reads are faster because the database serves the precomputed result instead of running the query.

-- View: only the query is saved; it runs whenever you read it.
CREATE VIEW daily_sales AS
  SELECT order_date, SUM(amount) AS total FROM orders GROUP BY order_date;

-- Materialized view: the result is computed once and stored.
CREATE MATERIALIZED VIEW daily_sales_mv AS
  SELECT order_date, SUM(amount) AS total FROM orders GROUP BY order_date;

The trade-off: staleness

The stored result goes stale as soon as the underlying tables change, so a materialized view has to be refreshed. How that happens depends on the database and how the view is configured:

• On-demand refresh: it's updated when someone asks for a refresh, or on a schedule. Between refreshes, reads can return old data.
• Incremental, automatic maintenance: where the database supports it, only the affected parts are updated as the underlying data changes.

So "updated dynamically" is only true for some databases or configurations. Many materialized views are stale until the next refresh.