While a materialized view is being refreshed, what happens to queries that read it, and how does a concurrent refresh mode (REFRESH MATERIALIZED VIEW CONCURRENTLY and its equivalents) change that?
answer
- plain refresh = exclusive lock, readers block
- concurrent = compute, diff, apply as DML
- unique index required to match rows
- slower + dead rows, but no reader outage
- first population must be non-concurrent
basics
~20 sA plain refresh typically takes an exclusive lock and rebuilds the view, so readers block for the whole refresh. A concurrent refresh builds the new result separately and merges the differences row by row, letting readers keep querying — at the cost of a slower refresh and a required unique index.
solid answer
~50 sA default refresh rebuilds the stored contents under a strong lock: readers of the materialized view block until the refresh commits. On a large view that is a multi-minute outage for every consumer, which is why teams discover it in production rather than in review. **Concurrent refresh** avoids that. The engine computes the new result into a temporary structure, diffs it against the current contents, and applies only the changed rows as ordinary DML. Readers see a consistent old snapshot throughout and then the new one — nobody blocks. The costs are real. Concurrent refresh requires a **unique index** on the view so rows can be matched between old and new, it does more total work (compute plus diff plus apply), it generates write-ahead log traffic and dead rows proportional to the change, and it cannot be used to populate a view that has never been refreshed. I default to concurrent for anything user-facing and reserve the blocking form for off-hours rebuilds.
code
sql · 8 linesCREATE UNIQUE INDEX daily_sales_pk
ON daily_sales (store_id, sale_date);
-- first population must be the blocking form
REFRESH MATERIALIZED VIEW daily_sales;
-- afterwards, readers are never blocked
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;go deeper
Know that a refresh is real work on the server and that the simple form can block readers while it runs.
Contrast rebuild-and-swap with diff-and-apply, and name the unique-index prerequisite.
Discuss operating it: lock timeouts, single-flight scheduling, refresh-lag alerting, bloat from repeated diffs, and publishing an "as of" timestamp.
Decide the refresh architecture against an availability and staleness budget, and say when you would move the workload out of refresh entirely into an incrementally maintained read model.
## The default: refresh as a rebuild The straightforward way to refresh is to recompute the query and swap in the new contents. To do that safely, the engine takes a lock that excludes readers for the duration — conceptually the view's contents are replaced wholesale. This is fast in terms of raw work (one bulk write, no diffing) but it means every session that selects from the view waits for the whole refresh. That is fine at 03:00 on a reporting view nobody reads. It is an incident when a dashboard refreshing every five minutes blocks for ninety seconds each time, or when refresh sits behind a long-running reader and the lock request queues everything behind *it* as well. ## Concurrent refresh Concurrent refresh trades work for availability: 1. Compute the new result set into a transient structure. 2. Compare it against the existing view contents on a unique key. 3. Apply the difference as regular inserts, updates and deletes inside a transaction. Because the change is ordinary DML under normal concurrency control, readers are never excluded. They see the pre-refresh snapshot until the transaction commits, then the post-refresh one — atomically, with no intermediate state visible. ### The unique index requirement Diffing needs identity: the engine must decide whether a new row *replaces* an existing row or is genuinely new. That requires a unique index over columns that uniquely identify a row of the view — commonly the grouping keys of an aggregate. Without one, concurrent refresh is refused. Practically this means: when you design a materialized view you intend to refresh concurrently, you must be able to name its key, and rows must be unique on it. A view with duplicate rows cannot be refreshed concurrently until you make it unique. ### Costs to plan for - **Slower.** You compute the full new result *and* diff *and* apply. Wall-clock refresh time is typically higher than the blocking form. - **Write amplification.** Applying the diff writes rows and index entries and leaves dead versions behind, so the view and its indexes need vacuuming/reorganisation attention over time. - **Not for first population.** A view that has never been populated has nothing to diff against, so the first refresh must be the blocking form. - **Still one refresh at a time.** Concurrent refresh does not mean two refreshes may run simultaneously; it means readers are not blocked. ## Indexing the view itself Since a materialized view is physical, it should be indexed for how it is read: the unique key that identifies a row, plus supporting indexes for filters and sorts consumers actually use. Two consequences follow. First, every index makes concurrent refresh's apply step more expensive, so index deliberately rather than exhaustively. Second, after a blocking rebuild the view's statistics may be stale — collecting statistics as part of the refresh job keeps the optimizer honest for readers. ## Operating it A refresh job needs the same care as any other batch process: - **A lock/statement timeout,** so a refresh cannot queue indefinitely behind a long reader and take the rest of the system down with it. - **Single-flight execution,** so a slow refresh does not overlap with the next scheduled one. - **Lag monitoring:** publish the last successful refresh timestamp and alert on age, not merely on job failure. A job that succeeds in twenty minutes when the staleness budget is five is failing quietly. - **A published "as of" value** so consumers can display data age. ## Vendor framing The names differ — one engine spells it `REFRESH MATERIALIZED VIEW CONCURRENTLY`, others provide out-of-place refresh, atomic swap, or partition-exchange style rebuilds — but the concept is constant: rebuild-and-swap blocks readers and is cheaper; diff-and-apply keeps readers online and costs more. Answer at that level and name the mechanism you have actually used.
- Why does concurrent refresh require a unique index on the materialized view?The refresh works by diffing the newly computed result against the stored rows and applying only the differences. To decide whether a computed row updates an existing row or is a new one, the engine needs a key that identifies a row uniquely. Without such an index there is no way to pair old and new rows, so the operation is rejected.
- Your concurrent refresh runs every five minutes and the view keeps growing on disk even though its row count is stable. Why?Applying the diff is ordinary DML, so updated and deleted rows leave dead versions in the view and its indexes. If cleanup (vacuum/reorganisation) cannot keep up with the churn rate, the view and its indexes bloat. Fixes include refreshing less often, reducing how many rows actually change per refresh, or tuning cleanup aggressiveness for that object.
Blocking refresh is closing the shop to restock the shelves. Concurrent refresh is restocking item by item while customers browse — slower for staff, invisible to customers.
saying these in an interview costs you the question
- Assuming readers are never blocked by an ordinary refresh
- Thinking "concurrent" means two refreshes can run at the same time
- Not knowing a unique index is a prerequisite
- Believing concurrent refresh is strictly faster
- Scheduling refreshes with no single-flight guard or lock timeout