What is a materialized view and how do you refresh it?

Unlike a regular view, a materialized view persists rows. That makes it useful for expensive aggregations and dashboards where slightly stale data is acceptable. The trade-off is refresh cost and staleness.

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month,
       SUM(total_cents) AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON monthly_revenue (month);

Refresh strategies

• REFRESH MATERIALIZED VIEW rebuilds the whole result and blocks reads by default
• REFRESH MATERIALIZED VIEW CONCURRENTLY can keep reads available but requires a unique index
• Schedule refreshes after ETL or on a cadence that matches the freshness requirement
• Incremental maintenance often needs a separate summary table rather than a full refresh
• Views can be indexed like tables, which helps large analytical queries
• Monitor refresh duration as data grows; a full rebuild can become the bottleneck

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

If the application cannot tolerate stale dashboards, a materialized view is the wrong tool. If it can tolerate minutes or hours of delay, a materialized view can remove a large repeated aggregation from the request path.