Skip to content

Core concepts

Timing parameters

Three numbers — all in seconds — control when a refresh fires after data changes.

Parameter Default What it means
refresh_lag 0 Wait this many seconds after the most recent change before refreshing. Works like a debounce: if changes keep arriving, the refresh keeps getting pushed out.
max_wait 0 No matter how many changes arrive, force a refresh after this many seconds from the first change. 0 means no cap — the refresh waits as long as refresh_lag.
cooldown 2 After a refresh completes, wait at least this many seconds before starting the next one. Prevents the view from being hammered with rapid successive refreshes.

How they work together — a worked example:

Assume you have refresh_lag = 5, max_wait = 30, cooldown = 10.

12:00:00  — first change arrives  (refresh_lag starts, max_wait clock starts)
12:00:03  — another change arrives (refresh_lag clock resets because 3 seconds elapsed is less than the 5 second lag setting, so refresh time resets to 12:00:08)
12:00:07  — another change arrives (refresh_lag clock resets to 12:00:12)
12:00:12  — quiet period — refresh_lag fires → refresh starts
              (only 12 seconds elapsed, well within max_wait of 30)
12:00:19  — refresh completes
12:00:29  — cooldown expires (10 seconds after completion)
              → next refresh can now start if other requests had been received

If changes kept arriving continuously, max_wait = 30 would force a refresh at 12:00:30 regardless and if it took 7 seconds to complete the refresh, then the cooldown would dictate that no further refreshes could occur before 12:00:47. This stops a constantly busy table from preventing the view from ever refreshing.

Choosing values for your situation:

  • refresh_lag=0, max_wait=0, cooldown=2 — refresh as fast as possible. Good for low-traffic tables where you need near-real-time data.
  • refresh_lag=10, max_wait=60, cooldown=30 — batch up changes over 10 quiet seconds, never wait more than 60 seconds, minimum 30 seconds between refreshes. Good for busy tables with bursty writes.
  • refresh_lag=300, max_wait=0, cooldown=60 — refresh 5 minutes after the last change. Good for expensive views where accuracy within minutes is acceptable.

Watch tables

Watch tables tell pg_auto_mv what to watch. When you register a view, you supply one or more watch tables and the events to watch on each one (INSERT, UPDATE, DELETE — any combination). pg_auto_mv installs triggers on those tables. When data changes, the trigger fires and the refresh cycle begins.

Watch tables are optional. You can register a view with no watch tables at all, and drive refreshes purely through a schedule, a chain (see below), or on-demand calls. This is the right choice for bulk-loaded tables where per-row trigger overhead is unwanted.

Specifying DELETE in the event list automatically covers TRUNCATE too — you do not need to list TRUNCATE separately (and you cannot, only DELETE is permitted).

Not all tables of a Materialised View need to be a watch table, only tables that you want to trigger a refresh should be included as a watch table. For example, imagine you have a Materialised View of daily revenue that includes columns from the Sales Order header and Sales Order lines. Revenue should only be counted when a Sales Order header record has a status of 'paid', then you would only need to refresh the Materialised View on the Sales Order header table on UPDATE, new headers, new lines, and same for deleted headers and lines are not relevant to the Materialised View. Only when a workflow updates the Sales Order header to approved, cancelled or rejected should the daily revenue Materialised View be refreshed. This ensures the processing occuring in the database is efficent and allows the system to scale. As part of designing the refresh logic, determine the events on all relevant tables that matter. Your Materialised View is likely to join to customer and lookup tables, so events on those tables triggering a Materialised View refresh would be wasteful.

Schedules

A schedule causes the view to refresh at a fixed time or interval, regardless of whether any data has changed. Schedules and watch-table triggers work independently and can be combined.

Two kinds: - 'time' — specific times of day on specific days of the week, or every day. All times are UTC. - 'interval' — a fixed period (e.g. every 90 minutes), measured from the previous scheduled slot.

Schedules are named and can be shared by many views, or created implicitly as a private schedule owned by one view. For example, returning to the Daily Revenue Materialised View mentioned above, if it is a 'daily' materialised view, then it should refresh at the start of each day, if the first Sales order header is not updated until 9:30am, then all references to that Materialised View would show yesterday's revenue. To avoid this, have a daily refresh of this Materialised View at 00:00:00 (or 00:00:01 depending upon your selection logic). You could create a 'DAILY' schedule that refreshes at the start of every day and all your daily views are assigned to that schedule.

Chaining materialised views

Chaining causes one view to automatically refresh immediately after another view finishes refreshing. This is useful when views are layered:

reporting.daily_revenue  →  reporting.weekly_revenue → reporting.MTD_revenue  →  reporting.QTD_revenue →  reporting.YTD_revenue

For example, if a new Sales Order is paid, the daily_revenue Materialised View is refreshed, and this then requests the weekly_revenue to refresh, which in turn requests the MTD revenue to refresh and this chain continues until Year-To-Date (YTD) revenue Materialised View is refreshed. Therefore, when the DAILY schedule is invoked for the reporting.daily_revenue Materialised View, the remaining Materialised Views will in turn be refreshed.

Chains can span multiple levels, and the chain still propagates even when the upstream view was refreshed by triggers, a schedule or an on-demand call.

Refreshing on demand

pgauto_mv.refresh_materialised_view_now(schema, name) queues an immediate refresh from outside the normal trigger and schedule flow. It is the right tool for ETL jobs and operator scripts that need to ensure a view is current at a specific point in time.

This function: - Bypasses refresh_lag and max_wait — the refresh is due immediately. - Honours cooldown — if the view refreshed 3 seconds ago with a 10-second cooldown, the refresh waits the remaining 7 seconds. - Returns immediately after queuing — it does not wait for the refresh to complete. - Still propagates to chained views.

Access to this function is restricted — your DBA needs to grant it to your role. See section 9 for full details.