Partitionnement PostgreSQL : plages et élagage

Le partitionnement de tables PostgreSQL répartit une table logique entre plusieurs tables physiques selon une clé de partition. Avec le partitionnement déclaratif par plages, vous définissez des intervalles sans chevauchement, insérez les lignes via la table parente et laissez PostgreSQL les orienter. Un filtre adapté permet d’écarter les partitions qui ne peuvent contenir aucune ligne correspondante.

Ce guide utilise des plages mensuelles de dates avec PostgreSQL 18.6. Nos 35 tests vérifient l’emplacement exact des lignes, les bornes, les partitions absentes, une partition par défaut, les contraintes de clé et les plans avec ou sans élagage. Les six lignes de l’exercice montrent le comportement ; elles ne démontrent pas un gain de vitesse en production.

Choisir la clé selon les requêtes et la maintenance

Le partitionnement par plages est un point de départ pertinent lorsque les requêtes et la conservation suivent des dates. Le partitionnement par liste regroupe des valeurs explicites ; celui par hachage répartit les clés dans un nombre défini de groupes. Le guide PostgreSQL du partitionnement décrit ces possibilités et leurs contraintes d’exploitation.

Ici, event_date représente une date métier. Le type date laisse les fuseaux horaires hors de l’exercice. Si vos données utilisent des horodatages, définissez le fuseau et la règle de découpage avant de créer les partitions. Choisissez la granularité à partir des filtres et de la durée de conservation réels ; une partition quotidienne n’est pas automatiquement préférable à une partition mensuelle.

Ce partitionnement diffère de PARTITION BY dans une fonction de fenêtre, qui regroupe des lignes pour un calcul. Cette configuration déclarative locale ne répartit pas non plus les données entre des serveurs de base indépendants et ne configure pas le sharding.

Créer la table parente et deux partitions mensuelles

Utilisez une base jetable nommée partition_demo, où partition_lab n’existe pas encore. Votre rôle doit pouvoir créer le schéma et les tables. Enregistrez les blocs SQL dans les fichiers indiqués et exécutez-les dans l’ordre, dans des invocations psql distinctes. Par exemple : psql -X -qAt -v ON_ERROR_STOP=1 -d partition_demo -f 01-create.sql. Ajoutez vos options d’hôte et d’utilisateur.

Les résultats utilisent le format non aligné de psql, sans en-têtes, avec une barre verticale entre les colonnes. Les fichiers 02, 05 et 06 échouent volontairement ; poursuivez avec le fichier suivant dans une nouvelle invocation. Pour préparer un environnement d’apprentissage distinct dans un conteneur, consultez notre guide PostgreSQL avec Docker Compose, en anglais.

Exécutez 01-create.sql :

CREATE SCHEMA partition_lab;
CREATE TABLE partition_lab.events (
    event_date date NOT NULL,
    event_id bigint NOT NULL,
    amount numeric(10,2) NOT NULL,
    PRIMARY KEY (event_date, event_id)
) PARTITION BY RANGE (event_date);
CREATE TABLE partition_lab.events_2026_01
    PARTITION OF partition_lab.events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE partition_lab.events_2026_02
    PARTITION OF partition_lab.events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO partition_lab.events VALUES
    ('2026-01-01', 1, 10), ('2026-01-31', 2, 20),
    ('2026-02-01', 3, 30), ('2026-02-28', 4, 40);
SELECT event_id, event_date, tableoid::regclass AS stored_in
FROM partition_lab.events ORDER BY event_id;

1|2026-01-01|partition_lab.events_2026_01
2|2026-01-31|partition_lab.events_2026_01
3|2026-02-01|partition_lab.events_2026_02
4|2026-02-28|partition_lab.events_2026_02

La plage de janvier inclut le 1er janvier et exclut le 1er février. Celle de février commence le 1er février et exclut le 1er mars. Cette convention, borne inférieure incluse et borne supérieure exclue, permet à deux plages adjacentes de se rejoindre sans chevauchement.

L’INSERT cible events, tandis que tableoid identifie la table physique contenant chaque ligne retournée. La ligne du 1er février va dans la partition de février. Notre vérification supplémentaire de ONLY partition_lab.events renvoie zéro ligne : la table parente n’en stocke aucune directement. Une requête ordinaire sur la parente retourne les quatre lignes, pour un total de 100.00.

La référence CREATE TABLE documente PARTITION BY, PARTITION OF et les bornes. Nos tests refusent des plages qui se chevauchent ainsi que l’insertion directe d’une date de février dans la partition de janvier. Cibler une table fille ne contourne pas sa limite.

Observer l’absence d’une partition

Exécutez 02-missing.sql :

INSERT INTO partition_lab.events VALUES ('2026-03-01', 5, 50);

L’instruction échoue avec le SQLSTATE 23514, car le 1er mars se trouve hors des deux plages. Cet INSERT ne crée pas la partition du mois suivant. Un test distinct pour le 31 décembre échoue également.

Dans un INSERT de plusieurs lignes contenant une ligne valide de janvier puis une ligne de mars sans destination, toute l’instruction échoue. La ligne de janvier insérée auparavant est elle aussi annulée. Traitez donc l’opération comme une instruction en échec, sans supposer que certaines lignes ont été conservées.

Pour une table de production organisée par dates, prévoyez la création des partitions futures et la détection des intervalles manquants. L’anticipation nécessaire dépend des dates que les applications peuvent légitimement envoyer, y compris les enregistrements rétroactifs ou futurs.

Ajouter une partition par défaut de façon délibérée

Exécutez 03-default.sql :

CREATE TABLE partition_lab.events_default
    PARTITION OF partition_lab.events DEFAULT;
INSERT INTO partition_lab.events VALUES
    ('2026-03-01', 5, 50), ('2025-12-31', 6, 60);
SELECT event_id, event_date, tableoid::regclass AS stored_in
FROM partition_lab.events WHERE event_id IN (5, 6)
ORDER BY event_id;
SELECT count(*), sum(amount) FROM partition_lab.events;

5|2026-03-01|partition_lab.events_default
6|2025-12-31|partition_lab.events_default
6|210.00

La ligne future de mars et celle de décembre entrent désormais dans events_default. La parente contient six lignes totalisant 210.00 ; la partition par défaut contient deux lignes totalisant 110.00.

Une partition par défaut accepte les clés hors des plages définies, mais peut masquer une création de partition oubliée si son contenu n’est pas surveillé. Elle n’assouplit pas les autres contraintes : notre insertion d’une date NULL échoue toujours, car event_date est NOT NULL.

Décidez selon l’application si les dates sans plage doivent être acceptées ou refusées. Une partition par défaut est utile si vous pouvez examiner et déplacer les lignes inattendues, mais elle ajoute du travail de maintenance. L’exemple de mars ci-dessous en montre la raison.

Vérifier l’élagage des partitions avec EXPLAIN

Exécutez 04-pruning.sql. Il compare le même filtre de février avec l’élagage activé puis désactivé :

ANALYZE partition_lab.events;
BEGIN;
SET LOCAL constraint_exclusion = off;
SET LOCAL enable_partition_pruning = on;
EXPLAIN (COSTS OFF)
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01';
SET LOCAL enable_partition_pruning = off;
EXPLAIN (COSTS OFF)
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01';
ROLLBACK;
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01'
ORDER BY event_id;

Seq Scan on events_2026_02 events
  Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
Append
  ->  Seq Scan on events_2026_01 events_1
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
  ->  Seq Scan on events_2026_02 events_2
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
  ->  Seq Scan on events_default events_3
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
3|2026-02-01
4|2026-02-28

Le premier plan contient uniquement events_2026_02. Le deuxième contient un nœud Append et des parcours de janvier, de février et de la partition par défaut. Les deux dernières lignes affichent les valeurs correspondantes ; elles ne font pas partie du plan.

SET LOCAL limite ces réglages de diagnostic à la transaction. Désactiver constraint_exclusion dans les deux plans évite que cet autre mécanisme d’exclusion brouille la comparaison. ROLLBACK restaure les réglages antérieurs de la session. Ne désactivez pas ces options globalement pour reproduire l’exercice.

Notre programme inspecte aussi les plans JSON et compare les résultats réels avec et sans élagage. Les deux requêtes renvoient exactement les ID 3 et 4. Une requête sans filtre de date conserve les trois partitions ; un filtre limité à mars sélectionne la partition par défaut.

Ces résultats montrent les partitions que le planificateur peut écarter pour ces bornes constantes. Ils ne mesurent pas le temps écoulé et ne démontrent pas l’élagage pour toutes les requêtes paramétrées. Le guide EXPLAIN explique la lecture des plans. EXPLAIN seul affiche le plan sans exécuter le SELECT ; EXPLAIN ANALYZE exécute l’instruction et fournit des informations d’exécution.

L’élagage et l’indexation répondent à des besoins différents. Écarter janvier évite de visiter cette partition ; choisir un index dans février concerne l’accès aux lignes restantes. Un parcours séquentiel de cette minuscule partition de février n’est pas un échec. Conservez les index utiles, collectez les statistiques et évaluez les plans avec des volumes et des requêtes représentatifs de votre charge.

Inclure la clé de partition dans la clé primaire

Exécutez cette définition volontairement invalide dans 05-invalid-key.sql :

CREATE TABLE partition_lab.invalid_key (
    event_date date NOT NULL,
    event_id bigint PRIMARY KEY
) PARTITION BY RANGE (event_date);

Elle échoue avec le SQLSTATE 0A000, car la clé primaire omet event_date. Dans la table fonctionnelle, PRIMARY KEY (event_date, event_id) garantit l’unicité du couple.

Cela ne garantit pas l’unicité globale de event_id seul. Nos tests acceptent l’ID 1 à une autre date de février, mais aussi à une autre date de janvier. Répéter exactement le même couple date-ID échoue avec 23505. Concevez les références et les identifiants applicatifs selon la contrainte réelle, au lieu d’ajouter la date uniquement pour faire disparaître une erreur de schéma.

Examiner la partition par défaut avant d’ajouter une plage

Exécutez 06-add-march.sql après l’arrivée de la ligne de mars dans la partition par défaut :

CREATE TABLE partition_lab.events_2026_03
    PARTITION OF partition_lab.events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

L’instruction échoue avec le SQLSTATE 23514. La partition par défaut contient déjà une ligne qui appartient à la plage de mars proposée. Nos contrôles ne trouvent aucune nouvelle table de mars : les six lignes sont conservées et l’ID 5 reste dans events_default. Créer une partition ne déplace pas automatiquement ces données existantes.

Préparez une migration distincte qui conserve les lignes concernées, coordonne les écritures concurrentes et déplace les données dans la nouvelle plage avant la validation finale. Examinez les verrous et les contrôles requis par la procédure CREATE ou ATTACH choisie. Ne supprimez pas les données de la partition par défaut simplement pour permettre la création.

Ce guide s’arrête volontairement à l’échec de création, sans imposer une recette de migration en production. Avant de modifier une arborescence de partitions contenant des données, répétez la procédure et vérifiez la récupération avec notre guide de sauvegarde et de restauration PostgreSQL, en anglais. Incluez le comportement applicatif, les nombres de lignes et le délai de récupération.

Nettoyer l’environnement jetable

Exécutez 07-cleanup.sql uniquement dans la base jetable de l’exercice :

DROP TABLE partition_lab.events;
DROP SCHEMA partition_lab;

Supprimer cette table parente supprime aussi ses partitions et leurs données de test. Notre dernière vérification confirme la disparition du schéma. Le programme de test arrête également son serveur isolé et efface son fichier de mot de passe temporaire.

L’archive téléchargeable contient le SQL, les sorties capturées, les plans JSON et les 35 vérifications. Utilisez ces exemples pour comprendre le routage et l’élagage, puis déterminez si le partitionnement répond à un besoin réel de requêtes ou de maintenance dans votre système.