We didn't need continuous aggregates

Part 5 of an ongoing series on Moty, a fitness data platform I build and run solo in production: one multi-tenant API, two Next.js frontends. All numbers come from production measurements and git history.

The shape of the data

The platform is built around a digital weight machine that streams sensor samples (force, velocity, position, from both sides of the body) ten times a second, for every second of every set. A twenty-second set lands a few hundred rows in the raw time-series table; a long one lands a few thousand. In production today that table holds about 1.4 million raw samples against about 3.6 thousand workout sets.

That ratio is the whole problem. Every analytics screen the product sells (daily summaries, monthly reports, clinical-style rehabilitation trends) is a question about sets, but the naive way to answer it reads samples. Ask "how did this member's strength change this quarter" the naive way and the database scans a few hundred rows for every set the member ever did. The read cost grows with the one variable you control least, which is how much your users train.

Three layers between analytics and the firehose

The design that shipped puts three separate barriers between read paths and raw data.

Layer one: compute on ingest. When the tablet posts a finished set, the API reduces the entire raw stream to roughly 24 scalar columns on the set row: volume, average and peak velocity per side, force, range of motion, split by concentric and eccentric phase. The calculator is a pure function with no repository dependencies, which keeps it trivially testable. From that moment on, most questions about the set can be answered from one row.

Layer two: a per-rep envelope with pointers. Some UI needs more than set-level scalars, like a per-rep weight chart or a force-development curve. Those get a JSONB envelope on the same row: per-rep aggregates, plus start and end indexes into the raw series for each rep. The UI that wants rep-level detail reads the envelope; the rare UI that wants the actual waveform uses the indexes to cut exactly one slice of raw data, no scanning.

Layer three: read-path isolation. This one is a structural guarantee rather than an optimization. Every analytics query service in the codebase, all ten of them, reads only the set and session tables. The raw hypertable is touched by exactly two read paths, both single-set drill-down charts. A query that aggregates raw data across sets doesn't exist, so it can't regress.

What the time-series database actually does here

Honesty section. We run TimescaleDB, and if you skimmed the architecture you might assume its continuous aggregates power the analytics. They don't; there isn't a single continuous aggregate, materialized view, or time_bucket in the codebase.

The reason is that our aggregation isn't time-shaped. A "rep" is a domain concept: its boundaries are detected from the motion signal, and the metrics differ by workout mode. That logic lives in application code and can't be expressed as a windowed SQL aggregate. Once aggregation happens at write time in the app layer, what remains for TimescaleDB is what it's genuinely good at: partitioning an append-heavy raw table into time chunks, so bulk inserts stay cheap and the two drill-down paths read one chunk instead of many.

Use the extension for what it's for, and don't credit it for what it didn't do.

The measurement

Numbers from production, via EXPLAIN ANALYZE on real data, warm cache:

• Before-style query (aggregate a member's history from raw): the raw table has no member column (a design fact: raw belongs to sets), so the plan is a parallel sequential scan across 25 time chunks, about 800ms.
• After-style query (same question from pre-aggregated set rows): an index read touching 281 buffers, about 1.3ms.

That's roughly ~590× faster, on top of a ~380× row fanout collapsed at write time (1.4M samples → 3.6k rows). On my small local dataset the same comparison gives only ~75×; the gap grows with the data, which is exactly the property you want from a design meant to outlive its current scale.

The price, stated plainly

Write-time aggregation has a cost that query-time aggregation doesn't. The aggregates are effectively append-only. If I change how a metric is computed, I can't recompute history; reconstructing per-rep aggregates from raw index pointers turned out to be imprecise enough that we made non-recomputation an explicit policy. New metrics are added as new columns going forward; old rows keep the numbers computed by the code that wrote them.

It's a real constraint, and it changed how we treat the aggregate schema. The schema is a contract now, not an implementation detail. I'd still take the trade. The write path pays once, at set completion, on a background tablet request nobody is waiting on. The read path, the thing users actually feel, became a constant-cost index read.

What I'd pass on

Put aggregation where the domain logic lives. If your aggregate is genuinely a windowed function of time, the database's machinery is excellent. Ours was a function of what a rep is. The moment aggregation logic belongs to the domain, the application layer isn't a compromise, it's the correct home. The database's job is then to make the raw firehose cheap to ingest and cheap to slice, a job it does very well when nobody asks it to do more.