Optimiser les suppressions de masse PostgreSQL avec le partitionnement de table

Dans la gestion de bases de données, la gestion efficace des opérations de données à grande échelle est essentielle. Un défi courant consiste à exécuter des suppressions massives sur de grandes tables sans nuire aux performances globales. Cet article examine comment la fonctionnalité de partitionnement de table de PostgreSQL peut accélérer considérablement le processus et
aider à maintenir des opérations de base de données fluides.

Découvrez plus de mon travail ici.

Le défi des suppressions massives

Supprimer un grand nombre de lignes d'une table PostgreSQL peut être une opération fastidieuse. Cela implique :

1. Parcourir le tableau pour trouver les lignes à supprimer
2. Suppression des lignes et mise à jour des index
3. Passer l'aspirateur sur la table pour récupérer de l'espace

Pour les tables comportant des millions de lignes, ce processus peut entraîner des transactions de longue durée et des verrous de table, ce qui peut avoir un impact sur la réactivité de la base de données.

Entrez le partitionnement de la table

Le partitionnement de table est une technique dans laquelle une grande table est divisée en morceaux plus petits et plus faciles à gérer appelés partitions. Ces partitions sont des tables distinctes qui partagent le même schéma que la table parent.

Ma configuration de référence

Pour quantifier les bénéfices du partitionnement, j'ai mis en place un benchmark avec trois scénarios utilisant PostgreSQL dans un environnement conteneurisé :

1. Table simple : Une table standard non partitionnée
2. Table partitionnée (suppression de lignes) : Une table partitionnée par semaine, supprimant les lignes de la première semaine
3. Table partitionnée (Partition Drop) : Identique au n°2, mais supprimant toute la partition de la première semaine

Spécifications du conteneur PostgreSQL

• Version PostgreSQL : 16.4
• Version Docker : 27.0.3
• Limites des ressources :
  • Limite du processeur : 8 processeurs
  • Limite de mémoire : 1 Go

Caractéristiques des données

• Total des records : 4 millions
• Distribution : Répartie uniformément sur 4 semaines (1 million par semaine)
• Indexation : Les deux tables (simples et partitionnées) ont un index sur la colonne temps

Principales conclusions

Scenario	Deletion Time	Table Size
Simple Table	1.26s	728 MB
Partitioned (Delete Rows)	734ms	908 MB
Partitioned (Drop Partition)	6.43ms	908 MB

1. Amélioration spectaculaire de la vitesse : La suppression d'une partition est 196 fois plus rapide que la suppression de lignes d'une simple table.
2. Compromis en matière de stockage : Les tables partitionnées utilisent environ 25 % de stockage en plus en raison de métadonnées supplémentaires et d'index par partition.
3. Impact d'insertion minimal : Le partitionnement n'a que légèrement augmenté le temps de remplissage des données (d'environ 2,8 %).

Pourquoi ça marche

1. Opérations ciblées : Le partitionnement permet à la base de données de fonctionner avec un sous-ensemble de données, réduisant ainsi la portée des opérations.
2. Opérations de métadonnées : La suppression d'une partition est avant tout une opération de métadonnées, évitant d'avoir à analyser et à supprimer des lignes individuelles.
3. Contention de verrouillage réduite : Des partitions plus petites signifient moins de verrous, permettant une meilleure concurrence.

Points saillants de la mise en œuvre

Voici un exemple simplifié de la façon de configurer une table partitionnée dans PostgreSQL :

CREATE TABLE records (<br>
    id BIGSERIAL,<br>
    time TIMESTAMPTZ NOT NULL,<br>
    body TEXT<br>
) PARTITION BY RANGE (time);

<p>CREATE TABLE records_week_1 PARTITION OF records<br>
    FOR VALUES FROM ('2023-01-01') TO ('2023-01-08');</p>

<p>-- Create index on the partition<br>
CREATE INDEX idx_records_week_1_time ON records_week_1 (time);</p>

<p>-- To delete a week's worth of data:<br>
ALTER TABLE records DETACH PARTITION records_week_1;<br>
DROP TABLE records_week_1;<br>
</p>

Conclusion

Pour les bases de données traitant des données de séries chronologiques ou tout scénario dans lequel les suppressions à grande échelle sont courantes, la mise en œuvre du partitionnement des tables peut entraîner des améliorations significatives des performances. Bien qu'il existe un petit compromis entre le stockage et la vitesse d'insertion, les gains en termes d'efficacité de suppression dépassent souvent de loin ces coûts.

En tirant parti du partitionnement, vous pouvez maintenir des performances élevées même si vos données augmentent, garantissant ainsi que votre base de données PostgreSQL reste réactive et efficace.

Lien vers le code de référence complet et les résultats détaillés

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!