The Dirty Secret of Incremental View Maintenance: You Still Need Batch

The appeal of Incremental View Maintenance (IVM) is its ability to efficiently process small changes to very large datasets. But there’s a catch: before an IVM engine can handle real-time updates, it must first ingest and process the entire historical dataset. In other words, a good IVM engine also needs to be a capable batch engine — and today’s systems aren’t.

This challenge, known as the backfill problem, is one of the biggest hurdles in building a production-ready IVM engine. Over the past few months, we’ve been working on a solution, and we’re about to announce it.

In this article, I’ll explain what exactly makes backfill tricky. Using a simple example, I’ll show why even a powerful IVM system like Feldera requires special care to make backfill efficient.

Example

Consider a SQL table that records the grades students receive in different classes. We define a view that augments each record with the average grade across all students in the same class:

create table grades (
    student string,
    class string,
    grade decimal(5,2)
);

create view enriched_grades as
select
    student,
    class,
    grade,
    AVG(grade) over (partition by class) AS class_avg
from grades;

We’d like to use IVM to keep this view up to date as teachers enter new grades in real time. But before we can do that, we first need to compute the current snapshot of the table — which may already contain billions of records (it’s a big school!). How do we go about this?

Here’s one idea. We have an IVM engine that can handle changes efficiently. Why not break up the input dataset into many small chunks and ingest them one by one? Unfortunately, this strategy backfires. Consider the state of the system shown below:

Let’s add two new records:

insert into grades values
    ('Carl', 'Algebra', 85),
    ('Carl', 'Physics', 97);

Each new grade changes the class average, which forces the system to update every record in that class. Old outputs are deleted and replaced with new ones:

Now let’s add two more:

insert into grades values
    ('David', 'Algebra', 96),
    ('David', 'Physics', 91);

Once again, both Algebra and Physics averages must be recomputed, triggering another full update of those classes:

What if we processed all four inputs at once? In that case, the engine would skip the intermediate updates and jump directly to the final result:

insert into grades values
    ('Carl', 'Algebra', 85),
    ('Carl', 'Physics', 97),
    ('David', 'Algebra', 96),
    ('David', 'Physics', 91);

In both cases, the engine does work proportional to the number of changes (including changes to the input and output view). But when ingesting in smaller chunks, much of that work is wasted — intermediate updates are overwritten by later ones.

Takeaways

This example shows that the ideal ingest granularity depends on the workload phase.

• Backfill. The most efficient way to process historical data is as one giant delta. This avoids redundant updates and minimizes the time to complete backfill.

• Real-time updates. For streaming inputs, low latency is critical, so the engine must process small changes immediately as they arrive.

The problem is that IVM and streaming engines are optimized for handling small deltas that can be processed in-memory. They are more than likely to fail or run out of memory when handling a billion-record delta during backfill.

The Feldera solution

Over the past few months, we’ve taught Feldera to process very large deltas efficiently. Importantly, this required no changes to the underlying theory, algorithms, or data structures — all the mathematical guarantees of DBSP hold for both small and large deltas. Instead, we focused on the runtime: introducing new operators and optimizing existing ones to handle changes larger than memory.

That story, however, deserves a blog post of its own.