Rollups and retention
Why detail is thrown away, what survives it, and how the seam is drawn honestly.
Detail is expensive and eventually useless; aggregates are cheap and permanently useful. The product keeps ninety days of the first and an unbounded amount of the second.
In plain terms
Keeping every individual lookup forever would fill the disk. So after ninety days the individual rows are deleted — but before they go, each hour is summarised and the summary is kept forever.
“Between 2pm and 3pm on this date the Kitchen iPad made 412 lookups, 88 of them blocked” survives. “Which domain, at 2:14:07” does not. So a chart of last year still draws; you just cannot click through to the individual queries behind it, and the interface says so rather than pretending otherwise.
detail rows · 14:00–15:00
- doubleclick.netHunter's iPhone
- samsungacr.comBedroom Samsung TV
- audio-fa.scdn.coKitchen Sonos
- teams.microsoft.comWork ThinkPad
- device-metrics-us.amazon.comFront door Ring
- googlevideo.comLiving room Apple TV
rollup_hourly_totals
437 blocked · 61 distinct domains
The five grains#
| Table | Key | Answers |
|---|---|---|
| rollup_hourly_totals | hour | The timeline, and every headline figure |
| rollup_hourly_device | hour, device | Per-device volume over long ranges |
| rollup_hourly_domain | hour, domain | Top-N domains. Capped per hour; see below |
| rollup_hourly_country | hour, country | Long-range destination totalsLong-range destination totals. Rolled up but always empty on Pi-hole, which reports no answer addresses |
| rollup_hourly_company | hour, company | Company rankings and tracker exposure |
Why the domain grain is capped#
Uncapped, it is the one grain that could outgrow the detail table it replaces: a long tail of once-queried names would each occupy a row forever. A domain queried once at 3am does not need a permanent record, so the grain keeps a top slice per hour and the tail is dropped.
That is a real limitation, stated rather than hidden: over ranges answered from rollups, the domain ranking is a top-N approximation. The totals, device, country and company grains are complete and sum exactly.
Idempotence#
The pass uses INSERT OR REPLACE rather than accumulating. Recomputing an hour from its detail rows is deterministic, so a pass that was interrupted and retried, or an hour revisited because a late entry arrived, converges on the correct value instead of double-counting.
What distinct counts cannot do#
Reads prefer rollups#
Not as a fallback, as the default. For any whole hour a rollup covers, the rollup is read even when the detail rows are still on disk, because summing twenty-two pre-aggregated rows beats aggregating eighty thousand raw ones. The partial hour at each end of a range comes from detail, and the two halves are exact against each other, which a verification script asserts against the live store.
The seam, drawn honestly#
Availability of detail and the source of a figure are two different facts. A bucket is marked on the timeline when its per-query rows have aged out, so a reader can tell a real dip from a compaction edge. A bucket that was answered from a rollup while its detail still exists is not marked, because nothing has been lost there.
The Overview states the floor of what can be charted at all. The demo you are reading this on has a genuine seam of exactly this kind; see the generated household.
Retention#
A daily sweep deletes detail older than RETENTION_DAYS. It never runs VACUUM: freed pages get reused by later inserts, so the file stops growing on its own at steady state, and rewriting a 400MB database to reclaim space that is about to be used again is gigabytes of pointless SD-card writes.
Dimension rows are deliberately left in place. They are small, and they carry first_seen, which the new-domain and new-device signals depend on; a domain dropped here would be reported as newly seen the next time it appeared.