Refreshing Materialized Views on Read Replicas

Problem statement: a materialized view is served to users from read replicas, and every time it refreshes, replica queries start failing with recovery-conflict errors — on a schedule.

Materialized views are an excellent fit for a replica read tier: they turn an expensive recurring computation into a cheap scan, and the scan can happen anywhere. What makes them awkward is that the refresh is a write, so it happens somewhere else and arrives as replayed WAL with all the consequences that carries. This page covers those consequences. The staleness accounting it feeds into is in caching and materialized reads for stale-tolerant workloads.

Symptom Identification

Look for a scheduled pattern — that is the distinguishing feature:

• ERROR: canceling statement due to conflict with recovery clustered tightly at the same minute past the hour, every hour, or at the same time each night.
• The error detail reads User query might have needed to see row versions that must be removed or references a lock conflict on the view’s relation.
• Replica lag spikes at the refresh time and decays afterwards, with a magnitude proportional to the view’s size.
• Dashboard data appears to “jump” rather than update smoothly, or is inexplicably older than the refresh interval suggests.
• pg_stat_database_conflicts on the replicas shows confl_lock incrementing in step with the refresh schedule.

The scheduled regularity separates this from ordinary recovery conflicts caused by vacuum, which are irregular. If your cancellations line up with a cron entry, this page is the cause.

Root Cause Analysis

A refresh is a write, so it runs on the primary and arrives at replicas as WAL. There is no way to refresh a view on a standby — the standby is read-only, and the view’s contents are physical pages replicated like any other relation. This has three consequences that compound.

A plain refresh replaces the relation under an ACCESS EXCLUSIVE lock. On the primary, that lock blocks readers of the view for the duration of the rebuild. On a standby, the lock is replayed: the recovery process must acquire it to apply the change, and it conflicts with any query currently reading that view. After max_standby_streaming_delay, the standby resolves the conflict by cancelling the query. The cancellation is not a bug; it is the standby choosing replay progress over one query, exactly as configured.

REFRESH ... CONCURRENTLY avoids the exclusive lock at the cost of doing more work. It builds the new contents into a temporary relation, computes the difference against the existing contents, and applies that difference as ordinary INSERT, UPDATE and DELETE operations. Readers are never blocked. The costs are a longer runtime and more WAL — a full-replacement refresh writes the new relation once, while a concurrent one writes the diff plus the temporary build.

The extra WAL lands on every standby. A large refresh is a large write, and a standby applying it while already behind falls further behind. This is the mechanism behind the common report that “the reporting job takes down the read tier”: the job did not touch the replicas at all, its WAL did.

Step-by-Step Resolution

Step 1 — Add the unique index CONCURRENTLY requires

-- CONCURRENTLY needs a UNIQUE index covering every row exactly once.
-- Build it CONCURRENTLY too, so creating it does not itself block readers.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS mv_daily_totals_key
    ON mv_daily_totals (account_id, day);

-- Verify it is genuinely unique across the current contents, or the index
-- build will fail partway and leave an INVALID index behind.
SELECT account_id, day, count(*)
  FROM mv_daily_totals GROUP BY 1, 2 HAVING count(*) > 1;

Inline verification: \d mv_daily_totals lists the index and it is not marked INVALID; the duplicate query returns no rows.

Step 2 — Switch the refresh to CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_totals;

Inline verification: while the refresh runs, a SELECT count(*) FROM mv_daily_totals on the primary returns immediately rather than blocking, and on a replica the same query is not cancelled.

Step 3 — Record completion in a refresh log

CREATE TABLE IF NOT EXISTS mv_refresh_log (
  view_name     text        NOT NULL,
  started_at    timestamptz NOT NULL,
  finished_at   timestamptz,
  rows_after    bigint,
  PRIMARY KEY (view_name, started_at)
);

-- Wrap the refresh so the log entry and the refresh share a transaction:
-- a rolled-back refresh must not leave a success record behind.
CREATE OR REPLACE PROCEDURE refresh_logged(p_view text)
LANGUAGE plpgsql AS $$
DECLARE started timestamptz := clock_timestamp();
BEGIN
  EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', p_view);
  EXECUTE format('INSERT INTO mv_refresh_log(view_name, started_at,
                    finished_at, rows_after)
                  SELECT %L, %L, clock_timestamp(), count(*) FROM %I',
                 p_view, started, p_view);
END $$;

CALL refresh_logged('mv_daily_totals');

Inline verification: SELECT * FROM mv_refresh_log ORDER BY started_at DESC LIMIT 3 shows a row with a non-null finished_at after each successful run.

Step 4 — Skip or defer when replicas are already behind

-- Run this guard on the primary before the refresh. If the fleet is already
-- behind, adding a large write makes it worse for no benefit.
CREATE OR REPLACE FUNCTION replicas_are_healthy(max_lag interval DEFAULT '1 second')
RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT coalesce(max(replay_lag), interval '0') < max_lag
    FROM pg_stat_replication WHERE state = 'streaming';
$$;

DO $$
BEGIN
  IF replicas_are_healthy() THEN
    CALL refresh_logged('mv_daily_totals');
  ELSE
    RAISE NOTICE 'skipping refresh: replicas behind';
  END IF;
END $$;

Inline verification: during an artificially induced lag (pause replay on one standby), the block raises the notice and no new mv_refresh_log row appears.

Step 5 — Alert on the view’s observed age

# The view's real age, from the log — not from the schedule.
# Alert at roughly 3× the interval so one skipped run is tolerated and two are not.
time() - mv_last_refresh_timestamp_seconds{view="mv_daily_totals"} > 900

-- The exporter query behind that metric.
SELECT view_name,
       extract(epoch FROM max(finished_at)) AS last_refresh_epoch
  FROM mv_refresh_log WHERE finished_at IS NOT NULL
 GROUP BY view_name;

Inline verification: deliberately skip one scheduled run and confirm the metric ages but the alert does not fire; skip three and confirm it does.

Configuration Snippet

-- Replica-side settings that decide how a refresh replay affects readers.
-- Set these in the REPLICA's parameter group, not the primary's.

-- How long the standby delays replay to let a conflicting query finish.
-- Larger = fewer cancellations, more replication lag during conflicts.
ALTER SYSTEM SET max_standby_streaming_delay = '30s';

-- Tell the primary which row versions standby queries still need, so vacuum
-- does not remove them. Reduces snapshot conflicts at the cost of bloat.
ALTER SYSTEM SET hot_standby_feedback = on;

SELECT pg_reload_conf();

# Scheduling. The two guards matter more than the cron expression.
materialized_views:
  - name: mv_daily_totals
    schedule: "7,22,37,52 * * * *"   # offset from :00 to avoid the job pile-up
    concurrently: true               # required — replicas read this continuously
    skip_if_replica_lag_above: 1s    # do not add WAL to an already-behind fleet
    alert_if_age_exceeds: 900s       # 3× the interval
    statement_timeout: 10min         # a refresh that hangs must not run forever

  - name: mv_monthly_rollup
    schedule: "17 3 * * *"
    concurrently: true
    alert_if_age_exceeds: 172800s
    statement_timeout: 60min

The minute offsets are not cosmetic. Scheduling at :00 puts the refresh in competition with every other hourly job — backups, vacuum runs, metric rollups — and the resulting WAL burst is what tips a replica fleet over. Spreading refreshes to arbitrary minutes costs nothing and removes a recurring correlated spike.

statement_timeout on the refresh is the guard against the worst case: a CONCURRENTLY refresh that cannot complete because the source data has grown will otherwise run indefinitely, holding a snapshot open and blocking vacuum on the underlying tables, which produces bloat on the primary and snapshot conflicts on every replica.

Verification and Rollback

Confirm the cancellations stopped:

-- On each replica. confl_lock should stop incrementing at refresh time.
SELECT datname, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
  FROM pg_stat_database_conflicts WHERE datname = current_database();

Take a reading before a refresh and another afterwards. With CONCURRENTLY, confl_lock should not move; confl_snapshot may still move, which is a different conflict caused by vacuum removing row versions a long-running standby query still needs.

Confirm the view is actually current on the replicas:

-- On a replica: does the view reflect the latest logged refresh?
SELECT (SELECT max(finished_at) FROM mv_refresh_log
          WHERE view_name = 'mv_daily_totals')      AS refreshed_at_primary,
       now() - pg_last_xact_replay_timestamp()      AS replica_replay_age;

The view’s age on a replica is the time since the refresh finished plus the replica’s replay age. Both terms need to be inside the budget, and only the second one appears on a standard lag dashboard.

Rollback. Reverting to a plain refresh is a one-word change, and there are legitimate reasons to do it — a CONCURRENTLY refresh that cannot keep up with the interval, for instance:

REFRESH MATERIALIZED VIEW mv_daily_totals;   -- blocking form

If you revert, move the refresh to a window when replica readers are absent, and raise max_standby_streaming_delay on the replicas so the cancellation window is wider. The unique index can stay: it costs a little write amplification and lets you switch back without a rebuild. Dropping it is the only genuinely irreversible step, since rebuilding it on a large view takes time you will not have during an incident.

Edge Cases and Gotchas

CONCURRENTLY cannot be used on an empty or never-populated view

A view created WITH NO DATA, or one that has never completed a refresh, has no contents to diff against, so the first refresh must be the plain blocking form. Schedule that first refresh deliberately during a maintenance window rather than letting the regular job hit the failure and then leave the view empty:

CREATE MATERIALIZED VIEW mv_daily_totals AS SELECT ... WITH NO DATA;
REFRESH MATERIALIZED VIEW mv_daily_totals;              -- first: blocking
CREATE UNIQUE INDEX CONCURRENTLY ... ;                  -- then the index
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_totals; -- thereafter

Chained views refresh in dependency order, and the order is not automatic

If mv_b selects from mv_a, refreshing mv_b first produces a view built from stale inputs — and nothing warns you. PostgreSQL tracks the dependency but does not cascade refreshes. Derive the order from pg_depend and refresh leaves-first, or keep the chain to one level.

A long concurrent refresh holds a snapshot open

While it runs, the refresh’s transaction pins a snapshot on the primary, which prevents vacuum from removing row versions newer than it. On a large view that takes many minutes, this produces measurable bloat on the underlying tables and increases confl_snapshot conflicts on standbys with hot_standby_feedback off. Keep the refresh duration well under the interval, and if it approaches it, the view is too large for full refreshes — move to an incrementally-maintained rollup table instead.

FAQ

Related

← Back to Caching & Materialized Reads for Stale-Tolerant Workloads

• Detecting Hot Standby Query Cancellations on Postgres Replicas — the conflict mechanism in full, beyond the refresh case.
• Serving Bounded-Staleness Reads with an Explicit Freshness Budget — where the view’s age is accounted against a budget.
• Redis Cache Versus Read Replica for Hot Read Paths — when a view is the right answer and when a cache is.