In community PostgreSQL, REFRESH MATERIALIZED VIEW (non-CONCURRENTLY) holds AccessExclusiveLock for the whole statement, so reads are blocked for the entire refresh. CONCURRENTLY doesn't block reads, but needs a UNIQUE index with no WHERE clause and does a row-by-row diff/merge (refresh_by_match_merge()), which is slow once the delta is large.
PolarDB for PostgreSQL claims you can have both — full rebuild speed and non-blocking reads (docs):
SET polar_enable_reduce_refresh_matview_lockmode = on;
REFRESH MATERIALIZED VIEW mv_name;
Readers see the old data throughout the defining query, the data write and the index rebuild. Two details from the docs look like the crux:
- a short mutual-exclusion window remains in the finalization phase, "to ensure correct replay on read-only nodes";
- if read transactions are still open at that point, the refresh waits for them to commit before committing itself.
My questions:
- The non-concurrent path ends in a relfilenode swap (
refresh_by_heap_swap()→finish_heap_swap()), and the old relfilenode is unlinked at commit. A reader that started earlier is still scanning that file — blocking it is exactly whatAccessExclusiveLockbuys. Is the whole trick "defer the unlink and drain readers before commit", or is more needed (relcache/catalog-snapshot handling for backends that already opened the old relfilenode)? - Why is the remaining exclusive window specifically about read-only nodes? Upstream standbys rely on replaying that
AccessExclusiveLockto raise a recovery conflict; if the primary never takes it, what does the replica conflict against? - Is there prior art upstream? I couldn't find a committed feature or accepted design for lowering this lock level. Links to
pgsql-hackersthreads with the actual objections would be ideal.
Answers grounded in matview.c and the lock-level / invalidation rules preferred — I'm after the mechanism, not a vendor comparison.