Refreshing Materialized Views Concurrently

Problem Statement

The materialized view is built and the endpoint is fast. What remains is the part that runs forever: a refresh job that must not block readers, must not overlap itself, must not fill the disk, and must be noticed when it stops working. This page is that job — the SQL, the Python around it, and the two alerts that make its failures visible.

Why the Naive Approach Fails

A cron entry running psql -c "REFRESH MATERIALIZED VIEW district_coverage" fails in three distinct ways over a year of operation.

It blocks every reader for the duration, because the plain form takes an ACCESS EXCLUSIVE lock. It overlaps itself the first time the refresh runs longer than the schedule interval, and each overlapping run makes the next one slower. And when it stops working — a rotated password, a full disk, a container that no longer has the binary — nothing reports anything, because cron’s idea of failure notification is an email nobody reads.

What happens when the refresh outgrows its schedule Two timelines with a ten-minute schedule. In the first, refresh duration grows past ten minutes and successive runs pile up, each waiting on the last. In the second, an advisory lock makes a late-arriving run exit immediately, so only one refresh is ever in flight. A ten-minute schedule and a twelve-minute job no lock — the runs stack each run waits on the last with pg_try_advisory_lock — late runs exit skipped runs exit in milliseconds The skip counter is the early warning: a job skipping half its runs is telling you the schedule no longer fits.

Production-Ready Implementation

The job, with the lock, the headroom check and the bookkeeping:

python
from __future__ import annotations

import logging
import time

import psycopg

log = logging.getLogger("mv-refresh")

VIEW = "district_coverage"
LOCK_KEY = 0x5041_5243        # any stable 32-bit constant, one per view
HEADROOM_FACTOR = 2.5         # concurrent refresh needs roughly 2× the view size


def refresh(dsn: str) -> str:
    """Refresh the view concurrently. Returns 'ok', 'skipped' or raises."""
    with psycopg.connect(dsn, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(%s)", (LOCK_KEY,))
        if not cur.fetchone()[0]:
            log.warning("refresh already running; skipping this run")
            return "skipped"

        try:
            cur.execute(
                "SELECT pg_total_relation_size(%s), "
                "       pg_size_bytes(current_setting('temp_file_limit', true)) "
                "       IS NOT NULL",
                (VIEW,),
            )
            size, _ = cur.fetchone()
            log.info("refreshing %s (%.1f GB on disk, ~%.1f GB needed)",
                     VIEW, size / 1e9, size * HEADROOM_FACTOR / 1e9)

            started = time.monotonic()
            cur.execute("SET statement_timeout = 0")         # the refresh may be long
            cur.execute(f"REFRESH MATERIALIZED VIEW CONCURRENTLY {VIEW}")
            elapsed = time.monotonic() - started

            cur.execute("""
                INSERT INTO mv_refresh_log (view_name, finished_at, seconds)
                VALUES (%s, now(), %s)
            """, (VIEW, round(elapsed, 1)))
            log.info("refreshed %s in %.1fs", VIEW, elapsed)
            return "ok"
        finally:
            cur.execute("SELECT pg_advisory_unlock(%s)", (LOCK_KEY,))

pg_try_advisory_lock returns immediately rather than waiting, which is what turns an overlap into a cheap skip. Because the lock is session-scoped and the connection closes on exit, a crashed job releases it automatically — no stale-lock cleanup required.

The log table is small and pays for itself the first time somebody asks how long the cache has been stale:

sql
CREATE TABLE mv_refresh_log (
    id          bigserial PRIMARY KEY,
    view_name   text        NOT NULL,
    finished_at timestamptz NOT NULL,
    seconds     numeric(8,1) NOT NULL
);
CREATE INDEX ON mv_refresh_log (view_name, finished_at DESC);

Alert on staleness, not on failure

The alert that catches a job which stopped existing A failure alert fires only when the job runs and errors, so it misses a disabled schedule, a deleted container and a credential that expired. A staleness alert measures the age of the data itself and fires for all of those causes as well as for genuine errors. Measure the data, not the job alert on job failure catches: the refresh errored misses: schedule disabled misses: container never deployed misses: every run skipped by the lock silence looks identical to success alert on staleness now() - max(finished_at) > 45 min catches all of the above and states the impact directly the threshold is the product's tolerance, written down
sql
-- the alerting query: one row, one boolean
SELECT now() - max(finished_at) AS staleness,
       now() - max(finished_at) > interval '45 minutes' AS alert
FROM mv_refresh_log
WHERE view_name = 'district_coverage';

Set the threshold at roughly three times the schedule interval. Tighter and a single skipped run pages somebody; looser and a broken job goes unnoticed through a working day.

Configuration and Tuning Knobs

statement_timeout must be cleared for the refresh session. A global timeout intended to protect the API will kill a long refresh, and the failure looks like a query cancellation rather than a configuration problem.

maintenance_work_mem is used for the index maintenance the refresh performs. Raising it to a gigabyte for the refresh session shortens the window noticeably on a large view.

Disk headroom should be at least twice the view’s size, plus the indexes rebuilt during the diff. A refresh that runs out of space fails cleanly, leaving the old contents intact — but it fails every time until somebody makes room.

Schedule offset matters. If the source data is refreshed by a nightly import, schedule the view refresh to start after the import finishes, not at a fixed clock time that drifts into it.

Refresh duration as the view grows Concurrent refresh duration against view size: 12 seconds at 340 rows, 96 seconds at 41 thousand rows, 8 minutes at 900 thousand and 34 minutes at 6 million, at which point the ten-minute schedule can no longer hold it. Concurrent refresh duration by view size 340 rows 12 s 41,000 rows 96 s · comfortable on a 10-minute schedule 900,000 rows 8 min · the schedule is now tight 6,000,000 rows 34 min · move to an incrementally updated table The curve is superlinear because the diff joins the new contents against the old ones.

Verification Steps

sql
-- recent refresh history: are runs completing, and is duration growing?
SELECT finished_at, seconds
FROM mv_refresh_log
WHERE view_name = 'district_coverage'
ORDER BY finished_at DESC
LIMIT 12;

-- does the view have the unique index a concurrent refresh requires?
SELECT indexrelid::regclass AS idx, indisunique
FROM pg_index
WHERE indrelid = 'district_coverage'::regclass AND indisunique;

-- and is anything currently holding the view's lock?
SELECT pid, state, left(query, 60) AS query, now() - query_start AS running
FROM pg_stat_activity
WHERE query ILIKE '%district_coverage%' AND state <> 'idle';

A duration that grows steadily across the twelve most recent runs is the signal to act before the job outgrows its schedule — either by narrowing the view’s defining query or by moving to an incrementally maintained table.

When the refresh outgrows the schedule

A refresh whose duration approaches its interval is a system with no slack, and it fails in a way that looks like a database problem rather than a scheduling one. Three responses, in order of preference.

Narrow the view. Most materialized views accumulate columns nobody uses. Dropping the ones no consumer reads shortens both the recompute and the diff, often substantially, because wide rows make the concurrent refresh’s join more expensive.

Lengthen the interval. If the staleness budget allows fifteen minutes and the job runs every five, the schedule is tighter than the requirement. Aligning them removes the pressure at no cost.

Replace the view with a table. Beyond a certain size, incremental maintenance is the only answer, and PostgreSQL does not offer it for materialized views. An ordinary table updated by an upsert that processes only changed source rows scales indefinitely, at the cost of writing and testing that logic yourself.

Gotchas Checklist

  • Concurrent refresh requires a unique index and fails loudly without one. The error names the view, not the missing index, which sends people looking in the wrong place.
  • The refresh holds an EXCLUSIVE lock, not ACCESS EXCLUSIVE. Reads proceed; a second refresh and any DDL do not.
  • Advisory locks are per session, not per transaction, in this form. Using pg_try_advisory_xact_lock inside an autocommit connection releases it immediately and protects nothing.
  • A skipped run is not an error but should be counted. Without the counter, a job skipping most of its runs looks perfectly healthy.
  • REFRESH runs the defining query with the privileges of the view’s owner. If the underlying tables gained row-level security since the view was created, the refreshed contents can differ from what a direct query returns.