Materialized view refresh strategies

Last updated: 6 Oct 20265 min read

tutorial

A materialized view trades extra storage and refresh work for faster reads; the refresh mode must match the source change pattern and the freshness contract.

Choose the result grain

A store-day sales summary should have one key per store and day. If the source query joins to a nonunique dimension, one order can multiply before aggregation. Define the key and test uniqueness before selecting a refresh strategy. Measure additivity determines which fields can be summed and which need recomputation from components.

Compare full and incremental work

A full refresh recomputes every result, making correctness straightforward but paying a read and write cost proportional to the full input. An incremental refresh reads changes and updates affected keys, but it needs stable row identity, delete handling and a way to revisit old groups after corrections. Small deltas do not guarantee cheap refresh when one change invalidates a large join or window.

Treat freshness as observed age

A target such as five minutes is a goal for how old the consumer-visible answer may be, not a promise that a scheduler runs exactly every five minutes. Record source commit time, last successful refresh time, table generation and serving pointer. If refresh duration exceeds the target, queueing more refreshes can deepen the backlog. The freshness contract is checked at the reader boundary.

Handle late and deleted input

A refund arriving tomorrow may change last month’s store-day aggregate. A watermark that only reads newer event dates misses it. Drive the refresh from change positions or affected keys, and include deletes and corrections in the same state transition. Keep a bounded replay horizon with a documented older-correction path; silently ignoring old changes makes the view fast but wrong.

Test publication and recovery

Build refreshed rows in a candidate generation and publish atomically after row-count, key and control-total checks. Inject a failed refresh and confirm the last good view remains queryable with its age visible. Benchmark both full and incremental modes under realistic update concentration, because a tiny uniform test data set hides skew and write amplification.

Implementation

sales = [("west", "2026-10-01", 2375),
         ("west", "2026-10-01", -125),
         ("east", "2026-10-01", 6400)]

def rebuild_store_day(events):
    totals = {}
    for store, day, cents in events:
        key = (store, day)
        totals[key] = totals.get(key, 0) + cents
    return totals

assert rebuild_store_day(sales)[("west", "2026-10-01")] == 2250

Performance and operating cost

The reference full rebuild costs O(N) expected time and O(G) state for N events and G store-day groups. Incremental work can approach O(C) for C changed records when affected groups are indexed, but maintaining that index and handling deletes adds state. Refreshes consume compute, storage and write I/O even when no user is querying the view.

Common Mistakes

Do not mistake a refresh schedule for a measured freshness guarantee.
Do not ignore deletes or corrections outside the newest event-date partition.
Do not assume incremental refresh is cheap for a broad join.