Materialized Views and the Rise of Incremental Maintenance
Materialized views have been part of the relational database vocabulary for decades, but for most of that time they came with an asterisk. Yes, you could precompute a query result and store it as a table. No, you probably didn’t want to, because keeping it fresh meant re-running the whole query from scratch on some schedule. That tradeoff shaped how people used them: mostly for reporting tables refreshed overnight, rarely for anything that needed to stay current.
Incremental view maintenance (IVM) is the technique that’s quietly rewritten that calculus, and it’s worth understanding both how it works and why it took this long to become practical.
The problem with full refresh
A standard materialized view is conceptually simple: take a SELECT statement, run it, and persist the output as if it were a table. The catch is staleness. The moment a row changes in any of the underlying tables, the materialized view no longer reflects reality. The traditional fix is to refresh it, which usually means dropping the result set and recomputing it from scratch.
That’s fine for a view over a few thousand rows that updates once a day. It falls apart for a view over billions of rows backing a dashboard that users expect to be current within seconds. Recomputing a large aggregation or join from zero every time a handful of rows change is enormously wasteful. You’re redoing 99.9% of the work to account for 0.1% of the change.
What incremental maintenance actually does
IVM flips the problem around. Instead of asking “what is the full result of this query right now,” it asks “given the rows that just changed, what’s the minimal update I need to apply to the existing result to keep it correct.” The database tracks changes to base tables (through something like a changelog, trigger-based delta capture, or integration with the write path) and propagates only the delta through the view’s logic.
The mechanics depend heavily on the operators involved:
- Simple filters and projections are easy. If a row is inserted and it matches the view’s
WHEREclause, insert the corresponding output row. If it’s deleted, remove it. - Aggregations are harder but tractable. A
SUMorCOUNTcan be updated by adding or subtracting the delta’s contribution rather than rescanning everything.AVGrequires tracking both sum and count under the hood.MIN/MAXare the awkward case, since removing the current minimum means you have to go find the next one. - Joins are the real complexity driver. A single row change on one side of a join can produce multiple output deltas, and the system has to correctly compute both the “new matches” and “no longer matches” sets without rescanning the other table in full. This is where most of the theoretical work in IVM (dating back to database research from the 1980s and 90s on “delta rules”) actually lives.
The underlying theory is often framed in terms of relational algebra: express the view as a composition of operators, derive how a small change at the input propagates as a small change at the output of each operator, and chain those derivations together. Done well, the cost of maintaining the view scales with the size of the change, not the size of the data.
Why this matured now
The idea isn’t new, so it’s fair to ask why it’s become mainstream infrastructure only in the last several years rather than decades ago. A few things had to line up:
Change data capture became a solved, productized problem. Reliable, low-overhead streams of row-level changes from a database (via logical replication, binlog parsing, or similar) are now a commodity rather than something each team builds by hand. IVM needs exactly this kind of change feed as its input.
Stream processing engines matured enough to express the delta propagation logic durably and correctly, including state management for joins and aggregations across node failures. Maintaining correct incremental state is a lot like maintaining a long-running stateful stream job, and the tooling for that only recently became robust and widely available.
There was also growing pressure from the use case side. Real-time analytics, operational dashboards, and feature pipelines for machine learning all want fresh, queryable aggregates without hammering the primary database with repeated expensive queries. Full-refresh materialized views couldn’t meet that bar, and plain caching doesn’t understand query semantics well enough to invalidate correctly on arbitrary writes.
The tradeoffs that remain
Incremental maintenance isn’t free. It shifts cost from “a periodic expensive refresh” to “a small amount of ongoing work on every write,” plus the memory or storage needed to hold whatever intermediate state the maintenance algorithm requires (join indexes, running aggregates, etc.). For views with very high write amplification relative to read value, a scheduled full refresh can still win on total resource cost.
There are also correctness traps. Some query constructs resist clean incremental derivation, particularly deep nested aggregations, certain window functions, and queries with non-deterministic or time-dependent predicates. Systems that support IVM typically support a defined subset of SQL for this reason, and it’s worth checking that subset carefully before assuming a complex view will maintain incrementally.
Despite the caveats, the shift is real: a growing number of database and stream processing systems now offer incremental view maintenance as a built-in feature rather than something application teams have to hand-roll. That turns materialized views from a batch reporting tool into something closer to a general-purpose mechanism for precomputing live, correct answers to expensive queries, which is a meaningfully different capability than what the term used to imply.