Materialized views: a cache whose admission was declared

When does rebuilding a materialized view beat maintaining it?

The difference between a materialized view (MV) and a cache is not the mechanism. It is who decided what gets stored. A cache discovers by watching reads. An MV is told at design time. Every other difference follows from this, which makes MVs the clean end of a continuum whose other end is an LRU.

Two consequences follow, and the second is deep.

P is paid whether or not anyone reads. A demand-populated cache wastes space on a bad guess. A declared view wastes compute, on a schedule, forever. The risk profile is entirely different, and so is the failure mode: unused MVs do not appear as a cache-miss metric. They appear as a refresh bill that nobody attributes.

Declaration is what buys incrementality. A demand-populated entry cannot be incrementally maintained, because at write time the system does not know the entry exists, let alone how it was derived. A declared view can hook into the write path. That one fact explains a difference in vocabulary: caches are invalidated and views are refreshed. It is not a difference in storage. It is that only one of the two has a derivation the system knows about. It also connects back to the result cache: "delete the cache entry, do not update it" is correct advice precisely because the system cannot compute what the updated value should be.

Full versus incremental refresh is decided by algebra, not by engineering

Maintenance cost is O(base) for a full rebuild and O(delta) for incremental refresh, if the aggregate allows it. Which aggregates allow it is algebra: SUM and COUNT have an inverse and can absorb deletes; MIN and MAX absorb inserts but not deletes; exact medians and distinct counts cannot be maintained at all (Incremental view maintenance derives the table). For a view, the consequence is a design rule: the view must store the monoid element, not the final projection.

AVG is the teaching case. To maintain the average of amount per article incrementally, store the pair (sum, count) in the view and compute sum / count when reading:

-- The view stores the carrier (sum, count), not the average itself.
CREATE MATERIALIZED VIEW article_amount AS
  SELECT article_id, SUM(amount) AS s, COUNT(*) AS c
  FROM events GROUP BY article_id;

-- New rows: s += delta_sum, c += delta_count. Deletes: subtract.
-- The projection happens on read:
SELECT article_id, s::numeric / c AS avg_amount FROM article_amount;

Mergeable sketches (HyperLogLog for distinct counts, t-digest or KLL for percentiles) restore maintainability to an aggregate that has none, by paying in exactness. In terms of trade-offs, this lets approximation (accepting small errors) purchase a better position on the precompute-versus-compute-on-read axis. That is a purchase that a plain list of "materialized views" and "approximate aggregation" as separate topics has no slot for. Sketch-backed views are the mechanism by which a non-decomposable aggregate becomes incrementally maintainable at all.

The MIN/MAX row has a consequence worth stating: those aggregates are fully maintainable under insert-only workloads and not under deletes. So append-only and event-sourced designs make strictly more views incrementally maintainable than mutable designs do. Immutability buys something again, for the third time here.

Joins and non-monotonic operators

Joins raise the bar in a different way. Incremental join maintenance must probe the full opposite relation for each delta row, so it needs an index on the opposite side's join key, or it degenerates into a full scan per delta row (the join delta rule). And non-monotonic operators (outer and anti joins, EXCEPT, HAVING) can let a deletion produce a row. That forces retraction semantics (the ability to emit "remove this earlier output") and a changelog. This is the boundary of what production systems offer. The machinery beyond it (differential dataflow, DBSP) belongs to stream processing and is only a pointer here.

The crossover, and a recurrence worth counting

Incremental maintenance wins roughly when the changed fraction of the base is small. The measured break-even in practice lands somewhere between a few percent and about 20%. The reason is not specific to views: incremental maintenance does random access for each delta row (index probes, scattered updates), while a full rebuild does a sequential scan at full bandwidth. The crossover is wherever random-access amplification overtakes sequential throughput.

Worked illustration (invented numbers). Suppose each delta row costs 10 times as much as one row scanned sequentially. A rebuild costs B (the base size in row-scan units); an incremental refresh of d delta rows costs 10·d. Break-even: 10·d = B, so d = B/10, a delta of 10% of the base. If the medium makes random access only 5× worse, break-even moves to 20%. If a slower disk makes it 50× worse, break-even drops to 2%.

This is the same crossover, for the same reason, with the same number, as the index-versus-full-scan selectivity decision in a query planner (an index probe is worth it only when the query selects a small fraction of rows). The maintain-or-rebuild decision and the index-or-scan decision are one arithmetic in two costumes. Both are governed by the random-to-sequential ratio of the underlying medium, so both crossovers moved when NVMe replaced spinning disks, in the same direction and by roughly the same factor. That is a satisfying compression, and a checkable one.

Honest typing: the algebra results are permanent constraints. The few-percent crossover is current engineering reality, depends on the medium, and should be measured per system, not quoted.

The fan-out cost nobody budgets

One table write must maintain k declared views, so write amplification is linear in the number of views, and the write path's latency now includes k maintenance operations (or a queue, which brings staleness back). This is the conservation of cost again: it moves from readers to writers. The organizational failure mode is structural rather than careless. Views get added one at a time, each justified against its own query, and the write path degrades with no single addition to blame.

In one sentence: an MV is a cache whose admission was declared. Declaration is what buys incrementality. Incrementality itself is decided by the aggregate's algebra, and it loses to a rebuild past a few percent delta, the same crossover as index versus scan.