PostgreSQL et le partitionnement

2017-12-01

1200 mots, 6 minutes de lecture

Lætitia Avrot

PostgreSQL 10 est arrivé, avec son lot de nouvelles fonctionnalités plus attendues les unes que mes autres. Regardons l’une des stars de cette nouvelle version : le partitionnement.

Qu’est-ce que c’est ?

Le partitionnement, c’est ce qui permet de découper un gros problème en plusieurs petits.

Imaginons que vous surveilliez la température d’une pièce (comme une chambre froide par exemple). Vous avez 4 capteurs par pièce et vous prenez une mesure par seconde. Chaque mesure est stockée dans la même table.

4 × 60 × 60 × 24 = 345 600

À la fin de la journée, on aura donc 345 600 mesures. Pas de souci, PostgreSQL peut gérer ça sans problème.

Maintenant, imaginons qu’on doive (pour des raisons légales, par exemple) conserver les mesures sur les 5 dernières années.

4 × 60 × 60 × 24 × 365,25 × 5 = 631 152 000

On doit maintenant stocker plus de 600 millions de lignes, et ceci pour une seule pièce !

Maintenant, imaginez que vous deviez fournir les mesures ayant eu lieu entre le 02 février 2015 à 10:23:00 UTC et le 20 février 2015 à 14:20:00 UTC. La requête risque d'être assez lente.

Ne serait-il pas plus simple de découper cette grosse table en plusieurs petites tables plus faciles à requêter ?

Si on décidait de créer une table par mois, la requête n’irait chercher les données que sur la petite table de février 2015, elle serait donc plus rapide.

Et si on voulait purger les données les plus anciennes, ne serait-il pas plus simple de supprimer la petite table qui ne contient que des données obsolètes, plutôt que de faire un DELETE, avec une clause WHERE sur la date, portant sur une grosse table ?

Le partitionnement, c’est exactement ça. Dans ce cas, la date de la mesure est ce qu’on appelle la clé de partitionnement.

Avant la v10

Le partitionnement est arrivé avec PostgreSQL 8.1. Le mécanisme repose sur l’héritage de tables. La mise en place du partitionnement demandait 4 étapes :

Créer la table mère. Cette table ne contiendra aucune donnée. Elle donne juste la structure qui sera reprise par chaque table fille.
Créer des tables filles, chacune héritant de la table mère, sans ajouter de colonne. Il s’agit des « partitions ».
Ajouter une contrainte sur chaque partition pour définir quelles valeurs de la clé de partitionnement sont autorisées dans chaque partition. Attention à bien vérifier qu’une clé de partitionnement ne donne accès qu'à une, et une seule partition.
Créer un trigger ou une rule, qui redirige chaque donnée insérée dans la table mère vers la bonne partition.

Bien penser à s’assurer que le paramètre constraint_exclusion est activé.

Pour chaque nouvelle partition, il fallait donc modifier le trigger ou la règle pour y ajouter une table et la contrainte sur cette table. Quand on sait que les partitions sont beaucoup utilisées pour les données temporelles, cela demandait donc régulièrement de faire ces opérations de « maintenance ».

Les nouveautés de la v10

PostgreSQL 10 n’apporte pas de nouvelle fonctionnalité au partitionnement, mais l’intègre en tant que capacité native de PostgreSQL. C’est la première étape nécessaire à l’amélioration du partitionnement dans le projet PostgreSQL. Il est cependant toujours possible de travailler avec du partitionnement par héritage.

Pour créer une table partitionnée, on peut désormais utiliser la syntaxe suivante :

CREATE TABLE table_name ( ... )
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }
    [ COLLATE collation ] [ opclass ] [, ... ] ) ]

Si on reprend l’exemple du capteur de température dans la chambre froide, on pourrait définir cette table :

CREATE TABLE temperature (measure_timestamp TIMESTAMPTZ,
                          sensor_id INTEGER,
                          measure_value DOUBLE,
                          measure_unite custom_enum_unit)
PARTITION BY RANGE (measure_timestamp);

On peut ajouter une nouvelle partition de cette manière :

CREATE TABLE table_name ( ... )
PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ]
                               [ column_constraint [ ... ] ]| table_constraint }
                               [, ... ]) ]
FOR VALUES IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
           FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
           TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )

Il n’est plus nécessaire de créer les contraintes ni les triggers pour maintenir le partitionnement.

Reprenons l’exemple du capteur. Pour créer une partition capable d’enregistrer les températures mesurées sur le mois de septembre, il faudrait faire comme ça :

CREATE TABLE temperature_201709
PARTITION OF temperature
FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');

On peut de plus détacher/rattacher une partition avec les syntaxes suivantes :

ALTER TABLE [ IF EXISTS ] table_name
ATTACH PARTITION partition_name
FOR VALUES IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
           FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
           TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )

ALTER TABLE [ IF EXISTS ] table_name
DETACH PARTITION partition_name

Dans notre exemple de la chambre froide, on aurait pu créer la partition et la rattacher après, puis la détacher ensuite comme cela :

CREATE TABLE temperature_201709 (measure_timestamp TIMESTAMPTZ,
                          sensor_id INTEGER,
                          measure_value DOUBLE,
                          measure_unite custom_enum_unit);

ALTER TABLE temperature
ATTACH PARTITION temperature_201709
FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');

ALTER TABLE temperature
DETACH PARTITION temperature_201709;

À ceux qui se posent la question de l’utilité du détachement de partition, cela permet tout simplement de purger les données obsolètes.

Enfin, on peut aussi utiliser comme partition des tables qui se trouvent sur d’autres instances, avec les Foreign Data Wrappers.

Cela peut-être utile lorsque l’on souhaite « archiver » des partitions moins utilisées sur une autre instance, pour soulager l’instance principale de cette charge.

Limites

Le but de ces changements n'étant pas d’ajouter de nouvelles fonctionnalités, il reste des limites. Nous espérons qu’une partie de celles-ci seront levées dans la version 11.

Il n’est pas possible de créer un index sur toutes les partitions automatiquement, il faut aller sur chaque partition pour créer l’index. Cela signifie aussi qu’on ne peut pas créer une clé primaire ou une contrainte d’unicité sur une table partitionnée.
Comme les clés primaires ne sont pas supportées, les clés étrangères ne peuvent pas référencer une table partitionnée, de même qu’il n’est pas possible de créer une clé étrangère d’une table partitionnée vers une autre table.
Si un déclencheur sur ligne (INSERT, UPDATE, DELETE) est nécessaire, il faudra le définir pour chaque partition.
Un UPDATE ne doit pas faire changer une ligne de partition.
Le partitionnement n’est possible que pour des valeurs de clé de partitionnement listées ou contenues dans un intervalle de valeurs.

Le partitionnement PostgreSQL dans le futur

Bien sûr, le projet PostgreSQL ne va pas s’arrêter d’améliorer le partitionnement. Voici une liste non exhaustive des améliorations prévues dans le futur :

Amélioration des performances ;
Jointures plus intelligentes avec les partitions ;
Réduction du niveau de verrous lors des écritures sur les partitions ;
Partitionnement de données hashées ;
Création d’index sur le parent ;
Gestion des clés primaires et étrangères ;
Amélioration de la gestion de l’autovacuum sur les tables partitionnées ;
Possibilité de changer une ligne de partition .