PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 10.23 » Langage SQL » Définition des données » Partitionnement de tables

5.10. Partitionnement de tables

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. Exécuter ALTER TABLE DETACH PARTITION ou supprimer une partition individuelle en utilisant DROP TABLE est bien plus rapide qu'une opération de masse. Cela supprime é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.

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 :

Partitionnement par intervalles

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 intervalles de valeurs affectées aux différentes partitions. Il est possible, par exemple, de partitionner par échelles de date ou par intervalles 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.

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.

5.10.2. Partitionnement déclaratif

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 contraintes CHECK et NOT NULL d'une table partitionnée sont toujours héritées par toutes les partitions. La création de contraintes CHECK qui sont marquées comme NO INHERIT n'est pas autorisée sur des tables partitionnées.

  • Utiliser ONLY pour ajouter ou supprimer une contrainte sur la table partitionnée uniquement est supporté s'il n'y a pas de partition. Une fois qu'il existe des partitions, utiliser ONLY remontera une erreur puisqu'ajouter ou supprimer des contraintes sur uniquement la table partitionnée, quand des partitions existent, n'est pas supporté. À la place, des contraintes peuvent être ajoutées ou supprimées, quand elles ne sont pas présentes sur la table parente, directement sur les partitions. Comme une table partitionnée ne contient jamais de données directement, essayer d'utiliser TRUNCATE ONLY sur une table partitionnée retournera toujours une erreur.

  • Les partitions ne peuvent pas avoir de colonnes qui ne sont pas présentes dans le parent. Il n'est pas non plus possible de spécifier des colonnes quand une partition est créée avec CREATE TABLE, pas plus qu'il n'est possible d'ajouter des colonnes aux partitions une fois celles-ci créées en utilisant ALTER TABLE. Des tables peuvent être ajoutées comme des partitions avec ALTER TABLE ... ATTACH PARTITION seulement si leurs colonnes correspondent exactement à celles du parent, en incluant toutes les colonnes oid.

  • Vous ne pouvez pas supprimer la contrainte NOT NULL d'une colonne d'une partition si la contrainte est présente dans la table parente.

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.

5.10.2.1. Exemple

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

Pour utiliser le partitionnement déclaratif dans ce cas d'utilisation, il faut suivre les étapes suivantes :

  1. 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 liste de la ou des 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);
         

    Vous pourriez décider d'utiliser plusieurs colonnes dans la clé de partitionnement pour le partitionnement par intervalles, si vous le souhaitez. Bien sûr, cela aura souvent pour conséquence un plus grand nombre de partitions, chacune étant individuellement plus petite. D'un autre côté, utiliser moins de partitions pourrait entraîner un critère de partitionnement plus grossier et moins de partitions. Une requête accédant à la table partitionnée aura à parcourir moins de partitions si les conditions impliquent une partie ou la totalité de ces colonnes. Par exemple, imaginez une table partitionnée par intervalles utilisant les colonnes lastname et firstname (dans cet ordre) comme clé de partitionnement.

  2. 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. Insérer des données dans la table parent qui ne correspondent pas à une des partitions existantes remontera une erreur  ; la partition appropriée doit être ajoutée manuellement.

    Les partitions ainsi créées sont de tous les points de vue des tables PostgreSQL normales (ou, potentiellement, des tables étrangères). Il est possible de spécifier un tablespace et des paramètres de stockage pour chacune des partitions séparément.

    Il n'est pas nécessaire de créer les contraintes de table décrivant les conditions limites de la partition pour les partitions. À la place, des contraintes de partitions sont générées implicitement à partir de la spécification de limite de partition chaque fois qu'il est nécessaire de s'y référer.

    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 implémenter le 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, sous réserve qu'elle satisfasse sa contrainte de partition) 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.

  3. 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. Si vous voulez que les valeurs de la clé soient uniques, alors vous devriez toujours créer un index unique ou une contrainte de clé primaire pour chaque partition.)

    CREATE INDEX ON measurement_y2006m02 (logdate);
    CREATE INDEX ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX ON measurement_y2007m11 (logdate);
    CREATE INDEX ON measurement_y2007m12 (logdate);
    CREATE INDEX ON measurement_y2008m01 (logdate);

    Assurez-vous que le paramètre de configuration constraint_exclusion 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.

5.10.2.2. Maintenance des partitions

Normalement, l'ensemble des partitions établies lors de la définition initiale de la table n'a pas vocation à demeurer statique. Il est normal de vouloir supprimer d'anciennes partitions de données et périodiquement ajouter de nouvelles partitions pour les nouvelles données. Un des avantages les plus importants du partitionnement est précisément qu'il permet d'exécuter cette tâche de maintenance normalement pénible instantanément en manipulant la structure de la partition, plutôt que de physiquement bouger 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é, qui est 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 :

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
    

Cela permet d'effectuer ensuite d'autres opérations sur les données avant de la supprimer. Par exemple, il s'agit souvent du moment idéal pour sauvegarder les données en utilisant COPY, pg_dump, ou des outils similaires. Cela pourrait également être le bon moment pour agréger les données dans un format moins volumineux, effectuer d'autres manipulations de données ou exécuter des rapports.

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 la première partition a été créée 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 utile de créer la nouvelle table en dehors de la structure de la partition, et d'en faire une partition plus tard. Cela permet de charger des données, les vérifier et effectuer des transformations avant que les données apparaissent dans la table partitionnée :

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

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 décrivant la contrainte de partition désirée. De cette manière, le système n'aura pas besoin d'effectuer un parcours de la table pour valider la contrainte de partition implicite. Sans la contrainte CHECK, la table sera parcourue pour valider la contrainte de partition tout en ayant un verrou de niveau ACCESS EXCLUSIVE sur la table parente. Vous pouvez alors supprimer la contrainte CHECK redondante après que ATTACH PARTITION soit fini.

5.10.2.3. Limitations

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

  • Il n'y a pas de commande disponible pour créer les index correspondant sur toutes les partitions automatiquement. Les index doivent être ajoutés sur chaque partition avec des commandes séparées. Cela signifie également qu'il n'y a pas de moyen de créer une clé primaire, un index unique ou une contrainte d'exclusion couvrant toutes les partitions  ; il est seulement possible de contraindre chaque partition de niveau feuille individuellement.

  • Puisque les clés primaires ne sont pas supportées sur les tables partitionnées, les clés étrangères référençant des tables partitionnées ne sont pas supportées, pas plus qu'une clé étrangère depuis une table partitionnée référençant une autre table.

  • Utiliser la clause ON CONFLICT avec des tables partitionnées remontera une erreur, car les contraintes unique ou d'exclusion ne peuvent être créées que sur les partitions individuelles. Il n'y a pas de support pour imposer l'unicité (ou une contrainte d'exclusion) sur l'intégralité d'une hiérarchie de partitionnement.

  • Un UPDATE qui a pour conséquence de déplacer une ligne d'une partition à une autre échouera, car la nouvelle ligne ne satisfera pas la contrainte de partition implicite de la partition d'origine.

  • Les triggers de lignes, si nécessaires, doivent être définis sur les partitions individuelles et non sur la table partitionnée.

  • Mixer des tables temporaires et permanentes dans le même arbre de partition n'est pas autorisé. De ce fait, si la table partitionnée est permanente, toutes ces partitions le sont. De la même façon si la table partitionnée est temporaire. Lors de l'utilisation de tables temporaires, tous les membres de l'arbre de partition doit provenir de la même session.

5.10.3. Implémentation utilisant l'héritage

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 :

  • Le partitionnement impose le fait que toutes les partitions doivent avoir exactement le même ensemble de colonnes que le parent, mais l'héritage de table permet aux enfants d'avoir des colonnes supplémentaires qui ne sont pas présentes dans la table parent.

  • L'héritage de table permet de multiples héritages.

  • Le partitionnement déclaratif ne supporte que le partitionnement par liste et par intervalles, alors que l'héritage de table autorise la division des données de la manière choisie par l'utilisateur. (Notez toutefois que si les contraintes d'exclusions ne sont pas capables de filtrer la liste des partitions de manière efficace, la performance des requêtes sera très mauvaise.)

  • Certaines opérations nécessitent un verrou plus fort en utilisant le partitionnement déclaratif qu'en utilisant l'héritage de table. Par exemple, ajouter ou supprimer une partition d'une table partitionnée nécessite de prendre un verrou de type ACCESS EXCLUSIVE sur la table parente, alors qu'un verrou de type SHARE UPDATE EXCLUSIVE est suffisant dans le cas de l'héritage classique.

5.10.3.1. Exemple

Nous utilisons la même table measurement, non partitionnée, que nous avons déjà utilisée au-dessus. Pour l'implémenter comme une table partitionnée en utilisant l'héritage, utilisez les étapes suivantes :

  1. Créez la table « master », à partir de laquelle toutes les partitions seront héritées. 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 partitions. 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 master correspond à la table measurement définie à l'origine.

  2. Créez plusieurs tables « enfant » qui chacune hérite de la table master. Normalement, ces tables n'auront aucune colonne supplémentaire par rapport à celles héritées de la table master. Tout comme avec le partitionnement déclaratif, ces partitions ont tous les aspects des tables (ou tables étrangères) PostgreSQL normales.

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

  3. Ajoutez les contraintes de tables qui ne se chevauchent pas sur les tables de partition pour définir les valeurs de clé autorisées dans chaque partition.

    Les 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 partitions. 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'on ne peut pas savoir à quelle partition appartient la valeur de clé 200.

    Il serait préférable de créer à la place les partitions comme cela :

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

  4. Pour chaque partition, 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);
           

  5. Nous voulons que notre application soit capable de dire INSERT INTO measurement ... et d'avoir les données redirigées dans la table de partition appropriée. Nous pouvons réaliser cela en attachant une fonction de déclencheur convenable sur la table master. Si les données doivent être ajoutées sur la dernière table de partition uniquement, nous pouvons utiliser une fonction de déclencheur 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 un déclencheur qui appelle la fonction de déclencheur :

    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''intervalle. 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

    En pratique, il pourrait être 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.

    Une approche différente est de rediriger les insertions dans la table de partition appropriée à l'aide de règles, plutôt qu'un déclencheur, sur la table master. 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 à un surcoût bien plus important qu'un trigger, mais le surcoût n'est payé qu'une fois par requête plutôt qu'une fois par ligne, cette méthode peut être avantageuse pour les situations d'insertions en masse. Toutefois, dans la plupart des cas, la méthode du déclencheur 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 de partition plutôt que dans la table master. COPY déclenche les triggers, vous pouvez donc l'utilisez normalement si vous utilisez l'approche par déclencheur.

    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 master à la place.

  6. Assurez-vous que le paramètre de configuration constraint_exclusion ne soit pas désactivé dans postgresql.conf. S'il l'est, les requêtes ne seront pas optimisées comme voulu.

Comme nous pouvons le voir, un schéma de partitionnement complexe peut nécessiter une quantité de DDL non négligeable. Dans l'exemple du dessus, nous créerions une nouvelle partition chaque mois, il serait donc sage d'écrire un script qui génère le DDL requis automatiquement.

5.10.3.2. Maintenance de partition

Pour supprimer les anciennes données rapidement, il suffit de supprimer la partition qui n'est plus nécessaire :

DROP TABLE mesure_a2006m02;
    

Pour supprimer la partition de la table partitionnée, mais pour garder l'accès à la table en tant que telle :

ALTER TABLE mesure_a2006m02 NO INHERIT mesure;
    

Pour ajouter une nouvelle partition pour gérer les nouvelles données, créez une partition vide tout comme les partitions originales ont été créées au-dessus :

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

De manière alternative, vous pourriez vouloir créer la nouvelle table en dehors de la structure de partition, et en faire une partition après avoir chargé, vérifié et transformé les données :

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.3.3. Restrictions

Les restrictions suivantes s'appliquent aux tables partitionnées utilisant l'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 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 measurement;
           

    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 déclencheurs ou les règles seront nécessaires pour rediriger les lignes vers la partition voulue, à moins que l'application ne soit explicitement au courant du schéma de partitionnement. Les déclencheurs peuvent être plus compliqués à écrire, et seront bien plus lents que la redirection de ligne effectuée en interne par le partitionnement déclaratif.

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 (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 :

  • 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'intervalle simples lors de partitionnement par intervalles 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, ce qui s'applique même aux tables partitionnées, car seules les colonnes indexables avec un index B-tree sont autorisées dans la clé de partitionnement. (Ce n'est pas un problème quand on utilise le partitionnement déclaratif, puisque les contraintes générées automatiquement sont suffisamment simples pour être comprises par l'optimiseur).

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

5.10.5. Bonnes pratiques pour le partitionnement déclaratif

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.