 Héritage
|
 
|
Autres objets de la base de
données
|
PostgreSQL™ offre un support basique du partitionnement de table. Cette section décrit pourquoi et comment vous pouvez implémenter le partitionnement dans la conception de votre base de données.
Le partitionnement fait référence à la division logique d'une grosse table en plusieurs parties physiques. Le partitionnement apporte plusieurs bénéfices :
Les performances des requêtes peuvent être significativement améliorées dans certaines situations, particulièrement lorsque la plupart des lignes les plus utilisées d'une table sont sur une seule partition ou sur un petit nombre de partitions. Le partitionnement se substitue aux colonnes principales des index, réduit la taille des index et fait en sorte que les parties les plus utilisées de l'index tiennent en mémoire.
Quand les requêtes ou les mises à jour accèdent à un gros pourcentage d'une seule partition, les performances peuvent être grandement améliorées en prenant avantage des parcours séquentiels sur cette partition plutôt que d'utiliser un index et des lectures aléatoires sur toute la table.
Les grosses charges de données et les suppressions peuvent s'accomplir en ajoutant ou en supprimant des partitions si ce besoin est pris en compte dans la conception du partitionnement. ALTER TABLE est bien plus rapide. Cela évite aussi entièrement la surcharge du VACUUM causé par un DELETE massif.
Les données peu utilisées peuvent être déplacées sur un média peu cher et lent.
Les bénéfices seront normalement intéressants quand une table sera suffisamment importante. Le point exact où une table bénéficierait d'un partitionnement dépend de l'application bien qu'une règle de base est que la taille de la table ne doit pas dépasser la mémoire physique du serveur.
Actuellement, PostgreSQL™ supporte le partitionnement via l'héritage des tables. Chaque partition doit être créée comme une table enfant d'une seule table parent. La table parent elle-même est habituellement vide ; elle existe seulement pour représenter l'ensemble complet des données. Vous devez être familier avec l'héritage (voir Section 5.8, « 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 » définis par une colonne clé ou par un ensemble de colonnes, sans intersection dans les ensembles de valeurs affectées aux différentes partition. Par exemple, il est possible de partitionner par date ou par ensemble d'identifieurs pour des objets métier particuliers.
La table est partitionnée en listant explicitement les valeurs clés apparaissant dans chaque partition.
Pour configurer une table partitionnée, faites ce qui suit :
Créez la table « maître » à partir de laquelle toutes les partitions vont hériter.
Cette table ne contiendra pas de données. Ne définissez pas de contraintes de vérification sur cette table sauf si vous avez l'intention de les appliquer sur toutes les partitions. Il n'y a non plus aucune raison de définir des index ou des contraintes uniques sur elle.
Créez plusieurs tables « filles » qui héritent chacune de la table maître. Normalement, ces tables n'ajouteront pas de colonnes à l'ensemble hérité du maître.
Nous ferons référence aux tables enfants par le mot partition bien qu'elles se comportent comme de vraies tables PostgreSQL™.
Ajoutez les contraintes de tables aux tables partition pour définir les valeurs des clés autorisées dans chaque partition.
Voici quelques exemples typiques :
CHECK ( x = 1 ) CHECK ( comté IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( ID >= 100 AND ID < 200 )
Assurez-vous que les contraintes garantissent qu'il n'y aura pas d'intersection entre les valeurs clés permises 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 )
Ceci est mauvais car il n'est pas aisé de savoir à quelle partition appartient la clé 200.
Notez qu'il n'y a aucune différence dans la syntaxe BETWEEN et le partitionnement de listes ; ces termes sont seulement descriptifs.
Pour chaque partition, créez 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 vous avez l'intention que les valeurs clés soient uniques, alors vous devez toujours créer une contrainte unique ou une clé primaire pour chaque partition.)
En option, définissez une règle ou un déclencheur pour rediriger les modifications de la table maître vers la partition appropriée.
Assurez-vous que le paramètre de configuration constraint_exclusion est activé dans postgresql.conf. Sans ce dernier, les requêtes ne sont pas optimisées.
Par exemple, supposons que nous construisons une base de données pour une grande société de glaces. La compagnie mesure les piques de températures chaque jour ainsi que les ventes de glaces dans chaque région. À la base, nous voulons une table comme ceci :
CREATE TABLE mesure (
id_ville int not null,
date_trace date not null,
temperature int,
ventes int
);
Nous savons que la plupart des requêtes accèdent seulement à la dernière semaine, au dernier mois ou au dernier trimestre des données car l'utilisation principale de cette table sera de préparer des rapports en ligne pour la gestion. Pour réduire le nombre de données anciennes devant être restaurées, nous décidons de conserver seulement les trois dernières années. Au début de chaque mois, nous supprimerons les données de l'ancien mois.
Dans cette situation, nous pouvons utiliser le partitionnement pour nous aider dans nos différents besoins pour la table mesure. 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-dessous.
Une partition est alors 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 complète avec leur propre droits mais leur définition est héritée de la table mesure.
Ceci résoud un de nos problèmes : supprimer les anciennes données. Chaque mois, tout ce que nous aurons à faire est un DROP TABLE sur l'ancienne table enfant et créez une nouvelle table enfant pour les données du nouveau mois.
Nous devons ajouter des contraintes de table qui ne permettent pas l'intersection des ensembles de données, donc notre 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_a2005mm11 (
CHECK ( date_trace >= DATE '2005-11-01' AND date_trace < DATE '2005-12-01' )
) INHERITS (mesure);
CREATE TABLE mesure_a2005mm12 (
CHECK ( date_trace >= DATE '2005-12-01' AND date_trace < DATE '2006-01-01' )
) INHERITS (mesure);
CREATE TABLE mesure_a2006mm01 (
CHECK ( date_trace >= DATE '2006-01-01' AND date_trace < DATE '2006-02-01' )
) INHERITS (mesure);
Nous avons probablement besoin d'index sur les colonnes clés :
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);
Nous choisissons de ne pas en ajouter d'autres pour l'instant.
Si les données seront seulement ajoutées dans la dernière partition, nous pouvons configurer une règle très simple pour insérer des données. Nous devons la redéfinir chaque mois pour qu'elle pointe toujours 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 );
Nous pourrions vouloir insérer des données et que le serveur situe automatiquement la partition où cette ligne doit être ajoutée. Nous pouvons le faire avec un ensemble de règles plus complexes.
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 );
Notez que la clause WHERE de chaque règle correspond exactement à la contrainte de vérification pour cette partition.
Comme nous pouvons le voir, un schéma complexe de partitionnement demanderait un ajout substentiel de DDL. Dans l'exemple ci-dessus, nous pouvons créer une nouvelle partition chaque mois, donc il est conseillé d'écrire un script qui génère 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;
Néanmoins, le besoin de recréer la vue ajoute une étape supplémentaire à l'ajout et à la suppression de partitions individuelles de l'ensemble de données.
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. Un besoin habituel est de supprimer les anciennes partitions de données et un autre est d'ajouter périodiquement de nouvelles partitions pour les nouvelles données. Un des avantages les plus importants du partitionnement est précisément qu'il permet une exécution pratiquement instantanée de cette tâche, autrement bien plus difficile. Cela se fait en manipulant la structure de la partition, plutôt qu'en déplaçant physiquement de grosses quantités de données.
L'option la plus simple pour supprimer des anciennes données est de supprimer la partition qui n'est plus nécessaire :
DROP TABLE mesure_a2003m02;
Ceci peut supprimer très rapidement plusieurs millions d'enregistrements car il n'a pas à supprimer chaque enregistrement.
Une autre option, souvent préférable, est de supprimer la partition de la table partitionnée mais de conserver l'accès à la table directement :
ALTER TABLE mesure_a2003m02 NO INHERIT mesure;
Ceci permet la réalisation d'opérations plus poussées sur les données avant qu'elle ne soit supprimée. Par exemple, c'est souvent le bon moment pour sauvegarder les données en utilisant COPY, pg_dump ou d'autres outils. C'est aussi le temps pour agréger des données en des formats plus petits, réaliser d'autres opérations sur les données ou créer des rapports.
De façon similaire, nous pouvons ajouter une nouvelle partition pour gérer les nouvelles données. Nous pouvons créer une partition vide dans une table partitionnée comme les partitions individuelles que nous avons créé ci-dessus.
CREATE TABLE mesure_a2006m02 (
CHECK ( date_trace >= DATE '2006-02-01' AND date_trace < DATE '2006-03-01' )
) INHERITS (mesure);
Comme alternative, il est quelques fois 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, vérifier et transformer avant son 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' -- travail de préparation des autres 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 pour les tables partitionnées de la façon décrite ci-dessus. En 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 devrait parcourir chacune des partitions de la table mesure. Avec l'exclusion de contrainte activée, le planificateur examinera les contraintes de chaque partition et tentera de prouver que la partition n'a pas besoin d'être parcourue parce qu'elle ne pourrait 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.
Vous pouvez utiliser la commande EXPLAIN pour afficher la différence avec entre un plan avec constraint_exclusion à on et un plan avec ce paramètre à 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_aa04mm02 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_aa04mm03 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
...
-> Seq Scan on mesure_aa05mm12 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
-> Seq Scan on mesure_aa06mm01 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
Certaines ou toutes les partitions pourraient utiliser des parcours d'index à la place de 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. Quand nous activons l'exclusion de contrainte, nous obtenons un plan réduit significativement et qui nous renvoie 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_aa06mm01 mesure (cost=0.00..30.38 rows=543 width=0)
Filter: (date_trace >= '2006-01-01'::date)
Notez que l'exclusion de contraintes est seulement piloté par les contraintes CHECK, pas par la présence d'index. Du coup, il n'est pas nécessaire de définir des index sur les colonnes clés. Le fait qu'un index ait besoin d'être créé pour une partition donnée dépend si vous vous attendez à ce que les requêtes qui parcourent la partition parcoureront aussi généralement une grande partie de la partition ou seulement une petite partie. Un index sera utile dans le dernier cas, pas dans le premier.
Voici quelques conseils s'appliquant aux tables partitionnées :
Actuellement, il n'existe pas de moyen de vérifier que toutes les contraintes de vérification (CHECK) sont mutuellement exclusives. Le concepteur de la base de données doit faire attention.
Il n'existe pas de façon simple de spécifier les lignes ne devant pas être insérées dans la table maître. Une contrainte CHECK (false) sur la table maître sera héritée par toutes les tables filles et ne peut donc pas être utilisée dans ce but. Une possibilité revient à configurer un déclencheur ON INSERT sur la table maître qui renvoie toujours une erreur (sinon, un déclencheur peut être utilisé pour rediriger les données dans la bonne table fille au lieu d'utiliser un ensemble de règles comme suggéré ci-dessus).
Quelques conseils qui s'appliquent aux contraintes d'exclusion :
L'exclusion de contrainte fonctionne seulement quand la clause WHERE de la requête contient des constantes. Une requête avec paramètre ne sera pas optimisée car le planificateur ne sait pas quelles partitions la valeur du paramètre pourrait sélectionner à son exécution. Pour la même raison, les fonctions « stables » comme CURRENT_DATE doivent être évitées.
Évitez les comparaisons inter-type dans les contraintes CHECK car le planificateur échouera à prouver que de telles conditions sont fausses. Par exemple, la contrainte suivante fonctionnera si x est une colonne de type integer, mais pas si x est un bigint :
CHECK ( x = 1 )
Pour une colonne bigint, nous devons 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, donc un grand nombre de partitions a tendance à augmenter considérablement le temps de plannification de la requête.
N'oubliez pas que vous avez toujours besoin d'exécuter ANALYZE individuellement sur chaque partition. Une commande comme
ANALYZE mesure;
traitera seulement la table maître.