Making analytics 480× faster with a pre-aggregated data model

Our analytics endpoints computed everything at request time. At launch, with a few thousand rows, that was invisible. Eighteen months in, a single trainer dashboard was scanning 40M workout records per page load — and p95 latency crossed four seconds.

The shape of the problem

Aggregating on read means cost grows with history. Aggregating on write means cost stays proportional to new data. The fix is conceptually simple — the hard part is migrating a live system to it.

CREATE TABLE workout_daily_agg (
  member_id   BIGINT      NOT NULL,
  bucket_date DATE        NOT NULL,
  total_reps  INT         NOT NULL DEFAULT 0,
  PRIMARY KEY (member_id, bucket_date)
);

Expand first, migrate readers one by one, contract last. Never make the database do two jobs in one deploy.

Designing the aggregate table

The bucket is one member-day. Every metric the dashboard shows — reps, sets, volume, session count — becomes a column that is incremented inside the same transaction that writes the raw workout row. Reads collapse from a 40M-row scan to an index range over at most 365 rows per member.

Migrating without downtime

We backfilled the aggregate table in date-ranged batches behind a feature flag, dual-wrote for a week while comparing results in CI, then flipped read endpoints over one by one. The raw table stayed untouched until every consumer was migrated.

Results

p95 for the trainer dashboard went from 4.1s to 8.5ms — roughly 480× — and the database CPU graph finally stopped tracking marketing campaigns. The aggregate table costs us about 90MB a year, which is the cheapest performance budget I have ever spent.