TopolearnMaterialized views make expensive reports instant without building a separate summary pipeline.
A regular view is just a saved query, executed each time you select from it. A materialized view runs the query once and stores the result like a table, so reading it is fast — at the cost of being stale until you refresh it.
CREATE MATERIALIZED VIEW daily_sales AS SELECT ...creates and fills it (addWITH NO DATAto fill later).REFRESH MATERIALIZED VIEW daily_salesrecomputes it from scratch. It takes a lock that blocks reads while it runs.REFRESH MATERIALIZED VIEW CONCURRENTLY daily_salescomputes the new result and applies the differences, so readers keep seeing the old data during the refresh. It requires a unique index on the materialized view and is slower overall.
You can index materialized views like tables, which is often the point: a pre-aggregated, indexed summary.
PostgreSQL has no built-in automatic or incremental refresh. Schedule refreshes with cron, the pg_cron extension or your job runner, and choose a frequency the business can accept as staleness. If you need always-current aggregates, maintain a summary table with triggers or in the application instead, or look at extensions for incremental view maintenance.
Good fits: dashboards, leaderboards, search facets, and expensive joins that change rarely compared with how often they're read.
Example
CREATE TABLE sale (id int PRIMARY KEY, region text, amount numeric, sold_on date);
INSERT INTO sale VALUES (1, 'north', 100, '2026-05-01'),
(2, 'south', 50, '2026-05-01'),
(3, 'north', 70, '2026-05-02');
CREATE MATERIALIZED VIEW daily_sales AS
SELECT sold_on, region, sum(amount) AS total, count(*) AS n
FROM sale GROUP BY sold_on, region;
CREATE UNIQUE INDEX ON daily_sales (sold_on, region); -- needed for CONCURRENTLY
INSERT INTO sale VALUES (4, 'south', 30, '2026-05-02');
SELECT count(*) FROM daily_sales; -- 3 (stale)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
SELECT * FROM daily_sales ORDER BY sold_on, region;
-- 2026-05-01 north 100 1 / 2026-05-01 south 50 1
-- 2026-05-02 north 70 1 / 2026-05-02 south 30 1Remember
- A materialized view stores the result and is stale until refreshed
- REFRESH ... CONCURRENTLY keeps it readable but needs a unique index
- Schedule refreshes yourself (cron, pg_cron)
Watch out for
- Expecting a materialized view to update itself when the base tables change.