Stratégies de réduction de la taille des bases de données dans PostgreSQL : une approche basée sur l'utilisation

Les modèles de tarification des bases de données sont difficiles. En tant que développeur à la recherche d'une base de données gérée, l'un des aspects les plus ennuyeux (et pourtant cruciaux) du processus de recherche consiste à évaluer non seulement le prix initial de la solution en fonction de la taille de votre base de données, mais également le fonctionnement de la tarification et son évolution. .

En plus des nuances lors de l'évaluation des prix des bases de données (par exemple, « De combien la facture augmente-t-elle à mesure que mes données augmentent ? », « Suis-je facturé par écriture ou par lecture ? » et « Dois-je payer plus par sauvegarde ? » ), les développeurs ont tendance à négliger un aspect : la façon dont le modèle de tarification d'une base de données est structuré influencera la façon dont vous gérez vos données et évaluez la taille de votre base de données Postgres.

Différents modèles de tarification nécessitent différentes approches pour exécuter PostgreSQL. Par exemple, si vous êtes limité à un disque volumineux, vous n'aurez peut-être pas la priorité de réduire la taille de votre base de données. Si vous êtes facturé par donnée lue, vous pourriez réfléchir à deux fois avant d'exécuter certaines requêtes, et si vous êtes facturé par entrée de données, vous pouvez être prudent quant à la fréquence et au volume des données nouvellement ingérées.

Chaque élément de tarification influence subtilement les stratégies et les comportements que vous finirez par adopter, vous poussant vers une manière particulière de gérer et d'interagir avec votre base de données pour garantir à la fois une rentabilité et des performances optimales.

Chez Timescale, nous sommes passés il y a quelques mois à un modèle de stockage basé sur l'utilisation , avec d'excellents retours clients. Dans cet article de blog, nous explorerons les avantages opérationnels que nous avons observés chez nos clients depuis la transition vers ce modèle, ainsi que les tactiques permettant de gérer de manière optimale votre stockage PostgreSQL dans un modèle basé sur l'utilisation.

Les modèles de stockage basés sur l'utilisation sont de plus en plus populaires dans le monde des bases de données : qui veut payer pour de l'espace disque qu'il n'utilise pas ? Néanmoins, un modèle basé sur l'utilisation n'est pas sans conséquences, et vous devez prendre en compte certaines bonnes pratiques pour y naviguer efficacement.

Récapitulatif rapide sur les modèles de stockage PostgreSQL

Pour établir un terrain d'entente pour notre discussion sur la gestion de la taille de votre base de données dans un modèle basé sur l'utilisation, commençons par expliquer rapidement le fonctionnement de la tarification dans notre plateforme PostgreSQL ( Échelle de temps ) et comment il se compare à d'autres produits PostgreSQL gérés, comme Amazon RDS pour PostgreSQL et Amazon Aurora.

Depuis hier (💥), nous proposons deux types de services de bases de données dans la plateforme Timescale :

• Services PostgreSQL dynamiques , où les développeurs peuvent créer des bases de données PostgreSQL avec calcul dynamique pour économiser de l'argent sans sacrifier les performances.

  
  

• Services Time Series, où les développeurs peuvent créer des bases de données PostgreSQL améliorées avec des performances et une évolutivité supplémentaires via des hypertables, une compression en colonnes, agrégats continus et un stockage hiérarchisé.

  
  

Concentrons-nous sur la façon dont nous tarifons le stockage sur notre plateforme. Ces deux services sont accompagnés d'un modèle de stockage basé sur l'utilisation, ce qui signifie ce qui suit :

• Les développeurs sont facturés en fonction du volume qu'ils utilisent, sans petits caractères : pas de taille de base de données minimale, ni d'étapes de mise à l'échelle minimales. Si à la fin du mois vous avez utilisé 128 Go, c'est ce qui vous sera facturé. Vous pouvez commencer avec 1 Go et atteindre plusieurs To.

• Il n'y a aucun frais basé sur les données écrites, les transferts de données ou les requêtes exécutées.

• Pas besoin d'allouer du stockage lors de la création d'une base de données ou de la mise à l'échelle.

• Pas besoin de réduire manuellement la taille des disques.

  
  

Pour comprendre cela, exposons les différences entre ce modèle, Amazon RDS PostgreSQL et Amazon Aurora :

En résumé, voici les trois principaux points à retenir de notre comparaison :

• Timescale et Aurora facturent tous deux la taille réelle de votre base de données. En revanche, RDS PostgreSQL facture le stockage provisionné. Vous ne pouvez pas réduire les volumes dans RDS.

  
  

• Timescale ne facture pas les données écrites, les transferts de données ou les opérations de requête. RDS et Aurora facturent tous deux par transfert de données, un stockage de sauvegarde supplémentaire, et vous pourriez devoir payer des frais d'E/S supplémentaires, en fonction du type de stockage que vous choisissez.

  
  

• Timescale n'a pas d'étapes de mise à l'échelle minimales, tandis qu'Aurora évolue par incréments de 10 Go après avoir commencé avec 10 Go.

  
  

Comme vous pouvez le constater, le modèle de Timescale est plus proche de Aurora I/O optimisé , à la différence que Timescale ne dispose pas d'étapes de mise à l'échelle ni de frais supplémentaires pour des éléments tels que le transfert de données. En revanche, RDS est une bonne représentation du modèle basé sur l’allocation, même si RDS ne dispose pas des « niveaux de stockage » que l'on trouve traditionnellement chez les fournisseurs de bases de données fonctionnant sur ce modèle.

Comment la tarification basée sur l'utilisation améliore la gestion du stockage PostgreSQL

Comme nous l'avons introduit précédemment, différents modèles de tarification impliquent différentes expériences de base de données. Lorsque nous sommes passés d'un modèle basé sur l'allocation à un modèle basé sur l'utilisation, nos clients nous ont dit avoir constaté des améliorations immédiates dans trois domaines opérationnels :

• Ils n'avaient pas besoin de provisionner le stockage dès le démarrage d'une base de données, ce qui réduisait les erreurs de surprovisionnement et, par conséquent, réduisait les factures de stockage.
• Ils n’ont pas eu à penser au stockage lors de la mise à l’échelle.
• Ils pourraient réduire leur production : par exemple, s'ils supprimaient des données, ils cesseraient de payer pour cela.

Les modèles basés sur l'utilisation éliminent le problème de surprovisionnement du stockage

Dans les modèles traditionnels basés sur l'allocation, les développeurs se retrouvent souvent à prédire leurs besoins en stockage, ce qui conduit très souvent à un surprovisionnement du stockage. Nous avons observé cela sur l'ensemble de notre flotte lorsque Timescale fonctionnait sur un modèle basé sur l'utilisation : la majorité de nos clients n'utilisaient pas la totalité de leur capacité disque, ce qui signifie qu'ils payaient essentiellement pour l'espace de stockage qu'ils n'utilisaient pas encore. Les modèles basés sur l’utilisation éliminent ce jeu de devinettes (et les conséquences de mauvaises suppositions).

Vous n'avez pas besoin de penser au stockage lors de la mise à l'échelle

« Le stockage basé sur l'utilisation de Timescale signifie que je n'ai plus à penser à la taille du disque. Notre coût de stockage a baissé de 30 %, et je n'ai rien eu à faire !"

Adam McCrea, Échelle de judo (Client à l'échelle de temps)

Dans les modèles basés sur l'utilisation, le stockage évolue de manière transparente à mesure que votre base de données se développe. L'une des principales sources de stress dans les modèles traditionnels basés sur l'allocation est le risque de manque d'espace disque, ce qui peut entraîner de nombreux problèmes allant des temps d'arrêt des applications et des transactions perdues à la corruption des données dans les pires scénarios.

Avec les modèles basés sur l'utilisation, les développeurs n'ont plus besoin de surveiller avec vigilance leur stockage pour éviter de se heurter à un mur de stockage. Dans le même temps, ils n’ont pas à se soucier des étapes lourdes de mise à l’échelle automatique ou des sauts de niveau.

Vous pouvez réduire la taille (arrêter de payer pour les données que vous avez supprimées)

Enfin et surtout, les modèles basés sur l'utilisation vous permettent de « réduire l'échelle ». Si vous supprimez des données (ou parvenez à réduire considérablement la taille de votre base de données), vous commencez à payer moins par stockage, ce qui semble juste. Comme nous le verrons dans la section suivante, Timescale dispose de quelques fonctionnalités pour aider les clients à réduire la taille de leur base de données PostgreSQL. L'adoption d'un modèle basé sur l'utilisation a permis à nos clients de réaliser immédiatement des économies en réduisant l'utilisation du disque, ce qui les a incités à maintenir leur base de données légère.

Comment naviguer efficacement dans un modèle basé sur l'utilisation : conseils pour réduire la taille de votre base de données Postgres

Les avantages que nous venons de mentionner améliorent considérablement l'expérience des développeurs lorsqu'ils travaillent avec le stockage, c'est pourquoi les modèles basés sur l'utilisation deviennent très populaires. Mais la tarification basée sur l'utilisation a une conséquence évidente (mais profonde) : elle oblige les développeurs à adopter de bonnes pratiques en matière de données pour réduire autant que possible la taille de leur base de données PostgreSQL.

Lorsque vous savez que vos coûts de stockage sont directement liés à la taille du disque que vous utilisez réellement, vous êtes désormais incité à être plus judicieux en matière de stockage de données. Si vous travaillez selon un modèle de stockage basé sur l'utilisation, il vous incombe de vous assurer que vous stockez efficacement les données.

D’une certaine manière, cela peut être considéré comme un « inconvénient » des modèles basés sur l’utilisation, et cela nécessite un certain travail, mais c’est en réalité une bénédiction déguisée. Dans les modèles traditionnels basés sur l'allocation, l'hygiène des données peut être quelque peu négligée car les coûts sont fixes : si vous êtes limité à un disque de 500 Go en RDS et que votre base de données fait 200 Go, vous ne semblez pas vraiment incité à le faire. rendre l'utilisation du stockage efficace.

Mais voici le problème : les bonnes pratiques en matière de données ne consistent pas seulement à économiser de l'argent. Pour faire évoluer une base de données PostgreSQL, il est essentiel de la maintenir optimisée. En adoptant de bonnes pratiques de gestion de données PostgreSQL, vous obtiendrez non seulement de meilleures performances, mais votre vie en tant qu'administrateur de base de données deviendra beaucoup plus facile.

Dans cet esprit, passons en revue quelques pratiques qui vous aideront à naviguer aussi efficacement que possible dans un modèle de stockage basé sur l'utilisation, en réduisant systématiquement la taille de votre base de données PostgreSQL. Dans le cas particulier de Timescale, nous disposons de fonctionnalités particulièrement utiles, que nous aborderons également.

Réduisez le gonflement de votre base de données PostgreSQL

Une première chose à faire dans un modèle basé sur l'utilisation est de prêter attention aux spécificités du fonctionnement du stockage PostgreSQL, c'est-à-dire que vous devez réduire régulièrement le gonflement. Cela vous aidera non seulement avec la taille de votre base de données, mais également avec vos besoins en E/S. Nous vous indiquerons quelques notions de base dans cette section, mais ce fil de discussion HN contient de bons conseils , et nous avons également écrit un article de blog sur le gonflement des tables dans PostgreSQL .

• Surveillez les tuples morts. Une approche proactive pour optimiser le stockage PostgreSQL commence par surveiller de près les tuples morts. Les tuples morts, si rien n’est fait, peuvent s’accumuler et entraîner des frais de stockage inutiles. L'extension pgstattuple() peut être un allié précieux pour comprendre comment les tables gèrent ces tuples.

  
  

• Passez l’aspirateur fréquemment. Pour lutter efficacement contre le gonflement des tables, vous souhaiterez peut-être configurer l'autovacuum pour qu'il s'exécute plus fréquemment. Plutôt que d'ajuster globalement les paramètres d'autovacuum dans postgresql.conf, il est plus précis d'affiner ces paramètres table par table. Cela tient au fait que différentes tables peuvent avoir différentes tendances à accumuler des tuples morts. Il est également crucial de surveiller les transactions de longue durée susceptibles d’entraver les opérations d’autovacuum.

  
  

• Récupérez les pages inutilisées. Alors que l'autovacuum et le vide traitent les tuples morts, la récupération des pages inutilisées nécessite une approche différente. Bien que VACUUM FULL puisse être utilisé à cette fin, il présente la limitation inhérente au verrouillage de la table entière. Pg_repack peut vous aider avec cela.

Effectuez quelques réglages précis de PostgreSQL

La réduction systématique de la taille de votre base de données PostgreSQL est étroitement liée à la capacité de faire évoluer votre base de données PostgreSQL efficacement, c'est-à-dire de maintenir la rapidité et l'agilité tout en ajoutant de plus en plus de données. Garder un œil sur (et peut-être ajuster) certains paramètres clés de PostgreSQL peut aider. Cet article passe en revue les paramètres de performances les plus importants . Voici quelques aspects que vous devez considérer :

• shared_buffers : contrôle la mémoire utilisée pour le cache des pages de PostgreSQL, et elle est généralement définie sur 25 % de la RAM totale du système.

  
  

• work_mem : il définit la mémoire allouée par opération au sein d'une requête. Dans Timescale, le paramètre recommandé est (25 % of RAM) / max_connections .

  
  

• max_connections : il définit le nombre maximum de connexions simultanées autorisées à la base de données. Les poolers de connexions peuvent aider à gérer de nombreuses connexions de courte durée.

  
  

• max_worker_processes : il détermine le nombre maximum de processus de travail que PostgreSQL peut lancer. Si vous utilisez Timescale, la formule pour définir ce paramètre est : max_worker_processes = 3 + timescaledb.max_background_workers + max_parallel_workers .

  
  

• max_parallel_workers : il spécifie le nombre maximum de Workers prenant en charge les requêtes parallèles. La valeur par défaut est de définir cette valeur égale au nombre de processeurs disponibles.

  
  

• autovacuum_max_workers : il détermine le nombre maximum de processus autovacuum simultanés. Dans Timescale, il est défini sur 10 par défaut.

Pratiquer l’hygiène de l’indexation

L'optimisation régulière des index contribuera à maintenir l'efficacité de votre PostgreSQL, en particulier à mesure que la base de données évolue. Même si les index peuvent vous aider à améliorer les performances des requêtes lorsqu'ils sont utilisés à bon escient, des index excessifs peuvent créer des problèmes dans les grands déploiements PostgreSQL.

La conséquence la plus évidente d’une indexation excessive est une consommation accrue de stockage, car chaque index nécessite un stockage séparé, qui augmente proportionnellement à la taille des tables. Cette surcharge peut devenir plus importante lorsque les tables sont partitionnées, comme dans les hypertables de Timescale.

Les index inutiles peuvent également être contre-productifs dans l'amélioration de vos opérations d'écriture, car chaque nouvelle saisie ou modification de données implique des mises à jour simultanées de l'index, entraînant davantage d'opérations d'E/S et un gonflement potentiel des tables. La surveillance de vos index vous aidera à identifier ceux qui ne sont plus utilisés, garantissant ainsi la simplicité.

Une façon de procéder consiste à utiliser pg_stat_user_indexes :

 -- Retrieve indexes that might not be used or are infrequently used SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan AS times_used FROM pg_stat_user_indexes WHERE idx_scan = 0 OR idx_scan < some_low_threshold -- replace 'some_low_threshold' with a value that makes sense for your context ORDER BY idx_scan ASC, schemaname, relname;

Si un index a une valeur de 0 dans la colonne idx_scan, cela signifie qu'il n'a pas été utilisé depuis la dernière réinitialisation des statistiques, ce qui signifie qu'il pourrait être candidat à l'optimisation. En définissant un seuil bas pour idx_scan , vous pouvez également identifier les index rarement utilisés.

Réduisez vos grandes tables via la compression Timescale

L'une des fonctionnalités les plus remarquables de Timescale est sa prise en charge native de la compression en colonnes, qui peut réduire considérablement l'espace disque utilisé par les grandes tables sans compromettre les performances des requêtes. La compression améliore les performances de nombreuses requêtes.

La compression de Timescale fonctionne en convertissant les données régulières basées sur les lignes en un format de colonnes plus compact. Ce processus est particulièrement efficace pour les données de séries chronologiques, où de nombreuses colonnes peuvent contenir des valeurs répétitives ; nous pouvons atteindre des taux de compression impressionnants (+90 %) en appliquant différents algorithmes de compression en fonction de chaque type de données. Cela signifie que vos grandes tables peuvent être réduites jusqu'à 10x.

Dans Timescale, la compression est activée table par table via une politique de compression basée sur le temps. Par exemple, ce code permet la compression dans une hypertable particulière et compresse automatiquement les données datant de plus de deux semaines :

 -- Enable compression on the hypertable ALTER TABLE your_hypertable SET ( timescaledb.compress, timescaledb.compress_segmentby = 'some_column' ); -- Set a compression policy to compress data older than two weeks SELECT add_compression_policy('your_hypertable', INTERVAL '2 weeks');

Pour en savoir plus sur la compression, consultez notre documentation .

Mettez en place une stratégie de gestion des données : déplacez les données plus anciennes vers un stockage moins cher, supprimez les données dont vous n'avez plus besoin

Les tactiques précédentes sont très utiles pour réduire la taille de votre base de données, mais il existe une autre voie que vous pouvez exploiter dans Timescale pour faire évoluer efficacement votre stockage : le stockage hiérarchisé.

En créant une stratégie de hiérarchisation simple, vous pouvez déplacer les données plus anciennes et moins accessibles vers une couche de stockage d'objets sans fond :

 -- This policy moves all data older than one month to object storage SELECT add_tiering_policy('example', INTERVAL '1 month);

Ce magasin d'objets présente les caractéristiques suivantes :

• Son prix est inférieur à celui de notre stockage hautes performances, vous permettant de stocker plusieurs To de données pour beaucoup moins cher.
• C'est infini, ce qui signifie que vous pouvez stocker autant de données que vous le souhaitez.
  

Cette architecture de stockage hiérarchisée est ancrée dans le backend de Timescale. Le stockage objet n'est pas un « compartiment » détaché de votre base de données ; il en fait plutôt partie intégrante. Les requêtes accéderont de manière transparente aux deux couches de stockage sans aucune action de votre part : vous pouvez simplement continuer à utiliser le SQL standard sur le même schéma. Une fois votre politique de hiérarchisation définie, il n’y a plus rien d’autre à considérer !

Nous vous recommandons de déplacer les données vers la couche de stockage à faible coût une fois que votre application n'y accède pas fréquemment, car cela a un coût en termes de performances (le magasin d'objets n'est pas aussi rapide que notre stockage habituel). Mais vous pouvez continuer à exécuter confortablement des requêtes ad hoc sur ces données (par exemple, des requêtes qui ne sont pas critiques pour l'expérience utilisateur de vos clients et pour lesquelles des performances optimales ne sont pas essentielles).

En plus d'offrir cette couche de stockage à faible coût, Timescale facilite la mise en place de politiques de conservation des données pour supprimer les données dont vous n'avez plus besoin :

 -- Set a data retention policy to drop data older than 10 months SELECT add_retention_policy('your_hypertable', INTERVAL '10 months');

Vous pouvez combiner des politiques de conservation des données comme celle ci-dessus avec des agrégats continus pour sous-échantillonner efficacement votre ensemble de données, par exemple en réduisant la granularité d'une seconde à une minute, en supprimant les valeurs d'une seconde mais en conservant les agrégats d'une minute. Lisez cet article de blog pour voir un exemple de la façon de procéder et de gérer de manière proactive les données à long terme. .

Modèles basés sur l'utilisation et paradigme de gestion des données

Même si les modèles basés sur l'utilisation peuvent à première vue sembler n'être qu'un changement de prix, ils entraînent un changement de paradigme dans la façon dont les développeurs envisagent la taille de leur base de données et dans la manière dont ils perçoivent et gèrent les données.

Les modèles basés sur l'utilisation favorisent une culture d'amélioration continue, où l'accent passe de la simple gestion du stockage à la santé et à l'efficacité des bases de données. Cela nécessite une certaine discipline au début, mais une fois que votre état d'esprit aura changé et que vous aurez appris quelques techniques, vous serez au meilleur endroit pour faire évoluer votre base de données PostgreSQL de manière efficace et efficiente.

Timescale dispose d'outils précieux pour vous aider à réduire systématiquement la taille de votre base de données PostgreSQL, comme les politiques de compression, de stockage hiérarchisé et de conservation des données. Cela vous permet de faire évoluer efficacement vos bases de données PostgreSQL dans un modèle basé sur l'utilisation. Pour en faire l'expérience vous-même, inscrivez-vous à Timescale : vous pouvez l'utiliser gratuitement pendant les 30 premiers jours .

- Écrit par Carlota Sota .