5.10. Partitionnement de tables

Créer une table measurement comme une table partitionnée en spécifiant la clause PARTITION BY, ce qui inclue la méthode de partitionnement ( RANGE dans ce cas) ainsi que la liste de la ou les 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édants à la table partitionnée aura à parcourir moins de partitions si les conditions implique une partie ou la totalité de ces colonnes. Par exemple, imaginez une table partitionner par intervalles utilisant les colonnes lastname et firstname (dans cet ordre) comme clé de partitionnement.

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 parents qui ne ne correspondent pas à une des partitions existante 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 table PostgreSQL™ normales (ou, potentiellement, des tables étrangères). Il est possible de spécifier une 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')
    TABLESPACE fasttablespace
    WITH (parallel_workers = 4);
 

Pour implémenter le sous-partitionnement, spécifiez la clause PARTITION BY dans les commandes utilisées pour créer des partitions individuelle, 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ée les partition 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ée afin que l'ensemble de donnée qu'elle accepte constitue un sous-ensemble de ce que les propres limites de la partition accepte; le système n'essayera pas de vérifier si c'est vraiment le cas.

Créer un index sur la ou les colonnées de la clé, ainsi que tout autre index 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énario. Si vous voulez que les valeurs de la clé soient unique 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'et, les requêtes ne seront pas optimisée comme voulu.

Créez la table « master », à partir de laquelle toutes les partitions seront héritées. Cette table ne contiendra aucune données. 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.

Créez plusieurs tables « enfant » qui chacune hérite de la table master. Normalement, ces tables n'auront aucune colonne supplémentaires par rapport à celles héritées de la table master. Tout comme avec le partitionnement déclaratif, ces partitions sont de 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);

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

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

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ée 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 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 surcout bien plus important qu'une trigger, mais le surcout 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 de masse. Toutefois, dans la plupart des cas la méthode du déclencheur offrira de meilleure performances.

Soyez conscient que COPY ignore les règles. Si vous voulez utiliser COPY pour insérer des données, vous cevrez les copier dans la bonne table de partitions plutôt que dans la table master. COPY déclenche les déclencheurs, 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.

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.