History query performance, indexing, and Postgres-tier tests #141

New issue

Closed

opened 2026-07-27 20:08:38 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-27 20:08:38 +00:00

Contributor

Parent: #22.

Concrete existing problem

/stats (app/main.py:333-341) runs one unbounded query over the entire retained history for every visible location, then aggregates in Python with collections.Counter (:344-383). The Python aggregation is deliberate and documented — there is no portable cross-dialect "group by calendar month" between Postgres date_trunc and the SQLite test tier's strftime.

At today's 90-day retention that is tolerable. At a 13-month horizon, with the Explorer's added capture volume, it is not.

Tasks

Bound the /stats query by date range and add a range selector; decide whether /stats folds into the Explorer's calendar view or stays a separate summary page.
Index review for the new access patterns: by-date-across-locations (calendar), by-day-by-location (day view), by-event (detail).
Decide whether sent_alerts, nws_alert_snapshots, and the lightning archive need time-based partitioning at 13 months, or whether plain indexes suffice. Measure before adding the complexity.
Postgres-tier tests (@pytest.mark.postgres) for the JSONB-heavy and date-bucketing queries SQLite cannot express — the existing two-tier split is exactly why /stats aggregates in Python today.
Seed a realistic multi-month history fixture to test and benchmark against.

Parent: #22. ## Concrete existing problem `/stats` (`app/main.py:333-341`) runs **one unbounded query over the entire retained history** for every visible location, then aggregates in Python with `collections.Counter` (`:344-383`). The Python aggregation is deliberate and documented — there is no portable cross-dialect "group by calendar month" between Postgres `date_trunc` and the SQLite test tier's `strftime`. At today's 90-day retention that is tolerable. At a 13-month horizon, with the Explorer's added capture volume, it is not. ## Tasks - [ ] Bound the `/stats` query by date range and add a range selector; decide whether `/stats` folds into the Explorer's calendar view or stays a separate summary page. - [ ] Index review for the new access patterns: by-date-across-locations (calendar), by-day-by-location (day view), by-event (detail). - [ ] Decide whether `sent_alerts`, `nws_alert_snapshots`, and the lightning archive need time-based partitioning at 13 months, or whether plain indexes suffice. **Measure before adding the complexity.** - [ ] Postgres-tier tests (`@pytest.mark.postgres`) for the JSONB-heavy and date-bucketing queries SQLite cannot express — the existing two-tier split is exactly why `/stats` aggregates in Python today. - [ ] Seed a realistic multi-month history fixture to test and benchmark against.

claude-bot added this to the v2.0.0 - Historical Weather Event Explorer milestone

2026-07-27 20:08:38 +00:00

claude-bot added the

area:observability

type:maintenance

v2.0.0

labels

2026-07-27 20:08:38 +00:00

claude-bot referenced this issue

2026-07-27 20:09:21 +00:00

Historical Weather Event Explorer #22

claude-bot referenced this issue

2026-08-01 05:58:34 +00:00

Flaky/order-dependent test: test_cloud_provider_prompt_filters_disallowed_metadata #174

claude-bot referenced this issue

2026-08-01 05:58:47 +00:00

Explorer: event JSON export (#142) #175

claude-bot commented

2026-08-01 06:03:07 +00:00

Author

Contributor

Picking this up — last engineering item of the milestone, now that all Explorer query shapes are on main (#135, #137–#140, #142). Plan per the task list: bound /stats with a range selector (kept as a separate summary page — the unbounded scan is the problem, not the page); index audit across the new access patterns (one known gap going in: sent_alerts.sent_at has no index despite being the stats/history slice key); the partitioning question answered by measurement — seeded multi-month fixture + EXPLAIN ANALYZE against real Postgres on the dev host, numbers recorded here before any complexity is added; Postgres-tier tests for the date-bucketing/JSONB queries SQLite can't express; and the #174 order-dependent flake hunted to its root cause. PR to follow.

Picking this up — last engineering item of the milestone, now that all Explorer query shapes are on main (#135, #137–#140, #142). Plan per the task list: bound `/stats` with a range selector (kept as a separate summary page — the unbounded scan is the problem, not the page); index audit across the new access patterns (one known gap going in: `sent_alerts.sent_at` has no index despite being the stats/history slice key); the partitioning question answered by **measurement** — seeded multi-month fixture + `EXPLAIN ANALYZE` against real Postgres on the dev host, numbers recorded here before any complexity is added; Postgres-tier tests for the date-bucketing/JSONB queries SQLite can't express; and the #174 order-dependent flake hunted to its root cause. PR to follow.