Context
analytics_view_events is append-only and currently has no retention or rollup. At today's traffic this is fine — sub-MB/year per low-traffic install. The table starts to deserve attention somewhere between 10M and 100M rows.
Three escalating fixes for three escalating scales. Each one is independent; do them in order, only when the previous threshold is actually hit.
1. Retention (cheap, do first)
Drop rows older than a configurable horizon (default 365 days). The MCP read tools (analytics_top_subjects, analytics_top_countries, analytics_subject_engagement, analytics_zero_result_searches) all cap sinceDays at 365 anyway, so nothing older is reachable through the public API.
Implementation:
- New env var
MUNIN_ANALYTICS_RETENTION_DAYS (default 365, set to 0 to disable).
- Daily worker (or pg_cron job) running
DELETE FROM analytics_view_events WHERE created_at < now() - $1 || ' days'. Same shape for analytics_search_events.
- Add a one-liner to
skills/track-website-traffic.md under Operations.
Why now: it's an afternoon's work, and forgetting it is the actual failure mode — tables silently grow to TB-scale because nobody scoped them.
2. Daily rollups (when 30-day queries get slow)
When raw scans on WHERE created_at > now() - 30d start showing up in slow logs — usually around ~10M raw rows in the hot 30-day window.
Shape:
- New table
analytics_view_events_daily keyed on (org_id, day, subject_type, subject_id, country) with columns views int, sum_dwell_ms bigint, count_distinct_visitors int.
- Nightly worker rolls up yesterday's raw rows into one summary row per group.
- After ~90 days, raw rows get dropped (or moved to cold storage); daily survives long-term.
- MCP tools route by window:
sinceDays ≤ 90 → raw, > 90 → daily. Add a SCALE.md note explaining the seam.
Gotcha — distinct visitors don't compose across days. A visitor seen Mon + Wed counts as 1 raw, 2 daily. Either accept "unique-per-day" as the granularity, OR store a per-day HyperLogLog sketch (Postgres hll extension) that you can union across days for an approximate cross-day distinct. HLL is the better answer if you want "uniques over the quarter" from rolled-up data, but adds an extension dep — defer until requested.
3. Monthly partitioning (when row count crosses ~50M)
When the table is large enough that VACUUM, DELETE-by-retention, or index bloat starts to matter.
Shape:
- Convert
analytics_view_events (and _daily) to time-range partitioned tables, one partition per month.
- Replace the retention
DELETE with DROP TABLE of old partitions — instant, no index bloat.
- Generate next month's partition via a small worker run on month rollover.
What NOT to do
- Don't pre-build this now. No service hits these thresholds yet, and each layer has real engineering cost (worker + schema + query routing + backfill story). Optimizing for a non-problem.
- Don't introduce TimescaleDB. Continuous aggregates would be elegant, but the Postgres-only path above gets us 95% of the way without a runtime dep that complicates self-hosting.
- Don't drop raw rows without rollups in place. The MCP tools'
sinceDays = 365 ceiling is a property of the tools, not the data — direct-SQL queries against the raw table still benefit from having a year of detail.
Acceptance criteria for stage 1 (retention only)
Context
analytics_view_eventsis append-only and currently has no retention or rollup. At today's traffic this is fine — sub-MB/year per low-traffic install. The table starts to deserve attention somewhere between 10M and 100M rows.Three escalating fixes for three escalating scales. Each one is independent; do them in order, only when the previous threshold is actually hit.
1. Retention (cheap, do first)
Drop rows older than a configurable horizon (default 365 days). The MCP read tools (
analytics_top_subjects,analytics_top_countries,analytics_subject_engagement,analytics_zero_result_searches) all capsinceDaysat 365 anyway, so nothing older is reachable through the public API.Implementation:
MUNIN_ANALYTICS_RETENTION_DAYS(default365, set to0to disable).DELETE FROM analytics_view_events WHERE created_at < now() - $1 || ' days'. Same shape foranalytics_search_events.skills/track-website-traffic.mdunder Operations.Why now: it's an afternoon's work, and forgetting it is the actual failure mode — tables silently grow to TB-scale because nobody scoped them.
2. Daily rollups (when 30-day queries get slow)
When raw scans on
WHERE created_at > now() - 30dstart showing up in slow logs — usually around ~10M raw rows in the hot 30-day window.Shape:
analytics_view_events_dailykeyed on(org_id, day, subject_type, subject_id, country)with columnsviews int,sum_dwell_ms bigint,count_distinct_visitors int.sinceDays ≤ 90→ raw,> 90→ daily. Add aSCALE.mdnote explaining the seam.Gotcha — distinct visitors don't compose across days. A visitor seen Mon + Wed counts as 1 raw, 2 daily. Either accept "unique-per-day" as the granularity, OR store a per-day HyperLogLog sketch (Postgres
hllextension) that you can union across days for an approximate cross-day distinct. HLL is the better answer if you want "uniques over the quarter" from rolled-up data, but adds an extension dep — defer until requested.3. Monthly partitioning (when row count crosses ~50M)
When the table is large enough that
VACUUM,DELETE-by-retention, or index bloat starts to matter.Shape:
analytics_view_events(and_daily) to time-range partitioned tables, one partition per month.DELETEwithDROP TABLEof old partitions — instant, no index bloat.What NOT to do
sinceDays = 365ceiling is a property of the tools, not the data — direct-SQL queries against the raw table still benefit from having a year of detail.Acceptance criteria for stage 1 (retention only)
MUNIN_ANALYTICS_RETENTION_DAYSenv var read at backend startup, validated as a non-negative integer.pg_cron) deletes rows inanalytics_view_eventsandanalytics_search_eventsolder than the horizon, running once per day.0(or unset for backward compat — pick one and document).skills/track-website-traffic.mdmentions the default and how to override.