REFRESH CONCURRENTLY Cost Analysis in PostgreSQL #

Choosing between REFRESH MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW CONCURRENTLY is a trade of availability against cost. Plain refresh is fast and clean but blocks every reader for its entire duration. Concurrent refresh keeps the matview readable but does measurably more work — a temporary rebuild, a diff, and row-level changes that leave bloat behind. This page quantifies both sides so you can pick deliberately rather than defaulting to CONCURRENTLY because it sounds safer.

Two Refresh Algorithms, Two Cost Profiles #

The two modes do fundamentally different things internally.

Plain refresh re-executes the matview’s defining query, writes the result into a brand-new heap, and then atomically swaps that heap in for the old one. Because the swap is a catalog operation, PostgreSQL holds an AccessExclusiveLock on the matview for the whole run — the same lock level as DROP TABLE. No session can SELECT from the matview until the refresh commits. The write itself is a single sequential bulk load, so it is cheap; the pain is purely the read block.

Concurrent refresh never blocks readers, and it earns that by doing more:

  1. It re-executes the defining query into a temporary relation.
  2. It computes a diff between the temporary result and the current matview contents, matched by the required UNIQUE index.
  3. It applies that diff as ordinary INSERT, UPDATE, and DELETE statements against the live heap.

Readers see a consistent snapshot the whole time because the changes are transactional row operations, not a heap swap. But steps 1–3 mean you pay to build the full new result plus the cost of comparing and mutating — and every UPDATE/DELETE leaves a dead tuple.

Plain vs Concurrent Refresh Timeline Two timelines. Plain refresh holds an AccessExclusiveLock for a short rebuild-and-swap during which reads are blocked. Concurrent refresh runs longer, building a temp result, diffing, and applying changes, while reads stay allowed throughout. Plain REFRESH rebuild + swap AccessExclusiveLock — reads BLOCKED time → REFRESH CONCURRENTLY build temp result diff by unique key apply INSERT/UPD/DEL light lock — reads ALLOWED throughout Costs of CONCURRENTLY: extra temp_bytes for the build, diff CPU/I/O, and dead tuples from UPDATE/DELETE → later vacuum

Measuring Both Refresh Modes #

Do not guess — time both against your real matview. Start by confirming the unique index that CONCURRENTLY demands actually exists, because without it the concurrent form errors out immediately. Building that index follows the same column-ordering discipline as any B-tree index optimization.

Time a plain refresh:

\timing on
REFRESH MATERIALIZED VIEW mv_daily_revenue;   -- note the elapsed time

While it runs, from a second session confirm the lock level:

-- Expect lockmode = 'AccessExclusiveLock' on the matview relation during plain refresh
SELECT relation::regclass, mode, granted
FROM   pg_locks
WHERE  relation = 'mv_daily_revenue'::regclass;

Time a concurrent refresh and watch temp usage:

\timing on
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;   -- expect longer than plain

The concurrent build spills its temporary result to temp files; capture the volume afterward:

-- temp_bytes attributable to this backend's sort/hash/temp activity
SELECT temp_files, temp_bytes
FROM   pg_stat_database
WHERE  datname = current_database();

During the concurrent run, pg_locks shows a far gentler lock (an ExclusiveLock that still permits SELECT), which is precisely why readers are not blocked.

Check the bloat it leaves behind:

-- After a concurrent refresh, dead tuples appear from the applied UPDATE/DELETE diff
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
WHERE  relname = 'mv_daily_revenue';

A steadily climbing n_dead_tup across refreshes is the bloat signal. If last_autovacuum lags well behind your refresh cadence, tune autovacuum for this matview or the dead tuples will inflate every subsequent matview index or seq scan.

Before/After: Lock Mode and Duration #

A representative comparison on a matview of ~2 million rows where roughly 3% of rows change per refresh:

-- PLAIN REFRESH
Duration: ~4.1 s
Lock on mv_daily_revenue: AccessExclusiveLock   -- all SELECTs blocked for 4.1 s
Dead tuples added: ~0 (heap swapped wholesale)
temp_bytes: minimal

-- REFRESH CONCURRENTLY
Duration: ~11.7 s
Lock on mv_daily_revenue: ExclusiveLock         -- SELECTs continue throughout
Dead tuples added: ~120 000 (from UPDATE/DELETE portion of the diff)
temp_bytes: full result materialized to temp (~180 MB)

The concurrent run took nearly 3× as long and left 120k dead tuples, but not a single reader waited. That is the trade in one picture: plain refresh is cheaper and cleaner but freezes reads for its whole duration; concurrent refresh costs more time, temp space, and vacuum debt to keep the matview live.

When Each Mode Fits #

Common Pitfalls #

Missing unique index. REFRESH MATERIALIZED VIEW CONCURRENTLY fails immediately unless a UNIQUE index covers all rows. If the matview grain has duplicates you cannot make one, and CONCURRENTLY is simply unavailable until the query is changed to produce a unique key.

CONCURRENTLY on a huge matview → long diff. On a very large matview where most rows change every cycle, the diff step compares and rewrites nearly everything, so the concurrent run can take many times longer than a plain rebuild while delivering little availability benefit. Match the mode to the change fraction, not just the size.

Autovacuum lag → runaway bloat. Frequent concurrent refreshes generate dead tuples faster than a default autovacuum schedule reclaims them, so n_dead_tup climbs and scan cost drifts up. Lower autovacuum_vacuum_scale_factor for the matview, or trigger a manual VACUUM after heavy refresh cycles.

Frequently Asked Questions #

Is REFRESH CONCURRENTLY always slower than a plain REFRESH? In wall-clock terms, yes, almost always. Concurrent refresh builds a full temporary copy of the new result, diffs it against the current contents by the unique key, then applies the changed rows — strictly more work than a rebuild-and-swap. You trade longer duration and extra I/O for keeping the matview readable throughout.

Why does REFRESH CONCURRENTLY create dead tuples? Concurrent refresh applies its diff as row-level INSERT, UPDATE, and DELETE against the live matview heap. Every updated or deleted row leaves a dead tuple that autovacuum must later reclaim. On a frequently refreshed matview this bloat accumulates and slowly inflates scan cost until vacuum catches up.

When should I use plain REFRESH instead of CONCURRENTLY? Use plain REFRESH when you have a maintenance window where a brief read block is acceptable, or when the matview is small enough that the rebuild finishes quickly. Plain refresh is faster and produces no bloat. Reserve CONCURRENTLY for matviews that must stay readable around the clock.

Related