Détection et élimination du gonflement des bases de données PostgreSQL avec VACUUM

Le gonflement des bases de données PostgreSQL se produit lorsque les anciennes versions de lignes et les index surdimensionnés occupent plus d'espace que vos données actives n'en nécessitent. Vous pouvez remarquer des sauvegardes plus volumineuses, des analyses plus lentes, des factures de stockage en hausse ou un autovacuum qui travaille constamment sans que la table ne rétrécisse sur le disque.

La solution dépend du type d'espace que vous devez récupérer. Un VACUUM standard rend l'espace des tuples morts réutilisable à l'intérieur de PostgreSQL. VACUUM FULL, REINDEX et des outils comme pg_repack peuvent physiquement réduire la taille des objets, mais ils entraînent des coûts opérationnels et de verrouillage différents.

Comprendre le MVCC de PostgreSQL et le gonflement

Pour lutter efficacement contre le gonflement, nous devons d'abord comprendre sa cause profonde. L'architecture MVCC de PostgreSQL garantit que les lecteurs ne bloquent jamais les rédacteurs et vice versa. Lorsqu'une ligne est mise à jour, PostgreSQL n'écrase pas l'ancienne ligne ; il insère une nouvelle version et marque l'ancienne version comme morte. De même, les lignes supprimées laissent derrière elles des tuples morts.

Le gonflement se produit lorsque ces tuples morts s'accumulent plus rapidement que les processus de maintenance (Autovacuum ou VACUUM manuel) ne peuvent les nettoyer ou réutiliser l'espace.

Conséquences du gonflement de la base de données

Le gonflement impacte les performances dans plusieurs domaines clés :

1. Augmentation de l'utilisation de l'espace disque : Les tuples morts occupent de l'espace physique, forçant les tables et les index à consommer plus de stockage que nécessaire.
2. Analyses séquentielles plus lentes : Le moteur de base de données doit lire les tuples morts lors des analyses de table, augmentant la charge d'E/S.
3. Indexation inefficace : Les index gonflés sont plus volumineux, entraînant davantage de lectures disque pour parcourir la structure de l'index.
4. Efforts d'autovacuum gaspillés : L'autovacuum doit travailler plus dur et plus longtemps pour nettoyer les tables, retardant potentiellement la maintenance critique sur d'autres tables.

Détection du gonflement de la base de données

La détection commence par les statistiques du catalogue et les tailles des objets. Traitez les requêtes simples comme un triage, et non comme une mesure exacte du gonflement, car la disposition de la table, le fillfactor, les données TOAST et les index affectent tous le nombre réel.

1. Identification des tables gonflées à l'aide de pg_stat_user_tables

La vue pg_stat_user_tables fournit des statistiques sur les tables définies par l'utilisateur. Nous pouvons calculer le gonflement approximatif en comparant la taille totale allouée à la table par rapport à la taille des données vivantes.

Indicateurs clés à surveiller :

• n_dead_tup : Nombre de tuples morts.
• last_autovacuum, last_vacuum : Date de la dernière maintenance.

Les comptages simples sont utiles car ils montrent où la pression du vacuum s'accumule. Un grand n_dead_tup par rapport à n_live_tup est une bonne raison d'inspecter une table de plus près.

Exemple de requête (Trouver les candidats au vacuum) :

Cette requête met en évidence les tables avec de nombreux tuples morts et indique la dernière exécution du vacuum :

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. Évaluation des index gonflés

Le gonflement affecte souvent les index de manière significative. Commencez par rechercher des index anormalement volumineux sur les tables à forte rotation :

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Une grande taille seule ne prouve pas le gonflement, mais elle vous indique quels index méritent d'être vérifiés avec des outils plus approfondis comme l'extension pgstattuple ou la requête de gonflement de votre plateforme de surveillance.

Gestion du gonflement : Le rôle de VACUUM

VACUUM est l'outil principal de PostgreSQL pour récupérer l'espace des tuples morts et mettre à jour les cartes de visibilité.

Autovacuum : La première ligne de défense

Par défaut, PostgreSQL exécute automatiquement les processus autovacuum. L'autovacuum effectue un VACUUM standard (qui marque l'espace comme réutilisable en interne mais ne le libère pas au système d'exploitation) lorsqu'un seuil est atteint. Ce seuil est défini par autovacuum_vacuum_scale_factor (par défaut 0,2 ou 20 % de la taille de la table) plus autovacuum_vacuum_threshold (par défaut 50 tuples).

Conseil de configuration : Pour les tables à forte rotation, envisagez de réduire le scale_factor pour déclencher la maintenance plus tôt, empêchant ainsi une accumulation importante de gonflement.

-- Exemple : Définition de paramètres d'autovacuum agressifs pour une table critique 'orders'
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

VACUUM standard vs. VACUUM FULL

Il existe deux modes principaux de nettoyage :

VACUUM standard

Un VACUUM standard marque les tuples morts pour réutilisation dans le fichier physique existant. Il ne réduit pas la taille du fichier de la table sur le disque. Ceci est non bloquant et sûr pour les tables à fort trafic.

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- Affiche les statistiques sur les tuples nettoyés

VACUUM FULL (L'outil de récupération d'espace)

VACUUM FULL réécrit l'intégralité du fichier de la table pour supprimer physiquement les tuples morts et récupérer l'espace vers le système d'exploitation.

Attention : VACUUM FULL nécessite un verrou ACCESS EXCLUSIVE sur la table pendant toute sa durée. Cela signifie que toutes les opérations de lecture et d'écriture sur cette table seront bloquées jusqu'à ce que VACUUM FULL soit terminé. Utilisez cette commande avec parcimonie sur les tables volumineuses et fortement utilisées.

VACUUM FULL table_name;

Meilleure pratique : N'utilisez VACUUM FULL que lorsque le gonflement est grave et que vous pouvez vous permettre un temps d'arrêt, ou pendant les fenêtres de maintenance planifiées.

Stratégies avancées anti-gonflement

Lorsque VACUUM FULL est trop perturbateur, il existe des méthodes alternatives pour récupérer de l'espace avec moins de temps d'arrêt.

1. Reconstruction des index

Les index individuels peuvent être reconstruits lorsque le gonflement de l'index est le problème principal. Pour les systèmes occupés, préférez la forme concurrente afin que les lectures et les écritures puissent se poursuivre pendant la majeure partie de l'opération :

REINDEX INDEX CONCURRENTLY index_name;

REINDEX INDEX index_name; simple est plus rapide mais prend des verrous plus forts, utilisez-le donc pendant une fenêtre de maintenance.

2. Utilisation de pg_repack pour les réécritures de tables en ligne

L'utilitaire pg_repack est la méthode préférée pour éliminer le gonflement des tables avec un temps d'arrêt minimal. Il fonctionne en créant une nouvelle copie propre de la structure et des données de la table à côté de l'ancienne table, en appliquant les modifications de manière synchrone, puis en échangeant les tables de manière atomique.

Comment fonctionne pg_repack :

1. Il crée une table temporaire (_new) reflétant l'originale.
2. Il surveille en continu les modifications sur la table d'origine à l'aide de déclencheurs.
3. Il effectue une copie synchronisée finale et un échange.

Installation et utilisation (Flux typique) :

Installez l'extension et le package CLI pour votre version de PostgreSQL, activez l'extension dans la base de données, puis exécutez la commande pg_repack depuis un shell :

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

Remarque sur pg_repack : Bien qu'il réduise considérablement le verrouillage par rapport à VACUUM FULL, il nécessite toujours la création de déclencheurs et la copie de données, ce qui consomme temporairement des E/S et du stockage supplémentaires.

À retenir

Le gonflement de la base de données est gérable lorsque vous le surveillez avant qu'il ne devienne une panne. La prévention via un autovacuum réglé est préférable aux réécritures d'urgence. Lorsque le gonflement se produit, suivez cette hiérarchie :

1. Surveiller : Vérifiez régulièrement pg_stat_user_tables pour des comptages élevés de n_dead_tup.
2. Régler l'autovacuum : Pour les tables actives, abaissez le scale factor pour garantir que le VACUUM standard s'exécute plus fréquemment.
3. Réparer : Si le gonflement est mineur, un VACUUM table_name standard peut suffire si l'activité de la table diminue.
4. Réparation agressive (Faible temps d'arrêt) : Utilisez pg_repack pour réécrire la structure de la table en ligne.
5. Réparation d'urgence (Temps d'arrêt élevé) : Utilisez VACUUM FULL uniquement en dernier recours lorsque le temps d'arrêt est acceptable, car il détient des verrous exclusifs.

Avant toute réécriture, vérifiez les transactions longues et assurez-vous de disposer de suffisamment d'espace disque libre pour l'opération.