Materialized vs Regular Views

Materialized Views Versus Regular Views

A regular view is a stored query, rerun on current data by every SELECT. A materialized view runs the query once and stores the rows in a heap you can index, showing the data as of its last refresh. This script defines monthly gross sales per genre both ways and reads June 2026 from each:

451-matview.sql: the same summary as a view and as a materialized viewSQL

CREATE VIEW mart.v_genre_month AS
SELECT date_trunc('month', order_date)::date AS month, genre,
       count(DISTINCT order_id) AS orders, sum(gross_amount) AS gross
FROM mart.sales WHERE status <> 'cancelled' GROUP BY 1, 2;
CREATE MATERIALIZED VIEW mart.mv_genre_month AS SELECT * FROM mart.v_genre_month;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM mart.v_genre_month WHERE month = '2026-06-01';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM mart.mv_genre_month WHERE month = '2026-06-01';

Output

               ->  Parallel Seq Scan on sales (actual rows=3550.00 loops=2)
...
 Execution Time: 140.115 ms
 Seq Scan on mv_genre_month (actual rows=6.00 loops=1)
...
 Execution Time: 0.027 ms

The view still scanned all 2,432 pages of mart.sales; the materialized view read one page of 96 rows. Over eight runs on the shared four-CPU host the view took 32 to 230 ms, the materialized view 0.02 to 0.03 ms. The price is staleness: in the same script, a late Travel line inserted into mart.sales (and rolled back) showed up in the view at once (14,962.50) while the materialized view kept 14,943.75.

PostgreSQL 1,289 has no incremental materialized views: every refresh reruns the whole query. The pg_ivm extension (github.com/sraoss/pg_ivm (https://github.com/sraoss/pg_ivm 1,490 ), PostgreSQL License, PostgreSQL 13 to 18) maintains some view shapes incrementally; dbt 37,942 's incremental models (Transforming Data with dbt) and ClickHouse 29,491 (Incremental Materialized Views) do it at scale.

MENU