Materialized Views and Refresh Strategies
A 90-second aggregation query runs on every page load. A materialized view pre-computes it once — the real challenge is refreshing it without blocking every reader while it happens.
A materialized view stores the actual result of a query as real, physical data, rather than recomputing it on every read the way a plain view does — a 90-second aggregation becomes an instant lookup, at the cost of the result going stale the moment underlying data changes, until something explicitly refreshes it. Keeping that view fresh is where a genuine tradeoff lives: a plain REFRESH MATERIALIZED VIEW recomputes the entire result from scratch and swaps it in atomically, but takes a lock strong enough to block every reader for the refresh's full duration — unacceptable for a materialized view meant to serve live traffic continuously.\n\nREFRESH MATERIALIZED VIEW CONCURRENTLY computes the new result separately, then merges only the actual differences into the existing view in place — slower overall, but under a genuinely weaker lock that does not conflict with ordinary reads. This lab proves both halves of that claim directly against pg_locks rather than citing it from documentation: the exact lock mode each command takes, checked live while it is actually running.
A Materialized View, and Its Unique-Index Requirement
CREATE MATERIALIZED VIEW sql_mv_daily_totals AS
SELECT sale_date, sum(amount) AS total, count(*) AS n FROM sql_mv_sales GROUP BY sale_date;
REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;
ERROR: cannot refresh materialized view "public.sql_mv_daily_totals" concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.
CONCURRENTLY needs a way to uniquely identify each row to compute a precise diff between old and new versions — without a unique index, there is no such identity to compare by.
CREATE UNIQUE INDEX ON sql_mv_daily_totals(sale_date);
Stale Data, Confirmed and Fixed
INSERT INTO sql_mv_sales VALUES (999999, '2025-01-01', 99999);
SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01'; -- 424990, stale
REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;
SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01'; -- 524989, correct
The view genuinely did not see the new row until explicitly refreshed — and genuinely did afterward.
The Real Lock Difference, Checked Live
-- Session A (held open with a 3-second sleep to observe it):
BEGIN; REFRESH MATERIALIZED VIEW sql_mv_daily_totals; SELECT pg_sleep(10); COMMIT;
-- Session B, checked mid-refresh:
SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass;
mode | granted
---------------------+---------
AccessExclusiveLock | t
A plain REFRESH genuinely holds AccessExclusiveLock — the strongest lock PostgreSQL has, conflicting with every other lock mode including a plain SELECT's AccessShareLock. Every reader is genuinely blocked for the refresh's entire duration.
-- REFRESH MATERIALIZED VIEW CONCURRENTLY, checked mid-refresh:
SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass;
mode | granted
---------------+---------
ExclusiveLock | t
ExclusiveLock — genuinely weaker. It conflicts with other writers, but not with a plain SELECT's AccessShareLock, which is exactly why readers are never blocked during a CONCURRENTLY refresh.
AccessExclusiveLock vs ExclusiveLock
PostgreSQL's strongest lock mode, AccessExclusiveLock, conflicts with every other lock mode including a plain SELECT's AccessShareLock — nothing else can access the relation at all while it is held. ExclusiveLock is one level weaker: it conflicts with other writers and with itself, but is compatible with AccessShareLock, meaning ordinary reads can proceed normally while it is held. A plain REFRESH MATERIALIZED VIEW takes the former; REFRESH MATERIALIZED VIEW CONCURRENTLY takes only the latter.
REFRESH MATERIALIZED VIEW CONCURRENTLY
Computes the view's new result into a separate temporary structure, then merges only the actual differences into the existing view using its required unique index to match up rows — an UPDATE/INSERT/DELETE-style diff rather than a full replace. This lets it hold a much weaker lock than a plain REFRESH, at the cost of typically taking longer overall and requiring a unique index to exist on the view first.
📸 Create a Materialized View and Hit the Unique-Index Requirement
Build a materialized view from an aggregation, try to refresh it CONCURRENTLY, and see exactly why it needs a unique index.
psql -U postgres -d beer_db -c "CREATE TABLE sql_mv_sales (id int PRIMARY KEY, sale_date date, amount numeric);" -c "INSERT INTO sql_mv_sales SELECT g, DATE '2025-01-01' + ((g%30)||' days')::interval, (g%500)+10 FROM generate_series(1,50000) g;" -c "CREATE MATERIALIZED VIEW sql_mv_daily_totals AS SELECT sale_date, sum(amount) AS total, count(*) AS n FROM sql_mv_sales GROUP BY sale_date;"psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;"psql -U postgres -d beer_db -c "CREATE UNIQUE INDEX ON sql_mv_daily_totals(sale_date);"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_mv_sales (id int PRIMARY KEY, sale_date date, amount numeric);" -c "INSERT INTO sql_mv_sales SELECT g, DATE '2025-01-01' + ((g%30)||' days')::interval, (g%500)+10 FROM generate_series(1,50000) g;" -c "CREATE MATERIALIZED VIEW sql_mv_daily_totals AS SELECT sale_date, sum(amount) AS total, count(*) AS n FROM sql_mv_sales GROUP BY sale_date;" SET CREATE TABLE INSERT 0 50000 SELECT 30 student@lab:~$ psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;" SET ERROR: cannot refresh materialized view "public.sql_mv_daily_totals" concurrently HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view. student@lab:~$ psql -U postgres -d beer_db -c "CREATE UNIQUE INDEX ON sql_mv_daily_totals(sale_date);" SET CREATE INDEX
🔄 Confirm a Stale Value, Then Refresh and Confirm the Fix
Insert a new row into the source table, confirm the materialized view is now stale, refresh it CONCURRENTLY, and confirm the real updated value.
psql -U postgres -d beer_db -c "INSERT INTO sql_mv_sales VALUES (999999, '2025-01-01', 99999);"psql -U postgres -d beer_db -c "SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01';"psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;"psql -U postgres -d beer_db -c "SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01';"student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO sql_mv_sales VALUES (999999, '2025-01-01', 99999);" SET INSERT 0 1 student@lab:~$ psql -U postgres -d beer_db -c "SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01';" SET total -------- 424990 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;" SET REFRESH MATERIALIZED VIEW student@lab:~$ psql -U postgres -d beer_db -c "SELECT total FROM sql_mv_daily_totals WHERE sale_date = '2025-01-01';" SET total -------- 524989 (1 row)
🔒 Prove a Plain REFRESH Blocks Every Reader
Hold a plain REFRESH open with an artificial delay, and check pg_locks from a second connection to see exactly what lock it holds.
(psql -U postgres -d beer_db -c "BEGIN;" -c "REFRESH MATERIALIZED VIEW sql_mv_daily_totals;" -c "SELECT pg_sleep(10);" -c "COMMIT;" > /tmp/refresh_plain.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass AND mode = 'AccessExclusiveLock';"psql -U postgres -d beer_db -c "SET lock_timeout = '500ms';" -c "SELECT count(*) FROM sql_mv_daily_totals;"for i in $(seq 1 30); do grep -q '^COMMIT' /tmp/refresh_plain.txt && break; sleep 1; done; cat /tmp/refresh_plain.txtstudent@lab:~$ (psql -U postgres -d beer_db -c "BEGIN;" -c "REFRESH MATERIALIZED VIEW sql_mv_daily_totals;" -c "SELECT pg_sleep(10);" -c "COMMIT;" > /tmp/refresh_plain.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass AND mode = 'AccessExclusiveLock';" SET mode | granted ---------------------+--------- AccessExclusiveLock | t (1 row) student@lab:~$ for i in $(seq 1 30); do grep -q '^COMMIT' /tmp/refresh_plain.txt && break; sleep 1; done; cat /tmp/refresh_plain.txt SET BEGIN REFRESH MATERIALIZED VIEW pg_sleep ---------- (1 row) COMMIT ERROR: canceling statement due to lock timeout
🔓 Prove REFRESH CONCURRENTLY Takes a Weaker Lock
Bulk up the source table so the concurrent refresh takes measurable time, then check pg_locks mid-refresh to see the real difference.
psql -U postgres -d beer_db -c "INSERT INTO sql_mv_sales SELECT g, DATE '2025-01-01' + ((g%30)||' days')::interval, (g%500)+10 FROM generate_series(50001,300000) g;"(psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;" > /tmp/refresh_conc.txt 2>&1 &) ; sleep 0.05; psql -U postgres -d beer_db -c "SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass AND mode = 'ExclusiveLock';"psql -U postgres -d beer_db -c "SET lock_timeout = '500ms';" -c "SELECT count(*) FROM sql_mv_daily_totals;"for i in $(seq 1 30); do grep -q '^REFRESH MATERIALIZED VIEW' /tmp/refresh_conc.txt && break; sleep 1; done; cat /tmp/refresh_conc.txtstudent@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO sql_mv_sales SELECT g, DATE '2025-01-01' + ((g%30)||' days')::interval, (g%500)+10 FROM generate_series(50001,300000) g;" SET INSERT 0 249999 student@lab:~$ (psql -U postgres -d beer_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sql_mv_daily_totals;" > /tmp/refresh_conc.txt 2>&1 &) ; sleep 0.05; psql -U postgres -d beer_db -c "SELECT mode, granted FROM pg_locks WHERE relation = 'sql_mv_daily_totals'::regclass AND mode = 'ExclusiveLock';" SET mode | granted ---------------+--------- ExclusiveLock | t (1 row)
🔍 Check Whether pg_cron Can Schedule This Automatically
Understand pg_cron's role in scheduling a periodic refresh, then check directly whether it is actually available on this server.
psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_cron';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_cron';" SET name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)
Lab 3.4.4 complete. Materialized views, refreshed and locked, proven directly:\n\n\n Unique-index requirement hit, fixed : ✅ real error, real fix\n Stale value confirmed, then corrected : ✅ 424990 -> 524989\n Plain REFRESH lock, caught live : ✅ AccessExclusiveLock\n CONCURRENTLY lock, caught live : ✅ ExclusiveLock, genuinely weaker\n pg_cron checked, not assumed : ✅ absent, crontab substitute understood\n
Enable JavaScript to run the live terminal and track your progress.