Tuning for busy tables

With the defaults (refresh_lag=0, max_wait=0, cooldown=2), the view refreshes within about 2 seconds of any change. For a sales.orders table receiving hundreds of inserts per second, that may trigger constant refreshing.

A better configuration for a high-volume table:

SELECT pgauto_mv.update_mv(
    p_mv_schema    => 'reporting',
    p_mv_name      => 'daily_revenue',
    p_refresh_lag  => 10.0,   -- wait 10 seconds after the last change
    p_max_wait     => 20.0,   -- never wait more than 60 seconds total
    p_cooldown     => 30.0    -- minimum 30 seconds between refreshes
);

How this plays out in practice:

• A batch of 500 updates arrives in a 2-second burst.
• No refresh fires during the burst — events are recorded but the timing gate holds.
• 10 seconds after the last update in the burst, the refresh fires and picks up all 500 changes at once.
• If inserts keep arriving continuously, max_wait=20 forces a refresh at least every 20 seconds.
• After each refresh, no further refresh starts for 30 seconds.

update_mv() uses COALESCE semantics — parameters you do not supply keep their current values. You can change just one:

SELECT pgauto_mv.update_mv(
    p_mv_schema => 'reporting',
    p_mv_name   => 'daily_revenue',
    p_cooldown  => 60.0
);

---