Partitionnement de table PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez à implémenter le partitionnement de tables dans PostgreSQL. L'objectif est de diviser une grande table en morceaux plus petits et plus gérables, ce qui peut améliorer considérablement les performances des requêtes et simplifier les tâches de gestion des données telles que les sauvegardes ou l'archivage.

Vous commencerez par créer une table principale "parente" conçue pour le partitionnement. Ensuite, vous définirez plusieurs tables "enfants", ou partitions, chacune contenant des données pour une plage de dates spécifique. Enfin, vous insérerez des données dans la table parente et observerez comment PostgreSQL les achemine automatiquement vers la partition correcte. Vous apprendrez également à interroger la table partitionnée et à voir comment PostgreSQL optimise ces requêtes en n'accédant qu'aux partitions pertinentes.

Créer la table parente partitionnée

Dans cette étape, vous allez créer la table principale sales, qui servira de table parente pour nos partitions. Cette table définit la structure de toutes ses partitions mais ne stockera aucune donnée elle-même.

Tout d'abord, vous devez vous connecter à la base de données PostgreSQL. Ouvrez un terminal et utilisez la commande suivante pour démarrer le shell interactif psql en tant qu'utilisateur postgres :

sudo -u postgres psql

Vous devriez maintenant voir l'invite PostgreSQL, qui ressemble à postgres=#. Toutes les commandes SQL ultérieures dans ce laboratoire seront exécutées à partir de cette invite.

Ensuite, créez la table sales. Cette table sera partitionnée par plage sur la colonne sale_date.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

Décomposons cette commande :

• CREATE TABLE sales (...) : Définit les colonnes pour nos données de ventes.
• PRIMARY KEY (sale_id, sale_date) : Dans une table partitionnée, la clé primaire doit inclure la colonne de partitionnement (sale_date).
• PARTITION BY RANGE (sale_date) : C'est la partie clé. Elle déclare que cette table est partitionnée en utilisant la méthode RANGE sur la colonne sale_date.

Après avoir exécuté la commande, vous devriez voir un message de confirmation CREATE TABLE.

Pour vérifier que la table a été créée, vous pouvez utiliser la commande \d dans psql pour décrire la structure de la table.

\d sales

La sortie affichera les colonnes de la table et, en bas, confirmera qu'il s'agit d'une "Partitioned table" (Table partitionnée) et listera la "Partition key" (Clé de partition).

                                       Table "public.sales"
   Column    |     Type      | Collation | Nullable |                    Default
-------------+---------------+-----------+----------+------------------------------------------------
 sale_id     | integer       |           | not null | nextval('sales_sale_id_seq'::regclass)
 sale_date   | date          |           | not null |
 product_id  | integer       |           |          |
 sale_amount | numeric(10,2) |           |          |
Partition key: RANGE (sale_date)
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0

Notez que le "Number of partitions" (Nombre de partitions) est de 0. Vous créerez les partitions réelles à l'étape suivante.

Définir les partitions pour les plages de dates

Maintenant que vous avez la table parente sales, vous devez créer les partitions réelles où les données seront stockées. Chaque partition contiendra des données pour une plage de dates spécifique. Dans cette étape, vous allez créer des partitions trimestrielles pour les années 2023 et 2024.

Vous devriez toujours être dans le terminal interactif psql.

Tout d'abord, créez les quatre partitions pour 2023. Chaque commande définit une nouvelle table comme partition de sales et spécifie la plage de dates qu'elle couvrira.

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

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

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

La clause FOR VALUES FROM ... TO ... définit la plage pour chaque partition. La borne inférieure est inclusive et la borne supérieure est exclusive. Par exemple, sales_2023_q1 stockera les enregistrements où sale_date est comprise entre le 2023-01-01 et le 2023-04-01 (exclu).

Ensuite, créez les partitions pour l'année 2024 en utilisant le même schéma trimestriel :

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Après avoir exécuté chaque commande CREATE TABLE, vous verrez un message de confirmation.

Pour vérifier que toutes les partitions ont été créées, vous pouvez lister à nouveau les tables de la base de données.

\dt

Vous devriez maintenant voir la table parente sales et les huit partitions que vous venez de créer (sales_2023_q1, sales_2023_q2, etc.) dans la sortie.

Insérer et router les données

Dans cette étape, vous allez insérer des données d'exemple. Une caractéristique clé du partitionnement est que vous insérez les données directement dans la table parente (sales), et PostgreSQL achemine automatiquement chaque ligne vers la partition correcte en fonction de la valeur de la clé de partition (sale_date).

Vous devriez toujours être dans le terminal interactif psql.

Exécutez l'instruction INSERT suivante pour ajouter 16 enregistrements de ventes d'exemple couvrant les années 2023 et 2024 :

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);

Une fois la commande terminée, vous verrez la sortie INSERT 0 16, ce qui indique que 16 lignes ont été insérées avec succès.

Pour vérifier que les données ont été correctement acheminées, vous pouvez interroger les partitions individuelles. Par exemple, vérifions le nombre d'enregistrements dans le premier trimestre de 2023 :

SELECT COUNT(*) FROM sales_2023_q1;

La sortie devrait être :

 count
-------
     2
(1 row)

Vérifions maintenant le nombre pour le quatrième trimestre de 2024 :

SELECT COUNT(*) FROM sales_2024_q4;

La sortie devrait également être 2. Cela confirme que PostgreSQL a placé les données dans les tables de partition sous-jacentes correctes.

Interroger les données et analyser les performances

Dans cette dernière étape, vous allez interroger la table partitionnée sales. Le principal avantage du partitionnement, connu sous le nom de "partition pruning" (élagage de partition), est que le planificateur de requêtes de PostgreSQL est suffisamment intelligent pour ne scanner que les partitions nécessaires, évitant ainsi un scan complet de l'ensemble des données.

Vous devriez toujours être dans le terminal interactif psql.

Tout d'abord, exécutez une requête pour récupérer toutes les ventes du premier trimestre 2023.

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Vous verrez les deux enregistrements qui entrent dans cette plage de dates. Pour voir comment PostgreSQL optimise cela, vous pouvez utiliser la commande EXPLAIN, qui affiche le plan d'exécution de la requête.

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

La sortie ressemblera à ceci :

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on sales_2023_q1 sales  (cost=0.00..31.75 rows=7 width=28)
   Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)

Remarquez la ligne Seq Scan on sales_2023_q1. Cela prouve que PostgreSQL n'a scanné que la partition sales_2023_q1 et a ignoré les sept autres, rendant la requête beaucoup plus rapide sur un grand ensemble de données.

Maintenant, exécutons une requête plus complexe pour trouver le montant total des ventes pour chaque produit en 2024.

SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;

Cette requête ne scannera efficacement que les quatre partitions de 2024 pour calculer le résultat. La sortie montrera les ventes totales pour chaque produit de 109 à 116.

Enfin, vous pouvez quitter le terminal interactif PostgreSQL en tapant :

\q

Vous reviendrez à votre invite de commande normale.

Résumé

Dans ce laboratoire, vous avez appris les bases du partitionnement de table dans PostgreSQL. Vous avez créé avec succès une table parente partitionnée par une plage de dates, défini des partitions spécifiques pour différentes périodes, et inséré des données qui ont été automatiquement acheminées vers la partition correcte. Plus important encore, vous avez utilisé la commande EXPLAIN pour observer le partition pruning (élagage de partition) en action, démontrant comment le partitionnement peut améliorer significativement les performances des requêtes en permettant à la base de données de ne scanner qu'un sous-ensemble des données. C'est une technique puissante pour gérer efficacement les ensembles de données à grande échelle.