When maintaining a materialized view, what are the trade-offs between refreshing it on-demand (when queried), on a fixed schedule, and in response to change events from the source data?

## Why the choice matters Refresh strategy is the central design decision in the Materialized View pattern, because it determines where on the freshness-vs-cost spectrum a given view sits, and it interacts directly with how the view is used downstream. There are three broad strategies, and most production systems combine more than one depending on the view's criticality. ## On-demand refresh On-demand refresh means the view is recomputed (or its staleness checked and recomputed if needed) at the moment a read arrives, rather than on a background cadence. Mechanically, this is often implemented as a **cache-aside** pattern: a read checks whether a cached/materialized result exists and is fresh enough (e.g., via a TTL or a version/watermark check); if not, it synchronously recomputes and stores the new result before returning it. - **The advantage** is that the view is never staler than "since the last time it was actually requested," which can be attractive for infrequently-accessed views where scheduling a background job to refresh data nobody is reading would be wasted work. - **The downside** is that the recompute cost lands directly in the response-time budget of whichever request triggers it — the first reader after invalidation pays the full cost of the expensive query. - **Under concurrency** this creates the classic "cache stampede" or "dog-pile" problem: if many requests arrive simultaneously after the view goes stale, they can all independently trigger a recompute, multiplying load on the source system exactly when it's least wanted. - **Mitigations** include a single-flight lock (only one request triggers the recompute; others wait or get the slightly-stale value) and serving stale-while-revalidate (return the old value immediately while refreshing in the background for the next request). ## Scheduled refresh Scheduled refresh runs the recompute on a fixed cadence — every minute, every 15 minutes, nightly — independent of whether the underlying data changed or whether anyone is currently reading the view. This is operationally the simplest strategy: a cron job, a scheduled workflow, or a database's built-in scheduled refresh job. Its predictability is valuable for capacity planning — you know exactly when the load spike from refresh will occur and can schedule it during low-traffic windows. Its cost is twofold: 1. it does **wasted work** when the source hasn't changed since the last run, and 2. it imposes a **worst-case staleness bound of one full interval** — a write made one second after a scheduled refresh completes won't be visible until the next interval, which might be minutes or hours later. This is acceptable for reporting/analytics use cases but wrong for anything resembling a live operational view. ## Event-driven refresh Event-driven refresh ties the view's update directly to writes on the source data, typically via **change data capture** (CDC, reading a database's write-ahead log or replication stream), domain events published by the writing service, or database triggers. When a relevant write occurs, a consumer picks up the change and applies an update to the materialized view — either a full recompute of the affected slice or, better, an incremental update that adjusts just the rows/aggregates touched by that change. This gives the lowest achievable staleness (typically sub-second to a few seconds, bounded by pipeline latency) without the wasted-work problem of fixed scheduling. The cost is architectural complexity: - you now need an event/streaming pipeline with its own failure modes (lag, redelivery, ordering), and you must handle out-of-order or duplicate events idempotently, since at-least-once delivery is the norm for most event systems; - incremental update logic is also strictly harder to get right than "just recompute everything" — a bug in the delta logic can cause silent, cumulative drift between the view and the true source state. ## Blending the strategies in production In production, teams frequently blend strategies: an event-driven pipeline keeps the view close to real-time, paired with a periodic full-reconciliation job (e.g., nightly) that recomputes the view from scratch and corrects any drift the incremental path accumulated — trusting the source of truth over the incrementally-maintained copy. Canonical examples of each: | Strategy | Canonical example | |---|---| | **On-demand** | a reverse-proxy's cache-aside lookup with lazy population | | **Scheduled** | a data warehouse's nightly ETL job populating summary tables | | **Event-driven** | a change-stream feeding a downstream index update | The right choice depends on read frequency relative to write frequency, the acceptable staleness window for the specific business use case, and how much operational complexity the team can absorb — there's no universally "best" strategy, only the one matched to those three variables.