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 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 spécifier comment diviser une table en morceaux appelés partitions. La table qui est divisée est appelée table partitionnée. La spécification consiste en une méthode de partitionnement et une liste de colonnes ou expressions à utiliser comme la clé de partitionnement.

Toutes les lignes insérées dans la table partitionnée seront redirigées vers une des partitions en se basant sur la valeur de la clé de partitionnement. Chaque partition a un sous-ensemble des données défini par ses limites de partition. Pour l'instant, les méthodes de partitionnement supportées sont le partitionnement par intervalles et par liste, où on assigne à chaque partition respectivement un intervalle de clés et une liste de clés.

Les partitions peuvent elles-même être définies comme des tables partitionnées, en utilisant ce qu'on appelle du sous-partitionnement. Les partitions peuvent avoir leurs propres index, contraintes et valeurs par défaut, différents de ceux des autres partitions. Les index doivent être créés séparément pour chaque partition. 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 contenant des données comme une partition d'une table partitionnée, ou de supprimer une partition d'une table partitionnée, la transformant en table standard; voir ALTER TABLE pour en apprendre plus sur les sous-commandes ATTACH PARTITION et DETACH PARTITION.

Dans les faits, les partitions individuelles sont liées à la table partitionnée grâce à l'héritage  ; il n'est pas possible d'utiliser une partie des fonctionnalités de l'héritage vues dans la section précédente avec les tables partitionnées et les partitions. Par exemple, une partition ne peut pas avoir d'autre parent qu'une table partitionnée dont elle est une partition, et une table standard ne peut pas hériter d'une table partitionnée faisant d'elle son parent. Cela veut dire que les tables partitionnées et les partitions ne participent pas à l'héritage avec les tables standard. Puisqu'une hiérarchie de partition est constituée d'une table partitionnée et de ses partitions, il s'agit toujours d'une hiérarchie d'héritage, tableoid et toutes les règles normales de l'héritage s'appliquent comme décrit dans Section 5.9 avec quelques exceptions, les plus notables étant :

Les partitions peuvent également être des tables étrangères (voir CREATE FOREIGN TABLE), bien que ce cas ait des limitations qui n'existent pas avec des tables normales. Par exemple, les données insérées dans la table partitionnée ne sont pas redirigées vers la partition qui est une table étrangère.

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'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
    

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 pourrait être utile. Le partitionnement peut être implémenté en utilisant l'héritage de table, ce qui permet plusieurs autres fonctionnalités qui ne sont pas 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'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 (les tables partitionnées qui utilisent le partitionnement déclaratif ainsi que celles qui sont implémentées en utilisant l'héritage). Par exemple :

SET constraint_exclusion = on;
SELECT count(*) FROM mesure WHERE date_trace >= DATE '2008-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 peut 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 non optimisé pour ce type de table est :

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2008-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 >= '2008-01-01'::date)
         ->  Seq Scan on mesure_a2006m02 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2008-01-01'::date)
         ->  Seq Scan on mesure_ay2006m03 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2008-01-01'::date)
...
         ->  Seq Scan on mesure_a2007m12 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2008-01-01'::date)
         ->  Seq Scan on mesure_a2008m01 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '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 anciennes partitions pour répondre à cette requête. Lorsque l'exclusion de contrainte est activée, un plan significativement moins coûteux est obtenu, qui délivre la même réponse :

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2008-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 >= '2008-01-01'::date)
         ->  Seq Scan on mesure_a2008m01 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2008-01-01'::date)

L'exclusion de contrainte 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.

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 est appliquée seulement aux requêtes qui semblent fonctionner avec des tables partitionnées. La valeur on fait que le planificateur examine les contraintes CHECK dans chaque requête, y compris les requêtes simples qui ont peu de chance d'en profiter.

Les restrictions suivantes s'appliquent à l'exclusion de contraintes, qui sont utilisées à la fois par l'héritage et par les tables partitionnées :

Le choix de la méthode de partitionnement d'une table doit être fait avec beaucoup d'attention car les performances de l'optimisation des requêtes et leur exécution peuvent être fortement affectées négativement par un mauvais design.

Une des décisions les plus critiques au niveau du design est le choix de la clé (ou des clés) de partitionnement. Souvent, le meilleur choix revient à partitionner par la (ou les) colonne(s) qui apparaissent le plus fréquemment dans les clauses WHERE des requêtes en cours d'exécution sur la table partitionnée. Les éléments de la clause WHERE qui correspondent ou sont compatibles avec la clé de partitionnement peuvent être utilisés pour ignorer les partitions inutiles. La suppression des données inutiles est aussi un facteur à considérer lors de la conception de votre stratégie de partitionnement. Une partition entière peut être détachée rapidement, donc il peut être bénéfique de concevoir la stratégie de partitionnement d'une telle façon que tout les données à supprimer d'un coup soient concentrées sur une seule partition.

Choisir le nombre cible de partitions pour la table est aussi une décision critique à prendre. Ne pas avoir suffisamment de partitions pourrait avoir pour conséquence des index trop gros, et un emplacement des données pauvre qui résulterait en un ratio bas de lecture en cache. Néanmoins, diviser la table en trop de partitions pourrait aussi causer des problèmes. Trop de partitions pourrait signifier une optimisation plus longue des requêtes et une consommation mémoire plus importante durant l'optimisation et l'exécution. Lors de la conception du partitionnement de votre table, il est aussi important de prendre compte les changements pouvant survenir dans le futur. Par exemple, si vous choisissez d'avoir une partition par client et que vous avez un petit nombre de gros clients, il est important de réfléchir aux implications si, dans quelques années, vous vous trouvez avec un grand nombre de petits clients. Dans ce cas, il serait mieux de choisir de partitionner par RANGE et de choisir un nombre raisonnable de partitions, chacune contenant un nombre fixe de clients, plutôt que d'essayer de partitionner par LIST en espérant que le nombre de clients ne dépasse pas ce qui est possible au niveau du partitionnement des données.

Le sous-partitionnement peut aussi être utile pour diviser encore plus les partitions pour lesquelles on s'attend à ce qu'elles deviennent bien plus grosses que les autres partitions. Un sous-partitionnement excessif peut facilement amener à un grand nombre de partitions et peut causer les problèmes mentionnés dans le paragraphe précédent.

Il est aussi important de considérer la surcharge du partitionnement lors de l'optimisation et de l'exécution. L'optimiseur est généralement capble de gérer les hiérarchies de partitions qui montent à quelques centaines de partitions. Les durées d'optimisation deviennent plus longues et la consommation de mémoire devient plus importante au fur et à mesure de l'ajout de partitions. Ceci est tout particulièrement vrai pour les commandes UPDATE et DELETE. Une autre raison de se soucier d'un grand nombre de partitions est que la consommation mémoire du serveur pourrait grossir de façon significative sur une période de temps, et tout spécialement si beaucoup de sessions touchent un grand nombre de partitions. Ceci est dû au chargement des métadonnées nécessaires pour chaque partition en mémoire locale.

Avec une charge de type entrepôt de données, il peut être sensé d'utiliser un plus grand nombre de partitions que pour une charge de type OLTP. En général, dans les entrepôts de données, le temps d'optimisation d'une requête est peu importante parce que la majorité du temps de traitement est passée sur l'exécution de la requête. Avec l'une de ces deux types de charges, il est important de prendre les bonnes décisions dès le début, car le re-partitionnement de grosses quantités de données peut être très lent. Les simulations de la charge attendue sont souvent bénéfiques pour optimiser la stratégie de partitionnement. Ne jamais supposer qu'un plus grand nombre de partitions est toujours mieux qu'un petit nombre de partitions, et vice-versa.