Flow
What to store in a summing rollup
1Counts and totals
They add up correctly. Store them directly and sum at read time.
2Averages
They don't add up. Store the total and the count, then divide after grouping.
3Labels you slice by
Must be in the sort key, or merges fuse rows and keep one label.
If a summing table stores an average, every merge makes that average wrong. A few of the rollup tables behind my usage dashboards were storing an average request time that way.
My raw telemetry lives in one big log table in ClickHouse. A few views roll it up by hour or day into smaller tables, so the dashboards stay fast. Those smaller tables use a summing engine.
Why a stored average goes wrong
Each insert writes a small batch of rolled-up rows. Later, the database merges batches. Rows with the same key become one row, and every number in them gets added.
That's fine for counts and totals. It's wrong for an average. Say one batch has one request at 100 ms and another has three requests averaging 300 ms. After a merge the stored average says 400. The real one is 1000 over 4, which is 250. More batches push it further off, and how far depends on timing you don't control.
As far as I can see, nothing in my app read that column yet.
What the views store now
The averages are gone. The views keep the total time and the request count, and the average gets worked out at read time. A small shape for that, with made-up names:
SELECT bucket, sum(total_ms) / nullIf(sum(calls), 0) AS mean_ms
FROM hourly_rollup
GROUP BY bucket;
Sum first, then divide. Don't average the stored averages, because a batch with one request would count as much as a batch with a thousand.
Why the key needs every label
The key decides which rows get merged. One rollup was keyed on organization, user and hour, but it also stored team and service. After a merge, the same user on two services in one hour becomes one row with one service name. The totals still look right. The per-service numbers don't.
Another rollup didn't have the user in its key at all, so per-user counts would have been mixed together. Both keys now include every column the view keeps. The cost is more rows, and I'd guess it's a small cost here.
If an old view is already out there
These views are only created when they don't exist yet, so changing the file doesn't change a view that's already built. If one exists, the rebuild goes like this:
- create the corrected table and view
- backfill a closed time range from the raw logs
- compare counts, totals and averages against the raw logs, with a small tolerance
- point the dashboards at the new table, then drop the old one
When you compare, slice by service and by user, not just by hour. A merged label leaves the totals alone, so only the slices will show it.