Materialized Views
REFRESH MATERIALIZED VIEW (non-concurrent)
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
refresh-matview-blocking REFRESH MATERIALIZED VIEW daily_revenue;What this does
Blocks all reads and writes on the materialized view for the entire time it takes to recompute and repopulate it.
Safe rewrite
Use CONCURRENTLY to allow reads during the refresh (requires a unique index on the materialized view)
-- 1. Ensure a unique index exists on the materialized view:
-- CREATE UNIQUE INDEX ON daily_revenue (...);
-- 2. Refresh concurrently:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
-- Note: CONCURRENTLY takes an EXCLUSIVE lock (blocks writes, allows reads)