Materialized View Refresh

ELI5 — The Vibe Check

Materialized views cache query results but eventually go stale. Refreshing them re-runs the query and updates the cache. You can refresh on a schedule (CRON) or after data changes. The trick is refreshing often enough to be useful but not so often that you kill performance.

Real Talk

Materialized view refresh re-executes the underlying query and updates the stored result set. PostgreSQL supports REFRESH MATERIALIZED VIEW (full refresh) and REFRESH MATERIALIZED VIEW CONCURRENTLY (non-blocking, requires unique index). Strategies include cron-based periodic refresh, trigger-based refresh after source changes, and application-level refresh after bulk updates.

Show Me The Code

-- Create the materialized view
CREATE MATERIALIZED VIEW sales_summary AS
  SELECT product_id, SUM(amount) as total
  FROM orders GROUP BY product_id;

-- Refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

When You'll Hear This

"Refresh the dashboard materialized view every 5 minutes via cron." / "Use CONCURRENTLY so reads aren't blocked during refresh."