Data aggregation level & Open Wearables usage at scale #489

I think it's very important topic when it comes to production deployments at scale.

Currenly in our staging we have 16 users, 17 active connections and 8 115 000 data points.

Tables datasize looks like this:

I've used this query to get this information:

SELECT
    relname AS tabela,
    pg_size_pretty(pg_relation_size(relid)) AS data,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes,
    pg_size_pretty(pg_total_relation_size(relid)) AS total
  FROM pg_catalog.pg_statio_user_tables
  ORDER BY pg_total_relation_size(relid) DESC;

By specific user & data source:

(the first one is me, data from 7 years collected mostly by Apple Watch)

When we look at number of records per type:

we see 10-12 types that makes this big numbers.

Below there is estimation of table sizes for 5k users:

Key takaeways:

• data_point_series dominates - it accounts for ~99% of total storage
• Indexes are larger than the data itself (1.56x ratio), driven by a composite unique constraint and a composite lookup index
• All other tables combined represent less than 3 GB
• PostgreSQL single-table limit is 32 TB - the projected 1.45 TB is well within bound

What should be discussed here?

1. Do we need per-sample granularity for all series types?

Series types like energy, basal_energy, distance_walking_running, and steps currently store individual samples (e.g. every few seconds/minutes). In most use cases, I believe daily aggregates would be sufficient for these metrics.

If we move to daily aggregates for these types, the number of rows could drop by orders of magnitude (e.g. from ~1440 samples/day to 1 row/day per type), significantly reducing storage and index pressure.

2. Where should aggregation happen — SDK or backend?

For SDKs we control (e.g. our mobile SDK), we have a choice:

Option A: Aggregate on the SDK side

• Pros: Less data transferred over the network, lower backend ingestion load
• Cons: Aggregation logic is duplicated across SDKs, harder to change retroactively, raw data is lost

Option B: Always send raw samples, aggregate on the backend

• Pros: Single source of truth for aggregation logic, raw data is preserved if granularity requirements change later
• Cons: Higher network and storage cost, heavier ingestion pipeline