Materialized view

What is a materialized view?

A materialized view is a query whose result is physically stored as a table, so reading it is fast. Instead of running the query every time someone asks for the numbers, the database runs it once, writes the answer to disk, and hands back that stored answer on every read. The trade-off is freshness: the data is only as current as the last refresh.

The contrast with a plain view makes it concrete. A plain view is just a saved query with a name: every time you select from it, the underlying query runs again on the base tables, so you get live data but pay the full cost each read. A materialized view runs the query once and keeps the output. Reads get much cheaper, but the result drifts out of date until you refresh it.

Think of a plain view as a saved question and a materialized view as the written-down answer, quick to read back but only true as of the last time you worked it out.

How refresh works, and why it differs by engine

What varies most between databases is when and how the stored result updates. Do not assume two engines behave alike here.

PostgreSQL keeps it manual. The view is populated when you create it and never updates on its own. You run REFRESH MATERIALIZED VIEW yourself, or schedule it, and until then the data stays frozen. PostgreSQL also will not rewrite a query to use the view, so you select from it by name.

Oracle gives you dials. A refresh can be COMPLETE, which re-runs the whole query, FAST, which applies only the changes recorded in materialized view logs, or FORCE, which tries fast and falls back to complete. It can fire ON COMMIT, as a transaction changes a base table, or ON DEMAND. With ENABLE QUERY REWRITE, the query optimizer can transparently send a query aimed at the base tables to the view instead.

Snowflake and BigQuery hide the refresh. Snowflake maintains the view through a background service and its optimizer rewrites base-table queries to use it, so the view shows up in the execution plan. BigQuery precomputes changes in the background, adds them incrementally, and reroutes any query it can answer from the view, within a max_staleness you set between 30 minutes and 3 days.

Databricks refreshes manually, on a schedule, or when upstream data changes, using an incremental refresh where it can and recomputing the whole query where it cannot.

So the name describes a shape, not one behaviour. And automatic maintenance is not free: when the engine keeps a view current as base tables change, as Oracle does ON COMMIT, every write to those tables pays for it.

Refreshing concurrently in PostgreSQL

A plain refresh in PostgreSQL locks the view while it rebuilds, so readers are blocked until it finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids that lock, but it needs a unique index. PostgreSQL is exact about which kind: at least one unique index on the materialized view that uses only column names and includes all rows, so no expression index and no WHERE clause.

CREATE MATERIALIZED VIEW daily_sales AS
SELECT region, sale_date, SUM(amount) AS total
FROM orders
GROUP BY region, sale_date;

CREATE UNIQUE INDEX ON daily_sales (region, sale_date);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

Without that unique index the concurrent refresh is rejected. The index gives PostgreSQL a stable way to match old rows to new and update only the difference in place, instead of swapping the whole table under a lock.

The staleness trade-off

A materialized view earns its place when a heavy aggregation or join is read far more often than the underlying data changes. That is usually the query behind a dashboard, often an aggregation over a star schema in a data warehouse.

Say a report shows revenue per region per day, and a hundred people open it through the morning. Computed live, the same sum over millions of rows runs a hundred times. Backed by a materialized view, it runs once at refresh and every reader hits the stored result. As long as figures from this morning are good enough, that is a clean win.

What you give up is currency. You trade live accuracy for read speed and a little storage, since the result is a second copy of the data. For day or week reporting that almost always pays off. For figures that must be right to the second it is the wrong tool, and you would read the base tables directly or stay live with DirectQuery in Power BI.

Materialized view versus the alternatives

The plain view contrast is the one above: a plain view recomputes and stays current, a materialized view stores the result and drifts stale. Two closer neighbours are worth separating out, both living in SQL, where the query optimizer and the execution plan decide much of what happens.

Versus a common table expression

A common table expression is a named subquery that lives only for the length of one statement. It structures a complex query and is gone when the statement finishes, saving nothing between runs. A materialized view persists across queries and sessions: a common table expression tidies one query, a materialized view banks the work for many.

Versus a hand-built rollup table

You can compute the same rollup yourself and write it to an ordinary table in a data pipeline. The stored result is identical; the difference is ownership. With a materialized view the database knows the table came from a query and manages the refresh. A hand-built summary table is yours to keep current, so rebuilding it is your job and a silent staleness bug is yours too.