Dans l’article précédent nous avons vu les principes du partitionnement ainsi que l’héritage. Ce second article traitera de la méthode déclarative ainsi que des améliorations qui y sont liées.
Partitionnement déclaratif (natif) :
Depuis la version 10, PostgreSQL propose le partitionnement déclaratif qui distribue automatiquement les données dans la bonne partition.
Il n’y a plus besoin de trigger, donc les performances sont meilleures.
Les versions majeures suivantes ont ajouté des fonctionnalités comme l’utilisation des Primary Key, Foreign Key, et d’autres.
Il existe plusieurs types de clé de partitionnement :
- RANGE : La table est partitionnée en intervalles définis par une colonne clé ou par un ensemble de colonnes, sans recouvrement entre les intervalles
- LIST : La table est partitionnée en listant explicitement les valeurs clés qui apparaissent dans chaque partition
- HASH : ajouté en 11 : Permet de répartir les données équitablement sur les partitions sans classement particulier
- REFERENCE : non supporté
Une table partitionnée ne peut pas contenir de données, elles sont stockées dans les partitions.
La table partitionnée racine est liée à des partitions ou des tables partitionnées (sous partitionnement) elles-mêmes liées à des partitions.
Une partition est une table attachée à une table partitionnée.
Une table partitionnée ne peut pas devenir une table et inversement.
Une partition peut devenir une table et inversement.
Depuis la 11, les index et contraintes créés sur la table partitionnée sont répliqués sur toutes les partitions et tables partitionnées de l’arbre.
Le partitionnement par RANGE permet d’utiliser des clés de partitionnement multicolonnes.
Exemple d’arbre de partition :
La table T9 est la table partitionnée racine.
La table T9_3 est une table partitionnée (cas de sous-partition).
Les autres tables sont des partitions.
Le choix de la clé de partitionnement dépend du fonctionnement de l’application.
Attention : un mauvais choix de partitionnement (type, choix de colonne, …) est pire que de ne pas utiliser de partitionnement. Il est donc impératif de faire des tests pour valider le partitionnement choisi !
Les mots clés “minvalue” et “maxvalue” peuvent être utilisés pour borner les partitions de type RANGE.
Depuis la version 11, il est possible de créer une partition par défaut, qui contiendra toutes les données qui ne peuvent allez dans les autres partitions.
Les avantages sont :
- Gros gain de performance
- Maintenance simplifiée (plus de trigger, table et index plus petit, sauvegarde possiblement plus rapide)
- Les index UNIQUE (11), les clés primaire (11) et étrangère (12) sont gérées
- Accepte les données “hors partition” : default partition et minvalue/maxvalue (11)
- Possibilité d’utiliser des foreign patitions1:
- Cela permet l’utilisation du pushdown2 dans le cas de l’utilisation de postgresql_fdw
- Pas de création d’index depuis l’instance locale sur la partition
1 Déconseillé avant la 12, sauf si la Foreign Partition est en Read Only 2 Pushdown : l'instance locale demande à l'instance distante des faire des actions comme le tri, certaines jointures avant de retourner les données à l'instance locale
Les limites :
- Pas de création automatique des partitions
- Discussion en cours dans ce sens
- Sauf avec extension ou script
- Vous ne pouvez pas créer de nouvelle partition si une partition contient des données qui devraient maintenant être dans la nouvelle partition
- Il faut d’abord créer une table, y déplacer les données (en les supprimant de la partition actuelle), puis attacher la table à la table partitionnée
- Les tables partitionnées (parentes) ne peuvent pas contenir de données, les données sont obligatoirement dans les partitions
- Pas d’écriture possible dans la foreign partition en 10
- Pas d’autoanalyze (autovacuum) car la table racine ne contient pas de données et donc elle n’arrive jamais au nombre de lignes modifiées déclenchant l’autovacuum
- Pas d’index globaux
- En version 10, les tables partitionnées ne pouvaient pas avoir d’index, ce qui empêchait la création de PK, FK, UK
- En version 10, il faut créer les index partition par partition
Matrice de fonctionnalités héritage / déclaratif :
| FEATURE | 9.6 | 10 | 11 | 12 |
|---|---|---|---|---|
| Création automatique des index/contraintes sur les tables filles | NON | NON | OUI | OUI |
| Partitionnement déclaratif | NON | OUI | OUI | OUI |
| Routage automatique de ligne – INSERT | NON | OUI | OUI | OUI |
| Routage automatique de ligne – UPDATE3 | NON | NON | OUI | OUI |
| Unique index / Primary key | NON | NON | OUI4 | OUI4 |
| Foreign keys | NON | NON | OUI5 | OUI |
| Default Partitions | OUI6 | NON | OUI | OUI |
| Parallel Partition Scans | NON | NON | OUI | OUI |
| Foreign partitions | NON | NON | OUI | OUI |
| Routage automatique de ligne – UPDATE avec Foreign table : | Foreign table en read only | |||
| Table locale vers foreign table | NON | OUI | OUI | |
| Foreign table vers table locale | NON | NON7 | OUI |
3 UPDATE nécessitant le changement de partition 4 L’index/PK doit contenir toutes les colonnes de la clé de partitionnement 5 Une table partitionnée ne peut faire référence que à une table non partitionnée 6 Dans la table parente ou via le trigger 7 Contournement : nécessite le couple DELETE + INSERT
Amélioration des performances :
Parallélisme :
Depuis la version 9.6, le moteur intègre la parallélisation pour différentes opérations (uniquement en lecture). Depuis, chaque version majeure l’étend à de nouveaux types de requêtes.
Parmi lesquels on peut trouver :
- Tris sur disque : Sort Method: external merge
- Table : Parallel Seq Scan
- Index : Index Scan, Bitmap heap scan, Index Only Scan
- Jointures : Nested-loop, merge join, parallel hash join
- Agrégation : count, sum, …
- Union d’ensembles : Append
Depuis la version 12, tous les niveaux d’isolations permettent de paralléliser les requêtes.
Partition Pruning :
Le partition pruning consiste à exclure les partitions inutiles. Postgres s’appuie sur les contraintes d’exclusion pour écarter des partitions à la planification.
L’algorithme n’a pas été prévu pour gérer un nombre important de partitions. La version 11 intègre un nouvel algorithme de recherche bien plus performant : Faster Partition Pruning. Il est effectif sur les requêtes SELECT en 11, INSERT et COPY en 12.
Le moteur ne pouvait exclure des partitions que lors de la planification, depuis la 11 il peut aussi exclure lors de l’exécution : c’est le Runtime Partition Pruning.
| Partition Pruning | 9.6 | 10 | 11 | 12 |
|---|---|---|---|---|
| Optimizer Partition Elimination | OUI8 | OUI8 | OUI | OUI |
| Executor Partition Elimination | NON | NON | OUI9 | OUI |
8 Utilise les contraintes d’exclusion 9 Uniquement sur les nœuds de type APPEND
| Faster Partition Pruning | 10 | 11 | 12 |
|---|---|---|---|
| SELECT | NON | OUI | OUI |
| INSERT | NON | NON | OUI |
| COPY | NON | NON | OUI |
Nouvelles fonctions :
Des clauses ont été ajoutées aux commandes “CREATE TABLE” et “ALTER TABLE”, notamment pour attacher (ATTACH PARTITION) et détacher (DETACH PARTITION) une partition.
Pour attacher une partition :
Alter table … attach partition … for values … ;
Cette commande permet d’attacher une table à une table partitionnée. Si la table parente à des index et/ou des contraintes, ils sont automatiquement créés sur la partition.
En 10 et 11, cette commande pose un verrou de niveau ACCESS EXCLUSIVE sur la table parente. Il est possible de contourner ce verrou en créant une contrainte de type CHECK sur la table à attacher décrivant la contrainte de partition désirée. Avec cette contrainte, la commande pose un verrou de niveau SHARE UPDATE EXCLUSIVE sur la table parente.
En 12, il n’y a plus de verrou sur la table parente.
Pour détacher une partition :
Alter table … detach partition … ;
Cette commande permet de détacher une partition, cette dernière conserve ses index et contraintes. Détacher une partition pose, très brievement, un verrou de type EXCLUSIVE sur la table parente.
Exemple de création / attach / detach pour RANGE, LIST, HASH :
-- Créer une table partitionnée par LIST CREATE TABLE t1 (c1 integer, c2 test) PARTITION BY LIST (c1) ; -- Créer une partition CREATE TABLE t1_1 PARTITION OF t1 FOR VALUES IN (1, 2, 3) ; -- Attacher une partition ALTER TABLE t1 ATTACH PARTITION t1_2 FOR VALUES IN (4, 5, 6) ;
-- Créer une table partitionnée par RANGE CREATE TABLE t2 (c1 integer, c2 test) PARTITION BY RANGE (c1) ; -- Créer une partition CREATE TABLE t2_1 PARTITION OF t2 FOR FROM (1) TO (100) ; -- Attacher une partition ALTER TABLE t2 ATTACH PARTITION t2_2 FOR FROM (100) TO (200) ;
-- Créer une table partitionnée par HASH CREATE TABLE t3 (c1 integer, c2 test) PARTITION BY HASH (c1) ; -- Créer une partition CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES WITH (modulus 2, remainder 0) ; CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES WITH (modulus 2, remainder 1) ;
-- Détacher une partition ALTER TABLE t1 DETACH PARTITION t1_2 ;
PostgreSQL 12 ajoute trois nouvelles fonctions permettant de simplifier l’accès à certaines informations concernant une table partitionnée.
- Pg_partition_tree(‘regclass’) : Liste des informations sur les tables et index dans un arbre de partition pour une table ou un index partitionné donné, avec une ligne par partition. Les informations fournies incluent le nom de la partition, le nom de son parent immédiat, une valeur booléenne indiquant si la partition est une feuille et un entier indiquant son niveau dans la hiérarchie. La valeur du niveau commence à 0 pour la table ou l’index en entrée dans son rôle de racine de l’arbre de partition, 1 pour ses partitions, 2 pour leurs partitions, et ainsi de suite (par rapport à regclass)
- Pg_partition_ancestors(‘regclass’) : Liste les relations ancêtres de la partition donnée, en incluant la partition elle-même
- Pg_partition_root(‘regclass’) : Renvoie la racine d’un arbre de partitionnement auquel la partition indiquée appartient
Exemple :
select * from pg_partition_ancestors('t9_3_1');
relid
--------
t9_3_1
t9_3
t9
select * from pg_partition_root('t9_3_1');
pg_partition_root
------------------
t9
select * from pg_partition_tree('t9_3');
relid | parentrelid | isleaf | level
--------+-------------+--------+-------
t9_3 | t9 | f | 0
t9_3_1 | t9_3 | t | 1
t9_3_2 | t9_3 | t | 1
select * from pg_partition_tree('t9');
relid | parentrelid | isleaf | level
--------+-------------+--------+-------
t9 | | f | 0
t9_1 | t9 | t | 1
t9_2 | t9 | t | 1
t9_3 | t9 | f | 1
t9_3_1 | t9_3 | t | 2
t9_3_2 | t9_3 | t | 2
Wanted / Missing :
Malgré toutes ces évolutions, il manque encore quelques améliorations :
- Création automatique de nouvelles partitions (type range)
- Pas de création de partition concernant les données déjà existante (default partition ou avec minvalue/maxvalue)
- Pas d’autoanalyze
- Pas d’index globaux
Conclusion :
Le partitionnement est une grosse évolution pour gérer des bases de forte volumétrie. Il permet de gros gains de performance aussi bien en lecture/écriture, en termes de maintenance et aussi de la gestion de la rétention des données (archivage, suppression, répartition sur plusieurs tablespaces, …).
Il manque encore quelques fonctionnalités pour que tout cela soit totalement transparent en termes d’administration (autoanalyze, création automatique des partitions, …). Ces manques seront corrigés dans les prochaines versions majeures.
Il est recommandé d’utiliser la version 11 ou 12 de PostgreSQL pour utiliser le partitionnement. La version 10 à de grosses limitations, qui ont été corrigées dans les versions suivantes.
Continuez votre lecture sur le blog :
- PostgreSQL : évolution du partitionnement de 9.6 à 12 (1/2) (Capdata team) [PostgreSQL]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
- Partitionnement sémantique (Capdata team) [Sybase]
- PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
- Oracle 19c : Les partitions hybrides (Emmanuel RAMI) [Oracle]