0

PostgreSQL : évolution du partitionnement de 9.6 à 12 (1/2)

twitterlinkedinmail

Le PostgreSQL Global Development Group (PGDG) vient d’annoncer la sortie de PostgreSQL 12 qui apporte de grosse amélioration sur le partitionnement. C’est l’occasion pour faire une présentation de l’évolution du partitionnement dans PostgreSQL de la version 9.6 à la version 12.

Dans cette première partie, je parlerais du principe de partitionnement, ainsi que de la méthode par héritage. La seconde partie traitera du partitionnement déclaratif et des amélioration liées.

Présentation du partitionnement :

Le partitionnement est le fait de diviser d’une table volumineuse en plusieurs plus petites, dans le but d’améliorer les performances.

Le partitionnement est une réponse à un problème de performance :

  • Soit liée en termes de Lecture / Ecriture
  • Soit en termes de maintenance (reindex, vacuum, sauvegarde, …)
  • Soit les deux

Il permet aussi de gérer simplement un large historique de données :

  • Supprimer des données obsolètes avec drop table après un detach plutôt que des millions de lignes à supprimer
  • Permet d’insérer en masse des données, avant un attach à la table parent

Cependant, il existe des contre-indications au partitionnement.
Il ne faut pas partitionner lorsque le problème de performance peut être résolu avec une autre solution :

  • Des index partiels utilisant les filtres des requêtes
  • CREATE INDEX ON users(login)
     WHERE date_part('year', create_date) = 2016;
  • Des index couvrants pour favoriser le parcours d’index seul
  • CREATE INDEX ON users(login, create_date)
     WHERE date_part('year', create_date) = 2016 ;
  • Des index BRIN ou des index BLOOM

Les principaux bénéfices du partitionnement sont les suivants :

  • Réduction de la taille des index
    • Un index par partition, les index tiennent en mémoire
  • Réduction de la taille des tables
    • Segmentation sur des critères utiles aux requêtes
    • Accès séquentiels privilégiés sur des volumes bien plus faibles
    • Les partitions tiennent en mémoire
  • Chargement de données plus rapide
    • Mise à jour d’index peu volumineux
    • Dispersion des partitions sur des disques ou serveurs différents
  • Purge des données par DROP TABLE plutôt que des DELETE lents
  • Taches de maintenance plus rapides
    • Reindex : les index sont locaux à chaque partition
    • Sauvegarde : des données statiques peuvent être omises lors de la sauvegarde après archivage

Pour ce dernier point, il y a plusieurs impactes en fonctions du type de sauvegarde :

  • Les données obsolètes peuvent être supprimées et donc moins de volumétrie à sauvegarder
  • Les sauvegardes physiques de type différentielle ou incrémentales devraient toucher moins de segments
  • Les exports peuvent omettre des partitions de données statiques qui ont déjà été sauvegardées, par exemple en cas d’archivage
  • Les partitions étant considérées comme des tables, l’export utilisera un process par partition au lieu d’un process pour la table si elle n’est pas partitionnée

Les principaux objectifs du partitionnement sont :

  • Archivage de données
  • Purge de données
  • Performances
  • Facilité d’administration (maintenance des partitions plus rapide)
  • Accélération des backups
  • Stockage sur différents types de disques

Partitionnement par héritage :

Avant de parler de partitionnement par héritage, il faut d’abord définir ce qu’est l’héritage en base de données.
Le principe est le même principe qu’en développement. Une table hérite des caractéristiques de la table source, mais pas de ses données. Elle peut aussi avoir plus de colonne que la table d’origine. L’héritage multiple est possible.
Un SELECT sur la table source ramènera aussi les données contenues dans la ou les tables héritée(s).

Le partitionnement par héritage est un détournement de ce fonctionnement. Une table parente est héritée par plusieurs tables (partitions).
Les actions (INSERT, UPDATE, DELETE) sur la table parente sont interceptées par des triggers qui déclenchent des fonctions qui réalisent les actions sur la bonne partition.
Il faut aussi mettre des contraintes CHECK sur les partitions pour garantir que les données insérées sont dans la bonne partition.

Ces fonctions peuvent intégrer la création de nouvelles partitions ainsi que la création des index associés.
Attention : plus ces fonctions sont complexes, plus les performances seront réduites.

Les avantages de cette méthode sont les suivantes :

  • La table parente peut contenir des données
  • Partitions par défaut supportées (via le trigger)
  • Gestion des foreign tables via Foreign Data Wrapper
  • Écriture dans les foreign tables (contrairement au partitionnement natif en 10)
  • Sous partitions possibles
  • Modification sur la clé de partitionnement par triggers (UPDATE)

Elle comporte aussi des inconvénients :

  • Lenteurs en raison des triggers
  • Toute “l’intelligence” se trouve dans les triggers
  • Administration compliquée même si des extensions la simplifie comme pg_partman
  • Impossible d’ajouter/supprimer une partition sans modifier le trigger, sauf si le trigger d’insertion gère la création des partitions/index
  • Les index doivent être créés manuellement sur chaque partition ou via le trigger d’insertion (qui ferait création de partition + création d’index sur la partition)
  • Si VACUUM ou ANALYZE sont lancés manuellement, il est obligatoire de les utiliser sur chaque table fille

Conclusion partie 1/2 :

Dans cette première partie, nous avons vu les principes du partitionnement ainsi que le partitionnement par héritage. Dans la seconde partie, nous regarderons le partitionnement déclaratif et toutes les évolutions liées (évolution des performances, …).

Continuez votre lecture sur le blog :

twitterlinkedinmail

Rémi VIDIER

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.