Measure capture-table storage after a convective season and revisit the retention windows #158

New issue

Open

opened 2026-07-28 15:39:34 +00:00 by claude-bot · 0 comments

claude-bot commented

2026-07-28 15:39:34 +00:00

Contributor

Split out of #133, which delivered the unified 13-month horizon but explicitly could not complete its "size the result at 13 months" task. Parent: #22.

Why this couldn't be done in #133

Production was measured on 2026-07-28 (full data on #133), a few hours after capture was ungated. The capture tables held essentially nothing:

Table	Rows at measurement	Window covered
`nws_alert_snapshots`	3	10.6 h
`alert_radar_frames`	31	3.8 h, n=2 events
`lightning_clusters`	0	archive deployed 14:27 UTC same day

Projecting 13 months from that would be arithmetic wearing a measurement's clothes — the same thing #130 and #132 were flagged for. Better to wait for real data.

What to do

Re-measure row counts, heap/index sizes and on-disk PNG bytes for nws_alert_snapshots, alert_radar_frames and lightning_clusters after a full convective season (revisit ~autumn 2026).
Post measured figures back to #130 (whose ~165 MB/location/year estimate now reads 73–113 MB/location/year on early data) and #132 (whose ~25–50 MB/year/region is still entirely unvalidated).
Confirm or change RETENTION_LIGHTNING_CLUSTERS_DAYS. It is pinned at 90 days on a reasoned but unmeasured argument — lightning being the highest-volume, fastest-decaying history. If measurement contradicts that, fold it into the horizon or pin it somewhere else deliberately.
Re-check frames per event against the alert_radar_frame_max_per_event = 24 cap. The current 15.5 average rests on n=2; the event rate (1.82/day/location) is solid, that figure is not.
Decide whether the horizon's storage cost warrants the B2/S3 offload that #131's storage abstraction was built to enable.

Watch items carried over

sent_alerts bloat. Measured 1,023 kB of live data sitting in a 21 MB heap — roughly 20× bloat from UPDATE churn (cleared_at, escalation_count, digest_pending, acknowledged_at), with autovacuum last run 6 days prior. #133 raised this table's window from 90 to 400 days, so it now stops pruning entirely for ~10 months while accruing ~9.1 rows/day. Worth confirming autovacuum keeps pace, and a one-off VACUUM FULL would reclaim ~20 MB today.

sent_alerts.radar_snapshot_path is NULL on all 1,708 rows while alert_radar_frames has 31. Most likely benign — both frame-bearing alerts were already active when capture ungated, so the first-fire moment had passed. But if it is still NULL after a fresh alert fires post-deploy, that is a #85 regression and deserves its own issue.

product_freshness_samples index cost. 63 MB total, and its indexes cost as much as its data (31 MB vs 32 MB) — five indexes on a 164-byte row, 78% of it spc_fetch. It is observability rather than history and correctly stays outside the horizon at 14 days, but it is the single largest table in the database and the index ratio looks worth a look on its own merits.

Split out of #133, which delivered the unified 13-month horizon but explicitly could **not** complete its "size the result at 13 months" task. Parent: #22. ## Why this couldn't be done in #133 Production was measured on 2026-07-28 (full data on [#133](https://git.rhoving.com/rbrooks/WeatherBot/issues/133#issuecomment-18937)), a few hours after capture was ungated. The capture tables held essentially nothing: | Table | Rows at measurement | Window covered | |---|---:|---| | `nws_alert_snapshots` | 3 | 10.6 h | | `alert_radar_frames` | 31 | 3.8 h, n=2 events | | `lightning_clusters` | 0 | archive deployed 14:27 UTC same day | Projecting 13 months from that would be arithmetic wearing a measurement's clothes — the same thing #130 and #132 were flagged for. Better to wait for real data. ## What to do - [ ] Re-measure row counts, heap/index sizes and on-disk PNG bytes for `nws_alert_snapshots`, `alert_radar_frames` and `lightning_clusters` after a full convective season (**revisit ~autumn 2026**). - [ ] Post measured figures back to #130 (whose ~165 MB/location/year estimate now reads 73–113 MB/location/year on early data) and #132 (whose ~25–50 MB/year/region is still entirely unvalidated). - [ ] Confirm or change `RETENTION_LIGHTNING_CLUSTERS_DAYS`. It is pinned at 90 days on a *reasoned but unmeasured* argument — lightning being the highest-volume, fastest-decaying history. If measurement contradicts that, fold it into the horizon or pin it somewhere else deliberately. - [ ] Re-check `frames per event` against the `alert_radar_frame_max_per_event = 24` cap. The current 15.5 average rests on **n=2**; the event *rate* (1.82/day/location) is solid, that figure is not. - [ ] Decide whether the horizon's storage cost warrants the B2/S3 offload that #131's storage abstraction was built to enable. ## Watch items carried over **`sent_alerts` bloat.** Measured 1,023 kB of live data sitting in a 21 MB heap — roughly **20× bloat** from UPDATE churn (`cleared_at`, `escalation_count`, `digest_pending`, `acknowledged_at`), with autovacuum last run 6 days prior. #133 raised this table's window from 90 to 400 days, so it now stops pruning entirely for ~10 months while accruing ~9.1 rows/day. Worth confirming autovacuum keeps pace, and a one-off `VACUUM FULL` would reclaim ~20 MB today. **`sent_alerts.radar_snapshot_path` is NULL on all 1,708 rows** while `alert_radar_frames` has 31. Most likely benign — both frame-bearing alerts were already active when capture ungated, so the first-fire moment had passed. But if it is still NULL after a fresh alert fires post-deploy, that is a #85 regression and deserves its own issue. **`product_freshness_samples` index cost.** 63 MB total, and its **indexes cost as much as its data** (31 MB vs 32 MB) — five indexes on a 164-byte row, 78% of it `spc_fetch`. It is observability rather than history and correctly stays outside the horizon at 14 days, but it is the single largest table in the database and the index ratio looks worth a look on its own merits.

claude-bot referenced this issue

2026-07-28 15:40:05 +00:00

Unify retention onto one 13-month horizon (#133) #157

claude-bot referenced this issue

2026-08-01 07:25:10 +00:00

Historical Weather Event Explorer #22