pg_auto_mv¶

Materialised views that refresh themselves.

A materialised view in PostgreSQL stores the answer to a query as a snapshot. It is fast to read because the work has already been done — but the moment the underlying tables change, that snapshot is out of date. Something has to refresh it.

pg_auto_mv is that something. You tell it which tables to watch, and it refreshes the view when they change — without blocking the transaction that made the change, and without a cron job that silently stops working.

Why not just use a cron job or a trigger?¶

Those are the two things most people try first, and both have a real problem.

A timer — refresh every five minutes — is guesswork. Too often and you burn CPU refreshing a view nothing changed. Too rarely and your reports are stale. Worse, if the job breaks, nothing tells you: the view just quietly stops updating.

A trigger that refreshes the view directly sounds better, but the refresh then runs inside your transaction. Your INSERT cannot commit until the whole view has been rebuilt. On a busy table this turns a millisecond insert into a multi-second stall, and concurrent writers queue up behind it.

pg_auto_mv takes a third path. The trigger does almost nothing: it records that a change happened and puts a request on a queue. Your transaction commits immediately. A separate background process picks the request up and does the refresh outside your transaction entirely.

What you get¶

Refreshes driven by real changes, not a guess about how often data arrives.
Three timing controls — wait for a quiet moment, cap the maximum wait, and enforce a minimum gap between refreshes — so a burst of a thousand inserts produces one refresh, not a thousand.
Schedules as well, if you want a nightly rebuild regardless of activity.
Chaining, so a view built from another view refreshes in the right order.
A full audit trail of every change that triggered a refresh and every refresh that ran, including failures.
Replication-aware — works on streaming standbys and multi-master clusters.

pg_auto_mv never creates, alters, or drops your materialised views. You manage those with ordinary PostgreSQL DDL. It only manages when they get refreshed.

A first look¶

-- Your view, created the ordinary way
CREATE MATERIALIZED VIEW reporting.daily_sales AS
    SELECT date_trunc('day', created_at) AS day, sum(total) AS revenue
    FROM sales.orders
    GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.daily_sales (day);

-- Hand it to pg_auto_mv
SELECT pgauto_mv.register_mv(
    p_mv_name      => 'daily_sales',
    p_mv_schema    => 'reporting',
    p_watch_tables => ARRAY[pgauto_mv.mv_watch('sales.orders')],
    p_refresh_lag  => 5.0,    -- wait 5 quiet seconds after the last change
    p_max_wait     => 30.0,   -- but never wait longer than 30 seconds
    p_cooldown     => 10.0    -- and leave 10 seconds between refreshes
);

That is the whole setup. Any INSERT, UPDATE, or DELETE on sales.orders now refreshes reporting.daily_sales automatically.

Where to start¶

New to pg_auto_mv?

Start with the User Guide. It assumes you know what a materialised view is and nothing else about this extension.
Installing it?

The DBA Guide covers installation, permissions, replication, and day-to-day maintenance.
On a managed cloud database?

RDS, Aurora, Azure, and Cloud SQL do not allow CREATE EXTENSION for third-party extensions. The Cloud Setup book explains the (straightforward) way around that.
Want the internals?

The Technical Reference documents the schema, the trigger architecture, the dispatch logic, and every public function.
Hit an unfamiliar word?

The Glossary explains the terms this documentation uses.

Before you install¶

pg_auto_mv needs two things in place:

PostgreSQL 15 or later.
pg_relay v1.1.2 or later, including its pgrelay.schedule type. pg_relay is the companion extension that runs the queue; it does the actual refresh dispatch. The schedule type is installed as a separate step — see Installation.

pg_relay has its own documentation at https://pg-relay.pebbleit.com.au/.