Analysing refresh frequency

Use get_mv_refresh_frequency() to see how often each view has been refreshing and spot if timing parameters need tuning:

SELECT
    mv_schema,
    mv_name,
    refreshes_15m,
    refreshes_daily,
    avg_interval_secs,
    rapid_refreshes,
    threshold_secs
FROM pgauto_mv.get_mv_refresh_frequency();

Filter to one schema:

SELECT *
FROM pgauto_mv.get_mv_refresh_frequency(p_mv_schema => 'reporting');

If rapid_refreshes is high, cooldown or refresh_lag may be too low. If avg_interval_secs is much longer than expected, check for errors in mv_refresh_log.

Purging old log data:

The event log and refresh log grow indefinitely. Run a purge periodically to control their size:

-- Keep 90 days of refresh logs, 30 days of events, 365 days of audit history
SELECT table_name, rows_deleted
FROM pgauto_mv.purge_mv_logs(
    p_refresh_log_days   => 90,
    p_events_days        => 30,
    p_audit_history_days => 365
);

Scope to one view:

SELECT table_name, rows_deleted
FROM pgauto_mv.purge_mv_logs(
    p_auto_mv_id  => (
        SELECT auto_mv_id FROM pgauto_mv.materialised_views
        WHERE mv_schema = 'reporting' AND mv_name = 'daily_revenue'
    ),
    p_events_days => 30
);

-1 (the default for each parameter) skips that table. 0 deletes all rows.

---