Admin stats materialized views are full recomputes and evict the OLTP working set

[Yuyz0112]

Problem

The admin dashboard materialized views defined in control-plane/migrations/001_init.sql
are refreshed on a cron by scheduler/src/admin-stats-refresh.ts. Every refresh is a
full recompute from scratch — there is no incremental path.

Three of them each embed the same subquery independently:

SELECT message_id, count(*) FROM session_events GROUP BY message_id

So a single refresh cycle aggregates the entire session_events table three times.
session_events is typically the largest table in the schema and grows without bound.

admin_daily_stats also computes active_workspaces as a range join of
generate_series(first_workspace_date, CURRENT_DATE, '1 day') against sessions
on a 7-day last_active_at window — cost grows with days × sessions, i.e. close to
quadratic as the deployment ages.

Impact

Observed on a long-running deployment:

view	buffer hit ratio	temp files written per refresh
admin_daily_stats	65%	tens of MB
admin_workspace_stats	43%	tens of MB
user_daily_interactions	59%	~10 MB
admin_token_daily_stats	28%	—

Each of the heavy views takes seconds to rebuild; a full cycle runs well over ten
seconds. The outputs are tiny — a few hundred rows, tens of kB each.

The wall-clock cost is not the real problem. Each pass reads far more than
shared_buffers holds, so it evicts the OLTP working set — ordinary queries go back
to disk right after every refresh. This showed up as periodic latency spikes across
the whole database, not just on the admin dashboard. The low buffer hit ratios in the
table above are the same effect seen from the refresh side.

Proposed direction

1. Maintain a shared message → event-count table incrementally (trigger on
   session_events, or a watermark-driven rollup). Removes three full aggregations
   of the largest table per cycle.
2. Roll up by day. Closed days never change, so recompute only today plus a short
   lookback for late arrivals.
3. Rewrite active_workspaces as a 7-day window over a per-day distinct-workspace
   rollup instead of the generate_series × sessions range join.
4. admin_workspace_stats has no date dimension (it groups all messages by
   workspace_id) and needs its own incremental design.
5. admin_token_daily_stats scans workspace_usage_events and gets the same
   per-day treatment.

Workaround in place

Lowering the refresh cadence (ADMIN_STATS_REFRESH_CRON, default */10 * * * *)
reduces the eviction duty cycle but does not bound the cost, which keeps growing
with the data.

[Yuyz0112]

Concrete design

Measured shape of the problem on a long-running deployment — inputs vs. outputs per
refresh cycle:

base table	rows	heap	feeds
session_events	~2.7M	~1.8 GB	admin_workspace_stats, admin_daily_stats, user_daily_interactions
messages	~220K	~150 MB	same three
workspace_usage_events	~1.3M	~320 MB	admin_token_{user,workspace,daily}_stats

matview	rows	size
admin_token_workspace_stats	~950	96 kB
admin_workspace_stats	~880	56 kB
admin_token_user_stats	~220	40 kB
admin_daily_stats	~170	16 kB
admin_token_daily_stats	~150	16 kB

Roughly 2.3 GB of heap read per cycle to produce ~200 kB — and the session_events
portion is read once per embedding view, not once per cycle.

Staged plan, ordered by value/risk. Step 1 is independently shippable; steps 2–4 form
one design.

1. Denormalize the event count onto messages

Add messages.event_count int not null default 0, maintained on session_events
insert (or by a watermark sweep). Every LEFT JOIN (SELECT message_id, count(*) FROM session_events GROUP BY message_id) disappears from all three views.

• Per-cycle scan drops from ~1950 MB to ~150 MB (~13x), and the duplicated
  aggregation of the largest table goes away with it.
• admin_workspace_stats becomes a single-table GROUP BY workspace_id over
  messages — close to free.
• No semantic change, so it can ship on its own.

This also unblocks the session_events retention work: once the count lives on
messages, deleting old events no longer changes any historical statistic.

2. Matviews become real tables, refreshed by watermark merge

workspace_usage_events is append-only with a monotonic bigint id and the token
views are purely additive aggregates, so a delta merge is exactly equivalent:

-- stats_watermark(view_name, last_id, refreshed_at)
INSERT INTO admin_token_user_stats (user_id, input_tokens, ...)
SELECT user_id, sum(input_tokens), ...
  FROM workspace_usage_events
 WHERE id > $last_id
 GROUP BY user_id
ON CONFLICT (user_id) DO UPDATE
  SET input_tokens = admin_token_user_stats.input_tokens + EXCLUDED.input_tokens, ...;

Cost goes from O(all history) to O(rows since the last tick). admin_workspace_stats
gets the same treatment keyed on messages.id, once step 1 has removed its join.

3. admin_daily_stats recomputes only a recent window

The view is keyed by a unique date. new_workspaces, new_sessions and
interactions are all derived from created_at, so a closed day never changes again;
only active_workspaces reaches backwards, and only by its 7-day window. So
DELETE WHERE date >= current_date - 7 plus reinserting that slice is sufficient —
historical rows are never rewritten, and the generate_series × sessions range join
is bounded to a fixed number of days instead of growing with deployment age.

4. Nightly full rebuild as a self-correcting baseline

The additive merge in steps 2–3 has one known gap: deleting sessions or messages makes
the counters drift upward, since a delta merge only ever adds. Rather than maintaining
delete triggers (more surface, easy to miss a path), run one full rebuild per day at a
low-traffic hour. Drift is then bounded to a single day, at a cost of once daily rather
than once per cron tick.

Considered and rejected: on-demand refresh with a TTL

Refreshing lazily on dashboard read would make the cost proportional to actual usage,
but it pushes an unpredictable multi-second delay onto whoever opens the page, and it
sidesteps the cost rather than bounding it. Keeping a fixed cadence is the right call —
and once the refresh is incremental, the cadence can go back up for fresher data
while total load stays far below what it is today.