Scheduling¶

Schedules provide a time-based refresh alongside (or instead of) watch-table triggers. A scheduled slot fires and runs the refresh regardless of whether any data changed since the last refresh.

pg_auto_mv schedules are named entities — create a schedule once, then attach it to any number of views. You can also create an inline private schedule that belongs to just one view.

Two kinds of schedule¶

'interval' — a fixed period expressed in hours and/or minutes. Each slot fires that duration after the previous slot.

'time' — specific times of day on specific days of the week (or every day). All times are UTC.

Building a time schedule: the `pgrelay.schedule` type¶

Time schedules use pgrelay.schedule — the shared recurrence type that ships with pg_relay 1.1.2 and is used by every pg_relay-related application, so you learn it once. pg_auto_mv has no schedule type of its own.

Prerequisite. pgrelay.schedule is not part of the pg_relay extension and is not in the pg_relay release tarball. A superuser installs it separately, after CREATE EXTENSION pg_relay, by running schedule/install.sql from the pg_relay source tree. CREATE EXTENSION pg_auto_mv fails with an actionable message if it is missing. Full documentation: https://pg-relay.pebbleit.com.au/latest/schedule-type/01-overview/

Build values with the pg_relay constructors rather than by hand:

pgrelay.daily('05:30')                  -- every day at 05:30 UTC
pgrelay.weekdays('08:00,17:00')         -- Mon-Fri at 08:00 and 17:00 UTC
pgrelay.weekends('09:00')               -- Sat and Sun at 09:00 UTC
pgrelay.on_dow('mon', '09:00,17:00')    -- Monday at 09:00 and 17:00 UTC
pgrelay.on_dow('mon,wed,fri', '06:00')  -- Mon, Wed and Fri at 06:00 UTC
pgrelay.on_dow('fri', '1700')           -- Friday at 17:00 UTC
pgrelay.on_dow('sat', '330')            -- Saturday at 03:30 UTC

Day tokens: mon…sun, ranges like mon-fri, or the words weekdays, weekends, everyday (eve and all also work). Time formats: 330 (3:30am), 4:30, 05:30, 645, 1700 (5:00pm). All times are UTC, to the minute — seconds are rejected rather than silently dropped.

Different times on different days¶

on_dow() applies the same times to every day you list. When each day needs its own times, use pgrelay.on_day_times() — one dow=times group per argument:

-- Monday at 05:00; Tuesday at 17:30 and 20:00; Wednesday at 12:00
pgrelay.on_day_times('mon=0500', 'tue=1730,2000', 'wed=1200')

-- Groups accept the same day and time grammar as everything else
pgrelay.on_day_times('mon-thu=0700', 'fri=1200,1600', 'weekends=1000')

That is a single schedule value, so it goes straight into the array as one element:

SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'staggered',
    p_schedule => ARRAY[pgrelay.on_day_times('mon=0500', 'tue=1730,2000', 'wed=1200')]
);

Why the parameter is an array¶

A schedule is supplied as an array so it can combine different kinds of recurrence. The next run is the earliest slot across all elements:

-- Weekday mornings, plus a month-end run at 23:30 whatever day that lands on
ARRAY[
    pgrelay.weekdays('07:00'),
    pgrelay.month_end('2330')
]

For plain per-day times you do not need multiple elements — on_day_times() covers that in one. Reach for several elements when you are mixing modes.

pg_auto_mv documents the day-of-week and per-day-times modes above. The type also supports nth-weekday-of-month (pgrelay.nth_dow()), every-N-weeks (pgrelay.every_weeks(), pgrelay.fortnightly()), and day-of-month (pgrelay.on_dom(), pgrelay.month_start(), pgrelay.month_end()) recurrences; those values work here too, and the pg_relay documentation is the reference for them.

Creating a named shared schedule¶

-- Interval: every 4 hours
SELECT pgauto_mv.create_mv_schedule(
    p_name           => 'every_4h',
    p_interval_hours => 4
);

-- Interval: every 90 minutes
SELECT pgauto_mv.create_mv_schedule(
    p_name             => 'every_90m',
    p_interval_minutes => 90
);

-- Time: every day at 05:30 UTC
SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'daily_0530',
    p_schedule => ARRAY[pgrelay.daily('05:30')]
);

-- Time: weekdays at 08:00 and 17:00 UTC
SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'business_hours',
    p_schedule => ARRAY[pgrelay.weekdays('08:00,17:00')]
);

Building a time schedule incrementally¶

set_mv_schedule_day() changes one day and leaves the others alone:

SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'custom_sched',
    p_schedule => ARRAY[pgrelay.on_dow('mon', '06:00')]
);

SELECT pgauto_mv.set_mv_schedule_day('custom_sched', 'wed', '06:00');
SELECT pgauto_mv.set_mv_schedule_day('custom_sched', 'fri', '06:00,14:00');

-- Clear a day:
SELECT pgauto_mv.set_mv_schedule_day('custom_sched', 'wed', NULL);

A time schedule must always keep at least one day, so clearing the last remaining day raises an error — use delete_mv_schedule() to remove the schedule itself. Note that create_mv_schedule() needs one mode or the other up front: there is no way to create an empty schedule and fill it in later.

Attaching a schedule when registering¶

Inline private schedule — created automatically for this one view:

-- Interval schedule: every 6 hours
SELECT pgauto_mv.register_mv(
    p_mv_name                  => 'daily_revenue',
    p_mv_schema                => 'reporting',
    p_watch_tables             => ARRAY[pgauto_mv.mv_watch('sales.orders')],
    p_schedule_interval_hours  => 6
);

-- Time schedule: every day at 05:30 UTC
SELECT pgauto_mv.register_mv(
    p_mv_name       => 'weekly_summary',
    p_mv_schema     => 'reporting',
    p_watch_tables  => NULL,
    p_schedule      => ARRAY[pgrelay.daily('05:30')]
);

Named shared schedule — must be created first, then referenced by name:

SELECT pgauto_mv.register_mv(
    p_mv_name       => 'weekly_summary',
    p_mv_schema     => 'reporting',
    p_watch_tables  => NULL,
    p_schedule_name => 'daily_0530'   -- name of an existing shared schedule
);

Attaching or changing a schedule after registration¶

-- Attach a shared schedule to an existing registration
SELECT pgauto_mv.update_mv(
    p_mv_schema     => 'reporting',
    p_mv_name       => 'daily_revenue',
    p_schedule_name => 'daily_0530'
);

-- Change to an inline interval schedule
SELECT pgauto_mv.update_mv(
    p_mv_schema               => 'reporting',
    p_mv_name                 => 'daily_revenue',
    p_schedule_interval_hours => 4
);

-- Remove the schedule entirely
SELECT pgauto_mv.update_mv(
    p_mv_schema      => 'reporting',
    p_mv_name        => 'daily_revenue',
    p_clear_schedule => true
);

SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'daily_0530',
    p_schedule => ARRAY[pgrelay.daily('05:30')]
);

SELECT pgauto_mv.update_mv('reporting', 'daily_revenue',   p_schedule_name => 'daily_0530');
SELECT pgauto_mv.update_mv('reporting', 'weekly_summary',  p_schedule_name => 'daily_0530');
SELECT pgauto_mv.update_mv('finance',   'budget_snapshot', p_schedule_name => 'daily_0530');

Each view maintains its own next-slot timestamp independently. They share the schedule definition but are not synchronised — a refresh of one does not affect when the others run.

Updating an existing schedule¶

-- Rename
SELECT pgauto_mv.update_mv_schedule(
    p_name     => 'daily_0530',
    p_new_name => 'nightly_04h'
);

-- Change the time (time schedules only) — replaces the ENTIRE recurrence spec.
-- Use set_mv_schedule_day() instead to change one day and keep the rest.
SELECT pgauto_mv.update_mv_schedule(
    p_name     => 'business_hours',
    p_schedule => ARRAY[pgrelay.daily('04:00')]
);

-- Change the interval (interval schedules only)
SELECT pgauto_mv.update_mv_schedule(
    p_name             => 'every_90m',
    p_interval_minutes => 120  -- change to every 2 hours
);

A schedule's kind ('time' or 'interval') is fixed after creation. You cannot change a 'time' schedule to 'interval' or vice versa — delete it and create a new one.

Deleting a schedule¶

A schedule cannot be deleted while any view still references it. Detach all views first:

SELECT pgauto_mv.update_mv('reporting', 'daily_revenue', p_clear_schedule => true);
SELECT pgauto_mv.delete_mv_schedule('daily_0530');

Private schedules (created inline at registration) are deleted automatically when the view is unregistered.

Inspecting schedules¶

-- All named schedules
SELECT schedule_name, schedule_kind, interval_seconds, is_private
FROM pgauto_mv.schedules;

-- Recurrence spec for a specific time schedule, rendered readably
SELECT s.schedule_name, pgrelay.describe(e) AS fires
FROM pgauto_mv.schedules s,
     unnest(s.schedule_spec) AS e
WHERE s.schedule_name = 'business_hours';
-- e.g. "Mon-Fri at 08:00, 17:00 UTC"

-- When each schedule next fires
SELECT schedule_name,
       pgauto_mv._next_scheduled_at(schedule_name) AS next_run_utc
FROM pgauto_mv.schedules
ORDER BY 2;

-- Which views are using each schedule
SELECT s.schedule_name, r.mv_schema, r.mv_name, sv.next_scheduled_at
FROM pgauto_mv.schedules s
JOIN pgauto_mv.materialised_views r ON r.schedule_id = s.schedule_id
JOIN pgauto_mv.mv_state sv ON sv.auto_mv_id = r.auto_mv_id
ORDER BY s.schedule_name, r.mv_schema, r.mv_name;

How scheduled refresh interacts with event-driven refresh¶

When both are configured:

Events trigger refresh independently of the schedule. If data changes, the timing policy (refresh_lag, max_wait, cooldown) decides when to refresh — the schedule is not involved.
The scheduled slot fires regardless of whether data changed. When the scheduled time arrives, pg_auto_mv refreshes the view even if nothing has changed since the last event-driven refresh.
If a scheduled slot fires while the view is in its cooldown window, the slot is recorded as skip_reason = 'in_cooldown' in the refresh log, and the next scheduled slot is computed and queued.
A direct refresh_materialised_view_now() call does not advance the schedule — the next scheduled slot still fires at its normal time.