Introduction au Partitionnement dans PostgreSQL

Le partitionnement de tables dans PostgreSQL consiste à diviser logiquement une table volumineuse en plusieurs sous-tables plus petites. Avant la version 10, cette fonctionnalité reposait exclusivement sur l'héritage de tables. Depuis la version 10, PostgreSQL a introduit le partitionnement déclaratif, qui est désormais la méthode recommandée. Dans les deux approches, la table parente agit comme un conteneur virtuel pour l'ensemble des données, tandis que les partitions (tables enfants) stockent physiquement les enregistrements.

Comparaison entre Partitionnement Déclaratif et Héritage

• Structure des colonnes : Le partitionnement déclaratif exige que les partitions aient exactement les mêmes colonnes que la table parente. L'héritage permet aux tables enfants d'avoir des colonnes supplémentaires.
• Héritage multiple : L'approche par héritage autorise l'héritage multiple, ce qui n'est pas le cas du partitionnement déclaratif.
• Stratégies de division : Le déclaratif supporte nativement les partitions par plage (Range), par liste (List) et par hachage (Hash). L'héritage repose sur des contraintes CHECK définies par l'utilisateur.
• Gestion des verrous : L'ajout ou la suppression de partitions en mode déclaratif nécessite un verrou ACCESS EXCLUSIVE sur la table parente, tandis que l'héritage ne requiert qu'un verrou SHARE UPDATE EXCLUSIVE.

Avantages et Cas d'Usage

Le partitionnement offre plusieurs bénéfices majeurs :

• Amélioration des performances de lecture : Les requêtes ciblant un sous-ensemble de données peuvent ignorer les partitions non pertinentes (élagage de partition).
• Optimisation des accès séquentiels : Pour les opérations de masse, le scan séquentiel d'une petite partition est plus rapide que l'utilisation d'index sur une table massive.
• Gestion du cycle de vie des données : L'archivage ou la suppression de données anciennes se fait instantanément en détachant ou supprimant une partition entière, évitant ainsi le coût d'un DELETE massif et la surcharge de VACUUM.
• Hiérarchisation du stockage : Les données froides peuvent être déplacées vers des tablespaces situés sur des disques plus lents et moins coûteux.

La règle générale recommandée par la documentation officielle est d'envisager le partitionnement lorsque la taille de la table dépasse la mémoire vive (RAM) disponible sur le serveur de base de données.

1. Le Partitionnement Déclaratif

---

1.1 Caractéristiques et Contraintes

Introduit dans PostgreSQL 10, le partitionnement déclaratif utilise des commandes dédiées comme ATTACH PARTITION et DETACH PARTITION. Bien qu'il utilise l'héritage en interne, il impose des règles strictes :

• Les contraintes CHECK et NOT NULL de la table parente sont automatiquement héritées.
• Les contraintes d'unicité, de clé primaire et de clé étrangère ne sont pas héritées globalement ; elles doivent être appliquées individuellement sur chaque partision.
• Une table partitionnée ne contient pas de données directement ; une commande TRUNCATE ONLY sur la table parente échouera.
• Les colonnes des partitions doivent correspondre exactement à celles de la table parente.

Limitations notables :

• Les contraintes d'exclusion ne peuvent pas couvrir l'ensemble des partitions.
• Une contrainte d'unicité ou une clé primaire doit inclure toutes les colonnes de la clé de partition.
• Les déclencheurs BEFORE ROW ne peuvent pas rediriger une ligne vers une autre partition.

1.2 Implémentation du Partitionnement Déclaratif

Création de la table parente avec une stratégie de partitionnement par plage (Range) :

CREATE TABLE system_events (
    event_id SERIAL,
    event_timestamp TIMESTAMP NOT NULL,
    payload_size INT
) PARTITION BY RANGE (event_timestamp);

Création des partitions annuelles :

CREATE TABLE events_y2021 PARTITION OF system_events
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE events_y2022 PARTITION OF system_events
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE events_y2023 PARTITION OF system_events
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Il est également possible de créer des sous-partitions (multi-niveaux). Par exemple, diviser l'année 2023 par trimestre :

CREATE TABLE events_y2023 PARTITION OF system_events
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    PARTITION BY RANGE (event_timestamp);

CREATE TABLE events_y2023_q1 PARTITION OF events_y2023
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE events_y2023_q2 PARTITION OF events_y2023
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

Vérification de la structure via la console psql :

\d+ system_events

1.3 Maintenance des Partitions Déclaratives

Ajouter une partition existante :

Si vous avez déjà une table standard, vous pouvez l'attacher :

CREATE TABLE events_y2024_staging (LIKE system_events INCLUDING ALL);
ALTER TABLE events_y2024_staging ADD CONSTRAINT chk_2024 
    CHECK (event_timestamp >= '2024-01-01' AND event_timestamp < '2025-01-01');

ALTER TABLE system_events ATTACH PARTITION events_y2024_staging
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Détacher ou supprimer une partition :

-- Détacher pour en faire une table indépendante
ALTER TABLE system_events DETACH PARTITION events_y2021;

-- Supprimer définitivement une partition
DROP TABLE events_y2021;

1.4 Optimisation avec l'Élagage de Partition (Partition Pruning)

Le paramètre enable_partition_pruning (activé par défaut) permet au planificateur de requêtes d'ignorer les partitions qui ne contiennent pas les données correspondant à la clause WHERE.

-- Vérification de l'activation
SET enable_partition_pruning = on;

-- Le plan d'exécution ne scannera que les partitions pertinentes
EXPLAIN SELECT * FROM system_events 
WHERE event_timestamp < '2022-06-01';

Si ce paramètre est désactivé, le planificateur effectuera un scan séquentiel sur toutes les partitions, dégradant considérablement les performances.

2. Le Partitionnement par Héritage (Traditionnel)

---

2.1 Concept et Mise en Place

Cette méthode, utilisée avant PostgreSQL 10, repose sur l'héritage de tables et des contraintes CHECK. La table parente ne stocke aucune donnée et sert de point d'accès.

Création de la table parente :

CREATE TABLE system_events (
    event_id INT NOT NULL,
    event_timestamp TIMESTAMP NOT NULL,
    payload_size INT
);

Création des tables enfants avec des contraintes d'exclusion :

CREATE TABLE events_y2021 (
    CHECK (event_timestamp >= '2021-01-01' AND event_timestamp < '2022-01-01')
) INHERITS (system_events);

CREATE TABLE events_y2022 (
    CHECK (event_timestamp >= '2022-01-01' AND event_timestamp < '2023-01-01')
) INHERITS (system_events);

2.2 Routage des Inserts via Trigger

Contrairement au partitionnement déclaratif, l'insertion dans la table parente ne redirige pas automatiquement les données. Un déclencheur (trigger) est nécessaire :

CREATE OR REPLACE FUNCTION route_event_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF (NEW.event_timestamp >= '2021-01-01' AND NEW.event_timestamp < '2022-01-01') THEN
        INSERT INTO events_y2021 VALUES (NEW.*);
    ELSIF (NEW.event_timestamp >= '2022-01-01' AND NEW.event_timestamp < '2023-01-01') THEN
        INSERT INTO events_y2022 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date hors limites pour le routage des événements !';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_route_event
BEFORE INSERT ON system_events
FOR EACH ROW EXECUTE FUNCTION route_event_insert();

Note : Les index et les contraintes d'unicité doivent être créés manuellement sur chaque table enfant.

2.3 Exclusion de Contraintes (Constraint Exclusion)

Pour que le planificateur ignore les tables enfants inutaires lors des requêtes sur la table parente, le paramètre constraint_exclusion doit être configuré sur partition (valeur par défaut).

SET constraint_exclusion = partition;

EXPLAIN SELECT * FROM system_events 
WHERE event_timestamp < '2022-01-01';

Si constraint_exclusion est réglé sur off, toutes les tables enfants seront scannées, annulant tout bénéfice de performance du partitionnement.

3. Maintenance Statistique : VACUUM et ANALYZE

---

Une particularité importante du partitionnement (qu'il soit déclaratif ou par héritage) est que les commandes de maintenance comme VACUUM et ANALYZE appliquées à la table parente ne se propagent pas automatiquement aux tables enfants pour la collecte de statistiques et le nettoyage des tuples morts.

Pour vérifier l'état de maintenance :

SELECT relname, last_vacuum, last_analyze 
FROM pg_stat_all_tables 
WHERE relname LIKE 'events_y%';

Exécuter un VACUUM sur la table parente :

VACUUM system_events;

Cette commande ne traitera que la table parente (qui est vide). Pour maintenir l'ensemble du schéma partitionné, il est impératif d'exécuter ces commandes sur chaque partition individuellement, ou d'utiliser un script qui itère sur toutes les tables enfants :

VACUUM events_y2021;
VACUUM events_y2022;
-- ... et ainsi de suite pour chaque partition.

Il est fortement recommandé d'automatiser cette tâche via des scripts ou des outils de gestion de base de données pour garantir des statistiques à jour et une réutilisation efficace de l'espace disque.