Data Engineering
Materialized views: SQL and refresh reading
Crux Read real SQL — REFRESH CONCURRENTLY setup, pg_ivm immv creation, and a staleness-window check — and pick the behaviour or the highest-leverage fix a senior would make first.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitThe refresh statement and the SQL around it are where materialized-view problems are actually born. Read each snippet, predict what the database will do, then choose the fix a senior reaches for first.
Goal
Practise the loop you run when reviewing an MV: read the DDL and the refresh path, predict the lock and staleness behaviour, and reach for the structural fix before the tuning knob.
Snippet 1 — the CONCURRENTLY prerequisite
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
region,
sum(amount) AS revenue
FROM orders
GROUP BY 1, 2;
-- nightly job:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue; Quiz
Completed
The nightly job fails immediately with 'cannot refresh materialized view concurrently' / a unique-index error. What is missing, and what exactly must it be?
Heads-up The requirement is a unique index on the materialized view itself, not the base table. CONCURRENTLY uses the view's unique index to identify and update changed rows during the swap.
Heads-up Even after a first plain refresh, CONCURRENTLY still errors without a unique index on the view. The unique index, not a prior refresh, is the hard prerequisite.
Heads-up CONCURRENTLY's required unique index must cover all rows with no WHERE clause and no expressions — a partial or expression index does not satisfy it.
Snippet 2 — incremental maintenance with pg_ivm
CREATE EXTENSION IF NOT EXISTS pg_ivm;
-- turn the aggregate into an incrementally-maintained view
SELECT create_immv('daily_revenue_ivm',
$$ SELECT date_trunc('day', created_at) AS day,
region,
sum(amount) AS revenue
FROM orders
GROUP BY 1, 2 $$); Quiz
Completed
After create_immv, a teammate notices that every INSERT into orders got measurably slower. Is this a bug, and what is happening?
Heads-up pg_ivm maintains the view synchronously inside the writing transaction by design; that is what gives near-zero staleness. The per-write slowdown is inherent, not a misconfiguration.
Heads-up An immv stores the result like a table; reads do not re-run the GROUP BY. The added cost is on the write path, from the maintenance triggers.
Heads-up Adding base-table indexes can help maintenance, but the structural slowdown is the synchronous delta-maintenance per write. Under a high write rate, that overhead dominates regardless.
Snippet 3 — detecting staleness
-- ops wants an alert if the MV has not refreshed recently.
-- they query the view's own data for the newest day:
SELECT max(day) AS newest_day FROM daily_revenue;
-- alert fires only if newest_day < current_date; Quiz
Completed
Why is alerting on max(day) inside the view an unreliable staleness check, and what should you track instead?
Heads-up Frequency is not the issue. The metric itself conflates 'newest data present' with 'recently refreshed' — those diverge exactly when a refresh silently fails, which is the case you most need to catch.
Heads-up Row count can be unchanged across a successful refresh (same groups, updated sums) and can stay frozen when refresh fails — same conflation. The reliable signal is the refresh job's own success and timestamp.
Heads-up CONCURRENTLY governs refresh, not reads; this SELECT already sees a consistent snapshot. The flaw is using view content as a proxy for refresh recency.
Snippet 4 — the refresh that never finishes
-- a long-running analytics session is open:
BEGIN;
SELECT ... FROM orders JOIN ... ; -- runs for 20+ minutes, still open
-- meanwhile, the scheduler fires:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue; -- appears to hang Quiz
Completed
The CONCURRENTLY refresh sits and does nothing while the analytics session is open. What is the mechanism, and what is the durable fix?
Heads-up It is not a deadlock — the refresh is waiting on transaction drain, a normal precondition for the consistent swap. The deadlock detector will not fire; the refresh just waits.
Heads-up Plain refresh takes an ACCESS EXCLUSIVE lock and would block the readers entirely, which is worse for a constantly-read dashboard. The fix is bounding the long transactions, not reintroducing read-blocking.
Heads-up One unique index already satisfies the requirement; extra indexes do not change the transaction-drain wait. The stall is about long open transactions, not index count.
Recap
The refresh path is where MV problems live: CONCURRENTLY needs a unique index on plain view columns or it errors; pg_ivm’s create_immv moves cost onto every write via triggers, so a per-write slowdown is the design, not a bug; staleness must be measured from the refresh job’s own success and timestamp, never from the view’s data content; and a CONCURRENTLY refresh stalls behind long open transactions, so bound them or it never catches up. Read the DDL and the refresh, predict the lock and staleness behaviour, then fix the structure before reaching for a knob.