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 niveaux élevés de index, 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 pourcentage important d'une seule partition, les performances peuvent être grandement améliorées par l'utilisation avantageuse d'un parcours séquentiel sur cette partition plutôt que d'utiliser un index qui nécessiterait 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. Supprimer une partition individuelle en utilisant
DROP TABLE
ou en exécutant ALTER TABLE
DETACH PARTITION
est bien plus rapide qu'une opération
groupée. Cela évite également la surcharge due 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.
Ces 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.
PostgreSQL offre un support natif pour les formes suivantes de partitionnement :
La table est partitionnée en « intervalles » (ou échelles)
définis par une colonne clé ou par un ensemble de colonnes clés, sans
recouvrement entre les intervalles de valeurs affectées aux
différentes partitions. Il est possible, par exemple, de partitionner
par intervalles de date ou par intervalles d'identifiants pour des
objets métier particuliers. Chaque limite de l'intervalle est
comprise comme étant inclusive au point initial et exclusive au point
final. Par exemple, si l'intervalle d'une partition va de
1
à 10
, et que le prochain
intervalle va de 10
à 20
, alors
la valeur 10
appartient à la deuxième partition,
et non pas à la première.
La table est partitionnée en listant explicitement les valeurs clés qui apparaissent dans chaque partition.
La table est partitionnée en spécifiant un modulo et un reste pour chaque partition. Chaque partition contiendra les lignes pour lesquelles la valeur de hachage de la clé de partition divisée par le modulo spécifié produira le reste spécifié.
Si votre application nécessite d'utiliser d'autres formes de
partitionnement qui ne sont pas listées au-dessus, des méthodes
alternatives comme l'héritage et des vues UNION ALL
peuvent être utilisées à la place. De telles méthodes offrent de la
flexibilité, mais n'ont pas certains des bénéfices de performance du
partitionnement déclaratif natif.
PostgreSQL donne un moyen de déclarer qu'une table est divisée en partitions. La table qui est divisée est appelée table partitionnée. La déclaration inclut la méthode de partitionnement, comme décrite ci-dessus, et une liste de colonnes ou d'expressions à utiliser comme clé de partitionnement.
La table partitionnée est elle-même une table « virtuelle » sans stockage propre. À la place, le stockage se fait dans les partitions, qui sont en fait des tables ordinaires mais associées à la table partitionnée. Chaque partition enregistre un sous-ensemble de données correspondant à la définition de ses limites de partition. Tous les lignes insérées dans une table partitionnée seront transférées sur la partition appropriée suivant les valeurs des colonnes de la clé de partitionnement. Mettre à jour la clé de partitionnement d'une ligne causera son déplacement dans une partition différente si elle ne satisfait plus les limites de sa partition originale.
Les partitions peuvent elles-mêmes être définies comme des tables partitionnées, ce qui aboutirait à du sous-partitionnement. Bien que toutes les partitions doivent avoir les mêmes colonnes que leur parent partitionné, les partitions peuvent avoir leurs propres index, contraintes et valeurs par défaut, différents de ceux des autres partitions. Voir CREATE TABLE pour plus de détails sur la création des tables partitionnées et des partitions.
Il n'est pas possible de transformer une table standard en table
partitionnée et inversement. Par contre, il est possible d'ajouter une
table standard ou une table partitionnée existante comme une partition
d'une table partitionnée, ou de supprimer une partition d'une table
partitionnée, pour la transformer en table standard ; ceci peut
simplifier et accélérer de nombreux traitements de maintenance. Voir
ALTER TABLE pour en apprendre plus sur les
sous-commandes ATTACH PARTITION
et DETACH
PARTITION
.
Les partitions peuvent également être des tables étrangères, mais il faut faire très attention car c'est de la responsabilité de l'utilisateur que le contenu de la table distante satisfasse la clé de partitionnement. Il existe aussi d'autres restrictions. Voir CREATE FOREIGN TABLE pour plus d'informations.
Imaginons que nous soyons en train de construire une base de données pour une grande société de crème glacée. La société mesure les pics de températures chaque jour, ainsi que les ventes de crème glacée dans chaque région. Conceptuellement, nous voulons une table comme ceci :
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales 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.
Pour utiliser le partitionnement déclaratif dans ce cas d'utilisation, il faut suivre les étapes suivantes :
Créer une table measurement
comme une table
partitionnée en spécifiant la clause PARTITION BY
,
ce qui inclut la méthode de partitionnement ( RANGE
dans ce cas) ainsi que la colonne ou la liste de colonnes à utiliser
comme clé de partitionnement.
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);
Créez les partitions. La définition de chaque partition doit spécifier les limites qui correspondent à la méthode de partitionnement ainsi qu'à la clé de partitionnement du parent. Veuillez noter que spécifier des limites telles que les valeurs de la nouvelle partition pourront se chevaucher avec celles d'une ou plusieurs autres partitions retournera une erreur.
Les partitions ainsi créées sont de tous les points de vue des tables PostgreSQL normales (ou, potentiellement, des tables distantes). Il est possible de spécifier un tablespace et des paramètres de stockage pour chacune des partitions séparément.
Pour notre exemple, chaque partition devrait contenir un mois de données pour correspondre au besoin de supprimer un mois de données à la fois. Les commandes pourraient ressembler à ceci :
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); ... CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'); CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01') TABLESPACE fasttablespace; CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01') WITH (parallel_workers = 4) TABLESPACE fasttablespace;
(Pour rappel, les partitions adjacentes peuvent partager une valeur de limite car les limites hautes sont traitées comme des limites exclusive.)
Si vous voulez mettre en place du sous-partitionnement, spécifiez la
clause PARTITION BY
dans les commandes utilisées
pour créer des partitions individuelles, par exemple :
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') PARTITION BY RANGE (peaktemp);
Après avoir créé les partitions de
measurement_y2006m02
, toute donnée insérée
dans measurement
qui correspond à
measurement_y2006m02
(ou donnée qui est
directement insérée dans
measurement_y2006m02
, ce qui est autorisé à
condition que la contrainte de partition soit respectée) sera
redirigée vers l'une de ses partitions en se basant sur la colonne
peaktemp
. La clé de partition spécifiée
pourrait se chevaucher avec la clé de partition du parent, il faut
donc faire spécialement attention lorsque les limites d'une
sous-partition sont spécifiées afin que l'ensemble de données qu'elle
accepte constitue un sous-ensemble de ce que les propres limites de la
partition acceptent ; le système n'essayera pas de vérifier si
c'est vraiment le cas.
Insérer des données dans la table parent, données qui ne correspondent pas à une des partitions existantes, causera une erreur ; une partition appropriée doit être ajoutée manuellement.
Il n'est pas nécessaire de créer manuellement les contraintes de table décrivant les conditions des limites de partition pour les partitions. De telles contraintes seront créées automatiquement.
Créez un index sur la ou les colonnes de la clé, ainsi que tout autre index que vous pourriez vouloir pour chaque partition. (L'index sur la clé n'est pas strictement nécessaire, mais c'est utile dans la plupart des scénarios.) Ceci crée automatiquement un index correspondant sur chaque partition, et toutes les partitions que vous créerez ou attacherez plus tard auront elles-aussi cet index. Un index ou une contrainte unique déclarée sur une table partitionnée est « virtuel » de la même façon que la table partitionnée l'est : les données réelles sont dans les index enfants sur les partitions individuelles.
CREATE INDEX ON measurement (logdate);
Assurez-vous que le paramètre de configuration enable_partition_pruning ne soit pas désactivé dans
postgresql.conf
. S'il l'est, les requêtes ne
seront pas optimisées comme voulu.
Dans l'exemple ci-dessus, nous créerions une nouvelle partition chaque mois, il serait donc avisé d'écrire un script qui génère le DDL nécessaire automatiquement.
Normalement, l'ensemble des partitions établies lors de la définition initiale de la table n'a pas vocation à demeurer statique. Il est courant de vouloir supprimer les partitions contenant d'anciennes données et d'ajouter périodiquement de nouvelles partitions pour de nouvelles données. Un des avantages les plus importants du partitionnement est précisément qu'il permet d'exécuter instantanément cette tâche de maintenance normalement pénible, en manipulant la structure partitionnée, plutôt que de bouger physiquement de grands ensembles de données.
Le moyen le plus simple pour supprimer d'anciennes données est de supprimer la partition qui n'est plus nécessaire :
DROP TABLE measurement_y2006m02;
Cela peut supprimer des millions d'enregistrements très rapidement, car
il n'est pas nécessaire de supprimer chaque enregistrement séparément.
Veuillez noter toutefois que la commande ci-dessus nécessite de prendre
un verrou de type ACCESS EXCLUSIVE
sur la table
parente.
Une autre possibilité, généralement préférable, est de ne pas supprimer la partition de la table partitionnée, mais de la conserver en tant que table à part entière. Cela peut prendre deux formes :
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;
Elles permettent d'effectuer ensuite d'autres opérations sur les données
avant la suppression. Par exemple, c'est souvent le moment idéal pour
sauvegarder les données en utilisant COPY
,
pg_dump, ou des outils similaires. Ce peut
aussi être le bon moment pour agréger les données dans un format moins
volumineux, effectuer d'autres manipulations des données, ou lancer des
rapports. La première forme de la commande requiert un verrou
ACCESS EXCLUSIVE
sur la table parent. Ajouter la
clause CONCURRENTLY
, comme indiqué dans la deuxième
forme, permet que l'opération de détachement ne réclame qu'un verrou
SHARE UPDATE EXCLUSIVE
sur la table parent.
Cependant, voir ALTER TABLE ...
DETACH PARTITION
pour des détails sur les
restrictions.
De la même manière, nous pouvons ajouter une nouvelle partition pour gérer les nouvelles données. Nous pouvons créer une partition vide dans la table partitionnée exactement comme les partitions originales ont été créées précédemment :
CREATE TABLE measurement_y2008m02 PARTITION OF measurement FOR VALUES FROM ('2008-02-01') TO ('2008-03-01') TABLESPACE fasttablespace;
De manière alternative, il est parfois plus pratique de créer la nouvelle
table en dehors de la structure partitionnée, et de l'attacher comme
partition plus tard. Cela permet de charger de nouvelles données, de
les vérifier et d'y effectuer des transformations avant que les données
apparaissent dans la table partitionnée. De plus, l'opération
ATTACH PARTITION
ne requiert que le verrou
SHARE UPDATE EXCLUSIVE
sur la table partitionnée
contrairement au verrou ACCESS EXCLUSIVE
qui est
nécessaire pour CREATE TABLE ... PARTITION OF
, donc
cette opération est préférable sur la table partitionnée.
L'option CREATE TABLE
... LIKE
est utile pour éviter de répéter à chaque fois la
définition de la table parent :
CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) TABLESPACE fasttablespace; ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- et éventuellement d'autres étapes de préparation ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
Avant d'exécuter une commande ATTACH PARTITION
, il est
recommandé de créer une contrainte CHECK
sur la table
qui doit être attachée correspondant à la contrainte de la partition
désirée, comme indiquée ci-dessus. De cette manière, le système pourra
ignorer le parcours de table qui est habituellement nécessaire pour
valider la contrainte implicite de partition. Sans la contrainte
CHECK
, la table sera parcourue pour valider la
contrainte de partition, alors qu'elle aura pris un verrou
ACCESS EXCLUSIVE
sur cette partition. Il est
recommandé de supprimer la contrainte CHECK
redondante après la fin de la commande ATTACH
PARTITION
. Si la table en cours d'attachement est elle-même
une table partitionnée, alors chacune de ses sous-partitions sera
verrouillée récursivement et parcourue jusqu'à ce qu'une contrainte
CHECK
convenable soit rencontrée ou que les
partitions feuilles sont atteintes.
De façon similaire, si la table partitionnée a une partition par défaut
(DEFAULT
), il est recommandé de créer une contrainte
CHECK
qui exclut la contrainte de la partition à
attacher. Si cela n'est pas fait, alors la partition
DEFAULT
sera parcourue pour vérifier qu'elle ne
contienne aucun enregistrement qui devrait être placé dans la partition
en cours d'attachement. Cette opération sera réalisée en détenant un
verrou ACCESS EXCLUSIVE
sur la partition par défaut.
Si la partition par défaut est elle-même une table partitionnées, alors
chacune de ses partitions sera vérifiée récursivement de la même façon
que la table en cours d'attachement, comme indiqué ci-dessus.
Comme expliqué ci-dessus, il est possible de créer des index sur des
tables partitionnées, pour qu'ils soient appliqués automatiquement sur
la hiérarchie entière. C'est très pratique, car non seulement les
partitions déjà créées seront indexées, mais aussi toutes les partitions
créées dans le futur le seront. Une limitation est qu'il n'est pas
possible d'utiliser CONCURRENTLY
pour créer un index
partitionné. Pour éviter des verrous trop longs, il est possible
d'utiliser CREATE INDEX ON ONLY
sur la table
partitionnée ; un tel index sera marqué invalide, et il ne sera pas
appliqué automatiquement sur les partitions. Les index sur les
partitions peuvent être créés individuellement avec
CONCURRENTLY
, et plus tard rattachés à l'index sur le
parent avec ALTER INDEX .. ATTACH PARTITION
. Une fois
les index de toutes les partitions attachés à l'index parent, celui-ci
sera automatiquement marqué comme valide. Exemple :
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales); CREATE INDEX CONCURRENTLY measurement_usls_200602_idx ON measurement_y2006m02 (unitsales); ALTER INDEX measurement_usls_idx ATTACH PARTITION measurement_usls_200602_idx; ...
Cette technique peut aussi être utilisée avec des contraintes
UNIQUE
et PRIMARY KEY
; les
index sont créés implicitement quand la contrainte est créée.
Exemple :
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate); ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate); ALTER INDEX measurement_city_id_logdate_key ATTACH PARTITION measurement_y2006m02_city_id_logdate_key; ...
Les limitations suivantes s'appliquent aux tables partitionnées :
Pour créer une contrainte d'unicité ou une clé primaire sur une table partitionnée, la clé de partitionnement ne doit pas inclure des expressions ou des appels de fonction, et les colonnes de la contrainte doivent inclure toutes les colonnes de la clé de partitionnement. Cette limitation existe parce que les index individuels créant la contrainte peuvent seulement forcer l'unicité sur leur propre partition ; de ce fait, la structure même de la partition doit garantir qu'il n'existe pas de duplicats dans les différentes partitions.
Il n'existe aucun moyen de créer une contrainte d'exclusion sur toute la table partitionnée. Il est seulement possible de placer une telle contrainte sur chaque partition individuellement. Cette limitation vient là-aussi de l'impossibilité de fixer les restrictions entre partitions.
Les triggers BEFORE ROW
ne peuvent pas changer la
partition de destination d'une nouvelle ligne.
Mélanger des relations temporaires et permanentes dans la même arborescence de partitions n'est pas autorisé. Par conséquent, si une table partitionnée est permanente, ses partitions doivent l'être aussi ; de même si la table partitionnée est temporaire, ses partitions doivent l'être aussi. Lors de l'utilisation de relations temporaires, tous les membres de l'arborescence des partitions doivent être issus de la même session.
Les colonnes générées sont ignorées par la réplication logique et
ne peuvent pas être indiquées dans une liste de colonnes de la
commande CREATE PUBLICATION
.
Les partitions individuelles sont liées à leur table partitionnée en utilisant l'héritage en arrière plan. Néanmoins, il n'est pas possible d'utiliser toutes les fonctionnalités génériques de l'héritage avec les tables en partitionnement déclaratif et leurs partitions, comme indiqué ci-dessous. Notamment, une partition ne peut pas avoir d'autres parents que leur table partitionnée. Une table ne peut pas non plus hériter d'une table partitionnée et d'une table normale. Cela signifie que les tables partitionnées et leur partitions ne partagent jamais une hiérarchie d'héritage avec des tables normales.
Comme une hiérarchie de partitionnement consistant en la table
partitionnée et ses partitions est toujours une hiérarchie d'héritage,
tableoid
et toutes les règles normales
d'héritage s'appliquent comme décrites dans
Section 5.10, avec quelques exceptions :
Les partitions ne peuvent pas avoir des colonnes qui ne sont pas
présentes chez le parent. Il n'est pas possible d'indiquer des
colonnes lors de la création de partitions avec CREATE
TABLE
, pas plus qu'il n'est possible d'ajouter des colonnes
aux partitions après leur création en utilisant ALTER
TABLE
. Les tables pourraient être ajoutées en tant que
partition avec ALTER TABLE ... ATTACH PARTITION
seulement si leurs colonnes correspondent exactement à leur parent,
en incluant toute colonne oid
.
Les contraintes CHECK
et NOT
NULL
d'une table partitionnée sont toujours héritées par
toutes ses partitions. La création des contraintes
CHECK
marquées NO INHERIT
n'est
pas autorisée sur les tables partitionnées. Vous ne pouvez pas
supprimer une contrainte NOT NULL
de la colonne
d'une partition si la même contrainte est présente dans la table
parent.
Utiliser ONLY
pour ajouter ou supprimer une
contrainte uniquement sur la table partitionnée est supportée tant
qu'il n'y a pas de partitions. Dès qu'une partition existe, utiliser
ONLY
renverra une erreur pour toute contrainte autre
que UNIQUE
et PRIMARY KEY
. À la
place, des
contraintes sur les partitions elles-mêmes peuvent être ajoutées et
(si elles ne sont pas présentes sur la table parent) supprimées.
Comme une table partitionnée n'a pas de données elle-même, toute
tentative d'utiliser TRUNCATE
ONLY
sur une table partitionnée renverra
systématiquement une erreur.
Bien que le partitionnement déclaratif natif soit adapté pour la plupart des cas d'usage courant, il reste certains cas où une approche plus flexible peut être utile. Le partitionnement peut être implémenté en utilisant l'héritage de table, ce qui permet d'autres fonctionnalités non supportées par le partitionnement déclaratif, comme :
Pour le partitionnement déclaratif, les partitions doivent avoir exactement les mêmes colonnes que la table partitionnée, alors qu'avec l'héritage de table, les tables filles peuvent avoir des colonnes supplémentaires non présentes dans la table parente.
L'héritage de table permet l'héritage multiple.
Le partitionnement déclaratif ne prend en charge que le partitionnement par intervalle, par liste et par hachage, tandis que l'héritage de table permet de diviser les données de la manière choisie par l'utilisateur. (Notez, cependant, que si l'exclusion de contrainte n'est pas en mesure d'élaguer efficacement les tables filles, la performance de la requête peut être faible).
Cet exemple construit une structure de partitionnement équivalente à l'exemple de partitionnement déclaratif ci-dessus. Procédez aux étapes suivantes :
Créez la table « mère » (ou racine, ou principale), de
laquelle toutes les tables « filles » hériteront. Cette
table ne contiendra aucune donnée. Ne définissez aucune contrainte de
vérification sur cette table, à moins que vous n'ayez l'intention de
l'appliquer de manière identique sur toutes les tables filles. Il n'y
a aucun intérêt à définir d'index ou de contrainte unique sur elle
non plus. Pour notre exemple, la table mère correspond à la table
measurement
définie à l'origine :
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );
Créez plusieurs tables « filles », chacune héritant de la table mère. Normalement, ces tables n'ajouteront aucune colonne à celles héritées de la table mère. Comme avec le partitionnement déclaratif, ces tables filles sont des tables PostgreSQL à part entière (ou des tables distantes).
CREATE TABLE measurement_y2006m02 () INHERITS (measurement); CREATE TABLE measurement_y2006m03 () INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 () INHERITS (measurement); CREATE TABLE measurement_y2007m12 () INHERITS (measurement); CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
Ajoutez les contraintes de tables, sans qu'elles se chevauchent, sur les tables filles pour définir les valeurs de clé autorisées dans chacune.
Des exemples typiques seraient :
CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 )
Assurez-vous que les contraintes garantissent qu'il n'y a pas de chevauchement entre les valeurs de clés permises dans différentes tables filles. Une erreur fréquente est de mettre en place des contraintes d'intervalle comme ceci :
CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
Cet exemple est faux puisqu'il n'est pas possible de savoir à quelle table fille appartient la valeur de clé 200. À la place, les intervalles devraient être définis ainsi :
CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement);
Pour chaque table fille, créez un index sur la ou les colonnes de la clé, ainsi que tout autre index que vous voudriez.
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
Nous voulons que notre application soit capable de dire
INSERT INTO measurement ...
, et de voir ses
données redirigées dans la table fille appropriée. Nous pouvons
réaliser cela en ajoutant un trigger sur la table mère. Si les
données doivent être ajoutées sur la dernière table fille uniquement,
nous pouvons utiliser un trigger avec une fonction très
simple :
CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO measurement_y2008m01 VALUES (NEW.*); RETURN NULL; END; $$ LANGUAGE plpgsql;
Après avoir créé la fonction, nous créons le trigger qui appelle la fonction trigger :
CREATE TRIGGER insert_mesure_trigger BEFORE INSERT ON mesure FOR EACH ROW EXECUTE FUNCTION mesure_insert_trigger();
Une telle fonction doit être redéfinie chaque mois pour toujours insérer sur la table fille active. La définition du trigger n'a pas besoin d'être redéfinie.
Il est également possible de laisser le serveur localiser la table fille dans laquelle doit être insérée la ligne. Une fonction plus complexe peut alors être utilisée :
CREATE OR REPLACE FUNCTION mesure_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.date_trace >= DATE '2006-02-01' AND NEW.date_trace < DATE '2006-03-01' ) THEN INSERT INTO mesure_a2006m02 VALUES (NEW.*); ELSIF ( NEW.date_trace >= DATE '2006-03-01' AND NEW.date_trace < DATE '2006-04-01' ) THEN INSERT INTO mesure_a2006m03 VALUES (NEW.*); ... ELSIF ( NEW.date_trace >= DATE '2008-01-01' AND NEW.date_trace < DATE '2008-02-01' ) THEN INSERT INTO mesure_a2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date en dehors de l''intervalle. Corrigez la fonction mesure_insert_trigger() !'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
La définition du trigger est la même qu'avant. Notez que chaque test
IF
doit correspondre exactement à la contrainte
CHECK
de la table fille correspondante.
Bien que cette fonction soit plus complexe que celle pour un seul mois, il n'est pas nécessaire de l'actualiser aussi fréquemment, les branches pouvant être ajoutées en avance.
En pratique, il vaudrait mieux vérifier d'abord la dernière table fille créée si la plupart des insertions lui sont destinées. Pour des raisons de simplicité, les tests du trigger sont présentés dans le même ordre que les autres parties de l'exemple.
Une approche différente du trigger est la redirection des insertions par des règles sur la table mère. Par exemple :
CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) DO INSTEAD INSERT INTO measurement_y2006m02 VALUES (NEW.*); ... CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) DO INSTEAD INSERT INTO measurement_y2008m01 VALUES (NEW.*);
Une règle a un surcoût bien plus important qu'un trigger, mais il n'est payé qu'une fois par requête plutôt qu'une fois par ligne. Cette méthode peut donc être avantageuse pour les insertions en masse. Toutefois, dans la plupart des cas, la méthode du trigger offrira de meilleures performances.
Soyez conscient que COPY
ignore les règles. Si vous
voulez utiliser COPY
pour insérer des données,
vous devrez les copier dans la bonne table fille plutôt que dans la
table mère. COPY
déclenche les triggers, vous
pouvez donc l'utiliser normalement si vous utilisez l'approche par
trigger.
Un autre inconvénient à l'approche par règle est qu'il n'y a pas de moyen simple de forcer une erreur si l'ensemble de règles ne couvre pas la date d'insertion ; les données iront silencieusement dans la table mère à la place.
Assurez-vous que le paramètre de configuration constraint_exclusion ne soit pas désactivé dans le
fichier postgresql.conf
; sinon il pourrait
y avoir des accès inutiles aux autres tables.
Comme nous pouvons le voir, une hiérarchie complexe de tables peut nécessiter une quantité de DDL non négligeable. Dans l'exemple ci-dessus, nous créerions une nouvelle table fille chaque mois, il serait donc sage d'écrire un script qui génère le DDL automatiquement.
Pour supprimer les anciennes données rapidement, il suffit de supprimer la table fille qui n'est plus nécessaire :
DROP TABLE measurement_a2006m02;
Pour enlever une table fille de la hiérarchie d'héritage, mais en en gardant l'accès en tant que table normale :
ALTER TABLE measurement_a2006m02 NO INHERIT measurement;
Pour ajouter une nouvelle table fille pour gérer les nouvelles données, créez une table fille vide, tout comme les tables filles originales ont été créées ci-dessus :
CREATE TABLE measurement_a2008m02 ( CHECK ( date_trace >= DATE '2008-02-01' AND date_trace < DATE '2008-03-01' ) ) INHERITS (measurement);
Une autre alternative est de créer et de remplir la nouvelle table enfant avant de l'ajouter à la hiérarchie de la table. Ceci permet aux données d'être chargées, vérifiées et transformées avant d'être rendues visibles aux requêtes sur la table parente.
CREATE TABLE measurement_a2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_a2008m02 ADD CONSTRAINT y2008m02 CHECK ( date_trace >= DATE '2008-02-01' AND date_trace < DATE '2008-03-01' ); \copy measurement_a2008m02 from 'measurement_a2008m02' -- quelques travaux de préparation des données ALTER TABLE measurement_a2008m02 INHERIT measurement;
Les restrictions suivantes s'appliquent au partitionnement par héritage :
Il n'existe pas de moyen automatique de vérifier que toutes les
contraintes de vérification (CHECK
) sont
mutuellement exclusives. Il est plus sûr de créer un code qui
fabrique les tables filles, et crée et/ou modifie les objets associés
plutôt que de les créer manuellement ;
Les contraintes d'index et de clés étrangères s'appliquent à des tables seules et non à leurs enfants par héritage, il y a donc des limitations à connaître.
Les schémas montrés ici supposent que les colonnes clés du
partitionnement d'une ligne ne changent jamais ou, tout du moins, ne
changent pas suffisamment pour nécessiter un déplacement vers une
autre partition. Une commande UPDATE
qui tentera
de le faire échouera à cause des contraintes
CHECK
. Si vous devez gérer ce type de cas, des
triggers sur mise à jour peuvent être placés sur les tables filles,
mais cela rend la gestion de la structure beaucoup plus complexe.
Si VACUUM
ou ANALYZE
sont lancés
manuellement, n'oubliez pas de les lancer sur chaque table fille. Une
commande comme :
ANALYZE measurement;
ne traitera que la table mère.
Les commandes INSERT
avec des clauses ON
CONFLICT
ont peu de chances de fonctionner comme attendu,
puisque l'action du ON CONFLICT
n'est effectuée
que dans le cas de violations d'unicité dans la table cible, pas dans
les filles.
Des triggers ou des règles seront nécessaires pour rediriger les lignes vers la table fille voulue, à moins que l'application ne soit explicitement au courant du schéma de partitionnement. Les triggers peuvent être compliqués à écrire, et seront bien plus lents que la redirection de ligne effectuée en interne par le partitionnement déclaratif.
L'élagage des partitions
(Partition pruning
) est une technique d'optimisation
des requêtes qui vise à améliorer les performances des tables à
partitionnement déclaratif. À titre d'exemple :
SET enable_partition_pruning = on; -- défaut SELECT count(*) FROM measurement WHERE date_trace >= DATE '2008-01-01';
Sans l'élagage de partition, la requête ci-dessus parcourrait chacune des
partitions de la table measurement
. Avec
l'élagage de partition activé, le planificateur examinera la définition
de chaque partition, et montrera qu'il n'est pas nécessaire de la
parcourir puisqu'elle ne contient aucune ligne respectant la clause WHERE
de la requête. Lorsque le planificateur peut l'établir, il exclut
(élague) la partition du plan de recherche.
En utilisant la commande EXPLAIN
et le paramètre de
configuration enable_partition_pruning, il est
possible de voir la différence entre un plan pour lequel des partitions
ont été élaguées et celui pour lequel elles ne l'ont pas été. Un plan
typique non optimisé pour ce type de configuration de table
serait :
SET enable_partition_pruning = off; EXPLAIN SELECT count(*) FROM measurement WHERE date_trace >= DATE '2008-01-01'; QUERY PLAN ----------------------------------------------------------------------------------- Aggregate (cost=188.76..188.77 rows=1 width=8) -> Append (cost=0.00..181.05 rows=3085 width=0) -> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) ... -> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-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 plus vieilles partitions pour répondre à cette requête. Lorsque l'élagage de partitions est activé, nous obtenons un plan significativement moins coûteux, pour le même résultat :
SET enable_partition_pruning = on; EXPLAIN SELECT count(*) FROM measurement WHERE date_trace >= DATE '2008-01-01'; QUERY PLAN ----------------------------------------------------------------------------------- Aggregate (cost=37.75..37.76 rows=1 width=8) -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date)
Il est à noter que l'élagage des partitions n'est piloté que par les contraintes définies implicitement par les clés de partition, et non par la présence d'index. Il n'est donc pas nécessaire de définir des index sur les colonnes clés. Pour savoir si un index doit être créé sur une partition donnée, il vous faut juger si les requêtes sur cette partition en parcourent généralement une grande partie, ou seulement une petite. Un index sera utile dans ce dernier cas, mais pas dans le premier.
L'élagage des partitions peut être effectué non seulement lors de la
planification d'une requête, mais aussi lors de son exécution. C'est
utile pour élaguer plus de partitions lorsque les clauses contiennent des
expressions de valeurs inconnues au moment de la planification de la
requête, par exemple des paramètres définis dans une instruction
PREPARE
, utilisant une valeur obtenue d'une
sous-requête, ou une valeur paramétrée sur la partie interne d'une
jointure en boucle imbriquée (nested loop join
).
L'élagage de partition pendant l'exécution peut être réalisé à l'un des
moments suivant :
Lors de l'initialisation du plan d'exécution. L'élagage de partition
peut être effectué pour les valeurs de paramètres connues dès cette
phase. Les partitions élaguées pendant cette étape n'apparaîtront pas
dans l'EXPLAIN
ou l'EXPLAIN
ANALYZE
de la requête. Il est même possible de déterminer le
nombre de partitions supprimées pendant cette phase en observant la
propriété « Subplans Removed »(sous-plans supprimés) dans
la sortie d'EXPLAIN
.
Pendant l'exécution effective du plan d'exécution. L'élagage des
partitions peut également être effectué pour supprimer des partitions
en utilisant des valeurs qui ne sont connues que pendant l'exécution
de la requête. Cela inclut les valeurs des sous-requêtes et des
paramètres issus de l'exécution, comme des jointures par boucle
imbriquée(nested loop join
) paramétrées. Comme la
valeur de ces paramètres peut changer plusieurs fois pendant
l'exécution de la requête, l'élagage de partitions est effectué chaque
fois que l'un des paramètres d'exécution utilisés pour celui-ci
change. Déterminer si les partitions ont été élaguées pendant cette
phase nécessite une inspection minutieuse de la propriété
loops
de la sortie d'EXPLAIN
ANALYZE
. Les sous-plans correspondant aux différentes
partitions peuvent avoir différentes valeurs dépendant du nombre de
fois où chacun d'eux a été élagué lors de l'exécution. Certains
peuvent être affichés comme (never executed)
(littéralement, jamais exécuté
) s'ils sont élagués
à chaque fois.
L'élagage des partitions peut être désactivé à l'aide du paramètre enable_partition_pruning.
Une contrainte d'exclusion est une technique d'optimisation de requêtes similaire à l'élagage de partitions. Bien qu'elle soit principalement utilisée pour les tables partitionnées avec l'ancienne méthode par héritage, elle peut être utilisée à d'autres fins, y compris avec le partitionnement déclaratif.
Les contraintes d'exclusion fonctionnent d'une manière très similaire à
l'élagage de partitions, sauf qu'elles utilisent les contraintes
CHECK
de chaque table (d'où le nom) alors que
l'élagage de partition utilise les limites de partition de la table, qui
n'existent que dans le cas d'un partitionnement déclaratif. Une autre
différence est qu'une contrainte d'exclusion n'est appliquée qu'à la
planification ; il n'y a donc pas de tentative d'écarter des
partitions à l'exécution.
Le fait que les contraintes d'exclusion utilisent les contraintes
CHECK
les rend plus lentes que l'élagage de
partitions, mais peut être un avantage : puisque les contraintes
peuvent être définies même sur des tables avec partitionnement
déclaratif, en plus de leurs limites internes, les contraintes
d'exclusion peuvent être capables de supprimer des partitions
supplémentaires pendant la phase de planification de la requête.
La valeur par défaut (et donc recommandée) de constraint_exclusion n'est ni on
ni
off
, mais un état intermédiaire appelé
partition
, qui fait que la technique n'est appliquée
qu'aux requêtes qui semblent fonctionner avec des tables partitionnées
par héritage. La valeur on
entraîne que le
planificateur examine les contraintes CHECK
dans
toutes les requêtes, y compris les requêtes simples qui ont peu de chance
d'en profiter.
Les avertissement suivants s'appliquent à l'exclusion de contraintes :
Les contraintes d'exclusion ne sont appliquées que lors de la phase de planification de la requête, contrairement à l'élagage de partition, qui peut être appliqué lors de la phase d'exécution.
La contrainte d'exclusion ne fonctionne que si la clause
WHERE
de la requête contient des constantes (ou des
paramètres externes). Par exemple, une comparaison avec une fonction
non immutable comme CURRENT_TIMESTAMP
ne peut pas
être optimisée car le planificateur ne peut pas savoir dans quelle
table fille la valeur de la fonction ira lors de l'exécution.
Les contraintes de partitionnement doivent rester simples. Dans le cas contraire, le planificateur peut rencontrer des difficultés à déterminer les tables filles qu'il n'est pas nécessaire de parcourir. Des conditions simples d'égalité pour le partitionnement de liste, ou des tests d'intervalle simples lors de partitionnement par intervalles sont recommandées, comme illustré dans les exemples précédents. Une règle générale est que les contraintes de partitionnement ne doivent contenir que des comparaisons entre les colonnes partitionnées et des constantes, à l'aide d'opérateurs utilisables par les index B-tree, car seules les colonnes indexables avec un index B-tree sont autorisées dans la clé de partitionnement.
Toutes les contraintes sur toutes les tables filles de la table parente sont examinées lors de l'exclusion de contraintes. De ce fait, un grand nombre de filles augmente considérablement le temps de planification de la requête. Ainsi, l'ancien partitionnement par héritage fonctionnera bien jusqu'à, peut-être, une centaine de tables enfant ; n'essayez pas d'en utiliser plusieurs milliers.
Il faut choisir avec soin le partitionnement d'une table car les performances à la planification et à l'exécution peuvent pâtir d'une mauvaise conception.
Un des choix les plus cruciaux portera sur la ou les colonnes par
lesquelles vous partitionnerez. Souvent le meilleur choix sera la colonne
ou l'ensemble de colonnes qui apparaissent le plus souvent dans les
clauses WHERE
des requêtes exécutées sur la table
partitionnée. Les clauses WHERE
qui sont compatibles
avec les contraintes des limites des partitions peuvent être utilisées
pour élaguer les partitions inutiles. Cependant, le choix peut vous être
imposé par des exigences sur la PRIMARY KEY
ou une
contrainte UNIQUE
. La suppression de données
indésirables est aussi un facteur à considérer pour préparer votre
stratégie de partitionnement. Une partition entière peut être détachée
assez vite, et cela peut valoir le coup de concevoir votre
partitionnement pour que toutes les données à supprimer en même temps
soient situées dans la même partition.
Choisir le nombre cible de partitions par lequel diviser la table est
aussi une décision critique à prendre. Ne pas avoir assez de partitions
peut signifier que les index resteront trop gros, et que la localité des
données restera faible, ce qui entraînera de mauvais hit
ratios. Cependant, diviser la table en trop de partitions
a aussi ses inconvénients. Trop de partitions peuvent entraîner des temps
de planification plus longs et une plus grande consommation de mémoire
pendant la planification comme pendant l'exécution, comme indiqué plus
bas. Lors du choix du partitionnement de votre table, il est aussi
important de considérer ce qui pourrait changer dans le futur. Par
exemple, si vous choisissez d'avoir une partition par client alors que
vous n'avez actuellement qu'un petit nombre de gros clients, considérez
les implications si, dans quelques années, vous vous retrouvez avec un
grand nombre de petits clients. Dans ce cas, il serait mieux de choisir
une partition par HASH
et de choisir un nombre
raisonnable de partitions plutôt que d'essayer de partitionner par
LIST
et d'espérer que le nombre de clients n'augmente
pas au-delà de ce qu'il est en pratique possible de partitionner.
Sous-partitionner peut être utile pour diviser encore des partitions qui devraient devenir plus grandes que d'autres partitions. Une autre option est d'utiliser le partitionnement par intervalle avec plusieurs colonnes dans la clé de partitionnement. Chacune de ses solutions peut facilement amener à un nombre excessif de partitions, il convient donc de rester prudent.
Il est important de considérer le surcroît de travail pour la planification et l'exécution dû au partitionnement. Le planificateur de requêtes est généralement capable de manipuler correctement des hiérarchies jusqu'à plusieurs milliers de partitions, pourvu que les requêtes courantes lui permettent d'élaguer toutes les partitions à l'exception d'un petit nombre. Les temps de planification s'allongent et la consommation de mémoire augmente s'il reste beaucoup de partitions une fois que le planificateur a fait l'élagage. Une autre raison de se méfier d'un grand nombre de partitions est que la consommation mémoire du serveur peut augmenter significativement au fil du temps, particulièrement si beaucoup de sessions touchent de nombreuses partitions. La cause en est que chaque partition a besoin que ses métadonnées soient chargées dans la mémoire locale d'une session qui y touche.
Avec une charge de type entrepôt de données, il y a plus de sens à utiliser un grand nombre de partitions que pour une charge de type OLTP. Généralement, en décisionnel, le temps de planification est moins un souci puisque la majorité du temps de traitement est dépensé pendant l'exécution. Avec l'un comme l'autre de ces types de charge, il est important de prendre tôt la bonne décision, car re-partitionner de grandes quantités de données peut être douloureusement long. Des simulation de la charge attendue sont souvent souhaitables pour optimiser la stratégie de partitionnement. Ne supposez jamais que plus de partitions valent mieux que moins de partitions et vice-versa.