Des vues matérialisées aux agrégats continus : améliorer PostgreSQL avec l'analyse en temps réel

Permettre aux utilisateurs d'accéder à des analyses de données en temps réel est une fonctionnalité clé de nombreuses applications modernes. Imaginez-vous en train d'utiliser votre plate-forme SaaS préférée : il existe probablement un tableau de bord intuitif présentant des données en temps réel et des informations historiques. Vous pouvez probablement interagir avec la plateforme, créer des rapports personnalisés, explorer des mesures détaillées et visualiser des tendances sur des semaines ou des mois.

Vous ne voudriez certainement pas que cette plateforme soit lente en tant qu'utilisateur. Cela signifie que la base de données qui alimente ces produits doit être rapide pour exécuter des requêtes sur de grands volumes de données, y compris des requêtes analytiques complexes.

Alors que PostgreSQL est aujourd'hui la base de données la plus appréciée des développeurs , il n'est pas connu pour être rapide dans l'interrogation de gros volumes de données. Mais ne vous inquiétez pas : Postgres a toujours un outil dans sa boîte à outils. L’un des meilleurs sont les vues matérialisées.

Que sont les vues matérialisées PostgreSQL ?

Basées sur la technique de matérialisation, les vues matérialisées PostgreSQL précalculent les requêtes couramment exécutées et stockent les résultats sous forme de tableau. Contrairement aux vues PostgreSQL standard, qui exécutent la requête sous-jacente à chaque fois que la vue est référencée, les vues matérialisées conservent le résultat de la requête source dans la base de données. L'avantage est que votre base de données n'a pas besoin d'exécuter la requête à chaque fois que vous l'exécutez : les résultats sont déjà accessibles sur le disque : vous obtiendrez la réponse à votre requête beaucoup plus rapidement.

Il s’agit d’un excellent moyen d’optimiser les réponses aux requêtes dont le calcul nécessite beaucoup de ressources. Par exemple, des requêtes pouvant impliquer le traitement de gros volumes de données, des agrégations ou des jointures multiples.

Travailler avec des vues matérialisées est très simple. Pour créer une vue, vous utiliserez l'instruction CREATE MATERIALIZED VIEW et la requête de votre choix.

Une fois votre vue matérialisée créée, vous pouvez l'interroger comme une table PostgreSQL standard :

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

Cette vue matérialisée deviendra rapidement obsolète jusqu'à ce que vous l'actualisiez : même si vous ajoutez de nouvelles données à la table de base (ou mettez à jour ou supprimez des données), la vue matérialisée n'inclut pas automatiquement ces modifications ; c'est un instantané au moment de sa création. Pour mettre à jour la vue matérialisée, vous devez exécuter REFRESH MATERIALIZED VIEW .

 REFRESH MATERIALIZED VIEW customer_orders;

Ce dernier point (la façon dont les actualisations sont gérées) est le talon d'Achille des vues matérialisées, comme nous le verrons dans la section suivante.

Qu’en est-il de l’analyse en temps réel ? Limites des vues matérialisées PostgreSQL

Comme nous le disions, les vues matérialisées PostgreSQL sont un outil puissant pour accélérer les requêtes fréquemment exécutées, surtout si ces requêtes portent sur de gros volumes de données. Mais les vues matérialisées présentent un aspect loin d’être idéal : pour maintenir vos vues matérialisées à jour, elles doivent être actualisées.

Ce seul problème crée trois limitations importantes :

Les actualisations sont inefficaces et coûteuses en calcul

Lors de l'actualisation d'une vue matérialisée, la requête est recalculée sur l'intégralité du jeu de données. En coulisse, lorsque vous exécutez une actualisation, les anciennes données matérialisées sont supprimées puis remplacées par de nouvelles données rematérialisées. Exécution actualisations incrémentielles (où seules les données modifiées sont mises à jour) rendrait le processus d'agrégation beaucoup plus efficace, mais il est difficile à mettre en œuvre correctement dans une base de données relationnelle cohérente. Des solutions de contournement sont parfois possibles avec des scripts supplémentaires, mais elles sont loin d'être simples, en particulier pour les requêtes complexes ou si les données arrivent tardivement.

Les actualisations ne sont pas automatiquement exécutées

Comme mentionné précédemment, les vues matérialisées n'intègrent pas automatiquement les dernières données. Ils doivent être actualisés en exécutant REFRESH MATERIALIZED VIEW . Il n’est pas possible d’exécuter des actualisations manuelles dans un environnement de production : une configuration beaucoup plus réaliste consisterait à automatiser l’actualisation.

Malheureusement, les vues matérialisées n'ont pas de fonctionnalité d'actualisation automatique intégrée, donc la création d'une planification d'actualisation automatique pour les vues matérialisées dans PostgreSQL nécessite un planificateur quelconque. Cela peut être géré dans la base de données avec une extension ou hors base de données avec un planificateur comme cron. Cependant, cela est géré car les actualisations sont coûteuses et prennent beaucoup de temps. Il est très facile de se retrouver dans une situation où vous ne pouvez pas actualiser la vue assez rapidement.

Les vues matérialisées n'affichent pas les résultats à jour

Une conséquence de la nature statique des vues matérialisées est que lorsqu'elles sont interrogées, elles manqueront les données ajoutées ou modifiées depuis la dernière actualisation (même si cette actualisation se produit selon un calendrier). Si votre fenêtre de planification est définie sur une heure, votre agrégat sera alors jusqu'à une heure plus l'heure réelle pour effectuer la mise à jour obsolète. Mais de nombreuses applications impliquent aujourd'hui l'ingestion d'un flux constant de données et, souvent, ces applications doivent offrir des résultats à jour à leurs utilisateurs pour garantir qu'ils récupèrent des informations précises lors de l'interrogation de la vue.

Il est dommage que les vues matérialisées soient limitées par ces limitations. Si vous créez une plate-forme SaaS à partir d'un ensemble de données en direct, avec de nouvelles données entrantes fréquemment, les vues matérialisées doivent-elles être complètement abandonnées ?

La réponse est non. Dans Timescale, nous avons créé une solution qui améliore efficacement les vues matérialisées pour les rendre plus adaptées aux applications modernes : les agrégats continus.

Découvrez les agrégats continus : vues matérialisées avec actualisations automatiques pour des analyses en temps réel

Imaginez un monde dans lequel les vues matérialisées ne sont pas de simples instantanés statiques mais mises à jour de manière dynamique et efficace. Vous accéderez à l’amélioration des performances des requêtes que vous recherchez sans vous soucier de quoi que ce soit d’autre. Eh bien, il semble que nous ayons décrit les agrégats continus de Timescale.

Les agrégats continus (disponibles pour toutes les bases de données PostgreSQL via l'extension TimescaleDB et dans AWS via la plateforme Timescale) sont des vues matérialisées améliorées par des capacités d'actualisation efficaces et automatisées et un élément en temps réel. Elles ressemblent presque exactement à des vues matérialisées, mais permettent les opérations suivantes :

• Actualisations automatiques via une politique d'actualisation
• Un processus de rafraîchissement plus efficace : lorsqu'un rafraîchissement s'exécute, il ne touchera que les données qui ont changé depuis le dernier rafraîchissement
• Des résultats à jour, élargissant les cas d'utilisation dans lesquels les vues matérialisées peuvent être exploitées (comme les analyses en temps réel, les tableaux de bord en direct, les rapports et autres)

Rendre les actualisations automatiques et économes en ressources

La création d'un agrégat continu est très similaire à la création d'une vue matérialisée (et elle peut également être interrogée comme une table PostgreSQL standard) :

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

Mais contrairement aux vues matérialisées, la création d’une politique d’actualisation est simple. Vous pouvez facilement définir l'intervalle d'actualisation dans la base de données, garantissant ainsi que votre agrégat continu est automatiquement et périodiquement mis à jour.

L'exemple ci-dessous configure une politique d'actualisation pour mettre à jour l'agrégat continu toutes les 30 minutes. Le paramètre end_offset définit la plage horaire des données à actualiser et le schedule_interval définit la fréquence à laquelle l'agrégat continu sera actualisé :

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

Lorsque cette politique d’actualisation entrera en vigueur, le processus sera beaucoup plus efficace que si nous utilisions une simple vue matérialisée. Contrairement à l'exécution REFRESH MATERIALIZED VIEW , lorsqu'un agrégat continu est actualisé, Timescale ne supprime pas toutes les anciennes données et ne recalcule pas l'agrégat par rapport à celles-ci : le moteur exécute simplement la requête sur la période d'actualisation la plus récente (par exemple, 30 minutes) et l'ajoute. à la matérialisation.

De même, les UPDATE et DELETE effectués au cours de cette dernière période sont identifiés, en recalculant le chunk (partition) qui les implique. (Agrégats continus construits sur Timescale hypertables , qui sont des tables PostgreSQL automatiquement partitionnées. Il s'agit d'un énorme avantage, permettant au moteur de recalculer uniquement des partitions spécifiques plutôt que la table entière lorsque les données ont changé.)

Affichage des résultats à jour pour des analyses en temps réel

Mais comment les agrégats continus résolvent-ils le problème de la visualisation des résultats à jour ? Que se passe-t-il si de nouvelles données ont été ajoutées après la dernière actualisation et que j'interroge l'agrégat continu ?

Pour permettre cette fonctionnalité, nous avons ajouté fonctionnalité d'agrégation en temps réel aux agrégats continus. Lorsque l'agrégation en temps réel est activée , et vous interrogez votre agrégat continu, le résultat que vous verrez combinera deux parties :

• Données matérialisées dans la vue matérialisée sous-jacente, qui ont été mises à jour lors de la dernière actualisation.
• Les données brutes les plus récentes, non encore matérialisées, qui résident toujours exclusivement dans votre table de base (ou hypertable, pour être exact).

Cette fonctionnalité transforme les vues matérialisées d'instantanés statiques en entités dynamiques, garantissant que les données stockées ne sont pas seulement un reflet historique mais une représentation à jour des ensembles de données sous-jacents.

Utilisation d'agrégats continus : exemple

Même si tout cela semble bien, cela ira (espérons-le) beaucoup mieux avec un exemple.

Imaginez une plateforme utilisée par les agences de transport et les sociétés de covoiturage. Cette plateforme contient un tableau de bord dans lequel les entreprises peuvent voir un aperçu de l'état de leur flotte, y compris un tableau avec le dernier état des indicateurs clés et deux visualisations montrant l'évolution des métriques ce jour-là et dans le contexte de la semaine.

Pour alimenter cette application, nous aurions d'abord une hypertable dans laquelle les données sur les manèges sont constamment insérées. L'hypertable pourrait ressembler à ceci :

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

Les hypertables sont très rapides et très évolutives : cette table restera performante même si elle comporte des milliards de lignes.

Pour alimenter le tableau en fournissant un aperçu en direct, nous utiliserions un agrégat continu pour regrouper les données par 30 minutes. Cela permettrait de maintenir le processus rapide et réactif :

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

Dans le code précédent, le paramètre end_offset garantit que l'agrégat ne tente pas immédiatement d'actualiser les toutes dernières données, ce qui laisse un certain temps de tampon pour s'adapter aux éventuels retards dans l'arrivée des données. Définir end_offset sur 10 minutes signifie que l'agrégat actualisera les données datant d'au moins 10 minutes, garantissant ainsi qu'il ne manquera pas de mises à jour en raison de retards mineurs dans l'afflux de données. Dans un cas d'utilisation réel, vous ajusteriez cette valeur en fonction du délai moyen que vous observez dans votre pipeline de données.

Pour alimenter la visualisation offrant la vue quotidienne, nous créerions un deuxième agrégat continu. Dans ce graphique, les données sont affichées par heure, nous n'avons donc pas besoin d'une granularité par minute comme dans le précédent :

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

Enfin, pour alimenter le graphique offrant la vue hebdomadaire, nous créerions un agrégat continu supplémentaire, agrégeant cette fois les données par jour :

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS Pour rendre l'expérience de définition d'agrégats continus encore plus efficace, L'échelle de temps a introduit des agrégats continus hiérarchiques dans TimescaleDB 2.9. Une fois que vous vous êtes familiarisé avec les agrégats continus, vous pouvez commencer à les créer par-dessus d'autres agrégats continus. Par exemple, dans l'exemple précédent, vous pouvez également définir les agrégats horaires au-dessus de l'agrégat par minute.

Conclusion

Même si PostgreSQL n'a pas été conçu à l'origine pour les applications qui doivent traiter de grands ensembles de données en direct, devinez quoi : ces types de charges de travail sont désormais partout. Cependant, PostgreSQL est livré avec des fonctionnalités qui facilitent cette tâche. Les vues matérialisées sont parmi les plus puissantes, car elles permettent de pré-calculer les résultats des requêtes et de les stocker sur disque pour une récupération rapide.

Cependant, les vues matérialisées présentent trois limites importantes. Premièrement, le déclenchement des actualisations est très inefficace sur le plan informatique. Deuxièmement, même la configuration de ces actualisations automatiques n’est pas un processus transparent. Troisièmement, les vues matérialisées n'affichent pas de résultats à jour, car elles excluent les données ajoutées ou modifiées depuis la dernière actualisation.

Ces limitations font des vues matérialisées une solution peu pratique pour de nombreuses applications modernes. Pour résoudre ce problème, nous avons construit des agrégats continus. Il s'agit de vues matérialisées PostgreSQL dans lesquelles vous pouvez facilement définir une politique d'actualisation, afin que les actualisations se produisent automatiquement. Ces actualisations sont également incrémentielles et donc beaucoup plus efficaces. Enfin, les agrégats continus permettent de combiner les données matérialisées avec les données brutes ajoutées et modifiées depuis le dernier rafraîchissement, garantissant ainsi d'obtenir uniquement des résultats à jour.

Si vous exécutez PostgreSQL sur votre matériel, vous pouvez accéder à des agrégats continus en installation de l'extension TimescaleDB . Si vous êtes sur AWS, consultez la plateforme Timescale . Les 30 premiers jours sont gratuits.

Écrit par Carlota Soto et Mat Arye.