A materialized view is a stored snapshot of a query. Refreshing it re-runs the query and replaces the contents. CONCURRENTLY is what makes refreshing safe on a live system, and it’s easy to silently defeat.
What CONCURRENTLY actually does
A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock: every reader blocks until the refresh finishes. On a dashboard people are watching, that’s a visible stall.
REFRESH MATERIALIZED VIEW CONCURRENTLY instead:
- builds the new result in a temporary place,
- diffs it against the current rows,
- applies only the inserts, updates, and deletes needed to reconcile them.
Readers never block, and if little changed, little work happens. (It requires a UNIQUE index on the view so rows can be matched between old and new.)
How to break it
The diff in step 2 compares rows by value. So anything non-deterministic in the view makes every row look changed, and CONCURRENTLY falls back to rewriting everything:
SELECT
account_id,
count(*) AS events,
NOW() AS refreshed_at -- different every refresh → every row "changes"
FROM ...NOW(), clock_timestamp(), random(), anything time- or randomness-dependent: each one poisons the diff. You keep the lock-free behavior but lose the incremental behavior, so the refresh is as expensive as a full rebuild while looking like it should be cheap.
The rule
Keep materialized views pure: deterministic aggregations of their inputs, nothing else. If you need to track when the view was refreshed, store that in a separate one-row-per-view table updated alongside the refresh, never as a column inside the view.
Takeaway
CONCURRENTLY gives you lock-free, incremental refreshes, but only for deterministic views. The moment a value changes every run, you’re rewriting the whole table on every refresh. Observability about the view belongs outside the view.
