4 Partitionnement sous PostgreSQL

4/ Partitionnement sous PostgreSQL

Ce chapitre reprend l’intégralité des pages PDF 205-268 du manuel complet PERF2, y compris les énoncés de travaux pratiques et les solutions.

4.1 Principe & intérêts du partitionnement

• Principes et intérêts
• Historique
• Les différents types
  • applicatif
  • par héritage (historique)
  • déclaratif (à favoriser)

Le partitionnement est une technique consistant à scinder des grosses tables en plusieurs « partitions », d’abord pour des raisons de place et de maintenance. Dans l’idéal, la table partitionnée reste utilisable en tant que table unique, de manière plus ou moins transparente.

Un partitionnement entièrement géré par le moteur n’existe réellement que depuis la version 10 de PostgreSQL.

Jusqu’à la version 9.6, PostgreSQL ne connaissait que le partitionnement « par héritage », nettement moins flexible et performant. Bien sûr, il existe encore beaucoup d’applications qui « partitionnent » les données elles-mêmes, sans utiliser les mécanismes du moteur.

4.1.1 Pourquoi partitionner ?

• Faciliter la maintenance de gros volumes
  • VACUUM (FULL), réindexation, déplacements, sauvegarde logique…
• Gain en performance
  • parcours complet sur de plus petites tables
  • statistiques par partition plus précises
  • purge par partitions entières
  • pg_dump parallélisable
  • tablespaces différents (données froides/chaudes)
• Attention à la maintenance sur le code

Maintenir de très grosses tables peut devenir fastidieux, voire impossible. Ce genre de problème peut survenir :

• VACUUM FULL trop longs voire impossibles à cause d’un espace disque insuffisant ;
• autovacuum pas assez réactif sur les grosses tables ;
• index trop gros et réindexations interminables ;
• pg_dump ne sait pas paralléliser la sauvegarde d’une table volumineuse ;
• purge de grandes quantité de données très longue ;
• statistiques plus assez fines (et augmenter l’échantillonnage ne suffit pas forcément) ;
• dilution des lignes actives dans la table au détriment du hit ratio et des I/O ;
• mauvaise corrélation de lignes récupérées ensembles ;
• impossibilité de séparer données actives et inactives au niveau physique (tablespaces).

Certains outils existent pour mitiger ces problèmes (index partiels, index BRIN, autovacuum plus agressif…) mais ne sont pas des solutions définitives.

Le partitionnement consiste à scinder une table en plusieurs sous-tables (partitions) elles-mêmes manipulables en tant que tables à part entière.

Maintenance

La maintenance s’effectue sur les partitions plutôt que sur l’ensemble complet des données. En particulier, un VACUUM FULL ou une réindexation peuvent s’effectuer partition par partition, ce qui permet de limiter les interruptions en production, et lisser la charge.

pg_dump peut sauvegarder les différentes partitions d’une même table en parallèle.

Le partitionnement est aussi un préalable au déplacement d’une partie des données dans un autre tablespace pour des raisons de place, ou pour déporter les parties les moins utilisées de la table vers des disques plus lents et moins chers. Cependant, il faut mettre en balance le coût de migration du modèle existant avec l’économie réalisée sur les disques.

Parcours complet de partitions

Certaines requêtes (notamment décisionnelles) ramènent tant de lignes, ou ont des critères si complexes, qu’un parcours complet de la table est souvent privilégié par l’optimiseur.

Un partitionnement, souvent par date, permet de ne parcourir qu’une ou quelques partitions au lieu de l’ensemble des données. C’est le rôle de l’optimiseur de choisir la partition (partition pruning), par exemple celle de l’année en cours, ou des mois sélectionnés.

Suppression des partitions

La suppression de données parmi un gros volume peut poser des problèmes d’accès concurrents ou de performance, par exemple dans le cas de purges. En configurant judicieusement les partitions, on peut résoudre cette problématique en supprimant une partition (ici en partitionnement déclaratif) :

DROP TABLE nompartition ;

ou en la détachant :

ALTER TABLE table_partitionnee DETACH PARTITION nompartition ;

pour l’archiver (et la réattacher au besoin) ou la supprimer ultérieurement.

D’autres optimisations sont décrites dans ce billet de blog d’Adrien Nayrat : statistiques plus précises au niveau d’une partition, réduction plus simple de la fragmentation des index, jointure par rapprochement des partitions…

La principale difficulté d’un système de partitionnement consiste à partitionner avec un impact minimal sur la maintenance du code par rapport à une table classique.

4.2 Partitionnement applicatif

…ou la réinvention de la roue

4.2.1 Partitionnement applicatif

• Gestion au niveau applicatif, table par table
• Complexité pour le développeur
• Intégrité des données ?

L’application peut gérer le partitionnement elle-même, par exemple en créant des tables numérotées par mois, année… Le moteur de PostgreSQL ne voit que des tables classiques et ne peut assurer l’intégrité entre ces données.

Sur le principe, c’est très simple. Mais le développeur doit réinventer la roue :

• choix de la table dans chaque requête ;
• cohérence des schémas de données entre chaque table/partition ;
• gestion des index sur chacune… La lecture des données qui concerne plusieurs tables peut devenir délicate.

Ce modèle extrêmement fréquent est bien sûr à éviter, Ce modèle est bien sûr à éviter, mais on le rencontre encore fréquemment.

4.3 Partitionnement par héritage

Historique, ou pour un cas très spécifique

4.3.1 Partitionnement par héritage

CREATE TABLE mammiferes (pattes int)   INHERITS (animaux) ;
CREATE TABLE primates (debout boolean) INHERITS (mammiferes) ;

• Table mère :
  • définie normalement, peut contenir des lignes
• Tables filles :
  • héritent des propriétés de la table mère
  • …mais pas des contraintes, index et droits
  • colonnes supplémentaires possibles
• Insertion applicative ou par trigger (lent !)

Cette technique était assez utilisée, faute de mieux, pour partitionner avant PostgreSQL 10. Des applications l’utilisant peuvent encore se rencontrer.

Principe du partitionnement par héritage :

PostgreSQL permet de créer des tables qui héritent les unes des autres. L’héritage d’une table mère transmet les propriétés suivantes à la table fille :

• les colonnes, avec le type et les valeurs par défaut ;
• les contraintes CHECK.

Les tables filles peuvent ajouter leurs propres colonnes. Par exemple :

CREATE TABLE animaux (nom text PRIMARY KEY);
INSERT INTO animaux VALUES ('Éponge');
INSERT INTO animaux VALUES ('Ver de terre');

CREATE TABLE cephalopodes (nb_tentacules integer) INHERITS (animaux);
INSERT INTO cephalopodes VALUES ('Poulpe', 8);

CREATE TABLE vertebres (nb_membres integer) INHERITS (animaux);

CREATE TABLE tetrapodes () INHERITS (vertebres);
ALTER TABLE ONLY tetrapodes ALTER COLUMN nb_membres SET DEFAULT 4 ;

CREATE TABLE poissons (eau_douce boolean) INHERITS (tetrapodes);
INSERT INTO poissons (nom, eau_douce) VALUES ('Requin', false);
INSERT INTO poissons (nom, nb_membres, eau_douce) VALUES ('Anguille', 0, false);

La table poissons possède les champs des tables dont elle hérite :

\d+ poissons
                    Table "public.poissons"
   Column   |  Type   | Collation | Nullable | Default | ...
------------+---------+-----------+----------+---------+---
 nom        | text    |           | not null |         | ...
 nb_membres | integer |           |          | 4       | ...
 eau_douce  | boolean |           |          |         | ...
Inherits: tetrapodes
Access method: heap

On peut créer toute une hiérarchie avec des branches parallèles, chacune avec ses colonnes propres :

CREATE TABLE reptiles (venimeux boolean) INHERITS (tetrapodes);
INSERT INTO reptiles VALUES ('Crocodile', 4, false);
INSERT INTO reptiles VALUES ('Cobra', 0, true);

CREATE TABLE mammiferes () INHERITS (tetrapodes);

CREATE TABLE cetartiodactyles (
    cornes boolean,
    bosse boolean
) INHERITS (mammiferes);
INSERT INTO cetartiodactyles VALUES ('Girafe', 4, true, false);
INSERT INTO cetartiodactyles VALUES ('Chameau', 4, false, true);

CREATE TABLE primates (debout boolean) INHERITS (mammiferes);
INSERT INTO primates (nom, debout) VALUES ('Chimpanzé', false);
INSERT INTO primates (nom, debout) VALUES ('Homme', true);

\d+ primates

                    Table "public.primates"
   Column   |  Type   | Collation | Nullable | Default | ...
------------+---------+-----------+----------+---------+---
 nom        | text    |           | not null |         | ...
 nb_membres | integer |           |          | 4       | ...
 debout     | boolean |           |          |         | ...
Inherits: mammiferes
Access method: heap

On remarquera que la clé primaire manque. En effet, l’héritage ne transmet pas :

• les contraintes d’unicité et référentielles ;
• les index ;
• les droits.

Chaque table possède ses propres lignes :

SELECT * FROM poissons ;

   nom    | nb_membres | eau_douce
----------+------------+-----------
 Requin   |          4 | f
 Anguille |          0 | f

Par défaut une table affiche aussi le contenu de ses tables filles et les colonnes communes :

SELECT * FROM animaux ORDER BY 1 ;

   nom
--------------
 Anguille
 Chameau
 Chimpanzé
 Cobra
 Crocodile
 Éponge
 Girafe
 Homme
 Poulpe
 Requin
 Ver de terre

SELECT * FROM tetrapodes ORDER BY 1 ;

    nom    | nb_membres
-----------+------------
 Anguille  |          0
 Chameau   |          4
 Chimpanzé |          4
 Cobra     |          0
 Crocodile |          4
 Girafe    |          4
 Homme     |          4
 Requin    |          4

EXPLAIN SELECT * FROM tetrapodes ORDER BY 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Sort  (cost=420.67..433.12 rows=4982 width=36)
   Sort Key: tetrapodes.nom
   ->  Append  (cost=0.00..114.71 rows=4982 width=36)
         ->  Seq Scan on tetrapodes  (cost=0.00..0.00 rows=1 width=36)
         ->  Seq Scan on poissons  (cost=0.00..22.50 rows=1250 width=36)
         ->  Seq Scan on reptiles  (cost=0.00..22.50 rows=1250 width=36)
         ->  Seq Scan on mammiferes  (cost=0.00..0.00 rows=1 width=36)
         ->  Seq Scan on cetartiodactyles  (cost=0.00..22.30 rows=1230 width=36)
         ->  Seq Scan on primates  (cost=0.00..22.50 rows=1250 width=36)

Pour ne consulter que le contenu de la table sans ses filles :

SELECT * FROM ONLY animaux ;

     nom
--------------
 Éponge
 Ver de terre

Limites et problèmes :

En conséquence, on a bien affaire à des tables indépendantes. Rien n’empêche d’avoir des doublons entre la table mère et la table fille. Cela empêche aussi bien sûr la mise en place de clé étrangère, puisqu’une clé étrangère s’appuie sur une contrainte d’unicité de la table référencée. Lors d’une insertion, voire d’une mise à jour, le choix de la table cible se fait par l’application ou un trigger sur la table mère.

Il faut être vigilant à bien recréer les contraintes et index manquants ainsi qu’à attribuer les droits sur les objets de manière adéquate. L’une des erreurs les plus fréquentes est d’oublier de créer les contraintes, index et droits qui n’ont pas été transmis.

Ce type de partitionnement est un héritage des débuts de PostgreSQL, à l’époque de la mode des « bases de donnée objet ». Dans la pratique, dans les versions antérieures à la version 10, l’héritage était utilisé pour mettre en place le partitionnement. La maintenance des index, des contraintes et la nécessité d’un trigger pour aiguiller les insertions vers la bonne table fille, ne facilitaient pas la maintenance. Les performances en écritures étaient bien en-deçà des tables classiques ou du partitionnement déclaratif apparu ensuite.

Table partitionnée en détournant le partitionnement par héritage :

CREATE TABLE t3 (c1 integer, c2 text);
CREATE TABLE t3_1 (CHECK (c1 BETWEEN       0 AND  999999)) INHERITS (t3);
CREATE TABLE t3_2 (CHECK (c1 BETWEEN 1000000 AND 1999999)) INHERITS (t3);
CREATE TABLE t3_3 (CHECK (c1 BETWEEN 2000000 AND 2999999)) INHERITS (t3);
CREATE TABLE t3_4 (CHECK (c1 BETWEEN 3000000 AND 3999999)) INHERITS (t3);
CREATE TABLE t3_5 (CHECK (c1 BETWEEN 4000000 AND 4999999)) INHERITS (t3);
CREATE TABLE t3_6 (CHECK (c1 BETWEEN 5000000 AND 5999999)) INHERITS (t3);
CREATE TABLE t3_7 (CHECK (c1 BETWEEN 6000000 AND 6999999)) INHERITS (t3);
CREATE TABLE t3_8 (CHECK (c1 BETWEEN 7000000 AND 7999999)) INHERITS (t3);
CREATE TABLE t3_9 (CHECK (c1 BETWEEN 8000000 AND 8999999)) INHERITS (t3);
CREATE TABLE t3_0 (CHECK (c1 BETWEEN 9000000 AND 9999999)) INHERITS (t3);

-- Fonction et trigger de répartition pour les insertions :

CREATE OR REPLACE FUNCTION insert_into() RETURNS TRIGGER
LANGUAGE plpgsql
AS $FUNC$
BEGIN
  IF NEW.c1    BETWEEN       0 AND  999999 THEN
    INSERT INTO t3_1 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 1000000 AND 1999999 THEN
    INSERT INTO t3_2 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 2000000 AND 2999999 THEN
    INSERT INTO t3_3 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 3000000 AND 3999999 THEN
    INSERT INTO t3_4 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 4000000 AND 4999999 THEN
    INSERT INTO t3_5 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 5000000 AND 5999999 THEN
    INSERT INTO t3_6 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 6000000 AND 6999999 THEN
    INSERT INTO t3_7 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 7000000 AND 7999999 THEN
    INSERT INTO t3_8 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 8000000 AND 8999999 THEN
    INSERT INTO t3_9 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 9000000 AND 9999999 THEN
    INSERT INTO t3_0 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$FUNC$;

CREATE TRIGGER tr_insert_t3
BEFORE INSERT ON t3 FOR EACH ROW
EXECUTE PROCEDURE insert_into();

Noter qu’il reste encore à gérer les mises à jour de lignes… À cause de ce trigger, le temps d’insertion peut être allègrement multiplié par huit ou dix par rapport à une insertion dans une table normale ou dans une table avec le partitionnement déclaratif moderne.

La même table en partitionnement déclaratif par liste est :

CREATE TABLE t2 (c1 integer, c2 text) PARTITION BY RANGE (c1);
CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (      0) TO ( 1000000);
CREATE TABLE t2_2 PARTITION OF t2 FOR VALUES FROM (1000000) TO ( 2000000);
CREATE TABLE t2_3 PARTITION OF t2 FOR VALUES FROM (2000000) TO ( 3000000);
CREATE TABLE t2_4 PARTITION OF t2 FOR VALUES FROM (3000000) TO ( 4000000);
CREATE TABLE t2_5 PARTITION OF t2 FOR VALUES FROM (4000000) TO ( 5000000);
CREATE TABLE t2_6 PARTITION OF t2 FOR VALUES FROM (5000000) TO ( 6000000);
CREATE TABLE t2_7 PARTITION OF t2 FOR VALUES FROM (6000000) TO ( 7000000);
CREATE TABLE t2_8 PARTITION OF t2 FOR VALUES FROM (7000000) TO ( 8000000);
CREATE TABLE t2_9 PARTITION OF t2 FOR VALUES FROM (8000000) TO ( 9000000);
CREATE TABLE t2_0 PARTITION OF t2 FOR VALUES FROM (9000000) TO (10000000);

Attention.

Si le partitionnement par héritage fonctionne toujours sur les versions récentes de PostgreSQL, il est déconseillé pour les nouveaux développements.

4.4 Partitionnement déclaratif

4.4.1 Partitionnement déclaratif

• Mise en place et administration simplifiées, intégrées au moteur
• Gestion automatique des lectures et écritures
  • et rapide
• Partitions
  • attacher/détacher une partition
  • contrainte implicite de partitionnement
  • expression possible pour la clé de partitionnement
  • sous-partitions possibles
  • partition par défaut

Le partitionnement déclaratif est le système à privilégier de nos jours. Apparu en version 10, il a été grandement amélioré dans les versions 11 à 13, en fonctionnalités comme en performances. Des améliorations continuent à arriver à chaque version : préférez les versions récentes de PostgreSQL. Son but est de permettre une mise en place et une administration simples des tables partitionnées. Des clauses spécialisées ont été ajoutées aux ordres SQL, comme CREATE TABLE et ALTER TABLE, pour attacher (ATTACH PARTITION) et détacher des partitions (DETACH PARTITION).

Grosse simplification par rapport à l’ancien partitionnement par héritage : il n’est pas nécessaire d’ajouter des triggers pour gérer les insertions et mises à jour. Le routage est géré de façon automatique en fonction de la définition des partitions, au besoin vers une partition par défaut, et sans pénalité notable en performances.

Contrairement au partitionnement par héritage, la table partitionnée ne contient pas elle-même de ligne, ce n’est qu’une coquille vide.

4.4.2 Partitionnement par liste

4.4.3 Partitionnement par liste : implémentation

• Liste de valeurs par partition

  • statut, client, pays, année ou mois…

• Clé de partitionnement forcément mono-colonne

• Syntaxe :

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1) ;

CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);
...

Il est possible de partitionner une table par valeurs. Ce type de partitionnement fonctionne uniquement avec une clé de partitionnement mono-colonne (on verra qu’il est possible de sous-partitionner). Il faut que le nombre de valeurs soit assez faible pour être listé explicitement. Le partitionnement par liste est adapté par exemple au partitionnement par :

• type d’un objet ;
• client final (si peu de clients) ;
• géographie : pays, entité commerciale, entrepôt… ;
• statut d’un objet, pour isoler les données froides dans leur partition ;
• année ou mois (ne pas oublier de créer régulièrement de nouvelles partitions) pour faciliter les purges.

Voici un exemple de création d’une table partitionnée par liste et de ses partitions :

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1);

CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);

Les noms des partitions sont à définir par l’utilisateur, il n’y a pas d’automatisme ni de convention particulière.

Lors de l’insertion, les données sont correctement redirigées vers leur partition, comme le montre cette requête :

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (5);
SELECT tableoid::regclass, * FROM t1;

 tableoid | c1 | c2
----------+----+----
 t1_a     |  1 |
 t1_a     |  2 |
 t1_b     |  5 |

Il est aussi possible d’interroger directement une partition (ici t1_a et non t1) :

SELECT * FROM t1_a ;

 c1 | c2
----+----
  1 |
  2 |

Si aucune partition correspondant à la clé insérée n’est trouvée et qu’aucune partition par défaut n’est déclarée, une erreur se produit.

INSERT INTO t1 VALUES (0);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t1 VALUES (6);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (6).

Si la clé de partitionnement d’une ligne est modifiée par un UPDATE, la ligne change automatiquement de partition (sauf en version 10, où ce n’est pas implémenté, et l’on obtient une erreur).

4.4.4 Partitionnement par intervalle

4.4.5 Partitionnement par intervalle : implémentation

• Clé de partitionnement mono- ou multicolonnes
  • dates, id…
• Bornes :
  • supérieure exclue
  • MINVALUE / MAXVALUE pour infinis
• Syntaxe :

CREATE TABLE t2(c1 integer, c2 text) PARTITION BY RANGE (c1);

CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (1) TO (100);
CREATE TABLE t2_2 PARTITION OF t2 FOR VALUES FROM (100) TO (MAXVALUE);
...

Le partitionnement par intervalle est très courant quand il y a de nombreuses valeurs différentes de la clé de partitionnement, ou qu’elle doit être multicolonnes, par exemple :

• selon une tranche de dates, notamment pour faciliter des purges ;
• selon des plages d’identifiants d’objets : client final, produit…

Voici un exemple de création de la table partitionnée et de deux partitions :

CREATE TABLE t2(c1 integer, c2 text) PARTITION BY RANGE (c1);
CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (1) to (100);
CREATE TABLE t2_2 PARTITION OF t2 FOR VALUES FROM (100) TO (MAXVALUE);

Le MAXVALUE indique la valeur maximale du type de données : t2_2 acceptera donc tous les entiers supérieurs ou égaux à 100.

Attention.

Noter que les bornes supérieures des partitions sont exclues ! La valeur 100 ira donc dans la seconde partition.

Lors de l’insertion, les données sont redirigées vers leur partition, s’il y en a une :

INSERT INTO t2 VALUES (0);

ERROR:  no PARTITION OF relation "t2" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t2 VALUES (10, 'dix');

INSERT INTO t2 VALUES (100, 'cent');
INSERT INTO t2 VALUES (10000, 'dix mille');
SELECT * FROM t2 ;

  c1   |    c2
-------+-----------
    10 | dix
   100 | cent
 10000 | dix mille
(3 lignes)

SELECT * FROM t2_2 ;

  c1   |    c2
-------+-----------
   100 | cent
 10000 | dix mille
(2 lignes)

La colonne système tableoid permet de connaître la partition d’où provient une ligne :

SELECT ctid, tableoid::regclass, * FROM t2 ;

 ctid  | tableoid |  c1   |    c2
-------+----------+-------+-----------
 (0,1) | t2_1     |    10 | dix
 (0,1) | t2_2     |   100 | cent
 (0,2) | t2_2     | 10000 | dix mille

4.4.6 Partitionnement par hachage

4.4.7 Partitionnement par hachage : principe

Pour une répartition uniforme des données :

• Hachage de valeurs par partition

  • indiquer un modulo et un reste

• Clé de partitionnement mono- ou multicolonnes

• Syntaxe :

CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);

CREATE TABLE t3_a PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 0);
CREATE TABLE t3_b PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 1);
CREATE TABLE t3_c PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 2);

Si la clé de partitionnement n’est pas évidente et que le besoin est surtout de répartir la volumétrie en partitions de tailles équivalentes, le partitionnement par hachage est adapté. Voici comment partitionner par hachage une table en trois partitions :

CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);
CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE t3_3 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 2);

Une grosse insertion de données répartira les données de manière équitable entre les différentes partitions :

INSERT INTO t3 SELECT generate_series(1, 1000000);

SELECT relname, count(*) FROM t3
JOIN pg_class ON t3.tableoid=pg_class.oid
GROUP BY 1;

 relname | count
---------+--------
 t3_1    | 333263
 t3_2    | 333497
 t3_3    | 333240

4.4.8 Clé de partitionnement multicolonnes

• Clé sur plusieurs colonnes :
  • si partitionnement par intervalle ou hash (pas par liste)
  • et si 1er champ toujours présent
• Syntaxe :

CREATE TABLE t1(c1 integer, c2 text, c3 date)
PARTITION BY RANGE (c1, c3) ;

CREATE TABLE t1_a PARTITION OF t1
FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11') ;
...

Avec le partitionnement par intervalle, il est possible de créer les partitions en utilisant plusieurs colonnes. On profitera de l’exemple ci-dessous pour montrer l’utilisation conjointe de tablespaces différents. Commençons par créer les tablespaces :

CREATE TABLESPACE ts0 LOCATION '/tablespaces/ts0';
CREATE TABLESPACE ts1 LOCATION '/tablespaces/ts1';
CREATE TABLESPACE ts2 LOCATION '/tablespaces/ts2';
CREATE TABLESPACE ts3 LOCATION '/tablespaces/ts3';

Créons maintenant la table partitionnée et deux partitions :

CREATE TABLE t2(c1 integer, c2 text, c3 date not null)
       PARTITION BY RANGE (c1, c3);
CREATE TABLE t2_1 PARTITION OF t2
       FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11')
       TABLESPACE ts1;
CREATE TABLE t2_2 PARTITION OF t2
       FOR VALUES FROM (100,'2017-08-11') TO (200, '2017-08-12')
       TABLESPACE ts2;

La borne supérieure étant exclue, la valeur (100, '2017-08-11') fera donc partie de la seconde partition. Si les valeurs sont bien comprises dans les bornes, tout va bien :

INSERT INTO t2 VALUES (1, 'test', '2017-08-10');
INSERT INTO t2 VALUES (150, 'test2', '2017-08-11');

Mais si la valeur pour c1 est trop petite :

INSERT INTO t2 VALUES (0, 'test', '2017-08-10');

ERROR:  no partition of relation "t2" found for row
DÉTAIL : Partition key of the failing row contains (c1, c3) = (0, 2017-08-10).

De même, si la valeur pour c3 (colonne de type date) est antérieure :

INSERT INTO t2 VALUES (1, 'test', '2017-08-09');

ERROR:  no partition of relation "t2" found for row
DÉTAIL : Partition key of the failing row contains (c1, c3) = (1, 2017-08-09).

Les valeurs spéciales MINVALUE et MAXVALUE permettent de ne pas indiquer de valeur de seuil limite. Les partitions t2_0 et t2_3 pourront par exemple être déclarées comme suit et permettront d’insérer les lignes qui étaient ci-dessus en erreur.

CREATE TABLE t2_0 PARTITION OF t2
       FOR VALUES FROM (MINVALUE, MINVALUE) TO (1,'2017-08-10')
       TABLESPACE ts0;

CREATE TABLE t2_3 PARTITION OF t2
       FOR VALUES FROM (200,'2017-08-12') TO (MAXVALUE, MAXVALUE)
       TABLESPACE ts3;

Enfin, on peut consulter la table pg_class afin de vérifier la présence des différentes partitions :

ANALYZE t2;
SELECT relname,relispartition,relkind,reltuples
FROM pg_class WHERE relname LIKE 't2%';

 relname | relispartition | relkind | reltuples
---------+----------------+---------+-----------
 t2      | f              | p       |         0
 t2_0    | t              | r       |         2
 t2_1    | t              | r       |         1
 t2_2    | t              | r       |         1
 t2_3    | t              | r       |         0

Performances :

Si le premier champ de la clé de partitionnement n’est pas fourni, il semble que l’optimiseur ne sache pas cibler correctement les partitions. Il balaiera toutes les partitions. Ce peut être gênant si ce premier champ n’est pas systématiquement présent dans les requêtes.

Le sous-partitionnement est une alternative à étudier, également plus souple.

Il faut faire attention à ce que le nombre de combinaisons possibles ne mène pas à trop de partitions.

4.4.9 Sous-partitionnement

S’il y a deux chemins d’accès privilégiés :

CREATE TABLE objets (id int, statut int, annee int, t text)
 PARTITION BY LIST (statut) ;

CREATE TABLE objets_123
 PARTITION OF objets FOR VALUES IN (1, 2, 3)
 PARTITION BY LIST (annee) ;

CREATE TABLE objets_123_2023
 PARTITION OF objets_123 FOR VALUES IN (2023) ;
CREATE TABLE objets_123_2024
 PARTITION OF objets_123 FOR VALUES IN (2024) ;

CREATE TABLE objets_45
 PARTITION OF objets FOR VALUES IN (4,5) ;

• Plus souple que le partitionnement multicolonnes

Principe :

Les partitions sont des tables à part entière, qui peuvent donc être elles-mêmes partitionnées. Ce peut être utile si les requêtes alternent entre deux schémas d’accès.

Exemple :

L’exemple ci-dessus crée deux partitions selon statut (objets_123 et objets_45). La première partition est elle-même sous-partitionnée par année (objets_123_2023 et objets_123_2024). Cela permet par exemple, de faciliter la purge des données ou d’accélérer le temps de traitement si l’on requête sur une année entière. Il n’a pas été jugé nécessaire de sous-partitionner la seconde partition objets_45 (par exemple parce qu’elle est petite).

\dt objets*
                     Liste des relations
 Schéma |       Nom       |        Type        | Propriétaire
--------+-----------------+--------------------+--------------
 public | objets          | table partitionnée | postgres
 public | objets_123      | table partitionnée | postgres
 public | objets_123_2023 | table              | postgres
 public | objets_123_2024 | table              | postgres
 public | objets_45       | table              | postgres

Il n’est pas obligatoire de sous-partitionner avec la même technique (liste, intervalle, hachage…) que le partitionnement de plus haut niveau.

Il n’y a pas besoin de fournir la première clé de partitionnement pour que les sous-partitions soient directement accessibles :

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE annee = 2023 ;

                 QUERY PLAN
--------------------------------------------
 Append
   ->  Seq Scan on objets_123_2023 objets_1
         Filter: (annee = 2023)
   ->  Seq Scan on objets_45 objets_2
         Filter: (annee = 2023)

Fournir uniquement la première clé de partitionnement entraînera le parcours de toutes les sous-partitions concernées :

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE statut = 3 ;

                 QUERY PLAN
--------------------------------------------
 Append
   ->  Seq Scan on objets_123_2023 objets_1
         Filter: (statut = 3)
   ->  Seq Scan on objets_123_2024 objets_2
         Filter: (statut = 3)

Bien sûr, l’idéal est de fournir les deux clés de partitionnement pour n’accéder qu’à une partition :

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE statut = 3 AND annee = 2024 ;

                 QUERY PLAN
---------------------------------------------
 Seq Scan on objets_123_2024 objets
   Filter: ((statut = 3) AND (annee = 2024))

Cette fonctionnalité peut être ponctuellement utile, mais il ne faut pas en abuser en raison de la complexité supplémentaire. Toutes les clés de partitionnement devront se retrouver dans les clés primaires techniques des tables. Le nombre de partitions peut devenir très important.

Comparaison avec le partitionnement multicolonnes :

Le partitionnement multicolonnes est conceptuellement plus simple. Pour un même besoin, le nombre de partitions est identique. Mais le sous-partitionnement est plus souple :

• mélange possible des types de partitionnement (par exemple un partitionnement par liste sous-partitionné par intervalle de dates) ;
• type de sous-partitionnement différent possible selon les partitions.

4.4.10 Partition par défaut

• Pour le partitionnement par liste ou par intervalle
• Toutes les données n’allant pas dans les partitions définies iront dans la partition par défaut

CREATE TABLE t2_autres PARTITION OF t2 DEFAULT ;

• La conserver petite

Ajouter une partition par défaut permet de ne plus avoir d’erreur au cas où une partition n’est pas définie. Par exemple :

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1);
CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);

INSERT INTO t1 VALUES (0);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t1 VALUES (6);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (6).

-- partition par défaut
CREATE TABLE t1_defaut PARTITION OF t1 DEFAULT ;
-- on réessaie l'insertion
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (6);

SELECT tableoid::regclass, * FROM t1;

 tableoid  | c1 | c2
-----------+----+----
 t1_a      |  1 |
 t1_a      |  2 |
 t1_b      |  5 |
 t1_defaut |  0 |
 t1_defaut |  6 |

Comme la partition par défaut risque d’être parcourue intégralement à chaque ajout d’une nouvelle partition, il vaut mieux la garder de petite taille.

Un partitionnement par hachage ne peut posséder de table par défaut puisque les données sont forcément aiguillées vers une partition ou une autre.

4.4.11 Attacher une partition

ALTER TABLE ... ATTACH PARTITION ... FOR VALUES ... ;

• La table doit préexister
• Vérification du respect de la contrainte par les données existantes
  • parcours complet de la table : lent !
  • …sauf si contrainte CHECK identique déjà ajoutée
• Si la partition par défaut a des données qui iraient dans cette partition :
  • erreur à l’ajout de la nouvelle partition
  • détacher la partition par défaut
  • ajouter la nouvelle partition
  • déplacer les données de l’ancienne partition par défaut
  • ré-attacher la partition par défaut

Ajouter une table comme partition d’une table partitionnée est possible mais cela nécessite de vérifier que la contrainte de partitionnement est valide pour toute la table attachée, et que la partition par défaut ne contient pas de données qui devraient figurer dans cette nouvelle partition. Cela résulte en un parcours complet de la table attachée, et de la partition par défaut si elle existe, ce qui sera d’autant plus lent qu’elles sont volumineuses.

Ce peut être très coûteux en disque, mais le plus gros problème est la durée du verrou sur la table partitionnée, pendant toute cette opération. Il est donc conseillé d’ajouter une contrainte CHECK adéquate avant l’ATTACH : la durée du verrou sera raccourcie d’autant. Pour que cette contrainte soit prise en compte, il faut faire attention aux bornes exactes en partitionnement par intervalle, et préciser IS NOT NULL si le champ n’est pas NOT NULL.

Si des lignes pour cette nouvelle partition figurent déjà dans la partition par défaut, des opérations supplémentaires sont à réaliser pour les déplacer. Ce n’est pas automatique.

Exemple :

\set ECHO all
\set timing off

DROP TABLE IF EXISTS t1 ;

-- Une table partitionnée avec partition par défaut
CREATE TABLE t1 (c1 integer, filler char (10))
PARTITION BY LIST (c1);

CREATE TABLE t1_123 PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_45  PARTITION OF t1 FOR VALUES IN (4, 5);
CREATE TABLE t1_default PARTITION OF t1 DEFAULT ;

-- Données d'origine
INSERT INTO t1 SELECT 1+mod(i,5) FROM generate_series (1,5000000) i;
-- Les données sont bien dans les partitions t1_123 et t1_45
SELECT tableoid::regclass, c1, count(*) FROM t1
GROUP BY 1,2 ORDER BY c1 ;

-- Création d'une table pour les valeurs 6 à attacher
CREATE TABLE t1_6 (LIKE t1 INCLUDING ALL) ;
INSERT INTO t1_6 SELECT 6 FROM generate_series (1,1000000);

\dt+ t1*

\timing on
-- on attache la table : elle est scannée, ce qui est long
ALTER TABLE t1 ATTACH PARTITION t1_6 FOR VALUES IN (6) ;
-- noter la nouvelle contrainte sur la table
\d+ t1_6
-- on la détache, la contrainte a disparu
ALTER TABLE t1 DETACH PARTITION t1_6 ;
\d+ t1_6

-- on remet manuellement la même contrainte que ci-dessus
-- (ce qui reste long mais ne pose pas de verrou sur t1)
ALTER TABLE t1_6 ADD CONSTRAINT t1_6_ck CHECK (c1 IS NOT NULL AND c1 = 6) ;
\d+ t1_6
-- l'ATTACH est cette fois presque instantané
ALTER TABLE t1 ATTACH PARTITION t1_6 FOR VALUES IN (6) ;

\timing off

-- On insère par erreur des valeurs 7 sans avoir fait la partition
-- (et sans avoir le droit de les enlever de t1 ensuite)
INSERT INTO t1 SELECT 7 FROM generate_series (1,100);
-- Plus tard, créer la partition pour les valeurs 7 échoue avec l'erreur
-- "constraint for default partition "t1_default" would be violated""
CREATE TABLE t1_7 PARTITION OF t1 FOR VALUES IN (7);

-- Pour corriger cela, au sein d'une transaction,
-- on transfére les données de la partition par défaut
-- vers une nouvelle table qui est ensuite attachée
CREATE TABLE t1_7 (LIKE t1 INCLUDING ALL) ;
ALTER TABLE t1_7 ADD CONSTRAINT t1_7_ck CHECK (c1 IS NOT NULL AND c1 = 7) ;
BEGIN ;
    INSERT INTO t1_7
    SELECT * FROM t1_default WHERE c1=7 ;
    DELETE FROM t1_default WHERE c1=7 ;
    ALTER TABLE t1 ATTACH PARTITION t1_7 FOR VALUES IN (7) ;
COMMIT ;

4.4.12 Détacher une partition

ALTER TABLE ... DETACH PARTITION ...

• Simple et rapide
• Mais nécessite un verrou exclusif
  • option CONCURRENTLY

Détacher une partition est beaucoup plus rapide qu’en attacher une. En effet, il n’est pas nécessaire de procéder à des vérifications sur les données des partitions.

Cependant, il reste nécessaire d’acquérir un verrou exclusif sur la table partitionnée, ce qui peut prendre du temps si des transactions sont en cours d’exécution. L’option CONCURRENTLY mitige le problème malgré quelques restrictions, notamment : pas d’utilisation dans une transaction, incompatibilité avec la présence d’une partition par défaut, et nécessité d’une commande FINALIZE si l’ordre a échoué ou été interrompu.

La partition détachée devient alors une table tout à fait classique. Elle perd la contrainte de partitionnement, mais elle conserve les autres contraintes, les index, etc. hérités de la table partitionnée originale, ou directement posés sur la table quand elle était une partition, voire posés avant que la table soit attachée comme partition à la table partitionnée.

4.4.13 Changer la définition d’une partition

• Si pas de conflit entre partitions :

BEGIN ;
  ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY ;
  ALTER TABLE ... ATTACH PARTITION ... FOR <nouveau critère> ;
END ;

• Sinon : déplacement de données manuel

Modifier la définition d’une partition n’est pas directement prévu. S’il s’agit d’élargir une partition (liste ou intervalle), sans avoir à gérer un recouvrement avec une autre partition, il est possible de détacher la partition, puis de la réattacher avec sa nouvelle définition, le tout au sein d’une partition. La re-vérification des données prendra cependant du temps. L’astuce du CHECK redondant avec la contrainte de partitionnement, posé avant le DETACH, fonctionne là aussi et réduit la durée du verrou.

S’il faut couper une partition en deux (split), ou fusionner des partitions (merge), il faudra migrer les données manuellement, si possible dans une seule transaction.

4.4.14 Supprimer une partition

DROP TABLE nom_partition ;

Une partition étant une table, supprimer la table revient à supprimer la partition, en détruisant bien sûr les données qu’elle contient. Il n’y a pas besoin de détacher la table auparavant. L’opération est simple et rapide, mais demande un verrou exclusif.

Il est fréquent de partitionner par date pour profiter de cette facilité dans la purge des vieilles données. La durée en est énormément réduite, ainsi que les écritures de journaux puisqu’il n’y a plus de DELETE.

4.4.15 Fonctions de gestion et vues système

• Sous psql : \dP
• pg_partition_tree ('logs') : liste entière des partitions
• pg_partition_root ('logs_2019') : racine d’une partition
• pg_partition_ancestors ('logs_201901') : parents d’une partition

Voici le jeu de tests pour l’exemple qui suivra. Il illustre également l’utilisation de sous-partitions (ici sur la même clé, mais cela n’a rien d’obligatoire).

-- Table partitionnée
CREATE TABLE logs (dreception timestamptz, contenu text) PARTITION BY RANGE(dreception);
-- Partition 2018, elle-même partitionnée
CREATE TABLE logs_2018 PARTITION OF logs FOR VALUES FROM ('2018-01-01') TO ('2019-01-01')
                       PARTITION BY range(dreception);
-- Sous-partitions 2018
CREATE TABLE logs_201801 PARTITION OF logs_2018 FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE logs_201802 PARTITION OF logs_2018 FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
...
-- Idem en 2019
CREATE TABLE logs_2019 PARTITION OF logs FOR VALUES FROM ('2019-01-01') TO ('2020-01-01')
                       PARTITION BY range(dreception);
CREATE TABLE logs_201901 PARTITION OF logs_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
...

Et voici le test des différentes fonctions :

SELECT pg_partition_root('logs_2019');

 pg_partition_root
-------------------
 logs

SELECT pg_partition_root('logs_201901');

 pg_partition_root
-------------------
 logs

SELECT pg_partition_ancestors('logs_2018');

 pg_partition_ancestors
------------------------
 logs_2018
 logs

SELECT pg_partition_ancestors('logs_201901');

 pg_partition_ancestors
------------------------
 logs_201901
 logs_2019
 logs

SELECT * FROM pg_partition_tree('logs');

    relid    | parentrelid | isleaf | level
-------------+-------------+--------+-------
 logs        |             | f      |     0
 logs_2018   | logs        | f      |     1
 logs_2019   | logs        | f      |     1
 logs_201801 | logs_2018   | t      |     2
 logs_201802 | logs_2018   | t      |     2
 logs_201901 | logs_2019   | t      |     2

Noter les propriétés de « feuille » (leaf) et le niveau de profondeur dans le partitionnement.

Sous psql, \d affichera toutes les tables, partitions comprises, ce qui peut vite encombrer l’affichage. \dP affiche uniquement les tables et index partitionnés :

=# \dP
                Liste des relations partitionnées
 Schéma |   Nom    | Propriétaire |        Type        |  Table
--------+----------+--------------+--------------------+----------
 public | logs     | postgres     | table partitionnée |
 public | t2       | postgres     | index partitionné  | bigtable

La table système pg_partitioned_table permet des requêtes plus complexes. Le champ pg_class.relpartbound contient les définitions des clés de partitionnement.

Conseil.

Pour masquer les partitions dans certains outils, il peut être intéressant de déclarer les partitions dans un schéma différent de la table principale.

Dans un cadre « multitenant » avec de nombreux schémas, et des partitions de même noms chacune dans son schéma, positionner search_path permet de sélectionner implicitement la partition, facilitant la vie au développeur ou permettant de « mentir » à l’application.

4.4.16 Clé primaire et clé de partitionnement

La clé primaire doit contenir toutes les colonnes de la clé de partitionnement.

• Idem pour une contrainte unique
• Idem pour les clés étrangères vers la table partitionnée
• Pas un problème si on partitionne selon la clé
• Plus gênant si partitionnement par date, statut…
• Relâcher l’intégrité ? Séparer en deux tables ? UUID v7 ?

Clé de partitionnement incluse dans la clé primaire

Le partitionnement impose une contrainte importante sur la modélisation : la clé de partitionnement doit impérativement faire partie de la clé primaire (et des contraintes uniques).

En effet, PostgreSQL ne maintient pas d’index global couvrant toutes les partitions. Chaque index est lié à une seule partition. PostgreSQL ne peut donc garantir l’unicité d’un champ qu’au sein de chaque partition.

Dans beaucoup de cas cela ne posera pas de problème, notamment si on partitionne justement sur tout ou partie de cette clé primaire. Par exemple, avec une table partitionnée par hachage ou plage de valeurs de sa clé primaire, il n’y aura aucun impact. L’unicité reste garantie par le partitionnement et les index de chaque partition.

CREATE TABLE factures
                  (id bigint PRIMARY KEY,
                   d timestamptz,
                   id_client int NOT NULL,
                   montant_c int NOT NULL DEFAULT 0)
PARTITION BY HASH (id);

\d factures
                Table partitionnée « public.factures »
  Colonne  |           Type           | Collationnement | NULL-able | ...
-----------+--------------------------+-----------------+-----------+---
 id        | bigint                   |                 | not null  |
 d         | timestamp with time zone |                 |           |
 id_client | integer                  |                 | not null  |
 montant_c | integer                  |                 | not null  | 0
Clé de partition : HASH (id)
Index :
    "factures_pkey" PRIMARY KEY, btree (id)

Conséquences du changement de clé primaire

Dans d’autres cas, c’est plus gênant. Si l’on partitionne par année, on remplace une clé primaire id par la clé (id,annee) : rien n’interdit plus à deux lignes de même id d’exister dans deux partitions par année différentes.

Le risque d’un doublon reste limité si la « vraie » clé primaire id est un identifiant géré par la base à l’insertion (serial ou IDENTITY). Mais avec des identifiants générés côté applicatif, il y a un risque d’introduire un doublon. Le problème est indépendant du type de l’identifiant (entier, UUID…).

Il peut être décidé d’accepter le risque. Rajouter des clés uniques sur l’identifiant dans chaque partition peut au moins garantir son unicité au sein de chaque partition.

Partitionner a aussi un impact sur les clés étrangères pointant vers la table partitionnée : elles doivent aussi contenir les deux colonnes (id,annee). C’est un peu plus fastidieux (surtout en cas de migration d’un existant important), et peut être le signe que la table concernée doit être partitionnée aussi.

Contournement avec une table supplémentaire

Une solution assez complexe est de créer une autre table non partitionnée avec la clé primaire réelle, et une contrainte de clé étrangère de la table partitionnée vers cette table.

Conceptuellement, cela revient à ne pas partitionner une grosse table, mais à en « sortir » la plupart des champs utilisés dans une autre table partitionnée portant une contrainte.

Il n’y aura pas forcément besoin d’utiliser une jointure supplémentaire dans les requêtes. Il faut estimer aussi la taille de la table non partitionnée portant juste les identifiants.

Exemple :

-- On voudrait partitionner ainsi par date, mais le moteur refuse
CREATE TABLE factures_p
                  (id bigint PRIMARY KEY,
                   d timestamptz,
                   id_client int NOT NULL,
                   montant_c int NOT NULL DEFAULT 0)
PARTITION BY RANGE (d);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DÉTAIL : PRIMARY KEY constraint on table "factures_p" lacks column "d" which is part of the partition key.

-- On se rabat sur une clé primaire incluant la date,
-- ce qui n'est pas idéal pour la manipulation
CREATE TABLE factures_p
                  (id bigint NOT NULL,
                   d timestamptz NOT NULL,
                   id_client int,
                   montant_c int,
                   PRIMARY KEY (id, d)
                   )
PARTITION BY RANGE (d);

CREATE TABLE factures_p_202310 PARTITION OF factures_p
  FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');
CREATE TABLE factures_p_202311 PARTITION OF factures_p
  FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');
CREATE TABLE factures_p_futur PARTITION OF factures_p
  FOR VALUES FROM ('2023-12-01') TO (MAXVALUE) ;
-- Ces contraintes sécurisent partiellement les clés primaires
-- au sein de chaque partition
ALTER TABLE factures_p_202310 ADD CONSTRAINT factures_p_202310_uq UNIQUE (id);
ALTER TABLE factures_p_202311 ADD CONSTRAINT factures_p_202311_uq UNIQUE (id);
-- Ajout de quelques lignes de 1 à 5 sur les deux partitions
INSERT INTO factures_p (id, d, id_client)
  SELECT i, '2023-10-26'::timestamptz+i*interval '2 days', 42
  FROM generate_series (1,5) i;

BEGIN ;
  -- Ce doublon est accepté car les deux valeurs 3 ne sont pas dans la même partition
  INSERT INTO factures_p (id, d, id_client)
  SELECT 3, '2023-11-01'::timestamptz-interval '1s', 42 ;
  -- Vérification que 3 est en double
  SELECT tableoid::regclass, id, d FROM factures_p ORDER BY id ;
ROLLBACK ;

-- Cette table porte la « vraie » clé
-- et garantit l'unicité dans toutes les partitions
CREATE TABLE factures_ref (id bigint NOT NULL PRIMARY KEY,
                       d timestamptz NOT NULL,
                       UNIQUE (id,d) -- nécessaire pour la contrainte
                       )  ;
INSERT INTO factures_ref SELECT id,d FROM factures_p ;

-- Contrainte depuis la table partitionnée
ALTER TABLE factures_p ADD CONSTRAINT factures_p_id_fk
FOREIGN KEY (id, d)  REFERENCES factures_ref (id,d) ;

-- Par la suite, il faut insérer chaque nouvelle valeur de `id`
-- dans les deux tables
-- Ce doublon est à présent correctement rejeté :
WITH ins AS (
    INSERT INTO factures_p (id, d, id_client)
    SELECT 3, '2023-11-01'::timestamptz-interval '1s', 42
    RETURNING id,d )
INSERT INTO factures_ref
SELECT id, d FROM ins ;

ERROR:  duplicate key value violates unique constraint "factures_ref_pkey"
DÉTAIL : Key (id)=(3) already exists.

À la suppression, il ne faut pas oublier de supprimer des lignes dans les deux tables, dans le bon ordre :

DELETE FROM factures_p   WHERE id = 2 ;
DELETE FROM factures_ref WHERE id = 2 ;

(Une alternative serait de passer par un ON DELETE CASCADE sur factures_ref.)

Si l’on tient à être totalement propre pour garantir la cohérence des id entre les deux tables, une contrainte dans l’autre sens est possible. Par contre, toute suppression ou insertion impose de lever la contrainte dans la transaction.

ALTER TABLE factures_ref ADD CONSTRAINT factures_ref_id_d_fkey
FOREIGN KEY (id,d) REFERENCES factures_p (id,d) DEFERRABLE ;

BEGIN ;
  SET CONSTRAINTS ALL DEFERRED ;  -- uniquement contraintes deferrables
  DELETE FROM factures_p   WHERE id = 1 ;
  DELETE FROM factures_ref WHERE id = 1 ;
  INSERT INTO factures_ref (id, d)
  VALUES (6, '2023-11-02'::timestamptz) ;
  INSERT INTO factures_p (id, d, id_client)
  VALUES (6, '2023-11-02'::timestamptz, 99) ;
COMMIT ;

Si l’on ne veut pas d’une contrainte deferrable ni d’un ON DELETE CASCADE, il y a aussi la possibilité d’insérer ou supprimer dans les deux tables à la fois en une seule transaction :

ALTER TABLE factures_ref ALTER CONSTRAINT factures_ref_id_d_fkey
NOT DEFERRABLE ;

WITH ref AS (DELETE FROM factures_ref
             WHERE id = 6
             RETURNING id)
DELETE FROM factures_p p USING ref
WHERE p.id = ref.id ;

WITH iq AS (
  INSERT INTO factures_p (id, d, id_client)
  SELECT 7, now(), 100
  RETURNING id, d
  )
INSERT INTO factures_ref (id, d)
SELECT iq.id, iq.d
FROM iq ;

Enfin, une table des lignes de facture peut être déclarée ainsi avec la clé étrangère sur id :

CREATE TABLE lignes_facture (
  lid int PRIMARY KEY,
  fid int NOT NULL REFERENCES factures_ref (id),
  montant_c int NOT NULL DEFAULT 0
  );

au lieu de cette version qui doit intégrer la clé de partitionnement complète dans sa clé étrangère :

CREATE TABLE lignes_facture (
  lid int PRIMARY KEY,
  fid int NOT NULL,
  fd  timestamptz NOT NULL,
  montant_c int NOT NULL DEFAULT 0,
  FOREIGN KEY (fid,fd) REFERENCES factures_p (id, d)
  );

Astuce des clés primaires signifiantes

Pour éviter de créer de nouvelles colonnes, une astuce est de remplacer la clé primaire par une chaîne servant au partitionnement, finissant par l’identifiant réel. Cela donnerait par exemple des valeurs de clé comme FR00000001234 pour un partitionnement par pays, ou 2025061200001234 pour un partitionnement par date. Générer des partitions par plage de valeurs de l’identifiant est alors assez simple. Cela ne fonctionne pas si la clé de partitionnement doit évoluer (partitionnement par statut), ce ne serait plus une clé primaire.

Mais cette technique est fragile, car elle réclame un peu de développement pour générer les clés. Conceptuellement, des clés signifiantes sont à éviter, même si cela se retrouve encore dans de vieilles applications. De plus, des identifiants sous forme de chaînes de caractères deviennent souvent trop gros, avec un impact sur les performances, surtout s’il y a des jointures. Et il faut réécrire les clés étrangères vers les tables partitionnées.

Astuce des UUID v7

Pour le cas particulier, très fréquent, d’un partitionnement par date, une astuce nettement plus propre consiste à utiliser des UUID en version 7 comme identifiant de ligne.

Non seulement les UUID v7 évitent les gros soucis des UUID des versions précédentes liés au cache, mais ils contiennent leur date de génération (attention au fuseau horaire). Il est donc possible de partitionner selon des valeurs d’UUID correspondant à des dates précises. La table partitionnée conserve une clé primaire monocolonne, même s’il faut la réécrire. Dans une requête, pour réduire un accès à une partition, il faut réutiliser la fonction de conversion d’une date vers un UUID, ou d’une tranche de dates vers une tranches d’UUID, ce qui alourdit le code.

Un exemple détaillé est présenté dans un billet de Florent Jardin, inspiré entre autres notamment par ce billet de blog de Daniel Vérité.

PostgreSQL 18 devrait contenir les fonctions nécessaires pour créer des UUID, y compris pour une date précise, mais ces fonctions peuvent être installées sur les versions précédentes.

Sur les UUID et leur manipulation, voir https://dali.bo/s22_html#uuid

4.4.17 Indexation

• Propagation automatique
• Index supplémentaires par partition possibles
• Clés étrangères entre tables partitionnées

Les index sont propagés de la table mère aux partitions : tout index créé sur la table partitionnée sera automatiquement créé sur les partitions existantes. Toute nouvelle partition disposera des index de la table partitionnée. La suppression d’un index se fait sur la table partitionnée et concernera toutes les partitions. Il n’est pas possible de supprimer un tel index d’une seule partition.

Gérer des index manuellement sur certaines partitions est possible. Par exemple, on peut n’avoir besoin de certains index que sur les partitions de données récentes, et ne pas les créer sur des partitions de données d’archives.

Une clé primaire ou unique peut exister sur une table partitionnée (mais elle devra contenir toutes les colonnes de la clé de partitionnement) ; ainsi qu’une clé étrangère d’une table partitionnée vers une table normale.

Il est possible de créer une clé étrangère vers une table partitionnée de la même manière qu’entre deux tables normales. Par exemple, si les tables ventes et lignes_ventes sont toutes deux partitionnées :

ALTER TABLE lignes_ventes
ADD CONSTRAINT lignes_ventes_ventes_fk
FOREIGN KEY (vente_id) REFERENCES ventes (vente_id) ;

4.4.18 Planification & performances

• Mettre la clé dans la requête autant que possible
• ou : cibler les partitions directement
• Temps de planification
  • nombre de tables, d’index, leurs statistisques…
  • max ~ 100 partitions
• À activer ?
  • enable_partitionwise_aggregate
  • enable_partitionwise_join

Cibler la partition par la clé :

Si la clé de partitionnement n’est pas fournie, l’exécution concernera toutes les partitions, qu’elles soient accédées par Seq Scan ou Index Scan.

Attention.

Autant que possible, le développeur doit fournir la clé de partitionnement dans chaque requête, et le plan ciblera directement la bonne partition.

Comme avec les index, il faut vérifier que la clé est bien claire pour PostgreSQL. Si ce n’est pas le cas toutes les partitions seront lues :

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid + 0 = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit  (cost=0.00..6.29 rows=1 width=97)
   ->  Append  (cost=0.00..314250.00 rows=50000 width=97)
         ->  Seq Scan on pgbench_accounts_1  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_2  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
        ...
        ...
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_99  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_100  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)