SQL Academy · Lesson

Materialized Views and REFRESH Strategies

Persist the result of an expensive query with MATERIALIZED VIEW and refresh on a schedule or on demand.

Materialized View = Saved Result

Unlike a regular view, a materialized view stores the result of its SELECT to disk. Queries hit the stored data — fast. Refresh on demand.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

Queries are just SELECTs on the materialised data:

SELECT * FROM daily_revenue WHERE day >= NOW() - INTERVAL '30 days';