5.9. Partitionnement
PostgreSQL™ offre un support
basique du partitionnement de table. Cette section explique pourquoi
et comment implanter le partitionnement lors de la conception de la
base de données.
Le partitionnement fait référence à la division d'une table logique
volumineuse en plusieurs parties physiques plus petites. Le
partitionnement comporte de nombreux avantages :
-
les performances des requêtes peuvent être significativement
améliorées dans certaines situations, particulièrement
lorsque la plupart des lignes fortement accédées d'une table
se trouvent sur une seule partition ou sur un petit nombre de
partitions. Le partitionnement se substitue aux colonnes
principales des index, réduisant ainsi la taille des index et
facilitant la tenue en mémoire des parties les plus utilisées
de l'index ;
-
lorsque les requêtes ou les mises à jour accèdent à un
important pourcentage d'une unique partition, les
performances peuvent être grandement améliorées par
l'utilisation avantageuse de parcours séquentiels sur cette
partition plutôt que d'utiliser un index et des lectures
aléatoires réparties sur toute la table ;
-
les chargements et suppressions importants de données peuvent
être obtenus par l'ajout ou la suppression de partitions,
sous réserve que ce besoin ait été pris en compte lors de la
conception du partitionnement.
ALTER TABLE
est bien plus
rapide qu'une opération de masse. Cela supprime également la
surcharge dû au
VACUUM
causé par un
DELETE
massif ;
-
les données peu utilisées peuvent être déplacées sur un média
de stockage moins cher et plus lent.
Les bénéfices ne sont réellement intéressants que si cela permet
d'éviter une table autrement plus volumineuse. Le point d'équilibre
exact à partir duquel une table tire des bénéfices du
partitionnement dépend de l'application. Toutefois, le
partitionnement doit être envisagé si la taille de la table peut
être amenée à dépasser la taille de la mémoire physique du serveur.
Actuellement, PostgreSQL™
supporte le partitionnement à travers l'héritage de tables. Chaque
partition doit être créée comme une table enfant d'une unique table
parent. La table parent est, elle, habituellement vide ; elle
n'existe que pour représenter l'ensemble complet des données. Il
est impératif de maîtriser les concepts de l'héritage (voir
Section 5.8,
« L'héritage ») avant de tenter d'implanter le
partitionnement.
Les formes suivantes de partitionnement peuvent être implantées
dans PostgreSQL™ :
-
Partitionnement par échelon
-
La table est partitionnée en « groupes » (ou échelles) définis par une
colonne clé ou par un ensemble de colonnes, sans recouvrement
entre les échelles de valeurs affectées aux différentes
partitions. Il est possible, par exemple, de partitionner par
échelles de date ou par échelles d'identifiants pour des
objets métier particuliers.
-
Partitionnement par liste
-
La table est partitionnée en listant explicitement les
valeurs clés qui apparaissent dans chaque partition.
Pour partionner une table, la procédure est la suivante :
-
Créer la table « maître ». C'est de celle-ci qu'héritent
toutes les partitions.
Cette table ne contient pas de données. Les contraintes de
vérification ne doivent être définies sur cette table que si
elles sont appliquées à toutes les partitions. Il n'y a de
plus aucune raison de définir des index ou des contraintes
d'unicité sur cette table.
-
Créer plusieurs tables « filles » (ou enfants) qui héritent
chacune de la table maître. Normalement, ces tables
n'ajoutent pas de colonnes à l'ensemble hérité du maître.
Par la suite, les tables enfants sont appelées partitions,
bien qu'elles soient, en tout point, des tables PostgreSQL™ normales.
-
Ajouter les contraintes de tables aux tables de partitions
pour définir les valeurs des clés autorisées dans chacune.
Quelques exemples typiques :
CHECK ( x = 1 )
CHECK ( comté IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( ID >= 100 AND ID < 200 )
Les contraintes doivent garantir qu'il n'y a pas de
recouvrement entre les valeurs clés autorisées dans les
différentes partitions. Une erreur commune est de configurer
des contraintes d'échelle de cette façon :
CHECK ( comté BETWEEN 100 AND 200 )
CHECK ( comté BETWEEN 200 AND 300 )
Il est dans ce cas difficile de savoir à quelle partition
appartient la clé 200.
Il n'y a aucune différence entre les syntaxes de
partitionnement par échelon et de partitionnement par liste ;
ces termes ne sont que descriptifs.
-
Pour chaque partition, créer un index sur la (ou les)
colonne(s) clé(s), ainsi que tout autre index nécessaire.
(L'index clé n'est pas vraiment nécessaire mais, dans la
plupart des scénarios, il est utile. Si les valeurs clés
doivent être uniques, alors il faut toujours créer une
contrainte d'unicité ou de clé primaire pour chaque
partition.)
-
Optionnellement, définir une règle ou un déclencheur pour
rediriger les modifications de la table maître vers la
partition appropriée.
-
S'assurer que le paramètre de configuration constraint_exclusion
est activé dans postgresql.conf.
Dans le cas contraire, les requêtes ne sont pas optimisées.
Soit la base de données d'une grande fabrique de glaces. La
compagnie mesure le pic de température journalier ainsi que les
ventes de glaces dans chaque région. Conceptuellement, la table
ressemble à cela :
CREATE TABLE mesure (
id_ville int not null,
date_trace date not null,
temperature int,
ventes int
);
La plupart des requêtes n'accèdent qu'aux données de la dernière
semaine, du dernier mois ou du dernier trimestre car cette table
est essentiellement utilisée pour préparer des rapports en ligne
pour la direction. Pour réduire le nombre de données anciennes à
stocker, seules les trois dernières années sont conservées. Au
début de chaque mois, les données du mois le plus ancien sont
supprimées.
Dans cette situation, le partitionnement permet de répondre aux
différents besoins identifiés sur la table des mesures. En suivant
les étapes indiquées ci-dessus, le partitionnement peut être
configuré de la façon suivante :
-
la table maître est la table mesure, déclarée exactement comme
ci-dessus ;
-
une partition est ensuite créée pour chaque mois actif :
CREATE TABLE mesure_a2004m02 ( ) INHERITS (mesure);
CREATE TABLE mesure_a2004m03 ( ) INHERITS (mesure);
...
CREATE TABLE mesure_a2005m11 ( ) INHERITS (mesure);
CREATE TABLE mesure_a2005m12 ( ) INHERITS (mesure);
CREATE TABLE mesure_a2006m01 ( ) INHERITS (mesure);
Chaque partition est une table à part entière mais sa
définition est héritée de la table mesure.
Ceci résoud un des problèmes : la suppression d'anciennes
données. Chaque mois, il suffit d'effectuer un
DROP TABLE
sur la table
enfant la plus ancienne et de créer une nouvelle table enfant
pour les données du nouveau mois.
-
Il faut ajouter des des contraintes de table qui interdisent
les recouvrements. Le script de création de table devient :
CREATE TABLE mesure_a2004m02 (
CHECK ( date_trace >= DATE '2004-02-01' AND date_trace < DATE '2004-03-01' )
) INHERITS (mesure);
CREATE TABLE mesure_a2004m03 (
CHECK ( date_trace >= DATE '2004-03-01' AND date_trace < DATE '2004-04-01' )
) INHERITS (mesure);
...
CREATE TABLE mesure_a2005m11 (
CHECK ( date_trace >= DATE '2005-11-01' AND date_trace < DATE '2005-12-01' )
) INHERITS (mesure);
CREATE TABLE mesure_a2005m12 (
CHECK ( date_trace >= DATE '2005-12-01' AND date_trace < DATE '2006-01-01' )
) INHERITS (mesure);
CREATE TABLE mesure_a2006m01 (
CHECK ( date_trace >= DATE '2006-01-01' AND date_trace < DATE '2006-02-01' )
) INHERITS (mesure);
-
Des index sur les colonnes clés sont probablement nécessaires
:
CREATE INDEX mesure_a2004m02_date_trace ON mesure_a2004m02 (date_trace);
CREATE INDEX mesure_a2004m03_date_trace ON mesure_a2004m03 (date_trace);
...
CREATE INDEX mesure_a2005m11_date_trace ON mesure_a2005m11 (date_trace);
CREATE INDEX mesure_a2005m12_date_trace ON mesure_a2005m12 (date_trace);
CREATE INDEX mesure_a2006m01_date_trace ON mesure_a2006m01 (date_trace);
À ce stade, c'est suffisant.
-
Si les données ne sont ajoutées que dans la dernière
partition, une règle très simple d'insértion des données peut
être configurée. Elle doit être redéfinie chaque mois pour
toujours pointer vers la partition en cours.
CREATE OR REPLACE RULE partition_actuelle_mesure AS
ON INSERT TO mesure
DO INSTEAD
INSERT INTO mesure_a2006m01 VALUES ( NEW.id_ville,
NEW.date_trace,
NEW.temperature,
NEW.ventes );
Il est également possible de laisser le serveur localiser la
partition dans laquelle doit être insérée la ligne proposée
en entrée. Un ensemble de règles plus complexes permet
d'obtenir ce résultat.
CREATE RULE mesure_insert_a2004m02 AS
ON INSERT TO mesure WHERE
( date_trace >= DATE '2004-02-01' AND date_trace < DATE '2004-03-01' )
DO INSTEAD
INSERT INTO mesure_a2004m02 VALUES ( NEW.id_ville,
NEW.date_trace,
NEW.temperature,
NEW.ventes );
...
CREATE RULE mesure_insert_a2005m12 AS
ON INSERT TO mesure WHERE
( date_trace >= DATE '2005-12-01' AND date_trace < DATE '2006-01-01' )
DO INSTEAD
INSERT INTO mesure_a2005m12 VALUES ( NEW.id_ville,
NEW.date_trace,
NEW.temperature,
NEW.ventes );
CREATE RULE mesure_insert_a2006m01 AS
ON INSERT TO mesure WHERE
( date_trace >= DATE '2006-01-01' AND date_trace < DATE '2006-02-01' )
DO INSTEAD
INSERT INTO mesure_a2006m01 VALUES ( NEW.id_ville,
NEW.date_trace,
NEW.temperature,
NEW.ventes );
La clause WHERE de chaque règle
correspond exactement à la contrainte de vérification de la
partition correspondante.
Un schéma complexe de partitionnement peut amener à écrire une
grande quantité de DDL. Dans l'exemple ci-dessus, une nouvelle
partition est écrite chaque mois. Il est donc conseillé d'écrire un
script qui engendre automatiquement la DDL requise.
Le partitionnement peut aussi se faire en utilisant une vue
UNION ALL :
CREATE VIEW mesure AS
SELECT * FROM measure_a2004m02
UNION ALL SELECT * FROM measure_a2004m03
...
UNION ALL SELECT * FROM measure_a2005m11
UNION ALL SELECT * FROM measure_a2005m12
UNION ALL SELECT * FROM measure_a2006m01;
Toutefois, l'obigation de recréer la vue impose une étape
supplémentaire à l'ajout et à la suppression de partitions.
5.9.3. Gérer les partitions
Généralement, l'ensemble des partitions établies lors de la
définition initiale de la table n'a pas pour but de rester
statique. Il n'est pas inhabituel de supprimer d'anciennes
partitions de données et d'en ajouter périodiquement de nouvelles
pour de nouvelles données. Un des principaux avantages du
partitionnement est précisément qu'il autorise une exécution
quasi-instantanée de cette tâche, autrement bien plus difficile, en
permettant la manipulation de la structure de la partition, plutôt
que de déplacer physiquement de grands volumes de données.
L'option la plus simple pour supprimer d'anciennes données consiste
à supprimer la partition qui n'est plus nécessaire :
DROP TABLE mesure_a2003m02;
Cela permet de supprimer très rapidement des millions
d'enregistrements car il n'est nul besoin de supprimer séparément
chaque enregistrement.
Une autre option, souvent préférable, consiste à supprimer la
partition de la table partitionnée mais de conserver l'accès à la
table en tant que telle :
ALTER TABLE mesure_a2003m02 NO INHERIT mesure;
Ceci permet la réalisation d'opérations ultérieures sur les données
avant qu'elles ne soient supprimées. Par exemple, c'est souvent le
bon moment pour sauvegarder les données en utilisant
COPY
, pg_dump ou tout autres outil. C'est aussi le
moment d'agréger des données en des formats plus petits, de
réaliser d'autres opérations sur les données ou de créer des
rapports.
De façon similaire, une nouvelle partition peut être ajoutée pour
gérer les nouvelles données. Une partition vide peut être créée
dans la table partitionnée de la même façon que les partitions
individuelles créées plus haut.
CREATE TABLE mesure_a2006m02 (
CHECK ( date_trace >= DATE '2006-02-01' AND date_trace < DATE '2006-03-01' )
) INHERITS (mesure);
Alternativement, il est parfois plus intéressant de créer la
nouvelle table en dehors de la structure de partitionnement et de
la transformer en une partition adéquate plus tard. Cela permet de
charger les données, les vérifier et les transformer avant leur
apparition dans la table partitionnée.
CREATE TABLE mesure_a2006m02
(LIKE mesure INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE mesure_a2006m02 ADD CONSTRAINT a2006m02
CHECK ( date_trace >= DATE '2006-02-01' AND date_trace < DATE '2006-03-01' );
\copy mesure_a2006m02 from 'mesure_a2006m02'
-- autre travail de préparation des données
ALTER TABLE mesure_a2006m02 INHERIT mesure;
5.9.4. Partitionnement et exclusion de contrainte
L'exclusion de contrainte est une
technique d'optimisation des requêtes pour améliorer les
performances sur les tables partitionnées telles que décrites plus
haut. Par exemple :
SET constraint_exclusion = on;
SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';
Sans exclusion de contrainte, la requête ci-dessus parcourt chacune
des partitions de la table mesure.
Avec l'exclusion de contrainte activée, le planificateur examine
les contraintes de chaque partition et tente de prouver que la
partition n'a pas besoin d'être parcourue parce qu'elle ne peutt
pas contenir de lignes correspondant à la clause WHERE de la requête. Quand le planificateur peut le
prouver, il exclut la partition du plan de requête.
La commande
EXPLAIN
permet d'afficher la différence entre un plan avec constraint_exclusion activé (
on
) et un
plan avec ce paramètre désactivé (
off
). Un plan
typique par défaut pour ce type de table est :
SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=158.66..158.68 rows=1 width=0)
-> Append (cost=0.00..151.88 rows=2715 width=0)
-> Seq Scan on mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_a2004m02 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_a2004m03 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
...
-> Seq Scan on mesure_a2005m12 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_a2006m01 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
Quelques partitions, voire toutes, peuvent utiliser des parcours
d'index à la place des parcours séquentiels de la table complète
mais le fait est qu'il n'est pas besoin de parcourir les anciennes
partitions pour répondre à cette requête. Lorsque l'exclusion de
contrainte est activée, un plan significativement réduit est
obtenu, qui délivre la même réponse :
SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Aggregate (cost=63.47..63.48 rows=1 width=0)
-> Append (cost=0.00..60.75 rows=1086 width=0)
-> Seq Scan on mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_a2006m01 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
L'exclusion de contraintes n'est pilotée que par les contraintes
CHECK, pas par la présence d'index. Il
n'est donc pas nécessaire de définir des index sur les colonnes
clés. Le fait qu'un index doive être créé pour une partition donnée
dépend de ce que les requêtes qui parcourent la partition
parcourent en général une grande partie de la partition ou
seulement une petite partie. Un index est utile dans le dernier
cas, pas dans le premier.
Les restrictions suivantes s'appliquent aux tables partitionnées :
-
il n'existe, actuellement, pas de moyen de vérifier que
toutes les contraintes de vérification (CHECK) sont mutuellement exclusives. C'est au
concepteur de la base de données de faire les vérifications
nécessaires ;
-
il n'existe, actuellement, pas de façon simple de préciser
que les lignes ne doivent pas être insérées dans la table
maître. Une contrainte CHECK (false)
sur la table maître est héritée par toutes les tables filles
et ne peut donc pas être utilisée dans ce but. Une
possibilité consiste à configurer un déclencheur ON INSERT sur la table maître qui lève
systématiquement une erreur. (Ou un déclencheur qui redirige
les données dans la bonne table fille au lieu d'utiliser un
ensemble de règles comme suggéré ci-dessus).
Les restrictions suivantes s'appliquent à l'exclusion de
contraintes :
-
l'exclusion de contrainte ne fonctionne que si la clause
WHERE de la requête contient des
constantes. Une requête avec paramètre n'est pas optimisée
car le planificateur ne peut avoir connaissance au préalable
des partitions sélectionnées par la valeur du paramètre à
l'exécution. Pour la même raison, il faut éviter les
fonctions « stable »s
comme CURRENT_DATE ;
-
les comparaisons inter-type dans les contraintes CHECK doivent être évitées car le
planificateur échouera à prouver que de telles conditions
sont fausses. Par exemple, la contrainte suivante fonctionne
si x est une colonne de type
integer, mais pas si elle est de
type bigint :
CHECK ( x = 1 )
Pour une colonne bigint, il faut
utiliser une contrainte comme celle-ci :
CHECK ( x = 1::bigint )
Le problème n'est pas limité au type bigint -- il peut survenir quand le type de
données par défaut de la constante ne correspond pas au type
de données de la colonne avec laquelle elle est comparée. Les
comparaisons inter-type dans les requêtes fournies sont
habituellement gérées, mais pas dans les conditions
CHECK ;
-
toutes les constraintes de toutes les partitions de la table
maître sont considérées pour l'exclusion de contraintes. De
ce fait, un grand nombre de partitions a tendance à augmenter
considérablement le temps de planification de la requête ;
-
il est toujours nécessaire d'exécuter un
ANALYZE
sur chaque
partition. Une commande comme
ANALYZE mesure;
ne traite que la table maître.
L'héritage
