Vue Activity: instant display on large databases (PR #2647)

Development
1

Following the release of 4.82 and discussions with @pierre-gilles about the performance of the new Activity view on heavy installations (at least mine), here’s the summary of PR #2647, tested on my database of 448 million states.

The Problem

On a large database, filtering Activity by a sparse category (e.g., « Openings »: few states, old) forced the server to scan a huge part of the history before responding: the server only responded once its page of 80 states was filled, regardless of the depth to traverse. Result: a frozen spinner for 20 to 50 seconds, sometimes 3 minutes.

We methodically measured all « server-only » leads (progressive windows, anchoring on the last activity — thanks @pierre-gilles for #2642 —, disjoint slices, physical compaction of the table): each improves, none removes the wall — the floor cost is the scan throughput of DuckDB multiplied by the volume to traverse.

The Solution: Let the Client Drive the Search

  • Server: getDeviceStatesHistory accepts a since bound → one query = a bounded time window, which returns what it contains (even less than 80) and never expands. A bounded window responds in milliseconds thanks to DuckDB zone maps. Without since, behavior strictly unchanged (backward-compatible).
  • Frontend: The Activity view probes 1 → 2 → 4 → 8 → 16 → 32 months then a final unlimited query, displays each batch as it arrives, with a banner « Searching for activities — March 2025… » while older windows are probed in the background.

The Measurements (448 M states)

Case Before After
« Openings » filter (few states, old) 20 to 33 s of frozen spinner First states in ~100 ms, the page completes in the background
« Buttons » filter (states at 17 months) 22 to 51 s Immediate banner indicating the scanned month, states as they are found
« All » view / live / chatty sensors ~100 ms Unchanged (~100 ms)

The total work of the worst case doesn’t change — but it’s done behind an already usable page instead of blocking the first display. This is the principle of the Home Assistant logbook, adapted to the Gladys API.

image
image1111×499 25.5 KB

image
image1814×755 83 KB

The PR is ready for review. It doesn’t touch the data model or the aggregates — it’s pure query splitting.

2

Hi @Terdious, thanks for this analysis! :folded_hands:

I’m still surprised that DuckDB doesn’t handle this case correctly already. It’s quite crazy to have to wait 30 seconds just for a SELECT [...] ORDER BY created_at DESC, when this was exactly one of DuckDB’s major promises.

In my opinion, we’re not using it the right way. Either we’re missing an index (keeping in mind that complex multi-column indexes can quickly become expensive in disk space), or our query isn’t optimal. :slightly_smiling_face:

3

Hi @pierre-gilles,

We had exactly the same question, and we tested both hypotheses (query and structure) before writing the PR. You can imagine, to answer you as best as possible, I put my points to the AI based on the tests and reflections we had during development. I hope it will be clearer than what I could do ^^:

Short answer: the query is not « just » an ORDER BY, and the index that would save it doesn’t exist in DuckDB — by design.

1. The real query. It’s not just SELECT … ORDER BY created_at DESC LIMIT 80 — DuckDB handles that very well (vectorized Top-N). It’s WHERE device_feature_id IN (the features of the category) ORDER BY created_at DESC LIMIT 80. For a rare category, the 80 matching rows are scattered far back in time — and without a secondary index, the engine has no way of knowing where they are: it scans until it finds them. Worst case = the entire table.

2. Why not an index? DuckDB’s only index is the ART, designed for primary keys and point lookups: it’s not used for ordering (no ordered traversal like a B-tree), the documentation discourages creating any beyond this case, and on 448 M rows it would cost gigabytes on top of an overhead on each of our tens of writes per second. DuckDB’s « real » index is the zonemaps (min/max per block of ~120 k rows) — effective only when the filter is correlated with the physical order of the data.

3. « Are we structuring our data poorly? » — also tested, it took about 30 minutes. I rewrote the entire table in ORDER BY created_at on my copy (the optimal layout for temporal zonemaps): bounded windows go down to a few ms :white_check_mark:… but the rare category only gains ×2 (33 s → 15 s). The problem isn’t the arrangement: you still have to read a year of dense data to find 80 states of a category that barely produces any. Clustering by feature instead would break the « All » view and unravel continuously (live inserts arrive in temporal order). Your anchor last_value_changed (#2642) and a variant with disjoint slices were also measured: same order of magnitude.

4. DuckDB’s promise is elsewhere — and it’s kept. Column + OLAP = ultra-fast massive scans, aggregations (our energy graphs on millions of rows!), and DB lightness, in exchange for abandoning secondary indexes. « The last 80 states of an entity » is the quintessential OLTP query — that’s exactly why Home Assistant serves it with a composite index (entity, time) on a row-store, paying what it costs: disk space and heavier writes. Same trade-off, other side.

Hence the choice of #2647: bounded time windows (where DuckDB excels, a few ms per query) controlled by the client, which displays as it goes — the worst case never blocks the first render again. If one day we also want to eliminate the total cost of the worst case, the « clean » path would be a mini index table (1 row per feature and per month) queried first — but that’s an addition to the data model.