Incremental View Maintenance Small Enough to Embed Turns Dashboard Queries Into Lookups
A dashboard tile shows today’s US traffic, so every page load runs SELECT count(*) FROM events WHERE country = 'US' over a table that grew by a few hundred thousand rows since yesterday. SQLite answers correctly. It also walks every matching index entry to do it, because its b-trees don’t keep counts, and it does the same walk for the next viewer and the one after. Between two page loads the answer moves by a handful of rows, yet each load pays for all of them.
Incremental view maintenance (IVM) is the textbook fix: keep the result stored and update it from each change instead of recomputing it. The version worth building is small. It takes a short list of watched queries, compiles each into plain triggers that update a summary table inside the writer’s transaction, and turns the read into a lookup. It refuses any query it can’t maintain correctly, and says why.
The serious engines solve the general problem. Materialize, built on differential dataflow, and RisingWave maintain streaming SQL results as a service. Feldera builds on DBSP, a formal model of incremental computation. Noria was a research system that kept query results as incrementally maintained state to serve reads, and ReadySet is its commercial descendant. On Postgres, pg_ivm maintains a supported subset of queries as an extension, and Oracle has long offered fast-refresh materialized views driven by logs of base-table changes. If you run Postgres and need a wide set of queries, start there. A small embeddable version competes in a narrower place: an app on SQLite that can’t justify another service.
It stays small by sticking to what’s common and easy: counts, sums, averages (as a sum and a count), min and max, GROUP BY, WHERE filters and joins on keys, all maintained in the same transaction as the write.
The Math Is Smaller Than the Theory
For an aggregate you apply a delta instead of recomputing. An insert adds one to a count and its value to a sum. A delete is a negative delta: subtract the row’s contribution. An update is a delete of the old row followed by an insert of the new one, and if the update changes the group key, the two land in different groups. Min and max work from inserts alone, since min(old_min, new) is all it takes. But deleting the current minimum leaves you needing the next smallest value, which means an index on the group and the value, a stored multiset of values, or a recompute of that one group.
Joins follow one rule. When both inputs change, the change to R ⋈ S is ΔR ⋈ S + R ⋈ ΔS + ΔR ⋈ ΔS: the new rows of R joined to S, plus R joined to the new rows of S, plus the new rows joined to each other. Row-level triggers give you the third term for free. They fire one row at a time, and each firing joins its changed row against the current state of the other table, which already includes everything earlier in the transaction. Deletes use the same joins with the sign flipped.
Compiling a Watch Into Triggers
watch() registers a query. The compiler parses it, checks that it falls inside the supported subset, and generates three things: a summary table keyed by the GROUP BY columns, insert, update and delete triggers on each source table, and a backfill that fills the table from existing rows. Reading a result becomes a primary key lookup, or a scan of the summary table when you want every group, which grows with the number of groups instead of the number of rows.
db.watch("by_country", """
SELECT country, count(*) AS hits, sum(ms) AS total_ms, max(ms) AS worst_ms
FROM requests WHERE status < 500 GROUP BY country""")
This is what the compiler would emit for the insert side:
CREATE TABLE watch_by_country (
country TEXT PRIMARY KEY,
hits INTEGER NOT NULL,
total_ms INTEGER NOT NULL,
worst_ms INTEGER
);
CREATE TRIGGER watch_by_country_ai AFTER INSERT ON requests
WHEN NEW.status < 500
BEGIN
INSERT INTO watch_by_country (country, hits, total_ms, worst_ms)
VALUES (NEW.country, 1, NEW.ms, NEW.ms)
ON CONFLICT (country) DO UPDATE SET
hits = hits + 1,
total_ms = total_ms + NEW.ms,
worst_ms = max(worst_ms, NEW.ms);
END;
The example assumes ms is declared NOT NULL. For a nullable column the compiler has to wrap each delta in coalesce, because sum() skips NULLs and plain addition doesn’t.
Because the triggers are plain SQL using built-in functions only, any program that opens the file keeps the summaries current, whatever language it’s written in. The logic travels with the data. A trigger that called an application-defined function would fail in a connection that doesn’t have it, so the compiler must never emit one.
Precomputing is one shipped example of this approach. You write a short policy naming the answers you want, it compiles to plain SQLite triggers so every INSERT keeps those answers current, and reading one is a single lookup. A Go engine runs the same policy and writes the same file. It’s version 0.1, and its SQL demo runs a compiled policy against simulated API traffic in SQLite’s WebAssembly build in the browser.
Where the Cost Shows Up
Every watch is a bet that reads outnumber writes. Each insert now runs one upsert per watching view, so ten watched queries mean ten extra writes per row, plus the indexes that min and max need. A hot group, say the country that sends most of your traffic, turns one summary row into the busiest row in the file; on a multi-writer database you’d stripe it across several slots and sum them on read. Bulk loads need a pause switch, because a million row-level trigger firings usually cost much more than one GROUP BY at the end. pause then resume should recompute from scratch.
Then the queries to refuse. Exact DISTINCT needs a counter for every distinct value, which can be nearly as big as the data. Window functions depend on neighboring rows. Outer joins aren’t monotonic: inserting the first matching row on one side deletes the null-padded row from the result. Subqueries multiply all of this. Refuse them in v0.1, with a message that names the construct and the reason and suggests an alternative, such as an approximate distinct count. Clear refusals are part of the product, because people’s first attempts will include queries it can’t maintain.
SQLite adds its own traps. INSERT OR REPLACE deletes the conflicting row without firing delete triggers unless recursive triggers are enabled, so the compiler has to turn that on or reject REPLACE. Backfill is the other one. Computing the initial result in a single statement holds the write lock for as long as the table scan takes. A gentler way: create the triggers first, but let them apply only to rows at or below a rowid watermark, then fill the summary in rowid chunks, raising the watermark in the same transaction as each chunk. Rows above the watermark get counted when the backfill reaches them, and rows below it are covered by the triggers.
What v0.1 Does
Version 0.1 takes single-table and key-join queries with count, sum, average, min, max, GROUP BY and WHERE, and maintains them inside the writer’s transaction. It ships an explain that lists the triggers and indexes each watch created, so nobody is surprised by the write cost. Everything else gets a refusal. A query budget in CI is a good way to find which queries deserve a watch, because the ones that blow the scan limit are the candidates.
Two neighbors cover the other directions. Group by a time bucket and the summary table is a rollup, which is where telemetry rollups pick up, with old buckets folding into coarser ones. Keep named values on entities instead of answers to queries and you get derived values with dependency graphs. The instinct also works one level up: make for APIs reruns only the steps downstream of what changed.
Answer the question once, when the row arrives.