Si j'ai de grandes quantités de données dans une table définie comme

CREATE TABLE sensor_values ​​( ts TIMESTAMPTZ(35, 6) NOT NULL,
value FLOAT8(17, 17) DEFAULT 'NaN' :: REAL NOT NULL,
sensor_id INT4(10) NON NULL, );

Les données arrivent chaque minute pour des milliers de points. Assez souvent, j'ai besoin d'extraire et de travailler avec des valeurs quotidiennes au fil des ans (sur une interface Web). Pour aider cela, je voudrais une table sensor_values_days qui n'a que les sommes quotidiennes pour chaque point, puis je peux l'utiliser pour des requêtes plus rapides sur des périodes plus longues.

Je ne veux pas d'un déclencheur pour chaque écriture sur la base de données car j'ai peur que cela ralentisse le goulot d'étranglement déjà des écritures sur la base de données.

Existe-t-il un moyen de déclencher uniquement après avoir inséré autant de lignes ? Ou peut-être un index et maintient-il un index d'une somme d'entrées sur plusieurs jours ? Je ne pense pas que ce soit possible.

Quelle serait la meilleure façon de faire cela. Il n'aurait pas besoin d'être très à jour. Perdre les dernières heures ou un jour ne serait pas un problème.

Merci

0
Glenn Pierce 19 mars 2021 à 14:51

2 réponses

Meilleure réponse

Quelle serait la meilleure façon de faire cela.

Installez clickhouse et utilisez le type de table AggregatingMergeTree.

Avec postgres :

Créez un tableau agrégé par période. Vous pouvez en avoir plusieurs avec une granularité différente, comme des heures, des jours et des mois.

Exécutez un cron ou une tâche planifiée à la fin de chaque période plus quelques minutes. Tout d'abord, sélectionnez le dernier horodatage dans le tableau par période, afin de savoir à quelle période commencer. Ensuite, agrégez toutes les lignes de la table principale pour les périodes postérieures à la dernière disponible. Ce processus fonctionnera également si le tableau par période est vide, ou s'il a raté la dernière mise à jour, il rattrapera son retard.

Afin de ne faire que des insertions et aucune mise à jour, vous devez l'exécuter à la fin de chaque période, pour vous assurer qu'il a toutes les données. Vous pouvez également stocker le premier et le dernier horodatage des lignes agrégées. Ainsi, plus tard, si vous vérifiez le tableau, vous verrez qu'il a utilisé toutes les données de la période.

Après agrégation, la table « heures » devrait être 60 x plus petite que la table « minutes », cela devrait aider !

Ensuite, répétez le même processus pour le tableau "jour" et "mois".

Si vous voulez des statistiques à jour, vous pouvez UNION TOUS les résultats du tableau "par jour" (par exemple) avec les résultats du tableau en direct, mais ne retirer que le jour en cours du tableau en direct, car tous les la valeur des données des jours précédents a été résumée dans le tableau "par jour". Espérons que les données du jour en cours seront mises en cache dans la RAM.

Il n'aurait pas besoin d'être très à jour. Perdre les dernières heures ou un jour ne serait pas un problème.

De plus, si vous souhaitez partitionner votre immense table, assurez-vous de le faire avant que sa taille ne devienne ingérable...

1
bobflux 19 mars 2021 à 12:49

Les vues matérialisées et un Cron toutes les 5 minutes peuvent vous aider à :

https://wiki.postgresql.org/wiki/Incremental_View_Maintenance

En PG14, nous aurons une VUE MATERIALISEE INCREMENTALE, mais pour le moment elle est en développement.

1
Frank N Stein 19 mars 2021 à 12:28