How Should You Store Health Time-Series Data?

A user's March workout gets rewritten in July. Not by them — by the platform. Apple documents that HealthKit condenses the samples attached to older workouts into series objects and that, once it has done so, "HealthKit deletes the originals." It applies to workouts "at least a few months old," it covers heartRate, activeEnergyBurned, basalEnergyBurned, distanceWalkingRunning and distanceCycling among others, and Apple adds that HealthKit "may condense samples for a given workout more than once ... if the condenser algorithm changes."

So four months after that workout synced cleanly, your pipeline receives a burst of deletions plus a set of new samples carrying March dates. If March 14's calorie total in your database is a counter you incremented at ingest, nothing in your system will ever repair it. It will not throw. It will not alert. It will just be a wrong number on a real person's history, forever.

That is the constraint that should drive your storage design, and it is the one that makes health data different from every append-only time series you have stored before. Store raw samples immutably, and make every user-facing aggregate a recomputable function of them rather than a counter you maintain. Do not increment; recompute the cells that got dirty.

Why this is not the append-only problem you already solved

Three properties, all specific to health data, break the usual time-series playbook:

Samples are late-arriving. A phone that was offline flushes a backlog on reconnect while live data keeps arriving, so out-of-order is the normal case, not the exception. Apple documents no rule tying a sample's startDate to the time it was saved — retro-dating is permitted right down to a platform floor exposed as earliestPermittedSampleDate().

Samples are retro-edited. Apple states plainly that "users can always modify their data outside your app," and that they can "view, add, delete, and manage their data" in the Health app. On top of user edits sit the platform's own rewrites described above. Health Connect models the same reality: insertRecords "has upsert capability" keyed on clientRecordId, so an existing record is overwritten in place when a higher version arrives.

Duplication silently doubles a real number. Apple's own words from WWDC20: "Both my Apple Watch and my iPhone are both recording steps. If we were to sum up all of my steps over all of my devices then we would easily be doublecounting a lot of those steps." Google's equivalent: only Activity and Sleep are deduped by Health Connect, and only through the Aggregate API — "For other types of data, the aggregated results combine all data of the type in Health Connect from all apps which wrote the data."

An order-events table has none of these. That is why the storage shape below is not the one you would pick for an event log.

The three storage shapes

The third shape is the one we would build, and the reason is narrow: it makes "is this number right?" answerable by recomputing one cell and comparing, rather than by auditing the history of a counter. With health data you will be asked that question, by a user, about a specific day, and "our pipeline is idempotent" is not an answer.

This is also exactly the shape Apple recommends for server sync. From WWDC20: "When the anchored object query updates you with changes in HealthKit, you can look at the dates of the returned samples and use that to create and run the statistics collection query. The statistics returned for those days can then be sent to the remote computer as updated graph data." The anchor tells you which days are dirty; a statistics query tells you what those days now total. Never sum the anchored query's samples into a running total yourself.

The natural key, and why it has five parts

For a health sample the natural key is (user, source, type, start, end). This is not our invention — it is Google's, stated in the Android 13 to 14 migration documentation for how records were deduplicated during that migration: "The data is deduped on clientRecordId if the record ID is provided by the client. If it isn't, time intervals (startTime and endTime for internal records, and time for instant records) are treated as key, along with the data type and package name of the app."

Read the fallback carefully: type, plus start and end, plus the writing app. The source is part of the key because two sources measuring the same interval are not duplicates — they are two honest measurements of the same reality, and collapsing them is as wrong as summing them. Which one wins is a per-metric policy decision, covered on deduplicating health data across sources; your schema's job is only to keep the information needed to make that decision later.

You also need the provider's own identifier, separately, and for one reason: deletions arrive as bare identifiers. HKDeletedObject exposes only uuid and metadata — no type, no dates, no value. Health Connect's DeletionChange "only contains the id of the deleted record, and not the record type, due to privacy," and Google's guidance is explicit: "If your app needs to read data from Health Connect, you must store the id." If you did not persist that identifier at insert time, an incoming deletion is unactionable. That is a schema decision made months before the first deletion arrives.

So: store both. The provider identifier resolves deletions. The natural key is your backstop when the identifier churns — and it does churn, because a condensed workout's original samples are deleted and new ones written, with new identity, for the same underlying reality.

Upserts: DO UPDATE, never DO NOTHING

Because providers re-notify the same day repeatedly as it fills in, INSERT ... ON CONFLICT DO NOTHING on a daily key is exactly as wrong as double-inserting: it pins the day to its first, partial, mid-morning value and never moves it. It must be DO UPDATE, gated on a monotonic version.

Both mobile platforms model the write this way, which is a strong hint it is the right shape. Health Connect: "If the version from the inserted data is higher than the version from the existing data, the upsert happens. Otherwise, the process ignores the change and the value remains the same," with the warning that upserts "don't automatically increment version whenever there are changes ... you have to manually supply it with a higher value." HealthKit: "If the new object has a greater sync version, the system replaces the old object with the new one."

Note the failure mode in the gate itself. A genuine correction shipped with a stale version is silently dropped. Version has to be monotonic and assigned by you, not inherited from whichever client happened to send the row.

The partitioning constraint nobody warns you about

If you partition your samples table by time, PostgreSQL's documentation imposes a rule that has a real design consequence here: "To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns."

So your uniqueness constraint on the provider identifier cannot be UNIQUE (provider, external_id). It must be UNIQUE (provider, external_id, start_time) — the partition key has to be in there. Which means the database cannot stop you from double-inserting the same provider sample if the provider changes its timestamp. A retro-edit that moves a workout from 23:50 to 00:10 crosses a partition boundary, satisfies the constraint, and lands as a second row that is a duplicate in every sense except the one the index checks.

There is no database-level fix. You need an application-level step: on ingest, if this external_id already exists at a different time, tombstone the old row before writing the new one. Budget for it, because the naive schema has this hole and it only shows up in production, on the days when a user edited something.

Heart rate does not roll up like steps

The primary reason people go looking for a time-series database is heart rate, because it is the metric that actually produces volume. It is also the metric where a single value column in your rollup table is wrong.

Steps are cumulative: the daily figure is a sum, and a sample that straddles midnight has to be attributed or split. Heart rate is discrete: the daily figures are minimum, average and maximum, and they are not derivable from each other. Apple enforces the distinction at the API level — "if you create a query using the discreteAverage option, you must access the results using the averageQuantity() method."

Two consequences for the schema. First, a rollup row for a discrete metric needs min, avg, max and n as separate columns, plus the duration basis the average was weighted by, because averaging unequal-duration samples unweighted gives you a number that is not the average of anything. Second, you cannot roll a week up from seven daily averages unless you kept the weights — which is another reason the rollup has to stay recomputable from raw rather than composable from other rollups.

Heart rate is also where HealthKit's series behaviour bites. Apple: "any samples associated with a workout may actually represent a series of higher-frequency data," detectable because "if a sample has a count greater than 1, it contains series data." Apple's guidance is to let statistics queries handle it — they "correctly handle quantity data, whether the samples represent a single quantity or a series." A pipeline keying on sample identity will see churn across condensing; a pipeline keying on daily statistics will not. Apple asserts that coalescing preserves statistics; it asserts nothing about sample counts, UUIDs or boundaries being preserved.

Engines, and what each one's dedupe actually promises

Two Timescale details worth knowing before you rely on continuous aggregates for health data.

refresh_continuous_aggregate() takes force => TRUE to "Force refresh every bucket in the time range ... even when the bucket has already been refreshed," with the documentation's own warning that this "can be very expensive." That is your repair tool after a provider rewrites last quarter, and for health data it is a routine operation rather than an emergency one. The same function's refresh_newest_first defaults to TRUE, which is the right ordering when a user is watching a repair job run.

And the caveat to read twice before you trust auto-repair: "it's not guaranteed that all buckets will be updated: refreshes will not take place when buckets are materialized with no data changes or with changes that only occurred in the secondary table used in the JOIN."

There is also a day-boundary trap. Timescale documents that "a continuous aggregate using the TIMESTAMP WITH TIME ZONE type aligns with the UTC time zone." A time_bucket('1 day', time) rollup is a UTC day. For a user in UTC+9 that daily step total is wrong by nine hours of steps, every day, for everyone. Bucket on a stored civil-date column instead — the reasoning is on timezones and day boundaries, and it is the single most consequential column in the schema below.

A schema sketch

Not production-ready; a starting point that encodes the decisions above.

CREATE TABLE health_sample (
  user_id          uuid        NOT NULL,
  provider         text        NOT NULL,  -- healthkit | health_connect | fitbit | ...
  source_id        text        NOT NULL,  -- bundle id / DataOrigin package / device id
  metric           text        NOT NULL,  -- YOUR name, never the vendor's
  start_time       timestamptz NOT NULL,
  end_time         timestamptz NOT NULL,
  utc_offset_s     integer,               -- offset in effect at start_time; NULL = unknown
  local_date       date        NOT NULL,  -- civil date, computed at ingest
  value            double precision NOT NULL,
  unit             text        NOT NULL,
  recording_method text        NOT NULL,  -- automatic | actively_recorded | manual_entry
  external_id      text,                  -- HKObject.uuid / Health Connect metadata.id
  external_version bigint,                -- sync version / clientRecordVersion, if given
  ingested_at      timestamptz NOT NULL DEFAULT now(),
  deleted_at       timestamptz,           -- tombstone; never DELETE a raw row
  PRIMARY KEY (user_id, provider, source_id, metric, start_time, end_time)
) PARTITION BY RANGE (start_time);

-- Must include the partition key. This is the hole described above:
-- it cannot catch a duplicate whose timestamp moved.
CREATE UNIQUE INDEX ON health_sample (provider, external_id, start_time)
  WHERE external_id IS NOT NULL;

Two columns there are carrying decisions made outside this page. metric holds your name rather than the vendor's, which presumes somebody has already ruled on what your names mean and which vendor fields may share one — the four layers of divergence is where that ruling happens, and it is upstream of every other column here. And recording_method earns its place because a typed value and a sensor reading need different plausibility envelopes and often a different dedupe rule; the flag cannot be reconstructed once an adapter has discarded it, and the two conflict strategies it selects between are what it exists to feed.

The rollup:

CREATE TABLE metric_daily (
  user_id        uuid NOT NULL,
  metric         text NOT NULL,
  local_date     date NOT NULL,
  source_id      text NOT NULL,          -- '*' = the merged, user-facing figure
  sum_value      double precision,       -- cumulative metrics
  min_value      double precision,       -- discrete metrics
  avg_value      double precision,
  max_value      double precision,
  weight_seconds double precision,       -- basis avg_value was weighted by
  sample_count   integer NOT NULL,
  metric_version integer NOT NULL,       -- which formula produced this row
  raw_watermark  timestamptz NOT NULL,   -- max(ingested_at) of the rows used
  computed_at    timestamptz NOT NULL,
  PRIMARY KEY (user_id, metric, local_date, source_id)
);

A NULL value means unknown, and it must never be coalesced to zero on any read path. Apple is unambiguous that this distinction is real: read authorization is designed so that denied reads are indistinguishable from no data, HKStatisticsCollection returns nil from sumQuantity() for an interval with no samples, and Apple's own instruction for limited authorization is to "Treat all data before that date as unknown — not an absence of data." Writing 0 because the query came back empty is a data-corruption bug that ships as a UI decision.

And the queue that drives everything:

CREATE TABLE dirty_day (
  user_id    uuid NOT NULL,
  metric     text NOT NULL,
  local_date date NOT NULL,
  reason     text NOT NULL,  -- ingest | deletion | metric_version | backfill
  priority   smallint NOT NULL DEFAULT 100,
  queued_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, metric, local_date)
);

The primary key is the point. Re-queueing is an upsert, so a storm of retro-edits across one day collapses into a single recompute rather than a thousand. Give backfill-origin rows a lower priority than live-ingest rows, so a two-year import cannot starve today's number.

Retention: you mostly cannot delete the raw data

The instinct once rollups exist is to expire raw samples on a rolling window. Resist it, and be honest about why.

Your rollup is only trustworthy because it is a pure function of raw rows for one cell. Delete the raw rows and the rollup stops being verifiable — you have converted a recomputable number into a number you simply have to believe. Worse, you have made metric versioning and recompute impossible for that period. When v2 of your sleep-efficiency or calorie formula ships, you cannot apply it to history you deleted. The recompute requirement is what sets your raw retention floor, and it is a product decision disguised as a storage one: how far back do you want a new metric definition to reach?

That collides directly with data minimisation, and we are not going to pretend it resolves cleanly. Two defensible positions:

• Keep raw for as long as your stated retention policy allows, accept that recompute stops at that boundary, and version-stamp the frozen rollups so a chart can honestly say which formula produced the older section.
• Keep raw longer and justify it in the retention policy as necessary to correct and recompute the user's own history.

Which one you can take is a policy question, not a schema question — see health data retention and deletion. What the schema owes you either way is the metric_version column, so the two eras of a chart are distinguishable rather than silently blended.

Two deletion traps specific to this design. First, PostgreSQL's fast-drop benefit applies to time-range drops, not per-user drops — and per-user erasure is the delete you will actually run. Partitioning by time does not help it at all. Second, deleting raw rows does not delete the rollups derived from them. A continuous aggregate is a separate materialized table holding that user's data in aggregated form, and per Timescale's own caveat a refresh is not guaranteed to touch every bucket. Deleting from the raw table and calling it done leaves the user's aggregates sitting there. The full inventory is on data deletion and export.

What we would actually build

One PostgreSQL database. health_sample, metric_daily, dirty_day, a recompute worker that claims dirty cells newest-first. No partitioning yet, no extension, no second engine.

That recommendation is more defensible than it sounds, and PostgreSQL's own documentation is the reason: "a rule of thumb is that the size of the table should exceed the physical memory of the database server" before partitioning is worthwhile. If you have not crossed that line, you have not earned partitioning — let alone a time-series database whose main advantage is over the partitioned version you have not built yet.

Do the arithmetic before you go shopping. Rows equals users times samples per user per day times days of history; multiply by your measured row width including index overhead. Measure your own row width — we have not benchmarked anything and any number we gave you would be invented. The point of the exercise is only to find out whether you are within an order of magnitude of the threshold PostgreSQL's own docs name. Most teams asking this question are not.

Then, in order:

1. Add time partitioning when the samples table exceeds server memory, and accept the unique-index consequence with an application-level tombstone step.
2. Add TimescaleDB when aggregation cost, not storage size, is the problem — and only after you have read the invalidation-threshold semantics, because a refresh policy that silently excludes the current bucket is a surprise you want to have on purpose. If you turn on continuous aggregates, bucket on your local_date column, keep refresh_continuous_aggregate(..., force => TRUE) in your runbook, and know that real-time aggregates exist for the current-bucket gap: "Real-time aggregates ... use the aggregated data and add the most recent raw data to it."
3. Add ClickHouse or similar only for analytics nobody will ever see on a home screen. Its eventual dedupe is a fine trade for cohort queries and a bad one for a user's step count.

The deeper argument for staying on PostgreSQL is that the hard part of health storage is not write throughput. It is identity and correction. Time-series databases are built for the append-only, high-cardinality workload of infrastructure monitoring; health data is low-volume per user, multi-source, and mutable. Reaching for one early buys you a solution to a problem you do not have, and pays for it with a weaker correction story — which is the exact thing this domain punishes.

What this design still gets wrong

Recompute is per-cell and full. That is cheap for a day and painful when a provider rewrites a year at once, which is precisely what platform re-condensing does. Rate-limit the recompute worker per user and expect a repair to take hours, not seconds.

The schema stores everything overlap resolution needs and decides none of it. Which source wins for a given metric, per user, is policy that lives in the recompute function — and Health Connect's aggregate figures are a function of a user-controlled priority list your backend cannot read at all ("Only end users can alter these priority lists"), so an aggregate-derived number is not reproducible by you.

The unknown-versus-zero discipline only works if every read path honours it. One COALESCE(sum_value, 0) in one endpoint and the NULLs stop meaning anything.

We have not benchmarked any of these engines, and there are no performance numbers on this page for that reason. The behaviours quoted are from each project's documentation.

What buying the ingest does not decide

For most teams, buying a health data aggregator is the right call and it frequently beats building, because the per-provider adapters it absorbs are months of work that never stops — start at what a health data aggregator is if the category is new to you.

What an aggregator does not do is decide your schema. Nobody sells you out of deciding what a "day" is for a user who flew to Tokyo, whether a rollup is recomputable, or how long you keep raw samples. Every point on this page still applies to a team that buys their ingest — they just get to skip the adapters.

We would build the ingest ourselves in three situations: you already have several direct integrations working and the marginal one is cheap; per-user aggregator pricing has overtaken engineering cost at your scale; or you need raw provider payloads that a normalised feed drops.

Where to go next

If you are still choosing the surrounding pieces rather than the tables inside them, fitness app tech stack picks the stack this schema sits inside. Everything else this schema depends on has already been linked where it was decided.