Stratégies d'implémentation de la compression en colonnes dans les grandes bases de données PostgreSQL

La mise à l'échelle d'une base de données Postgres est un rite de passage pour les applications en croissance. À mesure que vos tables s'étendent avec des millions, voire des milliards de lignes, vos requêtes autrefois rapides commencent à prendre du retard et les coûts d'infrastructure croissants commencent à jeter une ombre longue sur vos résultats. Vous êtes pris dans une énigme : vous ne voulez pas vous séparer de votre bien-aimé PostgreSQL, mais il semble que vous aurez besoin d'un moyen plus efficace de gérer vos ensembles de données croissants.

Dans cet article, nous vous raconterons comment nous avons construit un mécanisme de compression en colonnes flexible et hautes performances pour PostgreSQL afin d'améliorer son évolutivité. En combinant le stockage en colonnes avec des algorithmes de compression spécialisés, nous sommes en mesure d'atteindre des taux de compression impressionnants, inégalés dans toute autre base de données relationnelle (+95 %).

En compressant votre ensemble de données, vous pouvez développer davantage vos bases de données PostgreSQL. Comme nous le verrons tout au long de cet article, cette conception de compression très efficace vous permet de réduire la taille de vos grandes tables PostgreSQL jusqu'à 10 à 20 fois. Vous pouvez stocker beaucoup plus de données sur des disques plus petits (ce qui permet d'économiser de l'argent) tout en améliorant les performances des requêtes. La compression de l'échelle de temps est également entièrement modifiable, ce qui facilite la gestion et les opérations de la base de données : vous pouvez ajouter, modifier et supprimer des colonnes dans des tables compressées, et vous pouvez INSÉRER, METTRE À JOUR et SUPPRIMER des données directement.

Bienvenue dans un PostgreSQL plus évolutif !

Aperçu du contenu

• Pourquoi PostgreSQL a besoin d'une compression de base de données
• Mais qu’en est-il de TOAST ?
• Pourquoi la compression n'est-elle pas native pour PostgreSQL ? Une introduction aux bases de données orientées lignes et colonnes
• Bases de données orientées lignes ou colonnes : que choisir ?
• Création d'un stockage en colonnes sur une base de données orientée lignes
• Transformer PostgreSQL en un magasin hybride lignes-colonnes
• En coulisses : des données de ligne aux tableaux en colonnes compressés
• Interroger efficacement les données compressées
• Regroupement des données fréquemment interrogées par colonne segmentby
• Définir des colonnes segmentby
• Ajustement avancé via orderby
• L'évolution de la compression de l'échelle de temps
• Le résultat final : des requêtes plus rapides, moins d'empreinte de stockage pour les grandes bases de données PostgreSQL
• Compression et stockage hiérarchisé : cycle de vie du stockage à grande échelle
• Restez avec PostgreSQL

Pourquoi PostgreSQL a besoin d'une compression de base de données

Mais avant d'entrer dans les détails de la façon dont nous avons construit la compression, prenons quelques minutes pour répondre à cette question : pourquoi est-il même nécessaire d'ajouter un nouveau mécanisme de compression de base de données à PostgreSQL ?

Comprenons d'abord les besoins des applications modernes et un peu d'histoire du logiciel.

Nous adorons Postgres : nous pensons que c'est la meilleure base pour créer des applications, car sa combinaison de fiabilité, de flexibilité et d'écosystème riche est très difficile à égaler par n'importe quelle autre base de données. Mais Postgres est né il y a plusieurs décennies : cette robustesse n'est pas sans inconvénients.

Aujourd'hui, les développeurs utilisent PostgreSQL pour bien plus que le cas d'utilisation traditionnel OLTP (OnLine Transaction Processing) pour lequel il est surtout connu. De nombreuses applications exigeantes et gourmandes en données, fonctionnant 24h/24 et 7j/7 et gérant des volumes de données toujours croissants, sont optimisées par PostgreSQL :

• Les bases de données PostgreSQL sont utilisées pour ingérer de grandes quantités de données de capteurs provenant de systèmes de gestion du trafic, de réseaux de services publics et de moniteurs de sécurité publique.

  
  

• Les sociétés énergétiques utilisent PostgreSQL pour stocker et analyser les mesures des réseaux intelligents et des sources d'énergie renouvelables.

  
  

• Dans le secteur financier, PostgreSQL est au cœur des systèmes de suivi des données sur les tics de marché en temps réel.

• Les plateformes de commerce électronique utilisent PostgreSQL pour suivre et analyser les événements générés par les interactions des utilisateurs.

  
  

• Postgres est même utilisé comme base de données vectorielles pour alimenter la nouvelle vague d'applications d'IA.

  
  

En conséquence, les tables Postgres se développent très rapidement, et les tables atteignant des milliards de lignes sont la nouvelle norme en production.

Malheureusement, PostgreSQL est nativement mal équipé pour gérer ce volume de données : les performances des requêtes commencent à ralentir et la gestion des bases de données devient pénible. Pour remédier à ces limitations, nous avons construit Échelle de tempsDB , une extension qui adapte les performances de PostgreSQL aux applications exigeantes via un partitionnement automatique, une agrégation continue, des améliorations du planificateur de requêtes et bien d'autres fonctionnalités.

Construire un mécanisme de compression hautement performant pour PostgreSQL était une avancée tout aussi importante. Ces ensembles de données en constante croissance ne sont pas seulement difficiles à atteindre en termes de performances, mais leur accumulation de données conduit à des disques toujours plus volumineux et à des factures de stockage plus élevées. PostgreSQL avait besoin d'une solution.

Mais qu’en est-il de TOAST ?

Mais qu'en est-il de la méthode TOAST existante de PostgreSQL ? Malgré son nom étonnant 🍞😋, TOAST n'est pas efficace pour réduire systématiquement la taille de vos grandes bases de données PostgreSQL .

TOAST est le mécanisme automatique utilisé par PostgreSQL pour stocker et gérer des valeurs volumineuses qui ne rentrent pas dans les pages individuelles de la base de données. Bien que TOAST intègre la compression comme l'une de ses techniques pour y parvenir, son rôle principal n'est pas d'optimiser l'espace de stockage à tous les niveaux.

Par exemple, si vous disposez d'une base de données de 1 To composée de petits tuples, TOAST ne vous aidera pas à transformer systématiquement ce 1 To en 80 Go, quel que soit le niveau de réglage que vous essayez. TOAST compressera automatiquement les attributs surdimensionnés d'affilée lorsqu'ils dépassent le seuil de 2 Ko, mais TOAST n'aide pas pour les petites valeurs (tuples), et vous ne pouvez pas non plus appliquer des configurations plus avancées configurables par l'utilisateur, telles que la compression de toutes les données datant de plus d'un mois. dans un tableau spécifique. La compression de TOAST est strictement basée sur la taille des valeurs de colonnes individuelles, et non sur des caractéristiques plus larges d'une table ou d'un ensemble de données.

TOAST peut également introduire une surcharge d'E/S importante, en particulier pour les grandes tables avec des colonnes surdimensionnées fréquemment consultées. Dans de tels cas, PostgreSQL doit récupérer les données hors ligne de la table TOAST, ce qui est une opération d'E/S distincte de l'accès à la table principale, car PostgreSQL doit suivre les pointeurs de la table principale vers la table TOAST pour lire les données complètes. Cela conduit généralement à de moins bonnes performances.

Enfin, la compression de TOAST n'est pas conçue pour fournir des taux de compression particulièrement élevés, car elle utilise un algorithme standard pour tous les types de données.

Pourquoi la compression n'est-elle pas native pour PostgreSQL ? Une introduction aux bases de données orientées lignes et colonnes

Cette brève mention de TOAST nous aide également à comprendre les limites de PostgreSQL en matière de compression efficace des données. Comme nous venons de le voir, la compression de TOAST gère les données ligne par ligne, mais cette architecture orientée lignes disperse l'homogénéité sur laquelle prospèrent les algorithmes de compression, ce qui conduit à un plafond fondamental sur le degré d'opérationnalité d'une compression. C’est une raison fondamentale pour laquelle les bases de données relationnelles (comme Postgres natif) échouent souvent en matière d’optimisation du stockage.

Décomposons cela. Traditionnellement, les bases de données appartiennent à l'une des deux catégories suivantes :

• Les bases de données orientées lignes organisent les données par lignes, chaque ligne contenant toutes les données d'un enregistrement particulier. Ils sont optimisés pour le traitement transactionnel où les insertions, mises à jour et suppressions d'enregistrements sont fréquentes, et ils sont efficaces pour les systèmes OLTP où les opérations impliquent des enregistrements individuels ou de petits sous-ensembles de données (par exemple, la récupération de toutes les informations sur un client spécifique).

  
  

• Les bases de données orientées colonnes (alias « colonnes ») , en revanche, organisent les données par colonnes. Chaque colonne stocke toutes les données d'un attribut particulier dans plusieurs enregistrements. Ils sont généralement optimisés pour les systèmes OLAP (OnLine Analytical Processing), où les requêtes impliquent souvent des agrégations et des opérations sur de nombreux enregistrements.

  
  

Illustrons cela avec un exemple. Supposons que nous ayons une table users avec quatre colonnes : user_id , name , logins et last_login . Si cette table stocke les données d'un million d'utilisateurs, elle comportera effectivement un million de lignes et quatre colonnes, stockant physiquement les données de chaque utilisateur (c'est-à-dire chaque ligne) de manière contiguë sur le disque.

Dans cette configuration orientée ligne, la ligne entière pour user_id = 500 000 est stockée de manière contiguë, ce qui accélère la récupération. En conséquence, les requêtes superficielles et larges seront plus rapides sur un magasin de lignes (par exemple, « récupérer toutes les données pour l'utilisateur X ») :

 -- Create table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100), logins INT DEFAULT 0, last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Assume we have inserted 1M user records into the 'users' table -- Shallow-and-wide query example (faster in row store) SELECT * FROM users WHERE user_id = 500000;

En revanche, un magasin en colonnes stockera tous les user_id ensemble, tous les noms ensemble, toutes les valeurs de connexion ensemble, et ainsi de suite, de sorte que les données de chaque colonne soient stockées de manière contiguë sur le disque. Cette architecture de base de données favorise les requêtes approfondies et précises, par exemple « calculer le nombre moyen de connexions pour tous les utilisateurs » :

 -- Deep-and-narrow query example (faster in column store) SELECT AVG(logins) FROM users;

Les magasins en colonnes fonctionnent particulièrement bien avec les requêtes étroites sur des données étendues . Dans une base de données en colonnes, seules les données de la colonne logins doivent être lues pour calculer la moyenne, ce qui peut être fait sans avoir à charger l'intégralité de l'ensemble de données pour chaque utilisateur à partir du disque.

Comme vous l'avez peut-être déjà deviné, le stockage des données dans des lignes plutôt que dans des colonnes a également une influence sur la qualité de la compression des données. Dans une base de données en colonnes, les colonnes de données individuelles sont généralement du même type et proviennent souvent d'un domaine ou d'une plage plus limitée.

En conséquence, les magasins en colonnes se compressent généralement mieux que les bases de données orientées lignes. Par exemple, notre colonne logins serait auparavant toutes de type entier et serait probablement constituée d'une petite plage de valeurs numériques (et aurait donc une faible entropie, qui se compresse bien). Comparez cela à un format orienté ligne, dans lequel une large ligne entière de données comprend de nombreux types et plages de données différents.

Mais même s’ils présentent des avantages en matière de requêtes de style OLAP et de compressibilité, les magasins en colonnes ne sont pas sans compromis :

• Les requêtes récupérant des lignes individuelles sont beaucoup moins performantes (parfois même impossibles à exécuter).

  
  

• Leur architecture n'est pas aussi bien adaptée aux transactions ACID traditionnelles.

  
  

• Il n’est souvent pas possible d’effectuer des mises à jour dans les magasins en colonnes.

  
  

• Il est plus facile pour les magasins basés sur les lignes de profiter d'un indice (par exemple, B-tree) pour trouver rapidement les enregistrements appropriés.

  
  

• Avec un magasin de lignes, il est plus facile de normaliser votre ensemble de données, de sorte que vous puissiez stocker plus efficacement les ensembles de données associés dans d'autres tables.

Bases de données orientées lignes ou colonnes : que choisir ?

Alors, quoi de mieux : orienté lignes ou colonnes ?

Traditionnellement, vous évaluez les compromis entre les deux en fonction de votre charge de travail. Si vous exécutiez un cas d'utilisation OLTP typique, vous choisiriez probablement une base de données relationnelle orientée lignes comme PostgreSQL ; si votre cas d'utilisation était clairement OLAP, vous pourriez vous tourner vers un magasin en colonnes comme ClickHouse.

Mais que se passe-t-il si votre charge de travail est en réalité un mélange des deux ?

• Les requêtes de votre application peuvent généralement être superficielles et larges, avec une requête individuelle accédant à de nombreuses colonnes de données ainsi qu'à des données sur de nombreux appareils/serveurs/éléments différents. Par exemple, vous pouvez alimenter une visualisation destinée à l'utilisateur qui nécessite l'affichage de la dernière température et humidité enregistrées pour tous les capteurs d'une usine de fabrication spécifique. Une telle requête devrait accéder à plusieurs colonnes sur toutes les lignes qui correspondent aux critères de construction, couvrant potentiellement des milliers ou des millions d'enregistrements.

  
  

• Mais certaines de vos requêtes peuvent également être approfondies et précises, une requête individuelle sélectionnant un plus petit nombre de colonnes pour un capteur spécifique sur une période plus longue. Par exemple, vous devrez peut-être également analyser la tendance de la température d'un appareil spécifique au cours du mois dernier pour inspecter les anomalies. Ce type de requête se concentrerait sur une seule colonne (température) mais nécessiterait de récupérer ces informations à partir d'un grand nombre de lignes correspondant à chaque intervalle de temps sur la période cible.

  
  

• Votre application peut également être gourmande en données et lourde en insertion (ajout). Comme nous l’avons évoqué précédemment, gérer des centaines de milliers d’écritures par seconde est la nouvelle norme. Vos ensembles de données sont probablement également très granulaires, par exemple vous collectez peut-être des données toutes les secondes. En reprenant l'exemple précédent, votre base de données devra gérer ces écritures lourdes ainsi que des lectures constantes pour alimenter votre visualisation destinée aux utilisateurs en temps réel.

  
  

• Vos données sont principalement en ajout , mais pas nécessairement en ajout uniquement . Vous devrez peut-être occasionnellement mettre à jour d'anciens enregistrements ou éventuellement enregistrer des données arrivant tardivement ou dans le désordre.

  
  

Cette charge de travail n'est ni OLTP ni OLAP au sens traditionnel. Au lieu de cela, il comprend des éléments des deux. Alors que faire?

Optez pour l'hybride !

Création d'un stockage en colonnes sur une base de données orientée lignes

Pour servir une charge de travail comme dans l'exemple précédent, une seule base de données devrait inclure les éléments suivants :

• La capacité de maintenir des taux d'insertion élevés, facilement de l'ordre de centaines de milliers d'écritures par seconde

  
  

• Prise en charge de l'insertion de données en retard ou dans le désordre, ainsi que de la modification des données existantes

  
  

• Suffisamment de flexibilité pour traiter efficacement les requêtes superficielles et larges et approfondies et étroites sur un grand ensemble de données

  
  

• Un mécanisme de compression capable de réduire considérablement la taille des bases de données pour améliorer l'efficacité du stockage

  
  

C'est ce que nous visions à réaliser en ajoutant la compression en colonnes à TimescaleDB (et donc à PostgreSQL).

Transformer PostgreSQL en un magasin hybride lignes-colonnes

Comme nous l'avons mentionné dans une section précédente, nous avons construit TimescaleDB pour étendre PostgreSQL avec plus de performances et d'évolutivité, le rendant ainsi adapté aux charges de travail exigeantes telles que Données de séries chronologiques. TimescaleDB est implémenté en tant qu'extension PostgreSQL : ce faisant, il hérite de tout ce qui est génial dans PostgreSQL, comme le SQL complet, une grande flexibilité de requêtes et de modèles de données, une fiabilité éprouvée, une base de développeurs et d'utilisateurs passionnés et l'un des plus grands écosystèmes de bases de données. autour.

En théorie, cela signifie que TimescaleDB est également verrouillé dans le format de stockage orienté lignes de PostgreSQL, avec sa modeste compressibilité. En réalité, il n’y a rien qu’un peu d’ingénierie ne puisse résoudre.

Deux observations. D'abord, la plupart des charges de travail PostgreSQL volumineuses ont une structure de type série temporelle , c'est-à-dire qu'ils nécessitent beaucoup d'ajouts (par opposition à de nombreuses mises à jour) avec une clé principale vaguement séquentielle, telle qu'un horodatage ou un ID d'événement en série. Deuxièmement, ces ensembles de données sont régulièrement interrogés via des analyses ou des cumuls, et pas seulement des requêtes ponctuelles. Forts de ces observations, nous avons conçu une nouvelle fonctionnalité de stockage en colonnes pour TimescaleDB (que nous aborderons en détail dans la section suivante) qui nous permet d'atteindre des niveaux de compressibilité inégalés.

En fait, cette transformation ligne en colonne n'a pas besoin d'être appliquée à l'ensemble de votre base de données. En tant qu'utilisateur de Timescale, vous pouvez transformer vos tables PostgreSQL en magasins hybrides lignes-colonnes, en sélectionnant exactement les données à compresser sous forme de colonnes. via notre API simple et bénéficiant des deux architectures de stockage selon les besoins de votre application.

Illustrons comment cela fonctionne pratiquement avec un exemple. Imaginez un système de surveillance de la température collectant des relevés chaque seconde à partir de plusieurs appareils, stockant des données telles que l'horodatage, l'ID de l'appareil, le code d'état et la température.

Pour accéder efficacement aux données de température les plus récentes, en particulier pour les requêtes opérationnelles où vous souhaiterez peut-être analyser les dernières lectures de différents appareils, vous pouvez conserver les données les plus récentes (par exemple, la semaine dernière) dans la structure PostgreSQL traditionnelle non compressée et orientée lignes. . Cela prend en charge des taux d'ingestion élevés et est également idéal pour les requêtes ponctuelles sur des données récentes :

 -- Find the most recent data from a specific device SELECT * FROM temperature_data WHERE device_id = 'A' ORDER BY timestamp DESC LIMIT 1; -- Find all devices in the past hour that are above a temperature threshold SELECT DISTINCT device_id, MAX(temperature) FROM temperature WHERE timestamp > NOW() - INTERVAL '1 hour'   AND temperature > 40.0;

Mais une fois que ces données datent de quelques jours, les requêtes superficielles et larges comme la précédente ne sont plus fréquemment exécutées : au lieu de cela, les requêtes analytiques approfondies et étroites sont plus courantes. Ainsi, pour améliorer l'efficacité du stockage et les performances des requêtes pour ce type de requêtes, vous pouvez choisir automatiquement de transformer toutes les données datant de plus d'une semaine dans un format en colonnes hautement compressé. Pour ce faire dans Timescale, vous devez définir une politique de compression telle que celle-ci :

 -- Add a compression policy to compress temperature data older than 1 week SELECT add_compression_policy('temperature_data', INTERVAL '7 days');

Une fois vos données compressées, l’exécution de requêtes analytiques approfondies et précises sur les données de température (que ce soit sur un appareil spécifique ou sur plusieurs appareils) afficherait des performances de requête optimales.

 -- Find daily max temperature for a specific device across past year SELECT time_bucket('1 day', timestamp) AS day, MAX(temperature) FROM temperature_data WHERE timestamp > NOW() - INTERVAL '1 year'   AND device_id = 'A' ORDER BY day; -- Find monthly average temperatures across all devices SELECT device_id, time_bucket('1 month', timestamp) AS month, AVG(temperature) FROM temperature_data WHERE timestamp < NOW() - INTERVAL '2 weeks' GROUP BY device_id, month ORDER BY month;

Comment représenter le « passage » d’un format de ligne à un format de colonne ? Les hypertables de Timescale servent à partitionner les données en « morceaux » en fonction d'une clé de partitionnement, telle qu'un horodatage ou une autre colonne d'ID de série. Chaque morceau stocke ensuite les enregistrements correspondant à une certaine plage d'horodatages ou d'autres valeurs pour cette clé de partitionnement. Dans l'exemple ci-dessus, les données de température seraient partitionnées par semaine afin que le dernier morceau reste au format ligne et que toutes les semaines les plus anciennes soient converties au format colonne.

Ce moteur de stockage hybride ligne-colonne est un outil incroyablement puissant pour optimiser les performances des requêtes dans les grandes bases de données PostgreSQL tout en réduisant considérablement l'empreinte de stockage. Comme nous le verrons plus loin dans cet article, en transformant les données au format en colonnes et en appliquant des mécanismes de compression spécialisés, nous sommes non seulement en mesure d'accélérer vos requêtes analytiques, mais nous atteignons également des taux de compression allant jusqu'à 98 %. Imaginez ce que cela fait à votre facture de stockage !

En coulisses : des données de ligne aux tableaux en colonnes compressés

Avant d'entrer dans les détails sur les performances des requêtes et les économies de stockage, voyons d'abord comment ce mécanisme fonctionne sous le capot : comment la transformation des lignes en colonnes est réellement effectuée et comment la compression est appliquée aux données en colonnes.

Lorsque la politique de compression entre en vigueur, elle transforme essentiellement ce qui était traditionnellement de nombreux enregistrements individuels dans l'hypertable PostgreSQL d'origine (imaginez 1 000 lignes densément remplies) en une structure de lignes singulière et plus compacte. Dans cette forme compactée, chaque attribut ou colonne ne stocke plus les entrées singulières de chaque ligne. Au lieu de cela, il encapsule une séquence continue et ordonnée de toutes les valeurs correspondantes de ces 1 000 lignes. Appelons ces 1 000 lignes un lot .

Pour illustrer cela, imaginons un tableau comme celui-ci :

 | Timestamp | Device ID | Status Code | Temperature | |-----------|-----------|-------------|-------------| | 12:00:01 | A | 0 | 70.11 | | 12:00:01 | B | 0 | 69.70 | | 12:00:02 | A | 0 | 70.12 | | 12:00:02 | B | 0 | 69.69 | | 12:00:03 | A | 0 | 70.14 | | 12:00:03 | B | 4 | 69.70 |

Pour préparer ces données à la compression, Timescale transformerait d'abord ces données tabulaires en un magasin en colonnes. Étant donné un lot de données (~ 1 000 lignes), les données de chaque colonne sont regroupées dans un tableau, chaque élément du tableau correspondant à la valeur de l'une des lignes d'origine. Le processus aboutit à une seule ligne, chaque colonne stockant un tableau de valeurs de ce lot.

 | Timestamp | Device ID | Status Code | Temperature | |------------------------------|--------------------|--------------------|-------------------------------| | [12:00:01, 12:00:01, 12...] | [A, B, A, B, A, B] | [0, 0, 0, 0, 0, 4] | [70.11, 69.70, 70.12, 69....] |

Avant même d'appliquer des algorithmes de compression, ce format économise immédiatement du stockage en réduisant considérablement la surcharge interne par ligne de Timescale. PostgreSQL ajoute généralement environ 27 octets de surcharge par ligne (par exemple, pour le contrôle de concurrence multiversion ou MVCC). Ainsi, même sans aucune compression, si notre schéma ci-dessus fait, disons, 32 octets, alors les 1 000 lignes de données d'un lot qui prenaient auparavant [1 000 * (32 + 27)] ~= 59 kilo-octets prennent désormais [1 000 * 32 + 27 ] ~= 32 kilo-octets dans ce format.

[ À part : Cette notion de « regrouper » une table plus grande en lots plus petits puis de stocker les colonnes de chaque lot de manière contiguë (plutôt que celles de la table entière) est en fait une approche similaire aux « groupes de lignes » dans le format de fichier Apache Parquet. Même si nous n’avons réalisé cette similitude qu’après coup !]

Mais le gros avantage de cette transformation est que désormais, étant donné un format dans lequel des données similaires (horodatages, identifiants de périphérique, relevés de température, etc.) sont stockées de manière contiguë, nous pouvons utiliser des algorithmes de compression spécifiques au type afin que chaque tableau soit compressé séparément. . C'est ainsi que Timescale atteint des taux de compression impressionnants.

Timescale utilise automatiquement les algorithmes de compression suivants. Tous ces algorithmes sont « sans perte , » pour ne pas gâcher la précision ni introduire d'inexactitudes via notre compression ; toute décompression résultante reconstruit parfaitement les valeurs originales.

• Compression des gorilles pour flotteurs

  
  

• Delta-de-delta + Simple-8b avec codage en longueur compression pour les horodatages et autres types de type entier

  
  

• Compression de dictionnaire sur une ligne entière pour les colonnes avec quelques valeurs répétitives (+ compression LZ en haut)

  
  

• Compression de tableau basée sur LZ pour tous les autres types

  
  

Nous avons étendu Gorilla et Simple-8b pour gérer la décompression des données dans l'ordre inverse, ce qui nous permet d'accélérer les requêtes utilisant des analyses arrière.

Nous avons trouvé cette compression spécifique à un type assez puissante : en plus d'une compressibilité plus élevée, certaines techniques comme Gorilla et delta-of-delta peuvent être jusqu'à 40 fois plus rapides que la compression basée sur LZ lors du décodage, conduisant à des performances de requête bien améliorées. .

Lors de la décompression des données, Timescale peut opérer sur ces lots compressés individuels, en les décompressant lot par lot et uniquement sur les colonnes demandées. Ainsi, si le moteur de requête peut déterminer que seuls 20 lots (correspondant à 20 000 lignes de données d'origine) doivent être traités à partir d'un morceau de table qui comprenait à l'origine un million de lignes de données, alors la requête peut s'exécuter beaucoup plus rapidement, car elle lit et décompresse. beaucoup moins de données. Voyons comment il fait cela.

Interroger efficacement les données compressées

Le format précédent basé sur des tableaux présente un défi : à savoir, quelles lignes la base de données doit-elle récupérer et décompresser pour résoudre une requête ?

Reprenons notre exemple de données de température. Plusieurs types naturels de requêtes émergent encore et encore : la sélection et le classement des données par plages horaires ou la sélection des données en fonction de leur identifiant d'appareil (soit dans la clause WHERE, soit via un GROUP BY). Comment pourrions-nous répondre efficacement à de telles requêtes ?

Désormais, si nous avons besoin de données du dernier jour, la requête doit parcourir les données d'horodatage, qui font désormais partie d'un tableau compressé. Alors, la base de données devrait-elle décompresser des morceaux entiers (ou même l'hypertable entière) pour localiser les données de la journée récente ?

Ou même si nous pouvions identifier les « lots » individuels regroupés dans un tableau compressé (décrit ci-dessus), les données de différents appareils sont-elles entrecoupées, nous devons donc décompresser l'ensemble du tableau pour savoir s'il inclut des données sur un appareil spécifique ? Même si cette approche plus simple pourrait toujours donner une bonne compressibilité, elle ne serait pas aussi efficace du point de vue des performances des requêtes.

Pour résoudre le défi de la localisation et de la décompression efficaces des données pour des requêtes spécifiques dans leur format en colonnes, Timescale introduit le concept de colonnes « segmenter par » et « ordonner par » .

Regroupement des données fréquemment interrogées par colonne segmentby

Rappelez-vous que les données dans Timescale sont initialement converties sous forme de colonnes compressées morceau par morceau. Pour améliorer l'efficacité des requêtes qui filtrent en fonction d'une colonne spécifique (par exemple, des requêtes fréquentes par device_id ), vous avez la possibilité de définir cette colonne particulière comme « compress_segmentby " colonne. Cette approche est très bénéfique pour organiser les données compressées.

Ces colonnes segmentby sont utilisées pour partitionner logiquement les données dans chaque bloc compressé. Plutôt que de créer un tableau compressé de valeurs arbitraires comme indiqué ci-dessus, le moteur de compression regroupe d'abord toutes les valeurs qui ont la même clé segmentby .

Ainsi, 1 000 lignes de données sur le périphérique_id A sont sauvegardées de manière dense avant d'être stockées dans une seule ligne compressée, 1 000 lignes sur le périphérique_id B, et ainsi de suite. Ainsi, si device_id est choisi comme colonne segmentby , chaque ligne compressée comprend des lots de données en colonnes compressées sur un ID de périphérique spécifique, qui sont stockés non compressés dans cette ligne. Timescale crée en outre un index sur ces valeurs segmentées dans le morceau compressé.

 | Device ID | Timestamp | Status Code | Temperature | |-----------|--------------------------------|-------------|-----------------------| | A | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 0] | [70.11, 70.12, 70.14] | | B | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 4] | [69.70, 69.69, 69.70] |

Ce stockage contigu des données améliore considérablement l'efficacité des requêtes filtrées par la colonne segmentby . Lors de l'exécution d'une requête filtrée par device_id où device_id est la colonne segmentby , Timescale peut sélectionner rapidement (via un index) toutes les lignes compressées du bloc qui ont le ou les ID de périphérique spécifiés, et il ignore rapidement les données (et évite de décompresser ) données sans rapport avec les appareils demandés.

Par exemple, dans cette requête, Timescale localisera et traitera efficacement uniquement les lignes compressées contenant des données pour le device_id A :

 SELECT AVG(temperature) FROM sensor_data WHERE device_id = 'A'   AND time >= '2023-01-01'   AND time < '2023-02-01';

De plus, les hypertables Timescale stockent les métadonnées associées à chaque morceau spécifiant la plage de valeurs couverte par le morceau. Ainsi, si une hypertable est partitionnée par horodatage par semaine, lorsque le planificateur de requêtes exécute la requête ci-dessus, il sait qu'il ne doit traiter que les 4 à 5 morceaux couvrant le mois de janvier, améliorant ainsi encore les performances des requêtes.

Définir des colonnes segmentby

Vous pouvez spécifier les colonnes à utiliser pour segmentby lors de la première activation de la compression d'une hypertable. Le choix de la colonne à utiliser doit être basé sur la ou les colonnes souvent utilisées dans vos requêtes. En fait, vous pouvez utiliser plusieurs colonnes pour segmenter par : par exemple, plutôt que de regrouper les lots par device_id, vous pouvez (par exemple) regrouper les lots qui ont à la fois les mêmes tenant_id et device_id.

Attention néanmoins à ne pas exagérer la sélectivité : définir trop de colonnes segmentby diminuera l'efficacité de la compression puisque chaque colonne segmentby supplémentaire divise effectivement les données en lots de plus en plus petits.

Si vous ne pouvez plus créer 1 000 lots d'enregistrements de données, mais que vous n'avez que cinq enregistrements comportant les clés de segmentation spécifiées dans un bloc spécifique, la compression ne sera pas bonne du tout !

Mais une fois que vous avez identifié les colonnes sur lesquelles vous souhaitez segmenter, elles sont simples à configurer lors de l'activation de la compression dans votre hypertable :

 ALTER TABLE temperature_data SET (   timescaledb.compress,   timescaledb.compress_segmentby = 'device_id' );

Ajustement avancé via orderby

TimescaleDB améliore les performances des requêtes sur les données compressées grâce à un classement stratégique des données au sein de chaque bloc, dicté par le paramètre compress_orderby . Bien que le paramètre par défaut de classement par horodatage (la clé de partitionnement typique dans les données de séries chronologiques) soit adapté à la plupart des scénarios, comprendre cette optimisation peut être utile. Poursuivez votre lecture pour une perspective technique encore plus approfondie.

Prenons à nouveau l'exemple de blocs hebdomadaires et d'une requête qui ne demande que des données sur une seule journée. Dans une table normale avec un index d'horodatage, la requête pourrait parcourir efficacement cet index pour trouver les données du jour.

Cependant, la situation est différente avec les données compressées : les horodatages sont compressés et ne sont pas accessibles sans décompresser des lots entiers. Créer un index sur chaque horodatage individuel serait contre-productif, car cela pourrait annuler les avantages de la compression en devenant excessivement volumineux.

Timescale résout ce problème en « triant » essentiellement les données à regrouper en fonction de leur horodatage. Il enregistre ensuite les métadonnées sur les horodatages minimum et maximum pour chaque lot. Lorsqu'une requête est exécutée, ces métadonnées permettent au moteur de requête d'identifier rapidement les lignes compressées (lots) pertinentes pour la plage horaire de la requête, réduisant ainsi le besoin d'une décompression complète.

Cette méthodologie joue bien avec l'utilisation de colonnes segmentées. Au cours du processus de compression, les données sont d'abord regroupées par colonne segmentby, puis triées en fonction du paramètre orderby, et enfin divisées en « mini-lots » plus petits, ordonnés par horodatage, contenant chacun jusqu'à 1 000 lignes.

La combinaison de la segmentation et du classement de TimescaleDB améliore considérablement les performances des requêtes analytiques et de séries chronologiques courantes. Cette optimisation à la fois dans le temps (via orderby ) et dans l'espace (via segmentby ) garantit que TimescaleDB gère et interroge efficacement les données de séries chronologiques à grande échelle, offrant un équilibre optimisé entre compression et accessibilité.

L'évolution de la compression de l'échelle de temps

La première version de notre conception de compression a été publiée en 2019 avec TimescaleDB 1.5 . De nombreuses versions plus tard, la compression Timescale a parcouru un long chemin.

L'une des principales limitations de notre version initiale était que nous n'autorisions aucune modification supplémentaire des données (par exemple, INSERT, UPDATE, DELETE) une fois les données compressées sans d'abord décompresser manuellement l'intégralité du bloc hypertable dans lequel elles résidaient.

Étant donné que nous optimisions pour les cas d'utilisation gourmands en données basés sur des données analytiques et de séries chronologiques, qui nécessitent principalement beaucoup d'insertions et non de mises à jour, cela représentait beaucoup moins de limitations que cela ne l'aurait été dans un cas d'utilisation OLTP traditionnel. où les données sont fréquemment mises à jour (par exemple, un tableau d'informations client). Cependant, comme nous en discutons dans cet article , il existe des situations dans lesquelles un remplissage est nécessaire, ce qui complique considérablement les pipelines de développeurs qui utilisaient TimescaleDB.

Une autre limitation de notre version de compression initiale était que nous n'autorisions pas les modifications de schéma dans les tables, y compris les données compressées. Cela signifiait que les développeurs ne pouvaient pas faire évoluer leur structure de données sans décompresser l'intégralité de la table, comme l'ajout de nouvelles colonnes pour s'adapter à de nouvelles métriques ou à de nouveaux appareils .

Aujourd’hui, toutes ces limitations sont supprimées. Timescale vous permet désormais d'effectuer des opérations complètes en langage de manipulation de données (DML) et en langage de définition de données (DDL) sur des données compressées :

• Vous pouvez INSÉRER des données sur des morceaux compressés (avec d'excellentes performances).

• Vous pouvez effectuer des UPDATE, UPSERT et DELETE.

  
  

• Vous pouvez ajouter des colonnes, y compris avec des valeurs par défaut.

  
  

• Vous pouvez renommer et supprimer des colonnes.

  
  

Pour automatiser la modification des données sur les données compressées (la rendant transparente pour nos utilisateurs), nous avons modifié notre approche de compression en introduisant une « zone de transit » — essentiellement, un morceau superposé qui reste non compressé et dans lequel nous effectuons les opérations « sur les données non compressées » sous la hotte.

En tant qu'utilisateur, vous n'avez rien à faire manuellement : vous pouvez modifier vos données directement pendant que notre moteur s'occupe automatiquement de tout en coulisses. La possibilité d'apporter des modifications aux données compressées rend le moteur de stockage hybride ligne-colonne de Timescale beaucoup plus flexible.

Cette conception via la zone de préparation rend les INSERT aussi rapides que l'insertion dans des morceaux non compressés puisque c'est réellement ce qui se passe (lorsque vous insérez dans un morceau compressé, vous écrivez maintenant dans la zone de préparation). Cela nous a également permis de prendre en charge directement les UPDATE, UPSERT et DELETE : lorsqu'une valeur doit être modifiée, le moteur déplace une partie pertinente des données compressées vers la zone de transit, la décompresse, effectue la modification et (de manière asynchrone) la déplace à nouveau. à la table principale sous sa forme compressée.

(Cette région de données fonctionne généralement à l'échelle des « mini-lots » compressés comprenant jusqu'à 1 000 valeurs qui constituent une « ligne » dans le stockage PostgreSQL sous-jacent afin de minimiser la quantité de données qui doivent être décompressées pour prendre en charge les modifications.)

Cette « zone de transit » a toujours une sémantique transactionnelle régulière, et vos requêtes voient ces valeurs dès qu'elles y sont insérées. En d’autres termes, le planificateur de requêtes est suffisamment intelligent pour comprendre comment interroger correctement ces blocs de « stade » basés sur les lignes et le stockage en colonnes régulier.

Le résultat final : des requêtes plus rapides, moins d'empreinte de stockage pour les grandes bases de données PostgreSQL

À ce stade, la prochaine question logique à poser est : quel est le résultat final ? Quel est l'impact de la compression sur les performances des requêtes et quelle taille de disque puis-je économiser en l'utilisant ?

Performances des requêtes avant et après la compression

Comme nous l'avons évoqué dans cet article, les magasins en colonnes ne fonctionnent généralement pas très bien pour les requêtes récupérant des lignes individuelles, mais ils ont tendance à bien mieux fonctionner pour les requêtes analytiques examinant des valeurs agrégées. C'est précisément ce que nous constatons dans Timescale : les requêtes approfondies et étroites impliquant des moyennes voient des améliorations significatives des performances lors de l'utilisation de la compression.

Illustrons cela en exécutant quelques requêtes sur le Ensemble de données sur les taxis de New York , l'un des exemples d'ensembles de données que nous fournissons dans Timescale. Cet ensemble de données contient des informations sur les trajets en taxi, notamment les heures de prise en charge et de dépose, les emplacements, les distances, les tarifs, etc.

Considérez la requête suivante, demandant le montant du tarif le plus élevé à partir d'un sous-ensemble de l'ensemble de données de taxi sur une période spécifique :

 SELECT max(fare_amount) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01';

Lorsqu'elle est exécutée sur l'ensemble de données non compressé, le temps d'exécution de la requête s'élève à 4,7 secondes. Nous utilisons un petit service de test non optimisé et interrogeons plusieurs millions de lignes, ces performances ne sont donc pas les meilleures. Mais après compression des données, le temps de réponse descend à 77,074 millisecondes :

Partageons un autre exemple. Cette requête compte le nombre de trajets avec un code tarifaire spécifique dans une période donnée :

 SELECT COUNT(*) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01' AND   "RatecodeID" = 99;

Lorsqu’elle est exécutée sur les données non compressées, cette requête prendrait 1,6 seconde. La même requête exécutée sur des données compressées se termine en seulement 18,953 millisecondes. Encore une fois, nous constatons une amélioration immédiate ! Ce ne sont que des exemples rapides, mais ils illustrent à quel point la compression peut être puissante pour accélérer vos requêtes.

Comment la compression de Timescale réduit la taille du stockage PostgreSQL : exemples concrets

N'oublions pas ce qui nous a amené ici en premier lieu : nous avions besoin d'une tactique qui nous permettrait de réduire la taille de nos grandes bases de données PostgreSQL afin de pouvoir faire évoluer PostgreSQL davantage. Pour montrer à quel point la compression Timescale peut être efficace pour cette tâche, le tableau ci-dessous comprend quelques exemples réels de taux de compression observés chez les clients Timescale .

Ces économies de stockage se traduisent directement par des économies d’argent : La plateforme Timescale utilise une tarification basée sur l'utilisation pour le stockage , donc si votre espace de stockage diminue, votre facture diminue également proportionnellement.

Le taux de compression que vous obtiendrez finalement dépend de plusieurs facteurs, notamment de votre type de données et de vos modèles d'accès. Mais comme vous pouvez le constater, la compression Timescale peut être extrêmement efficace : nous l'utilisons même largement en interne pour alimenter notre produit Insights destiné aux clients avec une compression de 96 % .

Notre équipe peut vous aider à affiner la compression pour vous faire économiser le plus d'argent possible, alors n'hésitez pas à nous contacter .

"Avec la compression, nous avons constaté en moyenne une réduction de 97 % [de la taille du disque]."

(Michael Gagliardo, Industriel)

« Nous avons trouvé le taux de compression de Timescale absolument phénoménal ! Nous sommes actuellement à un taux de compression supérieur à 26, ce qui réduit considérablement l'espace disque nécessaire pour stocker toutes nos données.

(Nicolas Quintin, Octave)

"La compression de Timescale était aussi bonne qu'annoncée, ce qui nous a permis d'économiser +90 % d'espace [disque] dans notre hypertable sous-jacente."

(Paolo Bergantino, Groupe METER)

Compression et stockage hiérarchisé : cycle de vie du stockage à grande échelle

Enfin, nous ne pouvions pas conclure cet article sans mentionner le stockage hiérarchisé de Timescale, que nous venons de lancer en disponibilité générale .

En plus de la compression, vous disposez désormais d'un autre outil pour vous aider à faire évoluer encore plus vos bases de données PostgreSQL dans la plateforme Timescale : vous pouvez hiérarchiser vos données plus anciennes et rarement consultées vers un niveau de stockage d'objets à faible coût tout en pouvant y accéder via le standard. SQL.

Ce niveau de stockage à faible coût a un prix forfaitaire de 0,021 $ par Go/mois pour les données (moins cher qu'Amazon S3), vous permettant de conserver plusieurs To dans votre base de données PostgreSQL pour une fraction du coût.

Voici comment notre backend de stockage hiérarchisé fonctionne sur la plate-forme Timescale et comment le niveau de stockage faible joue avec la compression :

• Vos données les plus récentes sont écrites dans un niveau de stockage hautes performances optimisé pour les requêtes rapides et les acquisitions élevées. Dans ce niveau, vous pouvez activer la compression en colonnes Timescale pour réduire la taille de votre base de données et accélérer vos requêtes analytiques, comme nous l'avons discuté dans cet article. Par exemple, vous pouvez définir une politique de compression qui compresse vos données après 1 semaine.

  
  

• Une fois que votre application n’accède plus fréquemment à ces données, vous pouvez automatiquement la hiérarchiser vers un niveau de stockage d’objets moins coûteux en configurant une stratégie de hiérarchisation. Les données du niveau de stockage à faible coût restent entièrement interrogeables dans votre base de données, et il n'y a aucune limite à la quantité de données que vous pouvez stocker : jusqu'à des centaines de To ou plus. Par exemple, vous pouvez définir une stratégie de hiérarchisation qui déplace toutes vos données datant de plus de six mois vers le niveau de stockage à faible coût.

  
  

• Une fois que vous n'avez plus besoin de conserver ces données dans votre base de données, vous pouvez les supprimer via une politique de rétention. Par exemple, vous pouvez supprimer toutes les données après cinq ans.

Restez avec PostgreSQL

Nous avons doté Postgres d'un mécanisme de compression de base de données efficace en ajoutant des fonctionnalités de compression en colonnes. Il s'agit d'une fonctionnalité essentielle pour faire évoluer les bases de données PostgreSQL dans le monde actuel à forte intensité de données : la compression permet d'énormes économies d'utilisation du disque (stockage de plus de données à moindre coût) et d'amélioration des performances (exécution de requêtes analytiques sur de gros volumes en quelques millisecondes).

La conception de compression de Timescale atteint des taux de compression impressionnants en combinant les meilleurs algorithmes de compression avec une nouvelle méthode pour créer un stockage hybride ligne/colonne au sein de PostgreSQL. Cette fonctionnalité rend l'empreinte de stockage de Timescale (et donc de PostgreSQL) comparable à celle des bases de données en colonnes personnalisées et plus limitées.

Mais contrairement à de nombreux moteurs en colonnes, Timescale prend en charge la sémantique transactionnelle ACID et la prise en charge directe des modifications (INSERT, UPDATE, UPSERT, DELETE) sur les données en colonnes compressées. L’ancien modèle « une base de données pour les charges de travail transactionnelles, une autre pour les analyses » étant obsolète, de nombreuses applications modernes exécutent des charges de travail qui correspondent aux deux modèles. Alors pourquoi conserver deux bases de données distinctes alors que vous pouvez tout faire dans PostgreSQL ?

Timescale vous permet de démarrer sur PostgreSQL, d'évoluer avec PostgreSQL, de rester avec PostgreSQL.

Créer un compte gratuit et essayez Timescale aujourd'hui : cela ne prend que quelques secondes, aucune carte de crédit n'est requise.

- Écrit par Carlota Soto et Mike Freedman .