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™ :
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.
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 :
L'héritage
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'insertion 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.
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;
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 contraintes 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.