intermediateViews — Simple View & Materialized View

Why does REFRESH MATERIALIZED VIEW block concurrent readers, and how do you avoid that?

A plain REFRESH truncates the existing storage and reruns the defining query from scratch, taking an ACCESS EXCLUSIVE lock for the duration — any query touching the view blocks until it finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids this by computing the new result into a temporary copy and diffing it in, but it requires a unique index on the materialized view first.

This is a Pro chapter

Sign in, then upgrade to Pro or Power to unlock this and the full Databases Mastery library.

Why does REFRESH MATERIALIZED VIEW block concurrent readers, and how do you avoid that?

Next Step

Continue to Can a materialized view speed up a slow query on its own, just by creating it?← Back to all SQL questions