When SQL Is Enough for Streaming: What Incremental View Maintenance Guarantees — and What It Refuses

The default answer to "keep this aggregate fresh over a stream" used to be a hand-written stream job: define the state, pick the serializers, wire the timers, operate the cluster. Streaming databases compress that job into one statement. You write CREATE MATERIALIZED VIEW, the engine compiles the SQL into a dataflow, and every arriving event updates the result incrementally. The 2026 pitch — RisingWave and Materialize on one side, the Flink SQL ecosystem converging from the other — is that for read models expressible as SQL, the DataStream program is now optional.

I wanted to test that pitch instead of repeating it. So I built one view in RisingWave, subscribed to its change stream from Node, and then went looking for the two places the promise bends: the state the view silently accumulates, and the queries that refuse to be incremental at all. These notes are the result. The engine is RisingWave because it is Apache-licensed and runs locally in one container, but the cost model comes from the theory (DBSP) and applies to any engine in this family.

What a streaming CREATE MATERIALIZED VIEW actually promises

Start with what this is not. In Postgres, a materialized view is a cached query result. It goes stale the moment a base table changes, and REFRESH MATERIALIZED VIEW recomputes the whole thing from scratch. The view is a snapshot you must remember to retake.

A streaming database inverts that. The view definition becomes a standing dataflow graph — sources at the top, stateful operators in the middle, the view's storage at the bottom. Each event flows through the graph and touches only the rows it affects. The view is never refreshed because it is never stale by more than a bounded interval.

The interesting part is the consistency contract, and here the details matter more than the marketing. RisingWave injects barriers into every source stream — by default every 1 second (barrier_interval_ms = 1000, with a checkpoint on every barrier). All operators apply the changes between two barriers atomically, Chandy-Lamport style. Two consequences fall out of that design, and I verified both against the architecture docs:

First, a read never observes a half-applied event. If one order insert increments a per-customer sum and a per-region count in the same view, no query sees the sum updated but the count not.

Second, views commit at the same epoch. Two materialized views derived from the same table advance together, so a query joining them cannot catch one view ahead of the other.

That second property sounds abstract until you see what its absence does. In a 2021 experiment, Jamie Brandon ran a bank-ledger workload — 10 million transfers between 10 accounts, so the sum of all balances must always be zero — through several streaming systems and watched the intermediate outputs. Systems that emit per-event refinements without synchronizing their internal streams produced what he called "outputs that are completely impossible for any set of inputs". In one Flink Table API run, the total emitted roughly 38 million updates, and 13,325 of them were the correct value zero. A quarter of all outputs were a single arbitrary wrong number. The differential-dataflow implementation emitted exactly one output: zero, once, at the first timestamp. That property — every output is a correct answer for some consistent prefix of the input — is called internal consistency, and it is the whole reason barrier-aligned engines quantize their output the way they do. (The experiment predates several Flink improvements, but the taxonomy of failure modes is unchanged; Materialize, from the differential-dataflow lineage, sells the same property today under its strict-serializable mode.)

The price is written into the same mechanism: freshness is quantized to the barrier interval. You read a consistent state that is up to about one second old, not a per-event live wire. For a read path, I consider that the correct default. A dashboard that is one second stale is fine. A dashboard that briefly invents money is not.

The cost model: linear, bilinear, holistic

"Incremental" is doing a lot of work in the phrase incremental view maintenance, and the DBSP paper (VLDB 2023) is the cleanest account I found of what it costs. DBSP proves that any query built from relational operators can be mechanically rewritten into a program over deltas. The rewrite always exists. What varies — enormously — is the state each operator must keep to apply the next delta.

Three tiers cover the SQL I actually write:

• Linear operators — WHERE, projections, UNION ALL, per-row transforms. A delta in produces a delta out. No state at all. These are free, in the strict sense: cost proportional to the change, independent of history.
• Decomposable aggregates — SUM, COUNT, AVG via sum/count. One accumulator per group. An update touches one row of state. MIN and MAX sit in a trap door of this tier: on an insert-only stream one value per group suffices, but the moment deletes can arrive, removing the current minimum requires knowing the runner-up — so the operator must retain every value in the group.
• Bilinear operators — joins. The incremental form of A JOIN B is ΔA ⋈ B + A ⋈ ΔB + ΔA ⋈ ΔB: to process a change on either side, the operator needs the accumulated other side. Both inputs are retained, indefinitely, unless something bounds them.

The join tier is where "just write SQL" quietly becomes a capacity-planning exercise. A three-way stream join materializes intermediate state for each pairing; every arriving row is both a probe into the other sides' state and a permanent addition to its own. Nothing in the SQL surface warns you. The view definition that reads like a report query is, operationally, a promise to remember two unbounded streams forever.

Here is where the reader should picture one update rippling through the graph — the diagram below contrasts a delta touching only its matching join rows against the full-table rescan a batch refresh performs.

​

Two things keep the bill payable in practice. The first is time bounds: an interval join (ON a.ts BETWEEN b.ts - INTERVAL '5' MINUTE AND b.ts) plus a watermark lets the engine discard rows that can no longer match, converting unbounded state into a sliding window of it — RisingWave's streaming-joins writeup is explicit that this is the only join shape with predictable memory. The second is where the state lives. RisingWave keeps operator state in Hummock, an LSM tree backed by object storage with an LRU cache in memory and on local disk. An oversized join state degrades into cache misses and S3 latency instead of an OOM kill. That is a better failure mode, and also a subtler one: the symptom of an unbounded join is a barrier latency graph that slowly climbs, not a crash.

My rule from this study: before a view goes on the read path, count its join inputs and name the bound on each side — a time interval, a small dimension table, or an explicit decision that the state is worth its growth rate. If no bound exists, that is the design conversation, not the SQL.

Subscribing from Node

A view you can only poll is half a read model. The part I did not expect RisingWave to get this right is the subscription: the engine exposes each view's change stream over the plain Postgres wire protocol, so a service can consume inserts, updates, and deletes as they commit — no Kafka sink, no Debezium, no extra moving part. The subscription docs cover the SQL; the semantics that matter for a consumer took me some digging:

• An UPDATE to a view row arrives as two records sharing one rw_timestamp: an UpdateDelete carrying the old row, then an UpdateInsert carrying the new one. The op column encodes these — over a driver you may receive the numeric enum from the engine's data.proto (Insert=1, Delete=2, UpdateInsert=3, UpdateDelete=4) where psql renders names.
• rw_timestamp (Unix milliseconds) is the progress token. Persist it, and a restarted consumer resumes with SINCE <ts> — no loss, no duplicates — provided the timestamp is still inside the subscription's retention window.
• Ordering is guaranteed across different timestamps, and within one timestamp only per primary key. Cross-key interleaving inside a barrier is unspecified.
• Since v2.1 a fetch can block server-side with a timeout, so the consumer loop needs no sleep-and-poll.

One setup script and one self-contained consumer reproduce everything above. The SQL, via psql -h localhost -p 4566 -d dev -U root:

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL);

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, SUM(amount) AS total_spend, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

CREATE SUBSCRIPTION totals_sub FROM customer_totals WITH (retention = '1D');

CREATE TABLE IF NOT EXISTS sub_progress (
  sub_name VARCHAR PRIMARY KEY,
  progress BIGINT
) ON CONFLICT OVERWRITE;

And the consumer, one file:

// subscribe.ts — consume a RisingWave materialized view changefeed, with resume.
import { Client } from "pg";

const SUB = "totals_sub";
// Op enum from RisingWave's data.proto; psql shows names, drivers the numbers.
const DELETE_OPS = new Set(["2", "4", "Delete", "UpdateDelete"]);

// Local replica of the view, keyed by the view's primary key (customer_id).
const totals = new Map<number, { spend: string; orders: string }>();

async function main(): Promise<void> {
  const db = new Client({ host: "localhost", port: 4566, user: "root", database: "dev" });
  await db.connect();

  const saved = await db.query(
    "SELECT progress FROM sub_progress WHERE sub_name = $1", [SUB],
  );
  const since: string | undefined = saved.rows[0]?.progress;

  // FULL: current snapshot first, then deltas. SINCE <ts>: resume, exactly once.
  await db.query(
    since != null
      ? `DECLARE cur SUBSCRIPTION CURSOR FOR ${SUB} SINCE ${since}`
      : `DECLARE cur SUBSCRIPTION CURSOR FOR ${SUB} FULL`,
  );
  console.log(since != null ? `resuming since ${since}` : "consuming full snapshot");

  let unsaved = 0;
  for (;;) {
    // Blocks server-side until a change arrives or 5s passes (RisingWave >= 2.1).
    const res = await db.query("FETCH NEXT FROM cur WITH (timeout = '5s')");
    for (const row of res.rows) {
      const key = Number(row.customer_id);
      if (DELETE_OPS.has(String(row.op))) totals.delete(key);
      else totals.set(key, { spend: row.total_spend, orders: row.order_count });
      console.log(`op=${row.op} customer=${key} ->`, totals.get(key) ?? "gone");

      // Snapshot rows carry no rw_timestamp; only deltas advance progress.
      if (row.rw_timestamp != null && ++unsaved >= 10) {
        await db.query(
          "INSERT INTO sub_progress (sub_name, progress) VALUES ($1, $2)",
          [SUB, row.rw_timestamp],
        );
        await db.query("FLUSH"); // make the progress row durable before trusting it
        unsaved = 0;
      }
    }
  }
}

main().catch((err) => { console.error(err); process.exit(1); });

Run it with npm install pg tsx && npx tsx subscribe.ts.

Three lines deserve a walkthrough. The DELETE_OPS handling works because an update's delete-half always precedes its insert-half for the same key, so applying records in arrival order keeps the local map correct — that is exactly the per-key ordering the docs guarantee, and no stronger. The rw_timestamp != null guard matters because a FULL cursor first replays the existing snapshot, and snapshot rows carry no timestamp; checkpointing progress during the snapshot phase would write garbage. And the FLUSH after the progress upsert is what upgrades at-least-once to exactly-once across restarts: resume only from progress you made durable. Insert a few orders from psql, update one, kill the consumer mid-stream, restart it — the pairs arrive, the resume lands on the exact next event, and the local map converges to what SELECT * FROM customer_totals shows.

The queries that refuse

The DBSP result says every relational query has an incremental form — it does not say a cheap one, and engines draw the line differently. What I respect about the IVM family is that the line is drawn at CREATE time: the planner either accepts the query and maintains it, or rejects it outright. The failure is loud and early. The classic alternative — a cron job around REFRESH MATERIALIZED VIEW — fails quietly, as a freshness SLO that erodes with table size.

Where the line falls, from the docs and my own attempts:

• Holistic aggregates. percentile_cont, medians, anything needing the full sorted multiset — a delta tells you nothing without the rest of the data. These are the true non-starters for incremental maintenance; approximations (t-digest style sketches) are the standard escape hatch.
• Global ordering. An OVER window with an empty PARTITION BY is rejected in RisingWave — a global ranking is one serial partition, which would strangle the parallel dataflow. Relatedly, a bare ORDER BY in a view definition is accepted but not maintained; ordering is the reader's job.
• Cascade-prone rankings. RANK() inside a partition maintains incrementally, but one insert can shift every row below it — the delta is cheap to compute and expensive to emit.
• Set difference and correlated subqueries. EXCEPT and friends maintain, but a single input row can flip arbitrary output rows; the state and write amplification mirror a join's.
• Non-determinism. now() and random() have no coherent incremental meaning; engines either reject them or special-case them (RisingWave rewrites now()-based predicates into temporal filters that age rows out).

That list is also a decent proxy for when the hand-written job earns its complexity back. If the logic needs per-event side effects, business-rule timers, or CEP-style pattern matching over sequences, it is not a view — it is a program, and the DataStream API (or an actor, or a consumer with explicit state) is the honest tool. The 2021 rescue of Brandon's benchmark is instructive here: the internally consistent Flink solution came from a hand-built ProcessFunction, not the SQL layer. The low-level tools were always sufficient; the question was only ever who compiles the query — you or the engine.

What I keep from this

• Treat a streaming materialized view as a dataflow you are capacity-planning, not a query you are caching. The SQL is the easy part; the operator state is the bill.
• Before shipping a view, classify its operators: linear (free), decomposable aggregates (per-group state — beware MIN/MAX under deletes), joins (both sides retained — name the bound), holistic (refused).
• Prefer engines that reject the unmaintainable at CREATE time over schedulers that recompute quietly at 3 a.m.
• Consume views through the subscription cursor, persist rw_timestamp only after FLUSH, and never during the snapshot phase.
• Read barrier latency as your leading indicator: climbing barrier lag is what unbounded join state looks like from the outside.

Reach for incremental view maintenance when the read model is genuinely relational — aggregations, enrichment joins with bounded sides, filtered projections — and the freshness target is "about a second". Avoid it when the logic wants timers, per-event effects, or holistic statistics, or when no one can say what bounds the join state. That last sentence is the entire trade in one line: SQL is enough for streaming exactly when your state is enough for your SQL.

Sources: RisingWave subscription docs · RisingWave architecture · DBSP, VLDB 2023 · Internal consistency in streaming systems — Jamie Brandon · Understanding streaming joins in RisingWave​