Comment puis-je supprimer efficacement des millions de lignes de base de données par ID dans PostgreSQL?

Suppression haute performance de millions de lignes postgresql par id

La suppression de millions de lignes de base de données peut avoir un impact grave sur les performances. Cet article examine des stratégies efficaces pour supprimer environ deux millions de lignes d'une base de données PostgreSQL à l'aide d'une liste d'identifices, en abordant les goulots d'étranglement courants.

Le défi:

La tâche consiste à supprimer un grand ensemble de données sur la base d'une liste d'ID fournie. Des méthodes standard comme la suppression par lots et les requêtes de clause IN s'avèrent souvent inefficaces pour cette échelle.

Solutions optimales:

La meilleure approche dépend de plusieurs facteurs:

• Accès simultané: L'absence d'écrits simultanés simplifie considérablement le processus.
• Indexation: La suppression temporaire des index inutiles (à l'exclusion de ceux cruciaux pour la suppression) et les reconstruire par la suite peut augmenter la vitesse.
• TROCHERS: Désactiver ou éliminer les déclencheurs pendant le processus de suppression peut améliorer considérablement les performances.
• Clés étrangères: Gérer soigneusement les relations de clés étrangères; Considérons la désactivation ou la modification temporaire pour faciliter la suppression.
• Autovacuum: L'exécution VACUUM ANALYZE Avant d'optimiser les performances.
• Approche en mémoire (pour les ensembles de données restants plus petits): Si les données restantes après la suppression sont sensiblement plus petites que la table d'origine et s'inscrit dans RAM, considérez cette méthode très efficace:

BEGIN;
SET LOCAL temp_buffers = '1000MB';
CREATE TEMP TABLE tmp AS
SELECT t.*
FROM   tbl t
LEFT   JOIN del_list d USING (id)
WHERE  d.id IS NULL;  -- copy remaining rows
TRUNCATE tbl;             -- clear the table
INSERT INTO tbl SELECT * FROM tmp;        -- re-insert remaining data
COMMIT;

Cela préserve les clés, vues et autres dépendances étrangères, résultant en une table propre et optimisée.

• DELETE Vs. TRUNCATE: Pour les tables plus petites, DELETE peut être plus rapide que TRUNCATE car il maintient les déclencheurs et les contraintes de clés étrangères.

Considérations clés:

• TRUNCATE ne peut pas être utilisé sur les tables avec des références de clés étrangères, sauf si toutes les tables de référence sont également tronquées simultanément.
• TRUNCATE ne déclenche pas ON DELETE déclenche.
• post-suppression VACUUM (ou VACUUM FULL ANALYZE) est crucial pour récupérer l'espace disque et optimiser la taille de la table.

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!