Racing the specialist

Two of our own measurements pointed at the same hole. A study of 110 questions written by people who had never seen our operator list said the capability they wanted most was grouped aggregation. Our benchmark said ClickHouse beat us at aggregation by 8.7× at ten million events (it is 2.2× today, after later work on the scan). So we built the operator we were worst at, and raced the engine that was best at it.

We lost. The interesting part is what losing located.

Write the baseline before you write the operator

The rule we have used since the first baseline is that a query is not measured until its answer is verified — every system must produce the same canonical hash before anyone looks at a clock. For a brand-new operator we could take that one step further, so we did: the SQL and Cypher twins were written first, and the operator was raced against implementations that already existed. There is no version of this where the reference quietly grows to fit whatever we shipped.

The query is deliberately ordinary — count and distinct-recipient count per relationship type per time bucket, 196 groups over the whole window — because an unusual query is a way of not being compared. Four independent implementations across six systems agree on it: our operator — which has a fifth, a brute-force oracle, behind it in the test suite — ClickHouse SQL, PostgreSQL SQL, and one Cypher statement that runs unchanged on both Neo4j and Memgraph. Grouped aggregation is the one family Cypher states as directly as SQL, so the graph engines are reading here at their most natural rather than handicapped.

The result, which was a loss

Against the row stores it was never close: 47× faster than tuned PostgreSQL at a million events, on a query PostgreSQL answers with a plain GROUP BY. Against the specialist we were second, by 2.4× at ten million, and that is what this post said when it was published.

It is not what the numbers say now, and the reason is the whole point of what follows. Profiling that loss found the gap in one place, fixing that place moved the 10M query from 331 ms to 96.1 — and the same query ClickHouse answers in 138.0. So the honest current headline is: ahead at 200k, behind at a million by a margin barely outside the noise, ahead again at ten million. No clean crossover, and no story about one system simply being faster.

Where the remaining gap actually is

The obvious guess was that we lose because our scan is slower. We profiled it, and that is not what the time was made of. At ten million events the grouped query spent 38.8 ms selecting rows and the rest inside the aggregation itself — and about 232 ms of it in one aggregate, the exact count of distinct recipients per group.

So the two-phase design does work: per-thread partial aggregates over the parallel scan, group keys as fixed-width integer codes end to end — bucket index, relation code, dense endpoint id, never a string — merged deterministically so the answer is byte-identical whatever the thread count. What it does not do is make exact distinct counting cheap. Ours appends every endpoint id into a per-group list and sorts it once at the end; ClickHouse has spent years on that specific problem. That was the gap, and it was one aggregate rather than an architecture.

Which is a considerably better thing to find than a diffuse one, because it can be fixed. Endpoint identifiers are dense integers, so an exact distinct count does not need a sort at all — it needs a bitset and a population count, and the merge of two bitsets is an OR, which a partial-aggregate design already knows how to do. That aggregate now costs 9.6 ms instead of 240.7, and the query as a whole 96.1 ms instead of 331. Two things had been wrong rather than one: the representation, and the fact that the sort sat in the single serial stage of an otherwise parallel design.

One cell in that comparison is ours and it is bad, so it is published too: the portable fallback — the same operator running on our DuckDB backend — takes 36.5 seconds at ten million, three hundred and eighty times the native kernel, and it has not moved while the native path got 3.5× faster. It groups relationship types as ten million Python strings, which is precisely the dictionary-coding lesson the native path was careful to obey. Anyone using that backend for grouped aggregation is paying for it today.

The prediction we published, and missed

The study that ranked this operator first also carried a number: thirty questions were blocked by grouped aggregation alone. We printed it. It was the argument for building this and not something else. So the first thing the finished operator owed us was a re-audit of all 110 questions — against the new algebra, without touching the pre-registered table.

The seventeen that stayed blocked explain the miss. Eleven of them want a set or pair join — "pairs where A rated B and B rated A" groups by pair perfectly well and then has to match each pair against its transpose. Ten want ordered sequences inside a group: the longest gap between consecutive ratings, more than five ratings in any 24-hour period. Minimum and maximum give you a group's endpoints and never its interior, and a sliding window is not a bucket.

None of that was hidden — it was folded into a tag called "needs grouped aggregation" by readers who could see what was missing but not what sat underneath it, because the thing that would reveal the second layer did not exist yet. A capability tag assigned before the capability exists records the first blocker, and the first blocker is the shallowest one. The histogram still ranked the work correctly; nothing else on that list would have moved fourteen questions. But the number attached to it was an upper bound wearing a forecast's clothes, and the fix is cheap: make every tag name the operator call that would satisfy it, and the tags that cannot name one are the ones concealing a second capability.

Then we re-measured the old rows, and they had moved

Publishing a new row means re-running the table, and the table came back different: our bucketed-count query at a million events read 84 ms against a published 59.0 — same data, byte-identical answer. Chasing that number took two wrong explanations and ended somewhere none of us expected: an index built by an earlier query in the same process, which no scan reads and which costs every scan after it 18%. Because it also means the registry's query order has been quietly taxing our own aggregation numbers, it is its own note rather than a paragraph here — how a resident index slowed an unrelated scan by 18%, which also carries the between-day drift finding and the ±20% reproducibility bound that now sits on every table in this post.

What we would tell someone doing this

Three things carried, in the order we would repeat them. Write the baselines before the feature, so the reference cannot grow to fit what you shipped. Benchmark against the strongest specialist you can find, because second place against ClickHouse told us more about where we stand than any win over a general-purpose system would have. And profile the gap before you explain it — our first explanation of this one was wrong, it was confident, and it survived a single day of measurement.

For a reader deciding whether this matters to them: TGMS answers grouped aggregation over bi-temporal event data in one verified operation, at broadly the same cost as a dedicated column store — ahead at small and large scale on this workload, behind in the middle, none of the margins large. If aggregation over a flat event table is the whole job, the column store is still the simpler answer. If that aggregation has to be composed with history and graph structure, doing it in one system that can also answer what you believed last March is worth more than either margin.

What this result does not show

• It does not show TGMS is faster at aggregation in general — nor, in its earlier form, that we were slower. One query shape was raced, on one synthetic workload, at three scales, and the ordering is not monotonic in scale. The 1M gap and the 10M lead are both about 1.4×, which is outside this run's noise but not outside a between-day reproducibility band applied to both sides. Group count, key cardinality and how much of the window a query touches would all move it.
• It does not show our two-phase design is the reason we lose. The design carries the grouping at no measurable cost; one aggregate — exact distinct counting — is where the remaining time goes, and that is an implementation we have not optimised rather than an architecture that cannot.
• The coverage re-audit judges whether a question can be expressed, not whether a model answers it correctly. Those are different measurements.
• The 18% residency tax was measured on one host with one working-set size. It is a real effect with a mechanism, but the magnitude is not a constant.