5.10. Partitionnement

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.

5.10.1. Aperçu

Le partitionnement fait référence à la division d'une table logique volumineuse en plusieurs parties physiques plus petites. Le partitionnement comporte de nombreux avantages :

  • les performances des requêtes peuvent être significativement améliorées dans certaines situations, particulièrement lorsque la plupart des lignes fortement accédées d'une table se trouvent sur une seule partition ou sur un petit nombre de partitions. Le partitionnement se substitue aux colonnes principales des index, réduisant ainsi la taille des index et facilitant la tenue en mémoire des parties les plus utilisées de l'index ;

  • lorsque les requêtes ou les mises à jour accèdent à un important pourcentage d'une seule partition, les performances peuvent être grandement améliorées par l'utilisation avantageuse de parcours séquentiels sur cette partition plutôt que d'utiliser un index et des lectures aléatoires réparties sur toute la table ;

  • les chargements et suppressions importants de données peuvent être obtenus par l'ajout ou la suppression de partitions, sous réserve que ce besoin ait été pris en compte lors de la conception du partitionnement. ALTER TABLE NO INHERIT et DROP TABLE sont bien plus rapides qu'une opération de masse. Cela supprime également la surcharge dû au VACUUM causé par un DELETE massif ;

  • les données peu utilisées peuvent être déplacées sur un média de stockage moins cher et plus lent.

Les bénéfices ne sont réellement intéressants que si cela permet d'éviter une table autrement plus volumineuse. Le point d'équilibre exact à partir duquel une table tire des bénéfices du partitionnement dépend de l'application. Toutefois, le partitionnement doit être envisagé si la taille de la table peut être amenée à dépasser la taille de la mémoire physique du serveur.

Actuellement, PostgreSQL™ supporte le partitionnement à travers l'héritage de tables. Chaque partition doit être créée comme une table enfant d'une unique table parent. La table parent est, elle, habituellement vide ; elle n'existe que pour représenter l'ensemble complet des données. Il est impératif de maîtriser les concepts de l'héritage (voir Section 5.9, « L'héritage ») avant de tenter d'implanter le partitionnement.

Les formes suivantes de partitionnement peuvent être implantées dans PostgreSQL™ :

Partitionnement par échelon

La table est partitionnée en « intervalles » (ou échelles) définis par une colonne clé ou par un ensemble de colonnes, sans recouvrement entre les échelles de valeurs affectées aux différentes partitions. Il est possible, par exemple, de partitionner par échelles de date ou par échelles d'identifiants pour des objets métier particuliers.

Partitionnement par liste

La table est partitionnée en listant explicitement les valeurs clés qui apparaissent dans chaque partition.

5.10.2. Partitionner

Pour partionner une table, la procédure est la suivante :

  1. Créer la table « maître ». C'est de celle-ci qu'héritent toutes les partitions.

    Cette table ne contient pas de données. Les contraintes de vérification ne doivent être définies sur cette table que si elles sont appliquées à toutes les partitions. Il n'y a de plus aucune raison de définir des index ou des contraintes d'unicité sur cette table.

  2. Créer plusieurs tables « filles » (ou enfants) qui héritent chacune de la table maître. Normalement, ces tables n'ajoutent pas de colonnes à l'ensemble hérité du maître.

    Par la suite, les tables enfants sont appelées partitions, bien qu'elles soient, en tout point, des tables (ou tables externes) PostgreSQL™ normales.

  3. Ajouter les contraintes de tables aux tables de partitions pour définir les valeurs des clés autorisées dans chacune.

    Quelques exemples typiques :

    CHECK ( x = 1 )
    CHECK ( comté IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
    CHECK ( ID >= 100 AND ID < 200 )
    

    Les contraintes doivent garantir qu'il n'y a pas de recouvrement entre les valeurs clés autorisées dans les différentes partitions. Une erreur commune est de configurer des contraintes d'échelle de cette façon :

    CHECK ( comté BETWEEN 100 AND 200 )
    CHECK ( comté BETWEEN 200 AND 300 )
    

    Il est dans ce cas difficile de savoir à quelle partition appartient la clé 200.

    Il n'y a aucune différence entre les syntaxes de partitionnement par échelon et de partitionnement par liste ; ces termes ne sont que descriptifs.

  4. Pour chaque partition, créer un index sur la (ou les) colonne(s) clé(s), ainsi que tout autre index nécessaire. (L'index clé n'est pas vraiment nécessaire mais, dans la plupart des scénarios, il est utile. Si les valeurs clés doivent être uniques, alors il faut toujours créer une contrainte d'unicité ou de clé primaire pour chaque partition.)

  5. Optionnellement, définir un déclencheur ou une règle pour rediriger les données insérées dans la table maître vers la partition appropriée.

  6. S'assurer que le paramètre de configuration constraint_exclusion n'est pas désactivé dans postgresql.conf. S'il l'est, les requêtes ne sont pas optimisées.

Soit la base de données d'une grande fabrique de glaces. La compagnie mesure le pic de température journalier ainsi que les ventes de glaces dans chaque région. Conceptuellement, la table ressemble à :

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

La plupart des requêtes n'accèdent qu'aux données de la dernière semaine, du dernier mois ou du dernier trimestre car cette table est essentiellement utilisée pour préparer des rapports en ligne pour la direction. Pour réduire le nombre de données anciennes à stocker, seules les trois dernières années sont conservées. Au début de chaque mois, les données du mois le plus ancien sont supprimées.

Dans cette situation, le partitionnement permet de répondre aux différents besoins identifiés sur la table des mesures. En suivant les étapes indiquées ci-dessus, le partitionnement peut être configuré de la façon suivante :

  1. la table maître est la table mesure, déclarée exactement comme ci-dessus ;

  2. une partition est ensuite créée pour chaque mois actif :

    CREATE TABLE mesure_a2006m02 ( ) INHERITS (mesure);
    CREATE TABLE mesure_a2006m03 ( ) INHERIT (mesure);
    ...
    CREATE TABLE mesure_a2007m11 ( ) INHERITS (mesure);
    CREATE TABLE mesure_a2007m12 ( ) INHERITS (mesure);
    CREATE TABLE mesure_a2008m01 ( ) INHERITS (mesure);
    

    Chaque partition est une table à part entière mais sa définition est héritée de la table mesure.

    Ceci résoud un des problèmes : la suppression d'anciennes données. Chaque mois, il suffit d'effectuer un DROP TABLE sur la table enfant la plus ancienne et de créer une nouvelle table enfant pour les données du nouveau mois.

  3. Il est nécessaire de fournir des contraintes de table qui interdisent les recouvrements. Plutôt que de simplement créer les tables de la partition comme ci-dessus, le script de création de tables ressemble à ;

    CREATE TABLE mesure_a2006m02 (
        CHECK ( date_trace >= DATE '2006-02-01' AND date_trace < DATE '2006-03-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_a2006m03 (
        CHECK ( date_trace >= DATE '2006-03-01' AND date_trace < DATE '2006-04-01' )
    ) INHERITS (mesure);
    ...
    CREATE TABLE mesure_a2007m11 (
        CHECK ( date_trace >= DATE '2007-11-01' AND date_trace < DATE '2007-12-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_a2007m12 (
        CHECK ( date_trace >= DATE '2007-12-01' AND date_trace < DATE '2007-01-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_a2008m01 (
        CHECK ( date_trace >= DATE '2008-01-01' AND date_trace < DATE '2008-02-01' )
    ) INHERITS (mesure);
    
  4. Des index sur les colonnes clés sont probablement nécessaires :

    CREATE INDEX mesure_a2006m02_date_trace ON mesure_a2006m02 (date_trace);
    CREATE INDEX mesure_a2006m03_date_trace ON mesure_a2006m03 (date_trace);
    ...
    CREATE INDEX mesure_a2007m11_date_trace ON mesure_a2007m11 (date_trace);
    CREATE INDEX mesure_a2007m12_date_trace ON mesure_a2007m12 (date_trace);
    CREATE INDEX mesure_a2008m01_date_trace ON mesure_a2008m01 (date_trace);
    

    À ce stade, c'est suffisant.

  5. L'application doit dire INSERT INTO mesure... et les données être redirigées dans la table de partition appropriée. Pour cela une fonction déclencheur est attachée à la table maître. Si les données ne sont ajoutées que dans la dernière partition, la fonction est très simple.

    CREATE OR REPLACE FUNCTION mesure_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO mesure_a2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
         
    

    Le déclencheur qui appelle la fonction est créé à sa suite :

    CREATE TRIGGER insert_mesure_trigger
        BEFORE INSERT ON mesure
        FOR EACH ROW EXECUTE PROCEDURE mesure_insert_trigger();
         
    

    La fonction déclencheur doit être redéfinie chaque mois pour qu'elle pointe toujours sur la partition active. La définition du déclencheur n'a pas besoin d'être redéfinie.

    Il est également possible de laisser le serveur localiser la partition dans laquelle doit être insérée la ligne proposée en entrée. Une fonction déclencheur plus complexe peut être utilisée pour cela :

    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''échelle. Corrigez la fonction mesure_insert_trigger() !';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
         
    

    La définition du déclencheur ne change pas. Chaque test IF doit correspondre exactement à la contrainte CHECK de cette partition.

    Bien que cette fonction soit plus complexe que celle du mois seul, il n'est pas nécessaire de l'actualiser aussi fréquemment, les branches pouvant être ajoutées avant d'être utiles.

    [Note]

    Note

    En pratique, il pourrait préférable de vérifier prioritairement la dernière partition créée si la plupart des insertions lui sont destinées. Pour des raisons de simplicité, les tests du déclencheur sont présentés dans le même ordre que les autres parties de l'exemple.

Un schéma complexe de partitionnement peut amener à écrire une grande quantité de DDL. Dans l'exemple ci-dessus, une nouvelle partition est écrite chaque mois. Il est donc conseillé d'écrire un script qui engendre automatiquement la DDL requise.

5.10.3. Gérer les partitions

Généralement, l'ensemble des partitions établies lors de la définition initiale de la table n'a pas pour but de rester statique. Il n'est pas inhabituel de supprimer d'anciennes partitions de données et d'en ajouter périodiquement de nouvelles pour de nouvelles données. Un des principaux avantages du partitionnement est précisément qu'il autorise une exécution quasi-instantanée de cette tâche, bien plus difficile autrement, en permettant la manipulation de la structure de la partition, plutôt que de déplacer physiquement de grands volumes de données.

L'option la plus simple pour supprimer d'anciennes données consiste à supprimer la partition qui n'est plus nécessaire :

DROP TABLE mesure_a2006m02;
  

Cela permet de supprimer très rapidement des millions d'enregistrements car il n'est nul besoin de supprimer séparément chaque enregistrement.

Une autre option, souvent préférable, consiste à supprimer la partition de la table partitionnée mais de conserver l'accès à la table en tant que telle :

ALTER TABLE mesure_a2006m02 NO INHERIT mesure;
  

Ceci permet la réalisation d'opérations ultérieures sur les données avant qu'elles ne soient supprimées. Par exemple, c'est souvent le bon moment pour sauvegarder les données en utilisant COPY, pg_dump ou tout autres outil. C'est aussi le moment d'agréger des données en des formats plus denses, de réaliser d'autres opérations sur les données ou de créer des rapports.

De façon similaire, une nouvelle partition peut être ajoutée pour gérer les nouvelles données. Une partition vide peut être créée dans la table partitionnée de la même façon que les partitions individuelles créées plus haut :

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

Alternativement, il est parfois plus intéressant de créer la nouvelle table en dehors de la structure de partitionnement et de la transformer en une partition adéquate plus tard. Cela permet de charger les données, les vérifier et les transformer avant leur apparition dans la table partitionnée :

CREATE TABLE mesure_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;
  

5.10.4. Partitionnement et exclusion de contrainte

L'exclusion de contrainte est une technique d'optimisation des requêtes pour améliorer les performances sur les tables partitionnées telles que décrites plus haut. Par exemple :

SET constraint_exclusion = on;
SELECT count(*) FROM mesure WHERE date_trace >= DATE '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 qui 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 contraintes n'est pilotée que par les contraintes CHECK, pas par la présence d'index. Il n'est donc pas nécessaire de définir des index sur les colonnes clés. Le fait qu'un index doive être créé pour une partition donnée dépend de ce que les requêtes qui parcourent la partition parcourent en général une grande partie de la partition ou seulement une petite partie. Un index est utile dans le dernier cas, pas dans le premier.

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.

5.10.5. Autre méthode de partitionnement

Une approche différente pour la redirection des insertions dans la table fille appropriée est de configurer des règles, à la place d'un déclencheur, sur la table maître. Par exemple :

CREATE RULE mesure_insert_a2006m02 AS
ON INSERT TO mesure WHERE
    ( date_trace >= DATE '2006-02-01' AND date_trace < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO mesure_a2006m02 VALUES (NEW.*);
...
CREATE RULE mesure_insert_a2008m01 AS
ON INSERT TO mesure WHERE
    ( date_trace >= DATE '2008-01-01' AND date_trace < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO mesure_a2008m01 VALUES (NEW.*);
  

Une règle est plus coûteuse qu'un déclencheur mais ce coût est payé une fois par requête au lieu d'une fois par ligne, cette méthode peut donc s'avérer avantageuse lors de grosses insertions. Néanmoins, dans la majorité des cas, la méthode du trigger offre de meilleures performances.

La commande COPY ignore les règles. Si COPY est utilisé pour insérer des données, la copie doit être effectuée sur la partition adéquate plutôt que dans la table maître. COPY active les déclencheurs. Elle peut donc être utilisée normalement lorsque cette approche est choisie.

Un autre inconvénient de la méthode des règles est qu'il n'existe pas de moyens simples de forcer une erreur si l'ensemble des règles ne couvre pas la date d'insertion. La donnée est alors silencieusement insérée dans la table maître.

Le partitionnement peut aussi être arrangé à l'aide d'une vue UNION ALL, en lieu et place de l'héritage. Par exemple :

CREATE VIEW mesure AS
          SELECT * FROM mesure_a2006m02
UNION ALL SELECT * FROM mesure_a2006m03
...
UNION ALL SELECT * FROM mesure_a2007m11
UNION ALL SELECT * FROM mesure_a2007m12
UNION ALL SELECT * FROM mesure_a2008m01;
  

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 des données. En pratique, cette méthode a peu d'intérêt au regard de l'héritage.

5.10.6. Restrictions

Les restrictions suivantes s'appliquent aux tables partitionnées :

  • 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 partitions et crée et/ou modifie les objets associés plutôt que de les créer manuellement ;

  • 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 tente de le faire échoue à cause des contraintes CHECK. Pour gérer ce type de cas, des déclencheurs peuvent être convenablement positionnés pour la mise à jour sur les tables de partition mais cela rend la gestion de la structure beaucoup plus complexe.

  • si VACUUM ou ANALYZE sont lancés manuellement, il est obligatoire de les utiliser sur chaque partition. Une commande comme :

    ANALYZE mesure;
         
    

    ne traite que la table maître.

  • Les commandes INSERT avec des clauses ON CONFLICT ont probablement peu de chances de fonctionner comme attendu, dans la mesure où l'action du ON CONFLICT est uniquement effectuée dans le cas de violations qui sont uniques à la table cible, pas à ses tables enfants.

Les restrictions suivantes s'appliquent à l'exclusion de contraintes :

  • l'exclusion de contrainte ne fonctionne que si la clause WHERE de la requête contient des constantes (ou des paramètres externes). Par exemple, une comparaison entre une fonction non immutable telle que CURRENT_TIMESTAMP ne peut pas être optimisée car le planificateur ne peut pas savoir dans quelle partition 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 partitions qu'il n'est pas nécessaire de parcourir. Des conditions simples d'égalité pour le partitionnement de liste ou des tests d'échelle simples lors de partitionnement d'échelle sont recommandées, comme cela est illustré dans les exemples précédents. Une bonne règle consiste à s'assurer que les comparaisons entre colonnes de partitionnement et constantes utilisées par les contraintes de partitionnement se fassent uniquement à l'aide d'opérateurs utilisables par les index B-tree.

  • toutes les contraintes de toutes les partitions de la table maître sont examinées lors de l'exclusion de contraintes. De ce fait, un grand nombre de partitions augmente considérablement le temps de planification de la requête. Un partitionnement qui utilise ces techniques fonctionne assez bien jusqu'environ une centaine de partitions ; il est impensable de vouloir atteindre des milliers de partitions.