Skip to main content

materialized_views_refresh

🚀 Incremental Materialized View Refresh​

Create materialized views for expensive aggregations and use CONCURRENTLY refresh for minimal locking impact.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', sale_date) AS month,
       SUM(amount) AS total
FROM sales
GROUP BY 1;

-- Initial populate
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrent refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

For true incremental refresh, implement triggers on base tables writing deltas to a staging table and use WITH NO DATA plus selective INSERT/UPDATE on the matview.