Expire Everything, Rebuild Everything

The dedicated aggregation table bought V3 a clean expiry. This post is the function that spends it: the rewritten CreateAggregatedFacts, which expires every aggregate and rebuilds all of them from scratch on each run. It is the bluntest correct thing I could write, and the trade it represents — total rebuild versus incremental update — is one KC Star kept relitigating right up to V8's dependency engine. V3 is where I chose blunt, on purpose, and it is worth being clear about why.

The rewrite

Here is the V3 version, targeting SalesAggregationFact instead of SalesFact:

CREATE OR REPLACE FUNCTION CreateAggregatedFacts()
RETURNS VOID AS $$
BEGIN
    -- 1. expire ALL current aggregates, unconditionally
    UPDATE SalesAggregationFact
    SET IsCurrent = FALSE, ValidTo = CURRENT_TIMESTAMP
    WHERE IsCurrent = TRUE;

    -- 2. rebuild per-SaleType aggregates from current base facts
    WITH BaseFacts AS (
        SELECT sf.FactValue AS TotalSale, sf.SaleType
        FROM SalesFact sf
        WHERE sf.MeasureTypeId = 1 AND sf.IsCurrent = TRUE
    )
    INSERT INTO SalesAggregationFact
        (SaleType, MeasureTypeId, FactValue, FactCount, ValidFrom, IsCurrent, VersionHash)
    SELECT SaleType, 5, SUM(TotalSale), COUNT(*), CURRENT_TIMESTAMP, TRUE, digest(...)
    FROM BaseFacts GROUP BY SaleType
    UNION ALL
    SELECT SaleType, 6, AVG(TotalSale), COUNT(*), CURRENT_TIMESTAMP, TRUE, digest(...)
    FROM BaseFacts GROUP BY SaleType;

    -- 3. rebuild overall aggregates (measures 7, 8) the same way, no GROUP BY
END;
$$ LANGUAGE plpgsql;

Step 1 is the whole point. WHERE IsCurrent = TRUE — nothing else. It expires every current aggregate row, per-SaleType and overall alike, because in this table every row is an aggregate. Compare that to V2's leaky WHERE SaleType IS NULL, which could only reach the overall aggregates. The dedicated table made the correct clause the simplest clause. There is no way to under-expire here, because there is no discriminating predicate to get wrong.

Two other simplifications rode along. BaseFacts reads SaleType directly from SalesFact — no join back to SalesDim, because SaleType is a column and, in V3, a NOT NULL one. And there is no BaseFactId to compute, because aggregates no longer point back into the base table. The function got shorter and correct, which is the tell that the underlying structure finally matched the problem.

Blunt on purpose

Step 2 does not update aggregates. It throws them all away and computes every one from scratch, every time CreateSalesFacts runs — which is every time any dimension of any sale changes. Change the discount on a single sale and the function recomputes SaleTypeTotal, SaleTypeAverage, OverallTotal and OverallAverage for the entire warehouse, not just the sale type that moved.

That is obviously more work than necessary. A smarter function would notice that editing an 'Online' sale only affects 'Online' aggregates and the overalls, and leave the 'Store' rollups untouched. I did not write the smarter function in V3, and the choice was deliberate for three reasons.

Correctness first. V3's entire mandate was to stop the aggregate leak. The most defensible way to guarantee no stale aggregate survives is to expire all of them and rebuild all of them — there is no partial-update edge case to reason about because there are no partial updates. When you are fixing a correctness bug, a blunt correct instrument beats a clever one you have to trust.

The data is small. V3 targets small-to-medium datasets. Recomputing every aggregate over a few thousand base facts is milliseconds. The wasteful work is real but cheap, and paying it buys a function I can prove correct by inspection.

Incremental is a whole project. Doing it properly — “recompute only the aggregates a given change actually affects” — requires knowing which measures depend on which inputs. That is a dependency graph, and building one is not a tweak to V3; it is the entire premise of V8. I would rather ship blunt-and-correct now and earn the right to be clever five versions later, with a real dependency map behind it, than smuggle a half-formed optimisation into a bug-fix release.

The trade, named

The tension between these two poles — rebuild everything for simplicity, or update selectively for speed — is one of the load-bearing arguments of the whole project, so it is worth stating plainly.

Rebuild-everything is simple, obviously correct, and stateless: the output depends only on the current base facts, never on what the aggregates were before. It cannot drift, because it keeps nothing. Its cost is doing O(all data) work for an O(one change) event, which is fine until the data is big.

Selective recalculation does work proportional to the change, which is the only thing that scales. But it needs to know the blast radius of a change, which means dependency tracking, which means state that can itself be wrong. Every gain in efficiency is paid for in machinery that has to be maintained and can fail — as V8's honest audit eventually admits, its “selective” batch path quietly recomputes most measures anyway.

V3 sits firmly at the blunt end, and it should. It is a small, correctness-focused version, and rebuild-everything is exactly the right tool for that job. The interesting thing is that the project spent the next five versions inching toward the other pole and never fully arrived — a good reminder that the simple thing is often not the naive thing.

The two-table warehouse V3 produced is more capable than any before it, and the usage examples — the largest set in the early versions — are where that capability shows. Twenty queries against a dual star, next.