5.9. Partitionnement

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.

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

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.

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

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.

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.

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

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.

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

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.

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.