Materialized View Maintenance Strategies

The Central Problem: To Recompute or To Increment?

When the data in a base table changes due to INSERT, UPDATE, or DELETE operations, any materialized view built upon it becomes out of sync. A database must have a strategy to "refresh" the view. At a high level, there are two ways to do this:

1. Full Recomputation (or Complete Refresh): This is the simplest approach. The system completely discards the existing data in the materialized view and re-runs the original query from scratch to repopulate it. While straightforward, this can be extremely slow and resource-intensive, especially for the complex analytical queries that materialized views are meant to optimize.

2. Incremental Maintenance: A much more efficient approach. Instead of recomputing everything, the system calculates only the changes (the "delta") to the view based on the changes in the base tables and applies just those updates. For example, if one new order comes in, an incremental approach would update the total sales figure in the view rather than recalculating the sum over all historical orders.

Most practical maintenance strategies use some form of incremental update. These strategies generally fall into two categories based on when the update happens: deferred or immediate.

Deferred Maintenance: Updating on a Schedule

Deferred maintenance is an asynchronous approach where the materialized view is updated after the transaction that changed the base table has already committed. This means there will be a period of time when the view is stale.

The refresh can be triggered in several ways:

• On-demand: A user or application explicitly runs a command like REFRESH MATERIALIZED VIEW.
• On a schedule: A background process (like a cron job or an internal database scheduler) automatically refreshes the view at a defined interval (e.g., every hour, or once a night).

The following diagram illustrates the lifecycle of a materialized view's freshness under a scheduled refresh policy.

Fast Refresh vs. Complete Refresh

A crucial distinction within deferred maintenance is how the refresh is performed. It can be a full recomputation, or it can be a "fast refresh"—an incremental update.

To enable fast refreshes, the database needs to track changes to the base tables. This is typically done using a materialized view log (or sometimes called a snapshot log). This log is a special table associated with a base table that records information about every row that has been inserted, updated, or deleted since the last refresh.

When a fast refresh is triggered, the database doesn't query the entire base table. Instead, it reads the compact materialized view log, computes the net effect of those changes, and applies the resulting delta to the materialized view. This is vastly more efficient.

This next video provides a fantastic explanation of the difference between a full refresh and a fast refresh, and it also discusses the practical implications of scheduled refreshes.

As the video highlights, the main trade-off with deferred maintenance is data freshness vs. write performance. Because the refresh happens outside the write transaction, write operations on the base tables remain fast. However, applications reading from the materialized view must be able to tolerate potentially stale data.

Immediate Maintenance: Always Fresh Data

Immediate maintenance is a synchronous approach where the materialized view is updated within the same transaction that modifies the base table.

• Benefit: The materialized view is always perfectly consistent with the base tables. There is no data staleness.
• Drawback: This adds overhead to the write transaction. Every INSERT, UPDATE, or DELETE on a base table now has the additional work of updating any dependent materialized views before it can commit. This can significantly increase write latency.

A common way to implement immediate maintenance in a traditional DBMS is with triggers. An AFTER trigger on a base table can execute the logic needed to incrementally update the materialized view.

Let's explore the theory and implementation of these two maintenance strategies in more detail.

The approach of using AFTER triggers with transition tables (which capture the sets of inserted and deleted rows) is a powerful, low-level mechanism for building immediate, incremental maintenance inside the database.

From a broader software architecture perspective, this synchronous update pattern has parallels in event-driven systems and CQRS (Command Query Responsibility Segregation). This next clip frames the problem in that context.

The Ultimate Immediate Maintenance: Streaming Databases

The concept of immediate, incremental view maintenance has been taken to its logical conclusion in a modern class of systems called streaming databases. Systems like Materialize are architected from the ground up for this specific task.

Instead of thinking in terms of static tables that are occasionally updated, these systems operate on continuous streams of change data (e.g., from a CDC source like Debezium connected to an OLTP database). The core purpose of the database is to maintain materialized views over these constantly changing streams. The view maintenance isn't a bolt-on feature; it is the feature.

This is achieved using dataflow engines (like Timely Dataflow and Differential Dataflow) that are designed to perform complex computations, like joins and aggregations, incrementally and efficiently as new data flows through.

This streaming approach represents the state-of-the-art for immediate view maintenance, providing fresh, consistent results for real-time analytical workloads.

Summary of Strategies

Let's summarize the maintenance strategies and their key characteristics in a table.

Conclusion

We have seen that keeping materialized views up-to-date is a critical problem with a rich set of solutions. The choice of maintenance strategy hinges on a fundamental trade-off between data freshness and write performance.

Key Takeaways:

• Deferred Maintenance is asynchronous, allowing for low write latency on base tables at the cost of data staleness.
  • A full refresh is simple but inefficient.
  • A fast refresh uses materialized view logs for efficient, incremental updates.
• Immediate Maintenance is synchronous, ensuring the view is always up-to-date but at the cost of higher write latency.
  • This is often implemented using database triggers.
  • Streaming databases represent an architecture built entirely around continuous, immediate, and incremental view maintenance.
• The right strategy depends entirely on the application's requirements for data latency and write throughput.

Preview of the Next Lesson

So far in this course, we've largely been operating under the assumption of a traditional row-oriented storage layout, where all the fields for a given record are stored together. But for the large-scale analytical queries that often motivate the use of materialized views, is this the best layout?

In our next lesson, we will kick off a new module by challenging this assumption. We will explore column-oriented storage, an alternative physical layout that can provide massive performance gains for analytical workloads. We will begin by addressing the learning outcome: Explain the architectural differences between row-oriented and column-oriented storage layouts.