Materialized View Auto-Refresh Integration Guide

What Is Available Today

Capability	Status
`REFRESH MATERIALIZED VIEW` (full)	Available
`REFRESH MATERIALIZED VIEW CONCURRENTLY` (readers are not blocked)	Available
`REFRESH MATERIALIZED VIEW ... INCREMENTALLY` (applies only the changes)	Available
`pg_mv_staleness()`	Available
`WITH (auto_refresh = true, ...)` on `CREATE MATERIALIZED VIEW`	Accepted and stored; no automatic refresh yet
`ALTER MATERIALIZED VIEW ... SET (...)` refresh options	Accepted and stored; no automatic refresh yet
Background refresh worker with staleness threshold, CPU limit and priority	Planned
`pg_mv_auto_refresh_status()`, `pg_mv_refresh_history()`, `pg_mv_cpu_usage()`, `pg_mv_scheduler_stats()`	Planned with the refresh worker

Refreshing Views Today

-- Full refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Full refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

-- Apply only the changes since the last refresh
REFRESH MATERIALIZED VIEW sales_summary INCREMENTALLY;

Checking Staleness

SELECT * FROM pg_mv_staleness();

Column	Description
`view_name`	Materialized view name
`base_tables`	Tables the view reads
`last_update`	Time of the last refresh
`pending_changes`	Changes in the base tables since the last refresh
`staleness_sec`	Seconds since the last refresh
`status`	Refresh status of the view

Scheduling Refreshes

Until the refresh worker is available, schedule refreshes outside the database, for example with cron and psql:

# Every 15 minutes: incremental refresh of sales_summary
*/15 * * * * psql -h 127.0.0.1 -p 5432 -U postgres -c "REFRESH MATERIALIZED VIEW sales_summary INCREMENTALLY"

To refresh only views that are behind, query pg_mv_staleness() first and refresh the views whose staleness_sec or pending_changes exceed your limits.

Refresh Options

These options are accepted today and stored with the view. They take effect once the refresh worker is available. At creation the WITH clause accepts auto_refresh and max_cpu_percent; set the other options with ALTER MATERIALIZED VIEW ... SET.

At Creation

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('day', sale_date) AS sale_day,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY product_id, sale_day
WITH (
    auto_refresh = true
);

After Creation

-- Staleness threshold in seconds
ALTER MATERIALIZED VIEW sales_summary SET (staleness_threshold = 1800);

-- Refresh strategy: 'manual', 'auto' or 'incremental'
ALTER MATERIALIZED VIEW sales_summary SET (refresh_strategy = 'incremental');

-- Several options at once
ALTER MATERIALIZED VIEW sales_summary SET (
    staleness_threshold = 3600,
    max_cpu_percent = 25,
    incremental_enabled = true,
    priority = 2
);

Option	Type	Meaning once the worker is available
`staleness_threshold`	numeric	Seconds before the view counts as stale
`max_cpu_percent`	numeric	CPU limit for background refreshes
`priority`	numeric	0 = low, 1 = medium, 2 = high, 3 = critical
`refresh_strategy`	string	`manual`, `auto` or `incremental`
`incremental_enabled`	boolean	Allow incremental refresh

Planned Design

The planned refresh worker watches the staleness of views created with auto_refresh = true, queues the stale ones by priority, refreshes them incrementally when only a few rows changed, and pauses while CPU use is above max_cpu_percent. The monitoring functions listed above will report its status, history and CPU use.