Comprendre et implémenter le partitionnement déclaratif de tables dans PostgreSQL 14+

Le partitionnement PostgreSQL mérite d'être envisagé lorsqu'une table devient difficile à interroger, à nettoyer, à archiver ou à supprimer. L'exemple typique est une table d'événements qui reçoit des millions de lignes par jour et qui est presque toujours interrogée par plage de temps. Sans partitionnement, même de bons index peuvent laisser une table coûteuse à maintenir et pénible à vieillir.

Le partitionnement déclaratif permet à une table logique d'acheminer les lignes vers des tables physiques plus petites appelées partitions. PostgreSQL 10 a introduit la syntaxe native, et les versions ultérieures ont amélioré la planification, l'élagage, l'indexation et le comportement de maintenance. PostgreSQL 14+ est suffisamment mature pour que de nombreuses équipes puissent utiliser le partitionnement sans schémas d'héritage basés sur des déclencheurs, mais cela récompense toujours une conception minutieuse. Une mauvaise clé de partition peut rendre le système plus compliqué sans le rendre plus rapide.

Qu'est-ce que le partitionnement déclaratif de tables ?

Le partitionnement déclaratif est une fonctionnalité de base de données qui vous permet de diviser une seule table logique (la table parente ou partitionnée) en plusieurs tables physiques (tables enfants ou partitions) en fonction d'un ensemble de règles définies. Chaque partition contient un sous-ensemble des données de la table parente. La clé de partitionnement détermine à quelle partition appartient une ligne.

Les principaux avantages du partitionnement déclaratif incluent :

• Amélioration des performances des requêtes : Les requêtes qui filtrent sur la clé de partitionnement peuvent être plus rapides car PostgreSQL peut élaguer les partitions qui ne peuvent pas contenir de lignes correspondantes.
• Gestion simplifiée des données : Les opérations comme la suppression de données anciennes ou l'archivage peuvent être effectuées beaucoup plus efficacement en détachant ou en supprimant des partitions individuelles plutôt qu'en effectuant des opérations DELETE massives sur une seule grande table.
• Maintenance simplifiée : L'indexation et le nettoyage peuvent être gérés par partition, réduisant ainsi l'impact sur l'ensemble de la table.
• Unités de maintenance plus petites : Les index au niveau des partitions, les opérations de détachement et le nettoyage ciblé peuvent réduire le rayon d'impact de la maintenance de routine.

Types de partitionnement déclaratif

PostgreSQL prend en charge plusieurs méthodes de partitionnement déclaratif, chacune adaptée à différents modèles de distribution de données :

1. Partitionnement par plage

Le partitionnement par plage divise les données en fonction d'une plage continue de valeurs dans une colonne spécifique (par exemple, dates, nombres).

Cas d'utilisation : Idéal pour les données de séries temporelles, telles que les journaux, les données d'événements ou les enregistrements de ventes, où vous interrogez fréquemment des données dans des plages de dates ou numériques spécifiques.

Exemple : Partitionnement d'une table sales par la colonne sale_date.

Création d'une table partitionnée par plage

Tout d'abord, créez la table parente, en spécifiant la méthode et la clé de partitionnement :

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

Ensuite, créez les partitions individuelles. Chaque partition est définie avec une clause FOR VALUES spécifiant la plage qu'elle contiendra.

-- Partition pour les ventes de janvier 2023.
-- La limite supérieure est exclusive, donc cela inclut le 31 janvier.
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Partition pour les ventes de février 2023
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Partition pour les ventes de mars 2023
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

Astuce : Lors de la définition des plages, assurez-vous qu'elles sont contiguës et couvrent toutes les valeurs possibles. Évitez les plages qui se chevauchent. La valeur TO est exclusive.

2. Partitionnement par liste

Le partitionnement par liste divise les données en fonction d'une liste discrète de valeurs dans une colonne.

Cas d'utilisation : Convient aux colonnes avec un ensemble de valeurs fixes et connu, telles que les régions géographiques, les codes de statut ou les catégories de produits.

Exemple : Partitionnement d'une table orders par la colonne region.

Création d'une table partitionnée par liste

Définissez la table parente avec PARTITION BY LIST :

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

Créez des partitions pour des régions spécifiques :

-- Partition pour les commandes en 'Amérique du Nord'
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('Amérique du Nord');

-- Partition pour les commandes en 'Europe'
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- Partition pour les commandes en 'Asie'
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asie');

Important : Si vous insérez une valeur pour region qui ne correspond à aucune liste IN de partition existante et qu'il n'y a pas de partition DEFAULT, l'insertion échouera. Vous pouvez créer une partition DEFAULT pour capturer toutes les autres valeurs.

Création d'une partition par défaut

-- Partition par défaut pour toute région non explicitement listée
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. Partitionnement par hachage

Le partitionnement par hachage distribue les données sur un nombre de partitions en fonction d'une valeur de hachage de la clé de partitionnement.

Cas d'utilisation : Utile lorsque vous avez un grand volume de données et que vous souhaitez les répartir uniformément entre les partitions sans distribution claire basée sur une plage ou une liste. C'est bon pour l'équilibrage de charge.

Exemple : Partitionnement d'une table users par user_id.

Création d'une table partitionnée par hachage

Définissez la table parente avec PARTITION BY HASH et spécifiez le nombre de partitions :

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

PostgreSQL créera automatiquement des partitions pour vous si vous ne les spécifiez pas, mais il est généralement recommandé de les créer explicitement, surtout lorsque vous souhaitez contrôler le nombre et le nom des partitions.

Création de partitions de hachage explicites

-- Créer 4 partitions de hachage
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

Remarque : Lors de l'utilisation du partitionnement par hachage, vous devez spécifier le modulus (le nombre total de partitions) et le remainder (à quelle partition celle-ci appartient).

Implémentation du partitionnement déclaratif : bonnes pratiques

• Choisissez la bonne clé de partitionnement : La clé de partitionnement doit correspondre à vos filtres de requête les plus fréquents et à vos opérations de gestion des données. Une bonne clé améliore considérablement les performances.
• Considérez le nombre de partitions : Trop peu de partitions peuvent ne pas apporter suffisamment d'avantages, tandis qu'un trop grand nombre peut augmenter les frais généraux. Visez un nombre qui équilibre la gérabilité et les performances. Pour le partitionnement par plage, tenez compte de votre taux de croissance des données et de vos politiques de conservation.
• Utilisez pg_partman pour l'automatisation : Pour le partitionnement par plage, en particulier avec les données de séries temporelles, envisagez d'utiliser des extensions comme pg_partman. Il automatise la création de nouvelles partitions et l'archivage/suppression des anciennes, réduisant considérablement l'effort manuel.
• Indexez stratégiquement : Les index sont stockés physiquement par partition. La création d'un index sur le parent crée des index de partition correspondants, mais vous devez toujours vérifier si chaque partition a besoin du même modèle d'index.
• Élagage des partitions : Assurez-vous que vos requêtes sont écrites pour tirer parti de l'élagage des partitions en incluant la clé de partitionnement dans les clauses WHERE. La commande EXPLAIN peut montrer si l'élagage se produit.
• Partitions DEFAULT : Pour le partitionnement par liste, une partition DEFAULT est cruciale pour éviter les erreurs d'insertion si de nouvelles valeurs apparaissent de manière inattendue.
• Contraintes uniques : Une contrainte unique ou une clé primaire sur une table partitionnée doit généralement inclure toutes les colonnes de la clé de partition. Cela surprend de nombreuses conceptions initiales.
• Types de données : Assurez-vous que le type de données de la clé de partitionnement est approprié et cohérent entre les tables parente et enfants.

Gestion des partitions

Attacher et détacher des partitions

Bien que les partitions soient créées directement via CREATE TABLE ... PARTITION OF ..., vous pouvez également détacher et attacher des tables existantes en tant que partitions. Ceci est utile pour migrer des données ou gérer de grands ensembles de données.

Détacher une partition : Le détachement transforme la partition en une table normale tout en conservant ses données.

-- Détacher la partition sales_2023_01
ALTER TABLE sales DETACH PARTITION sales_2023_01;

Attacher une table en tant que partition : Vous pouvez attacher une table normale qui est conforme au schéma du parent et dont les données correspondent aux limites de la partition.

-- Supposons que sales_2022_12 soit une table normale avec les mêmes colonnes que sales
-- et uniquement des lignes de décembre 2022.
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

Avant d'attacher une grande table, ajoutez d'abord une contrainte CHECK correspondante. PostgreSQL peut utiliser cette contrainte pour éviter d'analyser toute la table afin de prouver que les lignes correspondent aux limites de la partition.

Supprimer des partitions

La suppression d'une partition est une opération rapide car elle supprime uniquement la table de partition, pas les données qu'elle contient (sauf indication contraire). C'est beaucoup plus rapide que DELETE.

-- Pour supprimer une partition, vous pouvez simplement supprimer la table enfant
DROP TABLE sales_2023_01;

Exemple : Amélioration des performances des requêtes avec l'élagage des partitions

Considérez la table sales partitionnée par sale_date comme indiqué précédemment.

Requête sans élagage de partition (hypothétique sur une table non partitionnée) :

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Si sales était une table massive et non partitionnée, cette requête analyserait toute la table. Cependant, avec le partitionnement déclaratif :

-- Cette requête analysera uniquement la partition sales_2023_01
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Le planificateur de requêtes de PostgreSQL reconnaît que sale_date est la clé de partitionnement et que la plage spécifiée se situe entièrement dans la partition sales_2023_01. Il n'analysera donc que cette partition, réduisant considérablement les E/S et améliorant les performances.

Pour vérifier cela, utilisez EXPLAIN :

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

La sortie doit montrer uniquement la partition pertinente, ou peut montrer des sous-plans supprimés selon la version de PostgreSQL et la forme du plan. Le signe important est que les partitions non liées ne sont pas analysées.

Une liste de contrôle de conception pratique

Partitionnez uniquement lorsque vous pouvez nommer le gain opérationnel. "La table est grande" n'est pas suffisant en soi. Une grande table avec des recherches ponctuelles bien indexées peut convenir. Le partitionnement a plus de sens lorsque la plupart des requêtes incluent la clé de partition, lorsque les données anciennes sont régulièrement archivées ou supprimées, ou lorsque la maintenance sur une seule table énorme cause déjà des problèmes.

Pour les tables de séries temporelles, choisissez des tailles de partition qui correspondent à vos modèles de requête et de conservation. Les partitions quotidiennes sont utiles pour une ingestion très élevée et une conservation courte. Les partitions mensuelles sont souvent plus faciles à gérer pour un volume d'événements modéré. Trop de petites partitions peuvent ralentir la planification et rendre la maintenance bruyante ; trop peu de partitions géantes peuvent ne pas résoudre le problème d'origine.

Planifiez les insertions avant de déployer. Si les lignes peuvent arriver en retard, gardez les anciennes partitions disponibles assez longtemps pour les recevoir. Si la clé de partition peut contenir des valeurs inattendues, créez une partition DEFAULT et surveillez-la. Une partition par défaut doit être un filet de sécurité, pas un endroit où les données oubliées s'accumulent silencieusement pendant des mois.

Enfin, testez avec des formes de requêtes réelles. L'élagage des partitions fonctionne mieux lorsque la clause WHERE expose la clé de partition clairement, comme sale_date >= '2023-01-01' AND sale_date < '2023-02-01'. Envelopper la clé dans des fonctions peut rendre l'élagage plus difficile :

-- Moins favorable à l'élagage
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';

-- Plus facile pour le planificateur
WHERE sale_date >= DATE '2023-01-01'
  AND sale_date <  DATE '2023-02-01';

Le partitionnement déclaratif est un outil de maintenance autant qu'un outil de requête. Bien utilisé, il rend les données anciennes peu coûteuses à supprimer et les données chaudes plus faciles à analyser. Utilisé avec désinvolture, il ajoute plus de tables, plus d'index et plus de cas particuliers. Commencez par le modèle d'accès, choisissez la clé de partition à partir de ce modèle et vérifiez le plan avant de considérer la conception comme terminée.

Pour une grande table existante, ne planifiez pas une conversion unique risquée pendant le trafic de pointe. Un chemin de migration courant consiste à créer une nouvelle table partitionnée, à copier les données par morceaux, à maintenir les nouvelles écritures via la logique d'application ou un déclencheur soigneusement testé, puis à échanger les noms pendant une courte fenêtre de maintenance. L'approche exacte dépend du volume d'écriture et de la tolérance aux temps d'arrêt, mais le principe est le même : prouvez la copie, prouvez les contraintes et répétez le basculement avant de toucher à la production.