0

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

twitterlinkedinmail

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 :

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.