A plain REFRESH MATERIALIZED VIEW rebuilds the heap under an ACCESS EXCLUSIVE lock, so readers wait. REFRESH ... CONCURRENTLY computes the new result in a temporary table, diffs it against the current rows and applies only the changes while readers continue. It needs a unique index on plain columns covering every row; without one, 452-refresh.sql got ERROR: cannot refresh materialized view "mart.mv_genre_month" concurrently, and after CREATE UNIQUE INDEX ON mart.mv_genre_month (month, genre) both forms took 0.6 to 0.9 s. refresh_locks.sh keeps a refresh open in one session while a second one reads with a two-second lock_timeout:
cd /home/dev/v7-l2/ch04 && rm -f refresh.fifo && mkfifo refresh.fifo
PSQL="docker exec -i l2-pg psql -U postgres -d booknest -XAtq"
for mode in "" "CONCURRENTLY"; do
$PSQL < refresh.fifo & exec 3>refresh.fifo # session 1: the refresh, kept open
echo "BEGIN; REFRESH MATERIALIZED VIEW $mode mart.mv_genre_month;" >&3
sleep 2
$PSQL -c "SELECT 'refresh ${mode:-plain} holds', mode FROM pg_locks
WHERE relation = 'mart.mv_genre_month'::regclass AND granted"
$PSQL -c "SET lock_timeout = '2s'" \
-c "SELECT 'dashboard reads', count(*) FROM mart.mv_genre_month" 2>&1 # session 2
echo "COMMIT;" >&3; exec 3>&-; wait
done
rm -f refresh.fifo... refresh plain holds|AccessExclusiveLock ERROR: canceling statement due to lock timeout ... refresh CONCURRENTLY holds|ExclusiveLock dashboard reads|96
The concurrent refresh's ExclusiveLock blocks writers and a second refresh but not SELECT. Its diff costs extra work, so when most rows change a plain rebuild is faster. Trigger refreshes from the loading pipeline (Orchestration and Pipelines).