Repository navigation
Raw-row retention: the on-device DB holds 17.2M raw rows and every re-score folds over all of it #1146
Description
Source: real WHOOP 4.0 capture noop-capture-260808-1527.txt + strap log (9.3.2-staging, 2026-08-08). Companion to #1144.
Strap log Volume: rawRows=17251067 — 17.2 million raw R-R/HR rows across 37 days. Every analyzeRecent pass (77 in 19.5h in this capture) folds over this, so re-score cost scales linearly with history — the dominant CPU/battery cost behind the frequent re-scoring, and it only grows.
Proposal (design-first — touches stored-data lifetime): a raw-sample retention window — keep N days at full resolution, downsample or archive older raw rows (the derived DailyMetric/HRV spine is already persisted, so old raw R-R is only needed for re-derivation). Needs a migration + a decision on what re-derivation still requires full-res raw. #1118 (R-R over-count) separately ~2× the row count, so the two compound.
Activity
ryanbr commentedon Aug 8, 2026
Alternative lever (from #1147, now closed): instead of shrinking the raw-row volume, the re-score cost can also be cut by shrinking which days each pass folds over. Today every analyzeRecent pass recomputes all ~21 days even when only today's HR changed (during a live session the HR fingerprint advances every second, so the whole-call watermark never short-circuits). Per-day fingerprints — re-score only days whose fingerprint changed — would target that waste directly. It's complementary to retention (retention shrinks each pass; per-day scoping skips passes). NOT the partial-day-window skip, which is the #789/#999 window-wide-delete data-loss trap (see reference_noop_analyzerecent_perf) — it must be a per-day gate, not a per-day delete.
ryanbr commentedon Aug 8, 2026
Design note — raw-sample retention
Mapped the data model. Summary: pruning old raw is safe for stored scores but has two real blockers (the 4000-day Effort rescore and the Deep Timeline), and there's a clean in-tree precedent to reuse.
What the 17.2M rows are
dbRowssums the decoded per-second/event tables:hrSample + rrInterval + event + spo2Sample + skinTempSample + stepSample + respSample + gravitySample(WhoopRepository.dataVolumeSnapshot:1404 / SwiftReads.swift:330).hrSample(1/s) +rrInterval(multi/s) dominate — in-tree note cites 746 MB for 3.1M hr rows, so ~17M rows ≈ a multi-GB store. #1118's crossSecondOverCount roughly doubles hr/rr, so #1118 + retention compound.What's safe, and the re-score reality
dailyMetric(MetricsCache.swift:386). Raw is only needed to re-derive, never to read a stored score — so pruning old raw loses no computed recovery/strain/sleep/HRV.analyzeRecentwindow is 21 days (maxDays=21,IntelligenceEngine.kt:144 /.swift:353). Raw older than that is not folded on a normal tick. A day needs ≥200 HR samples to score.🚧 The two blockers (must be resolved before any prune)
EFFORT_RESCORE_HISTORY_DAYS = 4000,IntelligenceEngine.kt:251) callsanalyzeRecent(maxDays = 4000)— it folds over all raw. This is literally the "every re-score folds over all of it" cost. Fix: cap it to the retention window (old days keep their last-computed Effort — which can't change without raw anyway), or persist a per-day intermediate it can re-derive from.FullDayChartScreen.kt:374hrSamplesUnion(…,200000), rr/spo2/skin/gravity). Pruning old raw means an old day's per-second zoom shows nothing below the daily aggregate. Fix: accept graceful degradation, or downsample old raw (1/s → 1/min) to keep a coarse timeline, or keep a longer "browse window" than the score window.Mechanism — reuse existing precedent
The 8 decoded tables have no retention today (deliberately:
Database.swift:541 "decoded biometric history … never pruned"). But two patterns already exist to copy:v18AuxSamplekeeps 604,800 rows (=~7 days @1Hz), swept every 10k rows (pruneV18Aux,WhoopDao.kt:107).rawImuSamplekeeps newest 3,600.DELETE FROM hrSample WHERE ts < :min OR ts > :maxon these exact tables (WhoopDao.kt:877). The closest model for a retention cutoff.Proposal (first cut)
Time-boxed keep-N-days-full-res + amortised oldest-first sweep, N ≈ 60–90 days (comfortably past the 21-day score window and a reasonable Deep-Timeline browse window). Reuse the v18Aux amortised-sweep shape (
DELETE … WHERE ts < cutoff, run every K committed rows so it's never a big delete). Gate behind: (1) cap the 4000-day Effort rescore to the window; (2) a one-line UI note that per-second detail is kept for N days. Downsampling older raw (option B) can come later if losing old Deep-Timeline detail is unacceptable.Cross-platform + migration constraints
schema_oracle.jsonunaffected. If a "pruned-through" watermark column is added, it goes in both platforms + re-pin both oracle copies.RawImuMigrationTest/DeepCaptureMigrationTestprecedent).ts < cutoffonly, never a window-interior delete (the diag: say WHY the sparse sleep bridge did nothing (#737) #789/SourceBadge: centre the label in the pill on both axes #999 data-loss trap).Relation to the other levers
Recommendation: land #1118 first (halves the growth, pure win, also fixes HRV), then this retention as a follow-up once the 4000-day Effort rescore is re-scoped. Happy to prototype the sweep + migration once the N-days value and the Deep-Timeline-degradation call are decided.
feat(diagnostics): attribute forced re-scores as newData yes/no (batt…
fix(ble): gate auto-continue on captured rows>0, not the looser class…
fix(ble): gate empty-offload auto-continue on the persisted-rows verd…
feat(diagnostics): attribute forced re-scores as newData yes/no (batt…
fix(ble): gate empty-offload auto-continue on the persisted-rows verd…
Cover the idle tick too, or the livelock survives through it
Fix missed sleep after partial history sync (#1734)
ryanbr commentedon Sep 7, 2026
Fresh measurements, a year of growth later, from two independent installs. The row count in the title has nearly doubled, and the cost is now measurable in minutes per capture rather than seconds.
Install A (mine, 2026-09-07, build 454/456)
Volume: rawRows=32040940— 32.0M, against the 17.2M this was opened with. 1.86x.Ten
analyzeRecentpasses in one capture:dayCache reused=20/21)reused=0/21)The window plan for the two shapes:
So a cache miss reads about 14x the rows and costs about 28x the time. Nothing is truncated, so this is the full fold the issue describes, not a clipped one.
Install B (a WHOOP 5.0 reporter's OnePlus, build 452, from #1937)
Worse, and the more useful of the two, because the cache never helped at all. Eight passes in a single capture, every one at cold scale:
That is 397 seconds — 6.6 minutes — of prep in one capture. Reads of 1.73M to 1.82M HR rows, serving 2.03M to 2.14M.
Its per-table counts come to 29.9M rows, with
gravity,hr,skinTempandstepseach around 4.9M andsleepStateat 5.1M.Two things the numbers make visible that the title does not
servedexceedsread. 1,801,737 served from 1,646,181 read on A; 2,139,992 from 1,816,244 on B. That gap is the overlapping-window re-serving, so the fold is not merely linear in history, it re-hands the overlap on top.The DB-size evidence saturates. Install B reports
db_bytes: 2147483647, exactly 2^31-1:TestBundleMeta.Storage.dbBytesis anIntandTestCentreScreenclamps withcoerceAtMost(Int.MAX_VALUE). Its row bytes alone total 1.20 GiB, so the real figure is past 2 GiB and cannot be read from any export. The one metric this issue is about stops growing in the data exactly when an install crosses the point worth measuring. Small and separable from the retention design, but worth knowing before anyone tries to size the problem from bundles.Why it is worth re-reading now
This came up while chasing a suspected battery drain that turned out not to be the widget. For scale, on install A the home-screen widget costs roughly 120 ms of CPU per hour; a single cold re-score costs 42 seconds. Whatever the retention design ends up being, this is where the time is going.
ryanbr commentedon Sep 28, 2026
Field update from a strap log taken today (11.8.0-staging build 546, WHOOP 5.0/MG active, Android 16).
rawRowsis now 36,779,406. This issue opened on 17,251,067 on 2026-08-08. That is 2.13x in seven weeks, and the day spine has not grown to match: still 37 day rows onmy-whoop. So the growth is raw sample accumulation, not more history being covered.I cannot decompose the 36.7M from this log alone. The latest night carries
hr=9694 rr=9746, about 19k rows, which does not multiply out to 36.7M over the spine's span, so most of it predates the current strap or sits in sample types the night funnel does not count. Worth naming rather than guessing at.A cheap instrument for the pass count
The same log gives an independent read on the re-score amplification this issue describes, without any new instrumentation.
hrv diag day=…is emitted once per day per analytics pass. In this log it appears 542 times across 19 unique days, averaging 28 emissions per day, and every repeat for a given day is BYTE-IDENTICAL. Identical output means identical inputs, so those 28 passes per day recomputed a value that had not changed, includingHrvAnalyzer.beatAccurateFractionover each day's R-R set (14k to 27k intervals on the larger days).That is a direct measurement of the same effect as this issue's "77 passes in 19.5h", taken from the other end: not how often the pass runs, but how many times each individual day inside the window is re-folded with nothing new to fold.
It is also a free regression check. If a retention window or a change-gate lands, this count should collapse toward 1 per day per genuine change, and it can be read off any strap log with a grep.
Not filed separately
The identical-repeat logging looked like its own defect at first, but it is a symptom of exactly what this issue tracks: the diag sits inside the per-day analytics loop, so it repeats precisely when the loop does. Fixing the re-score removes it. Recording it here rather than opening a duplicate.
Related: #2238 (re-score debt never settling), #2314 (the gravity witness walking 518k rows a pass), #2012 (re-scoring a night that is long finished).
ryanbr commentedon Sep 28, 2026
Correction to my comment above, and a better measurement from the same log.
I was wrong about the HRV diag. I said the 28 identical emissions per day meant 28 recomputations, "including
HrvAnalyzer.beatAccurateFractionover each day's R-R set". They do not. On a day-cache hitIntelligenceEnginereplays the stored lines and skips the work:That is deliberate, so a reused night is downstream-indistinguishable from a freshly-scored one in the export as well as in the numbers. The repeats are cheap string replays. I read repetition in the log and inferred cost without checking the hit path. The
rawRowsfigure in my previous comment stands; the cost claim beside it does not.What the same log actually shows. The day cache is working well:
Twenty of twenty-one nights reused, missing only the one day whose HR moved. And yet:
the completed-pass lines report 21 nights scored in 93438 ms and, on a later pass, 31958 ms.
The #1538 split says where that goes:
tPrep0starts after the cachecontinue, so those warm figures are ONE day: 4 to 7 seconds of store reads to prepare a single night, against 50 to 400 ms to score it.That settles the question #1538 was built to answer. Its own comment says the split "decides whether narrowing the read windows is worth building". On this device it is: prep outweighs score by 20x to 80x, and the day cache has already removed everything it can reach, so what remains is read cost on the days that genuinely changed. Narrowing the per-day windows (the 54-hour HR span plus the
limit: 200_000streams) is the remaining lever, not more caching.The
rawRows=36,779,406in my previous comment is the other half of the same story: prep reads scale with retained raw rows, which is what this issue proposes to bound.