What is pg_auto_mv?

A materialised view in PostgreSQL stores the result of a query as a snapshot. That snapshot goes stale whenever the underlying tables change. The traditional fix is a scheduled job that calls REFRESH MATERIALIZED VIEW on a timer — but timers are guesswork: too short wastes resources, too long means stale data, and if the job breaks it silently stops refreshing.

A different approach — putting a trigger on the source table to refresh the view directly — sounds appealing but has a serious problem: the refresh runs inside your transaction. Nothing else can commit until the view finishes refreshing. Under load, this turns a fast insert into a slow, blocking operation.

pg_auto_mv solves both problems.

• You tell it which tables to watch. It installs triggers that detect changes and put a refresh request into a queue — outside your transaction. Your insert completes immediately; the refresh happens separately in the background.
• A companion process called pg_relay watches that queue and runs REFRESH MATERIALIZED VIEW when the time is right.
• Three timing parameters give you fine control over how quickly the view responds to changes and how often it refreshes when data is arriving in bursts.
• A complete audit trail records every event that triggered a refresh and every refresh that ran.

pg_auto_mv does not create, alter, or drop materialised views. You manage the view with standard PostgreSQL DDL (CREATE MATERIALIZED VIEW, ALTER, DROP). pg_auto_mv only manages when the view gets refreshed.

We wrote an article on our Pebble IT website on why we created pg_relay and pg_auto_mv, you can read it here

---