PostgreSQLLa base de données la plus sophistiquée au monde.

5.9. Partitionnement

PostgreSQL™ offre un support basique du partitionnement de table. Cette section décrit pourquoi et comment vous pouvez implémenter le partitionnement dans la conception de votre base de données.

5.9.1. Aperçu

Le partitionnement fait référence à la division logique d'une grosse table en plusieurs parties physiques. Le partitionnement apporte plusieurs bénéfices :

  • Les performances de certains types de requêtes peuvent se voir grandement améliorées.

  • Les performances de mise à jour peuvent aussi être améliorées car chaque pièce de la table a des index plus petits que ne le serait l'index de l'ensemble complet des données. Quand un index n'est plus facilement contenu en mémoire, les opérations de lecture et d'écriture sur l'index demandent progressivement plus d'accès au disque.

  • Les suppressions en masse peuvent être accomplies en supprimant simplement une des partitions, si ce besoin est pris en compte dans la conception du partitionnement. DROP TABLE est bien plus rapide qu'un groupe de DELETE, sans parler de la surcharge occasionnée par le VACUUM qui suit.

  • Les données peu utilisées peuvent être déplacées sur un média peu cher et lent.

Les bénéfices seront normalement intéressants quand une table sera suffisamment importante. Le point exact où une table bénéficierait d'un partitionnement dépend de l'application bien qu'une règle de base est que la taille de la table ne doit pas dépasser la mémoire physique du serveur.

Actuellement, PostgreSQL™ supporte le partitionnement via l'héritage des tables. Chaque partition doit être créée comme une table enfant d'une seule table parent. La table parent elle-même est habituellement vide ; elle existe seulement pour représenter l'ensemble complet des données. Vous devez être familier avec l'héritage (voir Section 5.8, « Héritage ») avant de tenter d'implanter le partitionnement.

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

Partitionnement de groupe

La table est partitionnée en « groupes » définis par une colonne clé ou par un ensemble de colonnes, sans intersection dans les ensembles de valeurs affectées aux différentes partition. Par exemple, il est possible de partitionner par date ou par ensemble d'identifieurs pour des objets métier particuliers.

Partitionnement de liste

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

Le partitionnement par hachage n'est pas encore supporté.

5.9.2. Implanter le partitionnement

Pour configurer une table partitionnée, faites ce qui suit :

  1. Créez la table « maître » à partir de laquelle toutes les partitions vont hériter.

    Cette table ne contiendra pas de données. Ne définissez pas de contraintes de vérification sur cette table sauf si vous avez l'intention de les appliquer sur toutes les partitions. Il n'y a non plus aucune raison de définir des index ou des contraintes uniques sur elle.

  2. Créez plusieurs tables « filles » qui héritent chacune de la table maître. Normalement, ces tables n'ajouteront pas de colonnes à l'ensemble hérité du maître.

    Nous ferons référence aux tables enfants par le mot partition bien qu'elles se comportent comme de vraies tables PostgreSQL™.

  3. Ajoutez les contraintes de tables aux tables partition pour définir les valeurs des clés autorisées dans chaque partition.

    Voici quelques exemples typiques :

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

    Assurez-vous que les contraintes garantissent qu'il n'y aura pas d'intersection entre les valeurs clés permises dans les différentes partitions. Une erreur commune est de configurer des contraintes d'échelle de cette façon :

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

    Ceci est mauvais car il n'est pas aisé de savoir à quelle partition appartient la clé 200.

    Notez qu'il n'y a aucune différence dans la syntaxe BETWEEN et le partitionnement de listes ; ces termes sont seulement descriptifs.

  4. Pour chaque partition, créez un index sur la (ou les) colonne(s) clé(s), ainsi que tout autre index nécessaire (l'index clé n'est pas vraiment nécessaire mais, dans la plupart des scénarios, il est utile. Si vous avez l'intention que les valeurs clés soient uniques, alors vous devez toujours créer une contrainte unique ou une clé primaire pour chaque partition.)

  5. En option, définissez une règle ou un déclencheur pour rediriger les modifications de la table maître vers la partition appropriée.

  6. Assurez-vous que le paramètre de configuration constraint_exclusion est activé dans postgresql.conf. Sans ce dernier, les requêtes ne sont pas optimisées.

Par exemple, supposons que nous construisons une base de données pour une grande société de glaces. La compagnie mesure les pics de températures chaque jour ainsi que les ventes de glaces dans chaque région. À la base, nous voulons une table comme ceci :

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

Nous savons que la plupart des requêtes accèdent seulement à la dernière semaine, au dernier mois ou au dernier trimestre des données car l'utilisation principale de cette table sera de préparer des rapports en ligne pour la gestion. Pour réduire le nombre de données anciennes devant être restaurées, nous décidons de conserver seulement les trois dernières années. Au début de chaque mois, nous supprimerons les données de l'ancien mois.

Dans cette situation, nous pouvons utiliser le partitionnement pour nous aider dans nos différents besoins pour la table mesure. En suivant les étapes indiquées ci-dessus, le partitionnement peut être configuré de la façon suivante :

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

  2. Une partition est alors créée pour chaque mois actif :

    CREATE TABLE mesure_aa04mm02 ( ) INHERITS (mesure);
    CREATE TABLE mesure_aa04mm03 ( ) INHERITS (mesure);
    ...
    CREATE TABLE mesure_aa05mm11 ( ) INHERITS (mesure);
    CREATE TABLE mesure_aa05mm12 ( ) INHERITS (mesure);
    CREATE TABLE mesure_aa06mm01 ( ) INHERITS (mesure);
    

    Chaque partition est une table complète avec leur propre droits mais leur définition est héritée de la table mesure.

    Ceci résoud un de nos problèmes : supprimer les anciennes données. Chaque mois, tout ce que nous aurons à faire est un DROP TABLE sur l'ancienne table enfant et créez une nouvelle table enfant pour les données du nouveau mois.

  3. Nous devons ajouter des contraintes de table qui ne permettent pas l'intersection des ensembles de données, donc notre script de création de table devient :

    CREATE TABLE mesure_aa04mm02 (
        CHECK ( date_trace >= DATE '2004-02-01' AND date_trace < DATE '2004-03-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_aa04mm03 (
        CHECK ( date_trace >= DATE '2004-03-01' AND date_trace < DATE '2004-04-01' )
    ) INHERITS (mesure);
    ...
    CREATE TABLE mesure_aa05mm11 (
        CHECK ( date_trace >= DATE '2005-11-01' AND date_trace < DATE '2005-12-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_aa05mm12 (
        CHECK ( date_trace >= DATE '2005-12-01' AND date_trace < DATE '2006-01-01' )
    ) INHERITS (mesure);
    CREATE TABLE mesure_aa06mm01 (
        CHECK ( date_trace >= DATE '2006-01-01' AND date_trace < DATE '2006-02-01' )
    ) INHERITS (mesure);
    
  4. Nous avons probablement besoin d'index sur les colonnes clés :

    CREATE INDEX mesure_aa04mm02_date_trace ON mesure_aa04mm02 (date_trace);
    CREATE INDEX mesure_aa04mm03_date_trace ON mesure_aa04mm03 (date_trace);
    ...
    CREATE INDEX mesure_aa05mm11_date_trace ON mesure_aa05mm11 (date_trace);
    CREATE INDEX mesure_aa05mm12_date_trace ON mesure_aa05mm12 (date_trace);
    CREATE INDEX mesure_aa06mm01_date_trace ON mesure_aa06mm01 (date_trace);
    

    Nous choisissons de ne pas en ajouter d'autres pour l'instant.

  5. Si les données seront seulement ajoutées dans la dernière partition, nous pouvons configurer une règle très simple pour insérer des données. Nous devons la redéfinir chaque mois pour qu'elle pointe toujours vers la partition en cours.

    CREATE OR REPLACE RULE partition_actuelle_mesure AS
    ON INSERT TO mesure
    DO INSTEAD
        INSERT INTO mesure_aa06mm01 VALUES ( NEW.id_ville,
                                             NEW.date_trace,
                                             NEW.temperature,
                                             NEW.ventes );
    

    Nous pourrions vouloir insérer des données et que le serveur situe automatiquement la partition où cette ligne doit être ajoutée. Nous pouvons le faire avec un ensemble de règles plus complexes.

    CREATE RULE mesure_insert_aa04mm02 AS
    ON INSERT TO mesure WHERE
        ( date_trace >= DATE '2004-02-01' AND date_trace < DATE '2004-03-01' )
    DO INSTEAD
        INSERT INTO mesure_aa04mm02 VALUES ( NEW.id_ville,
                                             NEW.date_trace,
                                             NEW.temperature,
                                             NEW.ventes );
    ...
    CREATE RULE mesure_insert_aa05mm12 AS
    ON INSERT TO mesure WHERE
        ( date_trace >= DATE '2005-12-01' AND date_trace < DATE '2006-01-01' )
    DO INSTEAD
        INSERT INTO mesure_aa05mm12 VALUES ( NEW.id_ville,
                                             NEW.date_trace,
                                             NEW.temperature,
                                             NEW.ventes );
    CREATE RULE mesure_insert_aa06mm01 AS
    ON INSERT TO mesure WHERE
        ( date_trace >= DATE '2006-01-01' AND date_trace < DATE '2006-02-01' )
    DO INSTEAD
        INSERT INTO mesure_aa06mm01 VALUES ( NEW.id_ville,
                                             NEW.date_trace,
                                             NEW.temperature,
                                             NEW.ventes );
    

    Notez que la clause WHERE de chaque règle correspond exactement à la contrainte de vérification pour cette partition.

Comme nous pouvons le voir, un schéma complexe de partitionnement demanderait un ajout substentiel de DDL. Dans l'exemple ci-dessus, nous pouvons créer une nouvelle partition chaque mois, donc il est conseillé d'écrire un script qui génère automatiquement la DDL requise.

Voici quelques conseils :

  • Actuellement, il n'existe pas de moyen de vérifier que toutes les contraintes de vérification (CHECK) sont mutuellement exclusives. Le concepteur de la base de données doit faire attention.

  • Il n'existe pas de façon simple de spécifier les lignes ne devant pas être insérées dans la table maître. Une contrainte CHECK (false) sur la table maître sera héritée par toutes les tables filles et ne peut donc pas être utilisée dans ce but. Une possibilité revient à configurer un déclencheur ON INSERT sur la table maître qui renvoie toujours une erreur (sinon, un déclencheur peut être utilisé pour rediriger les données dans la bonne table fille au lieu d'utiliser un ensemble de règles comme suggéré ci-dessus).

Le partitionnement peut aussi être arrangé en utilisant une vue UNION ALL :

CREATE VIEW mesure AS
          SELECT * FROM mesure_aa04mm02
UNION ALL SELECT * FROM mesure_aa04mm03
...
UNION ALL SELECT * FROM mesure_aa05mm11
UNION ALL SELECT * FROM mesure_aa05mm12
UNION ALL SELECT * FROM mesure_aa06mm01;

Néanmoins, l'exclusion des contraintes n'est pas supportée pour les tables partitionnées définies de cette façon. De plus, le besoin de recréer la vue ajoute une étape à l'ajout et à la suppression des partitions individuelles de l'ensemble des données.

5.9.3. Partitionnement et exclusion de contrainte

L'exclusion de contrainte est une technique d'optimisation des requêtes pour améliorer les performances pour les tables partitionnées de la façon décrite ci-dessus. En exemple :

SET constraint_exclusion = on;
SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';

Sans exclusion de contrainte, la requête ci-dessus devrait parcourir chacune des partitions de la table mesure. Avec l'exclusion de contrainte activée, le planificateur examinera les contraintes de chaque partition et tentera de prouver que la partition n'a pas besoin d'être parcourue parce qu'elle ne pourrait pas contenir de lignes correspondant à la clause WHERE de la requête. Quand le planificateur peut le prouver, il exclut la partition du plan de requête.

Vous pouvez utiliser la commande EXPLAIN pour afficher la différence avec entre un plan avec constraint_exclusion à on et un plan avec ce paramètre à off. Un plan typique par défaut pour ce type de table est :

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';

                                          QUERY PLAN
-------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)
         ->  Seq Scan on mesure_aa04mm02 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)
         ->  Seq Scan on mesure_aa04mm03 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)
...
         ->  Seq Scan on mesure_aa05mm12 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)
         ->  Seq Scan on mesure_aa06mm01 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)

Certaines ou toutes les partitions pourraient utiliser des parcours d'index à la place de parcours séquentiels de la table complète mais le fait est qu'il n'est pas besoin de parcourir les anciennes partitions pour répondre à cette requête. Quand nous activons l'exclusion de contrainte, nous obtenons un plan réduit significativement et qui nous renvoie la même réponse :

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM mesure WHERE date_trace >= DATE '2006-01-01';
                                          QUERY PLAN
-------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)
         ->  Seq Scan on mesure_aa06mm01 mesure  (cost=0.00..30.38 rows=543 width=0)
               Filter: (date_trace >= '2006-01-01'::date)

Notez que l'exclusion de contraintes est seulement piloté par les contraintes CHECK, pas par la présence d'index. Du coup, il n'est pas nécessaire de définir des index sur les colonnes clés. Le fait qu'un index ait besoin d'être créé pour une partition donnée dépend si vous vous attendez à ce que les requêtes qui parcourent la partition parcoureront aussi généralement une grande partie de la partition ou seulement une petite partie. Un index sera utile dans le dernier cas, pas dans le premier.

Quelques conseils :

  • L'exclusion de contrainte fonctionne seulement quand la clause WHERE de la requête contient des constantes. Une requête avec paramètre ne sera pas optimisée car le planificateur ne sait pas quelles partitions la valeur du paramètre pourrait sélectionner à son exécution. Pour la même raison, les fonctions « stables » comme CURRENT_DATE doivent être évitées. Joindre la clé de la partition à une colonne d'une autre table ne sera pas non plus optimisé.

  • Évitez les comparaisons inter-type dans les contraintes CHECK car le planificateur échouera à prouver que de telles conditions sont fausses. Par exemple, la contrainte suivante fonctionnera si x est une colonne de type integer, mais pas si x est un bigint :

    CHECK ( x = 1 )
    

    Pour une colonne bigint, nous devons utiliser une contrainte comme celle-ci :

    CHECK ( x = 1::bigint )
    

    Le problème n'est pas limité au type bigint -- il peut survenir quand le type de données par défaut de la constante ne correspond pas au type de données de la colonne avec laquelle elle est comparée. Les comparaisons inter-type dans les requêtes fournies sont habituellement gérées, mais pas dans les conditions CHECK.

  • Les commandes UPDATE et DELETE sur la table maître ne traitent pas actuellement l'exclusion de contrainte.

  • Toutes les contraintes de toutes les partitions de la table maître sont considérées pour l'exclusion de contraintes, donc un grand nombre de partitions a tendance à augmenter considérablement le temps de plannification de la requête.

  • N'oubliez pas que vous avez toujours besoin d'exécuter ANALYZE individuellement sur chaque partition. Une commande comme

    ANALYZE mesure;
    

    traitera seulement la table maître.