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 :

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 :

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 to partitions, qui sont en fait des tables ordinaires mais associées avec 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 clonnes que leur parent partitionné, es 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.

CREATE TABLE mesure (
    id_ville        int not null,
    date_trace      date not null,
    temperature     int,
    ventes          int
);

DROP TABLE measurement_y2006m02;
    

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
    

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;
    

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' );
    

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;
...
    

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;
...
    

Bien que le partitionnement déclaratif natif soit adapté pour la plupart des cas d'usage courant, il y a 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 :

DROP TABLE mesure_a2006m02;
    

ALTER TABLE mesure_a2006m02 NO INHERIT mesure;
    

CREATE TABLE mesure_a2008m02 (
    CHECK ( date_trace >= DATE '2008-02-01' AND date_trace < DATE '2008-03-01' )
    ) INHERITS (mesure);
    

CREATE TABLE mesure_a2008m02
  (LIKE mesure INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE mesure_a2008m02 ADD CONSTRAINT y2008m02
   CHECK ( date_trace >= DATE '2008-02-01' AND date_trace < DATE '2008-03-01' );
\copy mesure_a2008m02 from 'mesure_a2008m02'
-- quelques travaux de préparation des données
ALTER TABLE mesure_a2008m02 INHERIT mesure;
    

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 mesure WHERE date_trace >= DATE '2008-01-01';

Sans l'élagage de partition, la requête ci-dessus parcourrait chacune des partitions de la table mesure. 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 mesure 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 mesure 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 :

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é 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 dès 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 :

Il faut choisir avec soin le partitionnement d'une table car les performances en 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 meilleur 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 grandit s'il reste beaucoup de partitions une fois que le planificateur a fait l'élagage. C'est particulièrement vrai pour les commandes UPDATE et DELETE. 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.