Part 3 of 5 · 1 min read
Refresh strategies
A stored result eventually goes stale, so it must be recomputed on some schedule or trigger.
Think of the printed specials menu again. It stays accurate only until the kitchen changes what is actually available, at which point someone has to decide when to print a new version.
In plain words: because a materialized view's stored result does not automatically update when the underlying tables change, it must be refreshed, meaning its query is re-run and the stored result replaced. Common refresh strategies include a full refresh on a fixed schedule, such as nightly, re-running the entire query from scratch; and an incremental refresh, which updates only the rows affected by recent changes, where supported, avoiding the cost of recomputing everything. Choosing a refresh strategy means choosing how stale the stored result is allowed to get between refreshes.
A worked example. customer_revenue might be refreshed nightly with a full refresh, meaning any order placed during the day is not reflected in the view until the next night's refresh runs.
The key lines.
REFRESH MATERIALIZED VIEW customer_revenue;
-- re-runs the underlying query and replaces the stored result
Refresh (materialized view)
A refresh re-runs a materialized view's underlying query and replaces its stored result, either fully from scratch or incrementally for just the changed rows, deciding how stale the view is allowed to become between refreshes.
Example: deciding when to print a new specials menu, after the kitchen's offerings change, mirrors deciding when to refresh a materialized view.