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;CREATE INDEX ON users(login, create_date)
WHERE date_part('year', create_date) = 2016 ;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 :
- PostgreSQL : évolution du partitionnement de 9.6 à 12 (2/2) (Capdata team) [PostgreSQL]
- Partitionnement sémantique (Capdata team) [Sybase]
- Oracle 19c : Les partitions hybrides (Emmanuel RAMI) [Oracle]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
- Verrous sur INSERT IGNORE en mode d’isolation par défaut (David Baffaleuf) [MySQL]