How would you choose between refreshing a materialized view on commit of every base-table transaction versus on demand on a schedule, and how do you decide the acceptable staleness window?
answer
- on-commit = writers pay + hotspot on shared aggregate rows
- on-demand = staleness window, batched cost
- window = interval + duration + recovery
- alert on refresh lag, not job success
- zero tolerance ⇒ don't use a materialized view
basics
~20 sOn-commit refresh keeps the view nearly current but charges every writing transaction and serialises writers on shared aggregate rows. On-demand refresh batches the cost but leaves a staleness window. Choose by the consumer's tolerance for stale data against the write throughput you can afford to lose.
solid answer
~60 sOn-commit (synchronous) refresh makes the materialized view part of every base-table transaction: writers pay the maintenance cost, commit latency rises, and concurrent writers touching the same aggregate row contend on it — a broad rollup can serialise an otherwise parallel write workload. In exchange readers never see stale data and there is no refresh job to operate. On-demand refresh runs on a schedule or a trigger event. Writes stay fast, cost is batched and amortised, and failures are visible and retryable — but readers see data as of the last refresh. I drive the choice from the consumer, not the database. I ask what decision is made on this number and how wrong it may be: a fraud counter blocking a payment may need seconds; an executive dashboard is fine at fifteen minutes; a monthly report is fine at a day. Then I set the refresh interval well inside that window, monitor *refresh lag* rather than job success, and publish an "as of" timestamp so stale never reads as wrong. On-commit is a last resort when correctness truly cannot tolerate any window — and often the better answer is then not a materialized view at all.
go deeper
Know that refreshes happen either as part of the writing transaction or later on a schedule, and that the later form means the data can be out of date.
Contrast the two, and give the staleness window as interval plus refresh duration; mention publishing an "as of" timestamp.
Bring the write-path costs — commit latency, row-level contention on aggregate rows, failure blast radius — and the monitoring signal of refresh lag.
Drive the whole answer from the consumer's tolerance, propose hybrid designs (tiered views, event-driven refresh, base-table overlay), and say plainly when a materialized view is the wrong tool.
## The two timing models **On commit (synchronous maintenance).** The view is updated inside the same transaction that changed the base data. Commit does not return until the derived rows are consistent. Readers can therefore never observe a discrepancy between base tables and the view. **On demand (asynchronous).** Something outside the writing transaction triggers refresh: a scheduler, a job after a bulk load, an operator, or a queue drain. Writers are untouched; readers are stale by up to the refresh interval plus refresh duration. ## What on-commit really costs Three costs, in order of how often they bite: 1. **Commit latency.** Every write now also maintains derived rows and their indexes. On a hot OLTP path this is directly visible in p99 latency. 2. **Write serialisation.** This is the one candidates miss. If the view aggregates `SUM(amount) GROUP BY store_id` and a thousand concurrent orders belong to the same store, all thousand transactions must update the *same* aggregate row. Row locks turn a parallel workload into a queue. The narrower the grouping, the worse the hotspot. 3. **Blast radius.** A failure or deadlock in view maintenance now fails the business transaction. Derived reporting data becomes able to reject a customer's order. On-commit also constrains the definition sharply — engines only support it for view shapes they can maintain incrementally and cheaply — and it is unavailable for anything referencing remote or non-deterministic data. ## What on-demand really costs One cost: a window in which the answer is wrong, whose size is *interval + refresh duration + failure recovery time*. The failure mode is not "slightly old data"; it is **unbounded** staleness when the job stops and nobody notices, because a stopped job produces no errors. Hence the operating rule: alert on the **age of the last successful refresh**, not on job exit status. Secondary costs are spikiness (a heavy refresh competing with peak traffic) and, for concurrent refresh, churn and bloat in the view. ## Choosing the staleness window Derive it from the decision the data supports, not from what feels fast: - **Zero tolerance** — the value gates a transactional decision (credit limit, stock reservation, fraud threshold). Do not use a materialized view. Query the base tables, or maintain the counter transactionally as first-class state that the domain owns. - **Seconds** — operational monitoring, near-real-time ops screens. Usually better served by a streaming read model or an incrementally maintained summary than by a scheduled full refresh. - **Minutes** — the sweet spot for dashboards and internal analytics. Schedule at a third to a half of the tolerance, so one missed run does not breach the budget. - **Hours or a day** — reporting, billing preparation, exports. Refresh in a maintenance window; complete refresh is usually fine. Then make staleness *visible*: publish the refresh timestamp with the data. Users tolerate "as of 14:05" and lose trust in an unlabelled number that disagrees with the transactional screen. ## Hybrid patterns worth naming - **Event-driven refresh** — refresh after a bulk load or ETL batch completes rather than on a clock, so consumers see whole loads and never a half-loaded state. - **Tiered views** — a small, frequently refreshed view over recent data unioned with a large, rarely refreshed historical view. Fresh where it matters, cheap where it does not. - **Base tables plus view** — the application reads the view for aggregates and falls back to the base tables for the current user's own recent activity, so read-your-own-writes holds without making the whole view synchronous. ## How to argue it in an interview Start from the consumer's tolerance, state the write-path cost of synchrony including the hotspot argument, pick a refresh interval with margin, and name the monitoring signal (refresh lag) and the user-visible artefact (an "as of" timestamp). Ending with "and if the tolerance is truly zero, a materialized view is the wrong tool" is the answer that separates a principal from a senior.
- Why can synchronous, on-commit maintenance of an aggregate view damage write throughput even when the maintenance work itself is tiny?Because concurrency, not CPU, is the bottleneck. Many concurrent base-table transactions map to the same aggregate row, so they must take the same row lock in sequence. Work that costs microseconds per transaction becomes a serialisation point that caps throughput at one update per lock hold, and lock waits and deadlock risk grow with contention.
- A scheduled refresh job reports success every run, yet users complain about wrong numbers. What do you check first?Refresh lag and refresh duration. A job can succeed while taking longer than its own interval, so runs queue or overlap and the effective staleness far exceeds the budget. Compare the last successful refresh timestamp to now, and confirm the job is not silently falling back to a complete refresh or skipping runs because a previous one still holds the single-flight lock.
- How do you keep a user from seeing their own just-submitted change missing from a materialized-view-backed screen?Do not serve that path from the view alone. Read aggregates from the view for the general case and overlay the user's own recent rows from the base tables, or route the user to a transactional read for a short window after their write. That preserves read-your-own-writes without forcing the entire view to be synchronously maintained.
On-commit is updating the scoreboard after every single point, with the scorer standing between the players. On-demand is updating it once a minute — cheaper, and everyone knows to read the clock next to it.
saying these in an interview costs you the question
- Treating on-commit refresh as "just more correct" with no write-path cost
- Missing the shared-aggregate-row contention argument
- Setting the refresh interval equal to the staleness tolerance, leaving no margin
- Alerting on job failure only, so a stalled job produces unbounded staleness
- Using a materialized view for a value that gates a transactional decision