Vue Activité : affichage instantané sur les grosses bases (PR #2647)

Développement
1

Suite à la sortie de la 4.82 et aux échanges avec @pierre-gilles sur les perfs de la nouvelle vue Activité sur des installations un peu lourdes (tout du moins comme la mienne), voici le bilan de la PR #2647, testée sur ma base de 448 millions d’états.

Le problème

Sur une grosse base, filtrer l’Activité sur une catégorie clairsemée (ex. « Ouvertures » : peu d’états, anciens) obligeait le serveur à scanner une énorme partie de l’historique avant de répondre : le serveur ne répondait qu’une fois sa page de 80 états remplie, quelle que soit la profondeur à parcourir. Résultat : un spinner figé pendant 20 à 50 secondes, parfois 3 minutes.

On a mesuré méthodiquement toutes les pistes « pur serveur » (fenêtres progressives, ancrage sur la dernière activité — merci @pierre-gilles pour la #2642 —, tranches disjointes, compaction physique de la table) : chacune améliore, aucune ne supprime le mur — le coût plancher, c’est le débit de scan de DuckDB multiplié par le volume à parcourir.

La solution : laisser le client piloter la recherche

  • Serveur : getDeviceStatesHistory accepte une borne since → une requête = une fenêtre temporelle bornée, qui rend ce qu’elle contient (même moins que 80) et ne s’élargit jamais. Une fenêtre bornée répond en millisecondes grâce aux zone maps DuckDB. Sans since, comportement strictement inchangé (rétro-compatible).
  • Front : la vue Activité sonde 1 → 2 → 4 → 8 → 16 → 32 mois puis une dernière requête illimitée, affiche chaque lot dès son arrivée, avec un bandeau « Recherche d’activités — mars 2025… » pendant que les fenêtres plus anciennes sont sondées en arrière-plan.

Les mesures (448 M d’états)

Cas Avant Après
Filtre « Ouvertures » (peu d’états, anciens) 20 à 33 s de spinner figé Premiers états en ~100 ms, la page se complète en fond
Filtre « Boutons » (états à 17 mois) 22 à 51 s Bandeau immédiat indiquant le mois scanné, états dès qu’ils sont trouvés
Vue « Tout » / live / capteurs bavards ~100 ms Inchangé (~100 ms)

Le travail total du pire cas ne change pas — mais il se fait derrière une page déjà utilisable au lieu de bloquer le premier affichage. C’est le principe du logbook Home Assistant, adapté à l’API Gladys.

image
image1111×499 25.5 KB

image
image1814×755 83 KB

La PR est prête pour review. Elle ne touche ni au modèle de données, ni aux agrégats — c’est du pur découpage de requêtes.

2

Salut @Terdious, merci pour cette analyse ! :folded_hands:

Je suis quand même surpris que DuckDB ne gère pas déjà ce cas correctement. C’est assez fou de devoir attendre 30 secondes juste pour un SELECT [...] ORDER BY created_at DESC, alors que c’était justement une des grandes promesses de DuckDB.

À mon avis, on ne l’utilise pas de la bonne manière. Soit il nous manque un index (en gardant en tête que des index multi-colonnes complexes peuvent vite coûter cher en espace disque), soit notre requête n’est pas optimale. :slightly_smiling_face:

3

Salut @pierre-gilles,

On s’est posé exactement la même, et on a testé les deux hypothèses (requête et structure) avant d’écrire la PR. Bon tu te doute, pour te répondre le mieux possible, j’ai posé mes point à l’IA en fonction des essais et réflexion qu’on a eu pendant le dev, j’espère qu’il sera plus clair que ce que je pourrais faire ^^ :

Réponse courte : la requête n’est pas « juste » un ORDER BY, et l’index qui la sauverait n’existe pas dans DuckDB — par design.

1. La vraie requête. Ce n’est pas SELECT … ORDER BY created_at DESC LIMIT 80 seul — ça, DuckDB le fait très bien (Top-N vectorisé). C’est WHERE device_feature_id IN (les features de la catégorie) ORDER BY created_at DESC LIMIT 80. Sur une catégorie rare, les 80 lignes qui matchent sont dispersées loin dans le passé — et sans index secondaire, le moteur n’a aucun moyen de savoir où : il scanne jusqu’à les trouver. Pire cas = toute la table.

2. Pourquoi pas un index ? Le seul index de DuckDB est l’ART, conçu pour les clés primaires et les point lookups : il n’est pas utilisé pour ordonner (pas de parcours ordonné à la B-tree), la doc déconseille d’en créer au-delà de ce cas, et sur 448 M de lignes il coûterait des Go en plus d’un surcoût sur chacune de nos dizaines d’écritures/seconde. Le « vrai » index de DuckDB, ce sont les zonemaps (min/max par bloc de ~120 k lignes) — efficaces uniquement quand le filtre est corrélé à l’ordre physique des données.

3. « On structure mal nos données ? » — testé aussi, ca à pris environ 30 minutes. J’ai réécrit la table entière en ORDER BY created_at sur ma copie (le layout optimal pour les zonemaps temporelles) : les fenêtres bornées passent à quelques ms :white_check_mark:… mais la catégorie rare ne gagne que ×2 (33 s → 15 s). Le problème n’est pas le rangement : il faut de toute façon lire un an de données denses pour y trouver 80 états d’une catégorie qui n’en produit presque pas. Et clusteriser par feature à la place casserait la vue « Tout » et se déferait en continu (les inserts live arrivent en ordre temporel). Ton ancrage last_value_changed (#2642) et une variante à tranches disjointes ont aussi été mesurés : mêmes ordres de grandeur.

4. La promesse de DuckDB est ailleurs — et elle est tenue. Colonne + OLAP = scans, agrégations massives ultra rapides (nos graphes énergie sur des millions de lignes !) et légèreté de la DB, en échange de l’abandon des index secondaires. « Les 80 derniers états d’une entité » est la requête OLTP par excellence — c’est exactement pourquoi Home Assistant la sert avec un index composite (entité, temps) sur un row-store, en payant ce que ça coûte : espace disque et écritures plus lourdes. Même arbitrage, autre versant.

D’où le choix de la #2647 : des fenêtres temporelles bornées (là où DuckDB excelle, quelques ms par requête) pilotées par le client, qui affiche au fil de l’eau — le pire cas ne bloque plus jamais le premier rendu. Si un jour on veut aussi éliminer le coût total du pire cas, la piste « propre » serait une mini table-index (1 ligne par feature et par mois) interrogée d’abord — mais c’est un ajout au modèle de données.