![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Dans l’article pr\u00e9c\u00e9dent<\/a> nous avons vu les principes du partitionnement ainsi que l\u2019h\u00e9ritage. Ce second article traitera de la m\u00e9thode d\u00e9clarative ainsi que des am\u00e9liorations qui y sont li\u00e9es. <\/p>\n Depuis la version 10, PostgreSQL propose le partitionnement d\u00e9claratif qui distribue automatiquement les donn\u00e9es dans la bonne partition. Une table partitionn\u00e9e ne peut pas contenir de donn\u00e9es, elles sont stock\u00e9es dans les partitions. <\/p>\n La table partitionn\u00e9e racine est li\u00e9e \u00e0 des partitions ou des tables partitionn\u00e9es (sous partitionnement) elles-m\u00eames li\u00e9es \u00e0 des partitions. Le partitionnement par RANGE permet d\u2019utiliser des cl\u00e9s de partitionnement multicolonnes. <\/p>\n Exemple d\u2019arbre de partition : La table T9 est la table partitionn\u00e9e racine. Le choix de la cl\u00e9 de partitionnement d\u00e9pend du fonctionnement de l’application. Les avantages sont : <\/p>\n Les limites : <\/p>\n Matrice de fonctionnalit\u00e9s h\u00e9ritage \/ d\u00e9claratif : <\/p>\n Depuis la version 9.6, le moteur int\u00e8gre la parall\u00e9lisation pour diff\u00e9rentes op\u00e9rations (uniquement en lecture). Depuis, chaque version majeure l\u2019\u00e9tend \u00e0 de nouveaux types de requ\u00eates. Depuis la version 12, tous les niveaux d\u2019isolations permettent de parall\u00e9liser les requ\u00eates. <\/p>\n Le partition pruning consiste \u00e0 exclure les partitions inutiles. Postgres s\u2019appuie sur les contraintes d\u2019exclusion pour \u00e9carter des partitions \u00e0 la planification. Des clauses ont \u00e9t\u00e9 ajout\u00e9es aux commandes \u201cCREATE TABLE\u201d et \u201cALTER TABLE\u201d, notamment pour attacher (ATTACH PARTITION) et d\u00e9tacher (DETACH PARTITION) une partition. <\/p>\n Alter table \u2026 attach partition \u2026 for values \u2026 ; Alter table \u2026 detach partition \u2026 ; PostgreSQL 12 ajoute trois nouvelles fonctions permettant de simplifier l\u2019acc\u00e8s \u00e0 certaines informations concernant une table partitionn\u00e9e. <\/p>\n Exemple : Malgr\u00e9 toutes ces \u00e9volutions, il manque encore quelques am\u00e9liorations : <\/p>\n Le partitionnement est une grosse \u00e9volution pour g\u00e9rer des bases de forte volum\u00e9trie. Il permet de gros gains de performance aussi bien en lecture\/\u00e9criture, en termes de maintenance et aussi de la gestion de la r\u00e9tention des donn\u00e9es (archivage, suppression, r\u00e9partition sur plusieurs tablespaces, \u2026). Il est recommand\u00e9 d\u2019utiliser la version 11 ou 12 de PostgreSQL pour utiliser le partitionnement. La version 10 \u00e0 de grosses limitations, qui ont \u00e9t\u00e9 corrig\u00e9es dans les versions suivantes.<\/p>\n Dans l’article pr\u00e9c\u00e9dent nous avons vu les principes du partitionnement ainsi que l\u2019h\u00e9ritage. Ce second article traitera de la m\u00e9thode d\u00e9clarative ainsi que des am\u00e9liorations qui y sont li\u00e9es. Partitionnement d\u00e9claratif (natif) : Depuis la version 10, PostgreSQL propose le… Continuer la lecture Partitionnement d\u00e9claratif (natif) :<\/h2>\n
\nIl n\u2019y a plus besoin de trigger, donc les performances sont meilleures.
\nLes versions majeures suivantes ont ajout\u00e9 des fonctionnalit\u00e9s comme l\u2019utilisation des Primary Key, Foreign Key, et d\u2019autres.
\nIl existe plusieurs types de cl\u00e9 de partitionnement : <\/p>\n\n
\nUne partition est une table attach\u00e9e \u00e0 une table partitionn\u00e9e.
\nUne table partitionn\u00e9e ne peut pas devenir une table et inversement.
\nUne partition peut devenir une table et inversement.
\nDepuis la 11, les index et contraintes cr\u00e9\u00e9s sur la table partitionn\u00e9e sont r\u00e9pliqu\u00e9s sur toutes les partitions et tables partitionn\u00e9es de l\u2019arbre. <\/p>\n
\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/11\/tree_part_2-300x130.png\")
<\/p>\n
\nLa table T9_3 est une table partitionn\u00e9e (cas de sous-partition).
\nLes autres tables sont des partitions. <\/p>\n
\nAttention : un mauvais choix de partitionnement (type, choix de colonne, \u2026) est pire que de ne pas utiliser de partitionnement. Il est donc imp\u00e9ratif de faire des tests pour valider le partitionnement choisi !
\nLes mots cl\u00e9s \u201cminvalue\u201d et \u201cmaxvalue\u201d peuvent \u00eatre utilis\u00e9s pour borner les partitions de type RANGE.
\nDepuis la version 11, il est possible de cr\u00e9er une partition par d\u00e9faut, qui contiendra toutes les donn\u00e9es qui ne peuvent allez dans les autres partitions. <\/p>\n\n
\n
\r\n1<\/sup> D\u00e9conseill\u00e9 avant la 12, sauf si la Foreign Partition est en Read Only\r\n2<\/sup> Pushdown : l'instance locale demande \u00e0 l'instance distante des faire des actions comme le tri, certaines jointures avant de retourner les donn\u00e9es \u00e0 l'instance locale<\/em>\r\n<\/pre>\n
\n
\n
\n
\n\n
\n \nFEATURE<\/th>\n 9.6<\/th>\n 10<\/th>\n 11<\/th>\n 12<\/th>\n<\/tr>\n<\/thead>\n \n Cr\u00e9ation automatique des index\/contraintes sur les tables filles<\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Partitionnement d\u00e9claratif <\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Routage automatique de ligne \u2013 INSERT<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Routage automatique de ligne \u2013 UPDATE3<\/sup><\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Unique index \/ Primary key<\/td>\n NON<\/td>\n NON<\/td>\n OUI4<\/sup><\/td>\n OUI4<\/sup><\/td>\n<\/tr>\n \n Foreign keys <\/td>\n NON<\/td>\n NON<\/td>\n OUI5<\/sup><\/td>\n OUI<\/td>\n<\/tr>\n \n Default Partitions <\/td>\n OUI6<\/sup><\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Parallel Partition Scans<\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Foreign partitions<\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Routage automatique de ligne \u2013 UPDATE avec Foreign table : <\/td>\n <\/td>\n Foreign table en read only<\/td>\n <\/td>\n <\/td>\n<\/tr>\n \n Table locale vers foreign table<\/td>\n <\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Foreign table vers table locale <\/td>\n <\/td>\n NON<\/td>\n NON7<\/sup><\/td>\n OUI<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \r\n3<\/sup> UPDATE n\u00e9cessitant le changement de partition\r\n4<\/sup> L\u2019index\/PK doit contenir toutes les colonnes de la cl\u00e9 de partitionnement\r\n5<\/sup> Une table partitionn\u00e9e ne peut faire r\u00e9f\u00e9rence que \u00e0 une table non partitionn\u00e9e\r\n6<\/sup> Dans la table parente ou via le trigger\r\n7<\/sup> Contournement : n\u00e9cessite le couple DELETE + INSERT<\/em>\r\n<\/pre>\n
Am\u00e9lioration des performances :<\/h2>\n
Parall\u00e9lisme :<\/h3>\n
\nParmi lesquels on peut trouver : <\/p>\n\n
Partition Pruning :<\/h3>\n
\nL\u2019algorithme n\u2019a pas \u00e9t\u00e9 pr\u00e9vu pour g\u00e9rer un nombre important de partitions. La version 11 int\u00e8gre un nouvel algorithme de recherche bien plus performant : Faster Partition Pruning. Il est effectif sur les requ\u00eates SELECT en 11, INSERT et COPY en 12.
\nLe moteur ne pouvait exclure des partitions que lors de la planification, depuis la 11 il peut aussi exclure lors de l\u2019ex\u00e9cution : c\u2019est le Runtime Partition Pruning. <\/p>\n\n\n
\n \nPartition Pruning<\/th>\n 9.6<\/th>\n 10<\/th>\n 11<\/th>\n 12<\/th>\n<\/tr>\n<\/thead>\n \n Optimizer Partition Elimination <\/td>\n OUI8<\/sup><\/td>\n OUI8<\/sup><\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n Executor Partition Elimination <\/td>\n NON<\/td>\n NON<\/td>\n OUI9<\/sup><\/td>\n OUI<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \r\n8<\/sup> Utilise les contraintes d\u2019exclusion\r\n9<\/sup> Uniquement sur les n\u0153uds de type APPEND<\/em>\r\n<\/pre>\n
\n\n
\n \nFaster Partition Pruning<\/th>\n 10<\/th>\n 11<\/th>\n 12<\/th>\n<\/tr>\n<\/thead>\n \n SELECT<\/td>\n NON<\/td>\n OUI<\/td>\n OUI<\/td>\n<\/tr>\n \n INSERT<\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n<\/tr>\n \n COPY<\/td>\n NON<\/td>\n NON<\/td>\n OUI<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Nouvelles fonctions :<\/h2>\n
Pour attacher une partition :<\/h3>\n
\nCette commande permet d\u2019attacher une table \u00e0 une table partitionn\u00e9e. Si la table parente \u00e0 des index et\/ou des contraintes, ils sont automatiquement cr\u00e9\u00e9s sur la partition.
\nEn 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\u00e9ant une contrainte de type CHECK sur la table \u00e0 attacher d\u00e9crivant la contrainte de partition d\u00e9sir\u00e9e. Avec cette contrainte, la commande pose un verrou de niveau SHARE UPDATE EXCLUSIVE sur la table parente.
\nEn 12, il n\u2019y a plus de verrou sur la table parente. <\/p>\nPour d\u00e9tacher une partition :<\/h3>\n
\nCette commande permet de d\u00e9tacher une partition, cette derni\u00e8re conserve ses index et contraintes. D\u00e9tacher une partition pose, tr\u00e8s brievement, un verrou de type EXCLUSIVE sur la table parente.
\nExemple de cr\u00e9ation \/ attach \/ detach pour RANGE, LIST, HASH : <\/p>\n-- Cr\u00e9er une table partitionn\u00e9e par LIST \r\nCREATE TABLE t1 (c1 integer, c2 test) PARTITION BY LIST (c1) ; \r\n-- Cr\u00e9er une partition \r\nCREATE TABLE t1_1 PARTITION OF t1 FOR VALUES IN (1, 2, 3) ; \r\n-- Attacher une partition \r\nALTER TABLE t1 ATTACH PARTITION t1_2 FOR VALUES IN (4, 5, 6) ; <\/pre>\n
-- Cr\u00e9er une table partitionn\u00e9e par RANGE \r\nCREATE TABLE t2 (c1 integer, c2 test) PARTITION BY RANGE (c1) ; \r\n-- Cr\u00e9er une partition \r\nCREATE TABLE t2_1 PARTITION OF t2 FOR FROM (1) TO (100) ; \r\n-- Attacher une partition \r\nALTER TABLE t2 ATTACH PARTITION t2_2 FOR FROM (100) TO (200) ; <\/pre>\n
-- Cr\u00e9er une table partitionn\u00e9e par HASH \r\nCREATE TABLE t3 (c1 integer, c2 test) PARTITION BY HASH (c1) ; \r\n-- Cr\u00e9er une partition \r\nCREATE TABLE t3_1 PARTITION OF t3 FOR VALUES WITH (modulus 2, remainder 0) ; \r\nCREATE TABLE t3_2 PARTITION OF t3 FOR VALUES WITH (modulus 2, remainder 1) ; <\/pre>\n
-- D\u00e9tacher une partition \r\nALTER TABLE t1 DETACH PARTITION t1_2 ; <\/pre>\n
\n
\n<\/p>\nselect * from pg_partition_ancestors('t9_3_1'); \r\nrelid \r\n-------- \r\nt9_3_1 \r\nt9_3 \r\nt9 \r\n\r\nselect * from pg_partition_root('t9_3_1'); \r\npg_partition_root \r\n------------------ \r\nt9\r\n\r\nselect * from pg_partition_tree('t9_3'); \r\nrelid | parentrelid | isleaf | level \r\n--------+-------------+--------+------- \r\nt9_3 | t9 | f | 0 \r\nt9_3_1 | t9_3 | t | 1 \r\nt9_3_2 | t9_3 | t | 1 \r\n\r\nselect * from pg_partition_tree('t9'); \r\nrelid | parentrelid | isleaf | level \r\n--------+-------------+--------+------- \r\nt9 | | f | 0 \r\nt9_1 | t9 | t | 1 \r\nt9_2 | t9 | t | 1 \r\nt9_3 | t9 | f | 1 \r\nt9_3_1 | t9_3 | t | 2 \r\nt9_3_2 | t9_3 | t | 2 \r\n<\/pre>\nWanted \/ Missing :<\/h2>\n
\n
Conclusion :<\/h2>\n
\nIl manque encore quelques fonctionnalit\u00e9s pour que tout cela soit totalement transparent en termes d\u2019administration (autoanalyze, cr\u00e9ation automatique des partitions, \u2026). Ces manques seront corrig\u00e9s dans les prochaines versions majeures. <\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"