6 Partitionnement déclaratif (introduction)

6/ Partitionnement déclaratif (introduction)

• Le partitionnement déclaratif apparaît avec PostgreSQL 10

• Préférer un PostgreSQL récent

• Ne plus utiliser l’ancien partitionnement par héritage.

Ce module introduit le partitionnement déclaratif introduit avec PostgreSQL 10, et amélioré dans les versions suivantes. Les améliorations à chaque nouvelle version de PostgreSQL doivent mener à favoriser une version récente de PostgreSQL.

Le partitionnement par héritage, au fonctionnement totalement différent, reste utilisable, mais ne doit plus servir aux nouveaux développements, du moins pour les cas décrits ici.

6.1 PRINCIPE & INTÉRÊTS DU PARTITIONNEMENT

• Faciliter la maintenance de gros volumes

• VACUUM (FULL), réindexation, déplacements, sauvegarde logique…

• Gain en performance

• parcours complet sur de plus petites tables

• statistiques par partition plus précises

• purge par partitions entières

• pg_dump parallélisable

• tablespaces différents (données froides/chaudes)

• Attention à la maintenance sur le code

Maintenir de très grosses tables peut devenir fastidieux, voire impossible. Ce genre de problème peut survenir:

• VACUUM FULL trop longs voire impossibles à cause d’un espace disque insuffisant;

• autovacuum pas assez réactif sur les grosses tables;

• index trop gros et réindexations interminables;

• pg_dump ne sait pas paralléliser la sauvegarde d’une table volumineuse;

• purge de grandes quantité de données très longue;

• statistiques plus assez fines (et augmenter l’échantillonnage ne suffit pas forcément);

• dilution des lignes actives dans la table au détriment du hit ratio et des I/O;

• mauvaise corrélation de lignes récupérées ensembles;

• impossibilité de séparer données actives et inactives au niveau physique (tablespaces).

Certains outils existent pour mitiger ces problèmes (index partiels, index BRIN, autovacuum plus agressif…) mais ne sont pas des solutions définitives.

Le partitionnement consiste à scinder une table en plusieurs sous-tables (partitions) ellesmêmes manipulables en tant que tables à part entière.

Maintenance

La maintenance s’effectue sur les partitions plutôt que sur l’ensemble complet des données. En particulier, un VACUUM FULL ou une réindexation peuvent s’effectuer partition par partition, ce qui permet de limiter les interruptions en production, et lisser la charge.

pg_dump peut sauvegarder les différentes partitions d’une même table en parallèle.

Le partitionnement est aussi un préalable au déplacement d’une partie des données dans un autre tablespace pour des raisons de place, ou pour déporter les parties les moins utilisées de la table vers des disques plus lents et moins chers. Cependant, il faut mettre en balance le coût de migration du modèle existant avec l’économie réalisée sur les disques.

Parcours complet de partitions

Certaines requêtes (notamment décisionnelles) ramènent tant de lignes, ou ont des critères si complexes, qu’un parcours complet de la table est souvent privilégié par l’optimiseur.

Un partitionnement, souvent par date, permet de ne parcourir qu’une ou quelques partitions au lieu de l’ensemble des données. C’est le rôle de l’optimiseur de choisir la partition (partition pruning), par exemple celle de l’année en cours, ou des mois sélectionnés.

Suppression des partitions

La suppression de données parmi un gros volume peut poser des problèmes d’accès concurrents ou de performance, par exemple dans le cas de purges. En configurant judicieusement les partitions, on peut résoudre cette problématique en supprimant une partition(ici en partitionnement déclaratif):

DROP TABLE nompartition ;

ou en la détachant:

ALTER TABLE table_partitionnee DETACH PARTITION nompartition ;

pour l’archiver (et la réattacher au besoin) ou la supprimer ultérieurement.

D’autres optimisations sont décrites dans ce billet de blog d’Adrien Nayrat¹: statistiques plus précises au niveau d’une partition, réduction plus simple de la fragmentation des index, jointure par rapprochement des partitions…

La principale difficulté d’un système de partitionnement consiste à partitionner avec un impact minimal sur la maintenance du code par rapport à une table classique.

6.2 PARTITIONNEMENT DÉCLARATIF

• Table partitionnée

• structure uniquement

• index/contraintes répercutés sur les partitions

• Partitions:

• 1 partition = 1 table classique, utilisable directement

• clé de partitionnement (inclue dans PK/UK)

• partition par défaut

• sous-partitions possibles

• FDW comme partitions possible

• attacher/détacher une partition

En partitionnement déclaratif, une table partitionnée ne contient pas de données par elle-même. Elle définit la structure (champs, types) et les contraintes et index, qui sont répercutées sur ses partitions.

Une partition est une table à part entière, rattachée à une table partitionnée. Sa structure suit automatiquement celle de la table partitionnée et ses modifications. Cependant, des index ou contraintes supplémentaires propres à cette partition peuvent être ajoutées de la même manière que pour une table classique.

La partition se définit par une « clé de partitionnement », sur une ou plusieurs colonnes. Les lignes de même clé se retrouvent dans la même partition. La clé peut se définir comme:

• une liste de valeurs;

• une plage de valeurs;

• une valeur de hachage.

Les clés des différentes partitions ne doivent pas se recouvrir.

Une partition peut elle-même être partitionnée, sur une autre clé, ou la même.

Une table classique peut être attachée à une table partitionnée. Une partition peut être détachée et redevenir une table indépendante normale.

Même des tables distantes (utilisant foreign data wrapper)peuvent être définies comme partitions, avec des restrictions. Pour les performances, penser aux accès asynchrones.

Les clés étrangères entre tables partitionnées ne sont pas un problème dans les versions récentes de PostgreSQL.

Le routage des données insérées ou modifiées vers la bonne partition est géré de façon automatique en fonction de la définition des partitions. La création d’une partition par défaut permet d’éviter des erreurs si aucune partition ne convient.

De même, à la lecture de la table partitionnée, les différentes partitions nécessaires sont accédées de manière transparente.

Pour le développeur, la table principale peut donc être utilisée comme une table classique. Il vaut mieux cependant qu’il connaisse le mode de partitionnement, pour utiliser la clé autant que possible. la complexité supplémentaire améliorera les performances. L’accès direct aux partitions par leur nom de table reste possible, et peut parfois améliorer les performances. Un développeur pourra aussi purger des données plus rapidement, en effectuant un simple DROP de la partition concernée.

6.2.1 Partitionnement par liste

6.2.2 Partitionnement par liste: implémentation

@ CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1); CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3); CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5); … =|

Le partitionnement par liste définit les valeurs d’une colonne acceptables dans chaque partition. Utilisations courantes: partitionnement par année, par statut, par code géographique…

6.2.3 Partitionnement par intervalle

6.2.4 Partitionnement par intervalle: implémentation

&-) CREATE TABLE logs (d timestamptz, contenu text) PARTITION BY RANGE (d); CREATE TABLE logs_201901 PARTITION OF logs FOR VALUES FROM ('2019-01-01') TO ('2019-02-01'); CREATE TABLE logs_201902 PARTITION OF logs FOR VALUES FROM ('2019-02-01') TO ('2019-03-01'); … CREATE TABLE logs_201912 PARTITION OF logs FOR VALUES FROM ('2019-12-01') TO ('2020-01-01'); … CREATE TABLE logs_autres PARTITION OF logs DEFAULT ; -- pour ne rien perdre

Utilisations courantes : partitionnement par date, par plages de valeurs continues, alphabétiques…

L’exemple ci-dessus utilise le partitionnement par mois. Chaque partition est définie par des plages de date. Noter que la borne supérieure ne fait pas partie des données de la partition. Elle doit donc

être aussi la borne inférieure de la partie suivante. La description de la table partitionnée devient:

=# \d+ logs
                                    Table partitionnée « public.logs »
 Colonne |           Type           | ... | Stockage | …
---------+--------------------------+-----+----------+ …
 d       | timestamp with time zone | ... | plain    | …
 contenu | text                     | ... | extended | …
Clé de partition : RANGE (d)
Partitions: logs_201901 FOR VALUES FROM ('2019-01-01 00:00:00+01') TO ('2019-02-01
             00:00:00+01'),
            logs_201902 FOR VALUES FROM ('2019-02-01 00:00:00+01') TO ('2019-03-01
             00:00:00+01'),
            …
            logs_201912 FOR VALUES FROM ('2019-12-01 00:00:00+01') TO ('2020-01-01
             00:00:00+01'),
            logs_autres DEFAULT

La partition par défaut reçoit toutes les données qui ne vont dans aucune autre partition: cela évite des erreurs d’insertion. Il vaut mieux que la partition par défaut reste très petite.

Il est possible de définir des plages sur plusieurs champs:

CREATE TABLE tt_a PARTITION OF tt
FOR VALUES FROM (1,'2020-08-10') TO (100, '2020-08-11') ;

6.2.5 Partitionnement par hachage

6.2.6 Partitionnement par hachage: implémentation

• Hachage des valeurs

• Répartition homogène

• Indiquer un modulo et un reste

CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);

CREATE TABLE t3_a PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 0);
CREATE TABLE t3_b PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 1);
CREATE TABLE t3_c PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 2);

Ce type de partitionnement vise à répartir la volumétrie dans plusieurs partitions de manière homogène, quand il n’y a pas de clé évidente. En général, il y aura plus que 3 partitions.

6.3 PERFORMANCES & PARTITIONNEMENT

• Insertions via la table principale

• quasi aucun impact

• Lecture depuis la table principale

• attention à la clé

• Purge

• simple DROP ou DETACH

• Trop de partitions

• attention au temps de planification

Des INSERT dans la table partitionnée seront redirigés directement dans les bonnes partitions avec un impact en performances quasi négligeable.

Lors des lectures ou jointures, il est important de préciser autant que possible la clé de jointure, si elle est pertinente. Dans le cas contraire, toutes les tables de la partition seront interrogées.

Dans cet exemple, la table comprend 10 partitions:

EXPLAIN (COSTS OFF)       SELECT COUNT(*) FROM pgbench_accounts ;

QUERY PLAN

---------------------------------------------------------------------------------
Finalize Aggregate

• -> Gather Workers Planned: 2

• -> Parallel Append -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_1_pkey on

• pgbench_accounts_1 pgbench_accounts -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_2_pkey on

• pgbench_accounts_2 pgbench_accounts_1

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_3_pkey on

• pgbench_accounts_3 pgbench_accounts_2

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_4_pkey on

• pgbench_accounts_4 pgbench_accounts_3

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_5_pkey on

• pgbench_accounts_5 pgbench_accounts_4

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_6_pkey on

• pgbench_accounts_6 pgbench_accounts_5

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_7_pkey on

• pgbench_accounts_7 pgbench_accounts_6

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_8_pkey on

• pgbench_accounts_8 pgbench_accounts_7

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_9_pkey on

• pgbench_accounts_9 pgbench_accounts_8

• -> Partial Aggregate

• -> Parallel Index Only Scan using pgbench_accounts_10_pkey on

• pgbench_accounts_10 pgbench_accounts_9

Avec la clé, PostgreSQL se restreint à la (ou les) bonne(s) partition(s):

EXPLAIN (COSTS OFF)        SELECT * FROM pgbench_accounts WHERE aid = 599999 ;

QUERY PLAN

Index Scan using pgbench_accounts_1_pkey on pgbench_accounts_1 pgbench_accounts
Index Cond: (aid = 599999)

Si l’on connaît la clé et que le développeur sait en déduire la table, il est aussi possible d’accéder directement à la partition:

EXPLAIN (COSTS OFF) SELECT * FROM pgbench_accounts_6 WHERE aid = 599999 ;

QUERY PLAN

QUERY PLAN

• Index Scan using pgbench_accounts_6_pkey on pgbench_accounts_6 Index Cond: (aid = 599999)

Cela allège la planification, surtout s’il y a beaucoup de partitions.

Exemples:

• dans une table partitionnée par statut de commande, beaucoup de requêtes ne s’occupent que d’un statut particulier, et peuvent donc n’appeler que la partition concernée (attention si le statut change…);

• dans une table partitionnée par mois de création d’une facture, la date de la facture permet de s’adresser directement à la bonne partition.

Il est courant que les ORM ne sachent pas exploiter cette fonctionnalité.

6.3.1 Attacher/détacher une partition

ALTER TABLE logs ATTACH PARTITION logs_archives FOR VALUES FROM ( MINVALUE ) TO ('2019-01-01');

• Vérification du respect de la contrainte

• parcours complet de la table: lent + verrou!

ALTER TABLE logs DETACH PARTITION logs_archives ;

• Rapide… mais verrou

Attacher une table existante à une table partitionnée implique de définir une clé de partitionnement. PostgreSQL vérifiera que les valeurs présentes correspondent bien à cette clé. Cela peut être long, surtout que le verrou nécessaire sur la table est gênant. Pour accélérer les choses, il est conseillé d’ajouter au préalable une contrainte CHECK correspondant à la clé, voire d’ajouter d’avance les index qui seraient ajoutés lors du rattachement.

Détacher une partition est beaucoup plus rapide qu’en attacher une. Cependant, là encore, le verrou peut être gênant.

6.3.2 Supprimer une partition

DROP TABLE logs_2018;

Là aussi, l’opération est simple et rapide, mais demande un verrou exclusif.

6.3.3 Limitations principales du partitionnement déclaratif

• Temps de planification! Attention si > 100 partitions

• Création non automatique

• Pas d’héritage multiple, schéma fixe

Certaines limitations du partitionnement sont liées à son principe. Les partitions ont forcément le même schéma de données que leur partition mère. Il n’y a pas de notion d’héritage multiple.

La création des partitions n’est pas automatique (par exemple dans un partitionnement par date). Il faudra prévoir de les créer par avance.

Une limitation sérieuse du partitionnement tient au temps de planification qui augmente très vite avec le nombre de partitions, même petites. En général, on considère qu’il faut éviter de dépasser 100 partitions.

Pour contourner cette limite, il reste possible de manipuler directement les partitions s’il est facile de trouver leur nom.

6.4 CONCLUSION

• Préférer une version récente de PostgreSQL

• Pour plus de détails sur le partitionnement - https://dali.bo/v1_html

le partitionnement déclaratif apparu en version10est mûr dans les dernières versions.il introduit une complexité supplémentaire, mais peut rendre de grands services quand la volumétrie augmente.

Le module V1 fournit plus de précisions sur l’intérêt, l’utilisation, l’indexation, les statistiques, la maintenance liées au partionnement.

6.5 QUIZ

https://dali.bo/v0_quiz

Source : manuel complet PDF, révision 26.09, pages PDF 305-318. Les travaux pratiques et leurs solutions proviennent exclusivement de la version complète.

Pour poursuivre la lecture, voir la sauvegarde physique et le PITR.