Data Engineering
Materialized views: free-recall review
Retrieval beats re-reading. For each prompt, say or write a full answer from memory before you open the model answer — the effort of recall is what makes the tradeoffs stick when you are in the design review.
Reconstruct the unit’s core mechanisms — what a materialized view buys, the refresh ladder, CONCURRENTLY’s hidden costs, incremental maintenance, streaming MVs, and the staleness failure mode — without looking back at the lesson.
- 01What does a materialized view actually buy you compared to a plain view, and what is the one decision that matters once you have one?
- 02Contrast plain REFRESH MATERIALIZED VIEW with REFRESH ... CONCURRENTLY, including the requirements and costs of CONCURRENTLY.
- 03What is incremental view maintenance, how does pg_ivm implement it in Postgres, and when does it pay off versus backfire?
- 04How do streaming materialized views (ClickHouse, Materialize) change the model, and what is the catch a senior must internalize for each?
- 05A teammate proposes a materialized view refreshed nightly to speed a slow report. What three risks do you raise before approving?
- 06Order the decisions a senior makes when reaching for a materialized view, and say why ownership comes last but matters most.
If you could reconstruct each answer from memory, you hold the unit’s spine: a materialized view trades freshness for read speed, so refresh is the only decision that matters; plain refresh blocks reads with an ACCESS EXCLUSIVE lock while CONCURRENTLY avoids that for the cost of a unique index, slower diffs, and waiting on old transactions; pg_ivm gives near-zero staleness but taxes every write, so it suits read-heavy low-write tables only; streaming MVs remove the refresh job but add a system and a consistency model to reason about; and across all of them the recurring failure is an unmonitored refresh serving stale data while everything looks green — so own the refresh, bound the staleness window, and alert on it.