Materialized view
What is a materialized view in PostgreSQL?
A materialized view stores the result of a query so it can be read like a table. Reads are fast, but the data only changes when you run REFRESH MATERIALIZED VIEW.
A materialized view runs its defining query once and stores the result, unlike a regular view, which runs its query every time it is read. Reading it is as fast as reading a table, and it can have its own indexes.
The stored data does not follow changes in the underlying tables; it changes only when you run REFRESH MATERIALIZED VIEW. A plain refresh blocks reads of the view while it runs. With CONCURRENTLY, reads can continue during the refresh, which requires at least one unique index on the view and can be faster when only a few rows change.
Example
The example totals sales by area in a materialized view, records a new sale and refreshes the view concurrently, which the unique index allows. The total for north includes the new sale only after the refresh.
CREATE TABLE sales (id int PRIMARY KEY, region text, amount numeric);
INSERT INTO sales
SELECT g, (ARRAY['north', 'south', 'east'])[1 + g % 3], g % 50
FROM generate_series(1, 30000) AS g;
CREATE MATERIALIZED VIEW sales_by_region AS
SELECT region, sum(amount) AS total FROM sales GROUP BY region;
CREATE UNIQUE INDEX ON sales_by_region (region);
INSERT INTO sales VALUES (30001, 'north', 1000);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_region;
SELECT * FROM sales_by_region ORDER BY region;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).