Overview

pg_auto_mv is a pure SQL/PL/pgSQL PostgreSQL extension that automates the refresh of materialised views in response to DML events and/or a configurable time-based schedule. It introduces no C code, no background workers, and no shared_preload_libraries entry.

The extension is built on top of pg_relay, which provides a persistent queue (pgrelay.queue), a channel-to-action dispatch table (pgrelay.actions), and a notify helper (pgrelay.notify()). The pg_relay binary polls its queue every second and executes the registered action when a row is due. pg_auto_mv treats pg_relay as its execution engine — it writes queue rows and registers channel actions; it never executes refreshes itself except via that dispatch path.

The refresh lifecycle is:

DML on watch table
  → generated trigger function
    → INSERT into mv_events + UPDATE mv_state
    → pgrelay.notify('pg_auto_mv.refresh_materialised_view', auto_mv_id)
      → row in pgrelay.queue
        → pg_relay binary dispatches when run_at <= now()
          → SELECT pgauto_mv._process_pending(auto_mv_id::uuid)
            → REFRESH MATERIALIZED VIEW [CONCURRENTLY] schema.mvname

Scheduled refreshes follow the same final path but the queue row is inserted directly by register() or _advance_schedule() at the computed next_scheduled_at timestamp, not triggered by DML.

---