PostgreSQL – Partitioning (Partitionnement) de tables par valeurs

PostgreSQL n’intègre pas le partitioning comme nous le connaissons dans Oracle ou MySQL.

Le partitioning passe par l’utilisation de l’héritage de table avec vérification de valeurs.

L’une des difficultés du partitioning de table réside dans la mise à jour de la clé qui oblige la ligne à changer de partition. Il ne s’agit plus d’une mise à jour simple, mais d’un mouvement de ligne. Cette difficulté est résolue par un effacement de la ligne dans l’ancienne partition et son insertion dans la nouvelle partition avec la nouvelle clé de partition.

Les éléments nécessaires à la création d’une table partitionnée par valeurs sont :

1. La table maître
2. Autant de tables filles que de partitions
3. Chaque table fille contient une clause CHECK qui contrôle quelle ligne sera éligible à l’insertion
4. Un trigger table maître pour les insertions
5. Un trigger par table fille pour les mises à jour
6. Une procédure trigger pour le trigger de table maître
7. Une procédure trigger pour tous les triggers de table fille
8. Utillisation du SQL dynamique pour simplifier le code

Pour mon exemple, je me suis fondée sur la structure de données des statistiques de recensement INSEE de 1968 à 2009, téléchargée début 2012. Je n’ai utilisé ici que 2 colonnes de la table de faits globale pour les besoins de l’exemple. Entretemps, les statistiques ayant évolué, leur téléchargement est toujours possible, mais sous une forme différente de celle que j’utilise pour les besoins de mes exemples.

Création de la table :

create table statistiques(
 statistiques_id serial,
 An_Recens character varying(4),
 Typ_Act character varying(1)
) tablespace recensement;

Commentaires des colonnes :

comment on column statistiques.An_Recens is 'Année du recensement';
comment on column statistiques.Typ_Act is 'Type d''Activité';

Création des partitions :

create table statistiques_1968(
 check(An_Recens = '1968')) inherits(statistiques) tablespace annee1968;
create table statistiques_1975(
 check(An_Recens = '1975')) inherits(statistiques) tablespace annee1975;
create table statistiques_1982(
 check(An_Recens = '1982')) inherits(statistiques) tablespace annee1982;
create table statistiques_1990(
 check(An_Recens = '1990')) inherits(statistiques) tablespace annee1990;
create table statistiques_1999(
 check(An_Recens = '1999')) inherits(statistiques) tablespace annee1999;
create table statistiques_2009(
 check(An_Recens = '2009')) inherits(statistiques) tablespace annee2009;

Création de la fonction trigger des mises à jour :

create or replace function tg_upd_statistiques()
returns trigger as $$
declare
    stmt character varying;
begin
    if(NEW.An_Recens != OLD.An_Recens) then
        stmt := 'delete from statistiques_' || OLD.An_Recens || 
        ' where statistiques_id = ($1::' || pg_typeof(OLD) || ').
          statistiques_id';
        execute stmt using OLD::text;
        stmt := 'insert into statistiques_' || NEW.An_Recens || 
        ' values(($1::' || pg_typeof(NEW) || ').*)';
        execute stmt using NEW::text;
        return null;
    end if;
    return NEW;
end;
$$
language plpgsql;

Cette fonction trigger se sert de la capacité du PL/pgSQL à exécuter dynamiquement du code au travers d’une variable chaîne de caractères constituée. La commande EXECUTE passe en variable à l’aide de USING les enregistrements OLD et NEW représentant les valeurs « avant » et « après » mise à jour (UPDATE). Les variables OLD et NEW sont retraitées dans la chaîne de caractères à l’aide de pg_typeof. « $1:: » correspond au passage de la variable OLD ou NEW.

On peut remarquer que l’insertion se fait avec les valeurs « NEW.* ». Comme il s’agit de SQL dynamique, il n’est pas possible d’écrire directement cette syntaxe. De ce fait, l’utilisation de « USING » et « pg_typeof » permettent de résoudre le passage de l’enregistrement NEW.

Tel que cette fonction trigger est écrite, elle permet d’adresser n’importe laquelle des partitions de la table sans avoir besoin d’écrire une fonction trigger par table ; C’est tout intérêt de l’utilisation du SQL dynamique.

« RETURN NULL » permet de ne pas exécuter l’ordre d’UPDATE initial. Le trigger se substitue donc totalement à l’ordre initial.

Création de la fonction trigger des insertions :

create or replace function tg_statistiques()
returns trigger as $$
declare
    stmt character varying;
begin
    stmt := 'insert into statistiques_' || NEW.An_Recens || 
     ' values(($1::' || pg_typeof(NEW) || ').*)';
    execute stmt using NEW::text;
    return null;
end;
$$
language plpgsql;

Les explications concernant la fonction trigger des mises à jour sont applicables à celle de la fonction trigger des insertions.

Création du trigger unique pour l’ensemble des insertions :

create trigger tg_statistiques before insert on statistiques
for each row execute procedure tg_statistiques();

Création d’un trigger par partition utilisant la fonction trigger unique créée plus haut :

create trigger tg_upd_statistiques_1968 before update on statistiques_1968
for each row execute procedure tg_upd_statistiques();
create trigger tg_upd_statistiques_1975 before update on statistiques_1975
for each row execute procedure tg_upd_statistiques();
create trigger tg_upd_statistiques_1982 before update on statistiques_1982
for each row execute procedure tg_upd_statistiques();
create trigger tg_upd_statistiques_1990 before update on statistiques_1990
for each row execute procedure tg_upd_statistiques();
create trigger tg_upd_statistiques_1999 before update on statistiques_1999
for each row execute procedure tg_upd_statistiques();
create trigger tg_upd_statistiques_2009 before update on statistiques_2009
for each row execute procedure tg_upd_statistiques();

Exemple d’utilisation :

recensement=> insert into statistiques(an_recens,typ_act) values('1982','1');
INSERT 0 0
recensement=> select an_recens,typ_act from statistiques;
 an_recens | typ_act 
-----------+---------
 1982 | 1
(1 ligne)

recensement=> insert into statistiques(an_recens,typ_act) values('1999','2');
INSERT 0 0
recensement=> insert into statistiques(an_recens,typ_act) values('1999','3');
INSERT 0 0
recensement=> select an_recens,typ_act from statistiques;
 an_recens | typ_act 
-----------+---------
 1982 | 1
 1999 | 2
 1999 | 3
(3 lignes)

recensement=> update statistiques set an_recens = '1968' where an_recens = '1999';
UPDATE 0
recensement=> select an_recens,typ_act from statistiques;
 an_recens | typ_act 
-----------+---------
 1968 | 2
 1968 | 3
 1982 | 1
(3 lignes)

recensement=> delete from statistiques where an_recens = '1982';
DELETE 1
recensement=> select an_recens,typ_act from statistiques;
 an_recens | typ_act 
-----------+---------
 1968 | 2
 1968 | 3
(2 lignes)

recensement=> insert into statistiques(an_recens,typ_act) values('1982','5');
INSERT 0 0
recensement=> select an_recens,typ_act from statistiques;
 an_recens | typ_act 
-----------+---------
 1968 | 2
 1968 | 3
 1982 | 5
(3 lignes)

recensement=> select an_recens,typ_act from statistiques_1968;
 an_recens | typ_act 
-----------+---------
 1968 | 2
 1968 | 3
(2 lignes)

recensement=> select an_recens,typ_act from statistiques_1982;
 an_recens | typ_act 
-----------+---------
 1982 | 5
(1 ligne)

recensement=> select an_recens,typ_act from statistiques_1999;
 an_recens | typ_act 
-----------+---------
(0 ligne)