The first time a plain REFRESH MATERIALIZED VIEW took our dashboards down, the refresh ran four minutes and eleven seconds, and for every one of those seconds it held an ACCESS EXCLUSIVE lock that turned forty concurrent readers into a queue. The connection pool filled, the API started timing out, and the on-call graph for database CPU looked perfectly healthy the entire time — because nothing was wrong except a lock. The general trade-offs of materialized views are covered in the materialized views field notes; what follows is strictly about the refresh path: its locks, its diff, its bloat, and the pattern I reach for when CONCURRENTLY stops paying.
The patient was mv_daily_revenue: roughly 40 million order lines aggregated into about 90 thousand day-and-region rows, six gigabytes on disk, refreshed every ten minutes by a cron job. That shape — big source, compact view, frequent refresh — is exactly where refresh mechanics stop being a footnote.
Why does plain REFRESH block every reader?
Because plain REFRESH holds an ACCESS EXCLUSIVE lock on the view for the entire build, and ACCESS EXCLUSIVE conflicts with the ACCESS SHARE lock that a plain SELECT takes. There is no partial visibility and no reader bypass: every query against the view queues behind the refresh from its first second to its last. The atomicity is genuine — readers never see a half-built view — but you pay for it with a full read outage of exactly the refresh's duration. Four minutes of build is four minutes of queued SELECTs. The documentation says this plainly and everyone rediscovers it in production anyway, because the lock only hurts once the view is popular. If your refresh is fast and your traffic is low, plain REFRESH is fine; the moment either stops being true, you need one of the two escape routes below.
What does CONCURRENTLY actually do during the refresh?
It computes the new result set off to the side, diffs it against the current contents row by row, and applies only the delta — deletes of rows that vanished or changed, inserts of their replacements — while readers keep seeing the old data the whole time. The lock it holds is EXCLUSIVE rather than ACCESS EXCLUSIVE, which blocks writes to the view but not reads. The price list is longer than the marketing line suggests. You need at least one UNIQUE index on the view that uses plain column names only — no expressions, no WHERE clause — because the diff joins old against new on that key. The view must already be populated, so a view created WITH NO DATA needs one plain, blocking refresh before CONCURRENTLY becomes legal — a view created WITH DATA can go concurrent from its first refresh onward. CONCURRENTLY cannot run from a function or a multi-command string, though an ordinary transaction block is fine. And only one refresh may run against a given view at a time, concurrent or not.
CREATE UNIQUE INDEX mv_daily_revenue_day_region_uidx
ON reporting.mv_daily_revenue (day, region);
REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.mv_daily_revenue;The part people under-budget is the diff itself. Finding the delta means joining the full old contents against the full new contents, so the cost of CONCURRENTLY scales with the size of both copies — not with how many rows actually changed. Our six-gigabyte view took about four minutes for a plain refresh and eighteen for a concurrent one, because the diff join and its internal sort dwarfed the aggregation underneath. The documentation warns it can be slower; eighteen versus four is what that warning feels like with real data.
Why does CONCURRENTLY bloat the view over time?
Because applying the delta is DELETE-and-INSERT traffic against the view's heap — a changed row is the old version deleted plus the new version inserted, never an UPDATE — and every changed or removed row leaves a dead tuple behind. If thirty percent of your rows churn per cycle, each refresh writes a third of the view as garbage that autovacuum must then chase — on a ten-minute cycle, forever. Ours grew from six gigabytes to fifteen over two weeks before anyone looked, and the bloat fed itself: every subsequent diff had more dead pages to wade through, so refreshes got slower, so vacuum fell further behind. The dead-tuple mechanics are the same ones the PostgreSQL vacuum guide walks through for regular tables; a materialized view refreshed with CONCURRENTLY is just a table with a very disciplined writer. Watch n_dead_tup for the view in pg_stat_user_tables and run ANALYZE after refreshes — the planner's row-count estimates for the view go stale fast under constant churn, and ANALYZE is cheap.
When is a swap-and-rename build better than CONCURRENTLY?
When the view is large and a big fraction of rows changes per cycle — past roughly a fifth churned rows, in my experience, the diff stops paying and a full rebuild with an atomic rename wins on every axis. The pattern: build a brand-new materialized view next to the live one (this touches nothing readers use), create its indexes, then swap names inside a transaction. The only ACCESS EXCLUSIVE window is the two renames, measured in milliseconds, and the result is a freshly built, compact heap with zero dead tuples.
BEGIN;
ALTER MATERIALIZED VIEW reporting.mv_daily_revenue RENAME TO mv_daily_revenue_old;
ALTER MATERIALIZED VIEW reporting.mv_daily_revenue_new RENAME TO mv_daily_revenue;
COMMIT;
DROP MATERIALIZED VIEW reporting.mv_daily_revenue_old;Three honest caveats. You need disk for two full copies during the build. Grants do not follow the new view — reapply them after the build, not after the swap, or readers with explicit privileges error out at the rename. And objects that depend on the view reference it by OID, not by name — a dependent view follows the renamed _old object and keeps querying stale data instead of the new view wearing the old name, so the swap is only honest when nothing depends on the view, and when something does, the DROP of the old view refuses under RESTRICT until you rebuild those dependents against the new one. Our eighteen-minute concurrent refresh became a seven-minute build plus a three-hundred-millisecond swap, and every later refresh ran against a clean heap. That is the pattern I default to now for views above a few gigabytes.
How do you schedule refreshes and measure staleness?
PostgreSQL keeps no last-refreshed timestamp anywhere — pg_matviews tells you the owner, the definition, and whether the view is populated, and nothing about time — so the classic trick is to bake the timestamp into the data: add now() AS refreshed_at to the view definition and read MAX(refreshed_at) to compute staleness. One catch that pairs badly with CONCURRENTLY: the diff compares whole rows, and a timestamp that changes every row on every refresh turns each cycle into a full delete-and-reinsert — if you refresh concurrently, have the refresh job stamp a one-row side table instead. Scheduling is pg_cron or your own scheduler, but serialize it: a slow cycle that runs past the next tick queues the following refresh behind a lock wait, and then every cycle is late. A pg_try_advisory_lock guard that skips a cycle while the previous one is still running turns a cascading pile-up into one skipped beat.
SELECT cron.schedule('refresh-mv-daily-revenue', '7-59/10 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.mv_daily_revenue');
SELECT max(refreshed_at) AS last_refresh,
now() - max(refreshed_at) AS staleness
FROM reporting.mv_daily_revenue;Alert on staleness, not on the refresh job's exit code: the job can succeed every time and still leave you stale if it is queuing behind its predecessor. Two missed cycles is my page threshold; one is a warning.
Watching refresh health with MonPG
Everything above is counter-shaped. Dead tuples and autovacuum runs on the view track the bloat curve; lock waits in pg_stat_activity spike when a plain REFRESH meets traffic; the REFRESH statements themselves show up in pg_stat_statements with mean execution time, so duration regressions surface before the dashboard does; and the staleness query is a one-line scheduled check. MonPG graphs exactly these series — dead tuples, vacuum activity, lock contention, per-statement latency — as part of its PostgreSQL monitoring, which is how the four-minute lockout in the opening paragraph became a thirty-second diagnosis instead of a morning of log archaeology.