Prévenir le gonflement : Stratégies avancées de VACUUM PostgreSQL pour la performance

PostgreSQL, une base de données relationnelle open-source puissante et polyvalente, repose sur plusieurs mécanismes internes pour maintenir l'intégrité des données et la performance. Parmi ceux-ci, l'opération VACUUM joue un rôle crucial dans la récupération de l'espace de stockage et la prévention de la dégradation des performances causée par les tuples morts. Bien que VACUUM soit souvent abordé en termes simples, comprendre et mettre en œuvre des stratégies avancées de vidage peut avoir un impact significatif sur la santé et la rapidité de votre base de données PostgreSQL.

Le gonflement des tables, un problème courant dans les bases de données très sollicitées, se produit lorsque les lignes supprimées ou mises à jour laissent derrière elles des tuples morts qui ne sont pas immédiatement supprimés. Ces tuples morts consomment de l'espace disque et peuvent ralentir l'exécution des requêtes, car la base de données doit parcourir plus de données. L'autovacuum, le processus automatisé en arrière-plan de PostgreSQL, vise à gérer cela, mais ses paramètres par défaut ne sont pas toujours optimaux pour chaque charge de travail. Le travail utile consiste à savoir quelles tables nécessitent un nettoyage plus agressif, lesquelles peuvent être laissées tranquilles, et quand une fenêtre de maintenance manuelle vaut la peine d'être perturbée.

Comprendre le gonflement des tables et son impact

PostgreSQL utilise un système de contrôle de concurrence multiversion (MVCC). Lorsqu'une ligne est mise à jour, une nouvelle version de la ligne est créée, et l'ancienne version est marquée comme morte. De même, lorsqu'une ligne est supprimée, elle est marquée comme morte mais n'est pas immédiatement supprimée. Ces tuples morts restent dans la table jusqu'à ce qu'une opération VACUUM les nettoie. Si VACUUM ne s'exécute pas assez souvent ou n'est pas assez agressif, les tuples morts s'accumulent, entraînant un gonflement de la table.

Les conséquences du gonflement des tables sont significatives :

Augmentation de l'utilisation du disque : Les tables gonflées consomment plus d'espace disque que nécessaire, ce qui peut entraîner des problèmes de stockage et des temps de sauvegarde plus longs.
Performances des requêtes plus lentes : Les requêtes qui analysent des tables gonflées doivent traiter plus de données, y compris les tuples morts, ce qui entraîne des temps d'exécution plus longs. Le gonflement des index peut avoir un effet similaire et néfaste.
Efficacité réduite du cache : Les tables et index gonflés occupent plus d'espace dans le cache de la base de données, ce qui peut réduire la quantité de données activement utilisées pouvant être conservées en mémoire.
Surcharge de l'autovacuum : Si l'autovacuum a du mal à suivre le rythme des mises à jour et des suppressions de tuples, il peut lui-même devenir un goulot d'étranglement des performances.

Réglage de l'autovacuum : La première ligne de défense

L'autovacuum est un processus en arrière-plan conçu pour exécuter automatiquement les opérations VACUUM et ANALYZE sur les tables qui ont subi des modifications significatives. Bien qu'il soit activé par défaut, son efficacité dépend fortement d'une configuration appropriée. Le réglage des paramètres de l'autovacuum est crucial pour prévenir le gonflement sans provoquer une charge système indue.

Paramètres clés de configuration de l'autovacuum trouvés dans postgresql.conf :

autovacuum_vacuum_threshold : Le nombre minimum de tuples mis à jour ou supprimés avant qu'un VACUUM ne soit exécuté sur une table. La valeur par défaut est 50.
autovacuum_vacuum_scale_factor : Une fraction de la taille de la table avant qu'un VACUUM ne soit exécuté. La valeur par défaut est 0,2 (20 %).
- Un VACUUM est déclenché si (nombre de tuples morts) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (nombre de tuples vivants).
autovacuum_analyze_threshold : Le nombre minimum de tuples insérés, mis à jour ou supprimés avant qu'un ANALYZE ne soit exécuté. La valeur par défaut est 50.
autovacuum_analyze_scale_factor : Une fraction de la taille de la table avant qu'un ANALYZE ne soit exécuté. La valeur par défaut est 0,1 (10 %).
- Un ANALYZE est déclenché si (nombre de tuples modifiés) > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * (nombre de tuples vivants).
autovacuum_vacuum_cost_delay : Le temps de pause si la limite de coût est dépassée (en millisecondes). La valeur par défaut est 20 ms.
autovacuum_vacuum_cost_limit : Le montant maximum de coût que le processus de vidage peut accumuler avant de se mettre en pause. La valeur par défaut est -1 (ce qui signifie qu'il utilise vacuum_cost_limit s'il est défini, sinon il est effectivement illimité, ce qui n'est pas idéal).
autovacuum_max_workers : Le nombre maximum de processus de vidage en arrière-plan pouvant s'exécuter simultanément. La valeur par défaut est 3.
autovacuum_nap_time : Le délai minimum entre le démarrage des tâches d'autovacuum. La valeur par défaut est 1 minute.

Scénarios pratiques de réglage de l'autovacuum :

Bases de données à taux de transaction élevé : Pour les tables avec des mises à jour et des suppressions fréquentes, vous devrez peut-être abaisser autovacuum_vacuum_threshold et autovacuum_vacuum_scale_factor pour déclencher le vidage plus fréquemment. Par exemple, sur une table très sollicitée, vous pourriez définir :
```
ALTER TABLE votre_table SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE votre_table SET (autovacuum_analyze_threshold = 200, autovacuum_analyze_scale_factor = 0.02);
```
Cela rend le vidage plus agressif sur cette table spécifique.
Grandes tables statiques avec mises à jour occasionnelles : Pour les tables qui sont principalement lues et rarement mises à jour, les paramètres par défaut peuvent être suffisants, ou vous pouvez même augmenter le scale_factor pour réduire la surcharge de vidage inutile.
Contrôle de l'impact de l'autovacuum : Pour empêcher l'autovacuum de consommer trop de ressources, vous pouvez ajuster autovacuum_vacuum_cost_delay et autovacuum_vacuum_cost_limit. Les bonnes valeurs dépendent de la vitesse de stockage et de la charge de travail, alors testez pendant le trafic normal plutôt que de copier un nombre aveuglément.
```
ALTER TABLE votre_table SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);
```
session_replication_role n'est pas un contrôle de réglage de l'autovacuum. Il affecte le comportement des déclencheurs et des règles et ne doit pas être utilisé comme un raccourci de gestion du gonflement.

Meilleures pratiques pour le VACUUM manuel

Bien que l'autovacuum soit essentiel, il existe des situations où les opérations manuelles VACUUM sont nécessaires ou bénéfiques :

Après des chargements/suppressions de données importants : Effectuer un VACUUM manuel après des opérations en masse significatives peut immédiatement récupérer de l'espace et empêcher l'accumulation de gonflement.
Lorsque l'autovacuum prend du retard : Si vous observez un gonflement important malgré l'exécution de l'autovacuum, un VACUUM manuel peut fournir un nettoyage immédiat.
VACUUM FULL pour un gonflement extrême : En cas de gonflement sévère où même un VACUUM régulier ne suffit pas, VACUUM FULL peut être utilisé. Cependant, VACUUM FULL réécrit la table entière dans un nouveau fichier, ce qui est une opération bloquante (nécessite un verrou exclusif) et peut prendre beaucoup de temps sur les grandes tables. Il doit être utilisé avec une extrême prudence et idéalement pendant une fenêtre de maintenance.
VACUUM (FREEZE) : Cette option force un VACUUM à geler tous les tuples restants qui sont assez anciens pour être considérés comme définitivement visibles par toutes les transactions futures. Cela peut aider à prévenir les avertissements VACUUM et à réduire la probabilité de problèmes de bouclage des identifiants de transaction.

Commandes VACUUM manuelles :

VACUUM standard : Récupère de l'espace et le rend disponible pour une réutilisation. Il ne réduit pas significativement la taille du fichier sur le disque à moins que TRUNCATE ne soit utilisé.
```
VACUUM votre_table;
VACUUM VERBOSE votre_table; -- Fournit plus de sortie
```
VACUUM ANALYZE : Effectue VACUUM puis met à jour les statistiques de la table. Ceci est crucial pour le planificateur de requêtes.
```
VACUUM ANALYZE votre_table;
```
VACUUM FULL : Réécrit la table, récupérant tout l'espace inutilisé et réduisant le fichier. Nécessite un verrou exclusif.
```
VACUUM FULL votre_table;
```
VACUUM (FREEZE) : Force le gel des anciens tuples.
```
VACUUM (FREEZE) votre_table;
```
VACUUM (TRUNCATE) : Disponible dans PostgreSQL 13+, cette option peut récupérer de l'espace à la fin du fichier de la table, similaire à TRUNCATE mais sans verrou exclusif pour l'ensemble de l'opération. Elle nécessite toujours un bref verrou exclusif à la fin.
```
VACUUM (TRUNCATE) votre_table;
```

Stratégies avancées et considérations

Au-delà du réglage de base de l'autovacuum et des commandes VACUUM manuelles, plusieurs techniques avancées peuvent optimiser davantage le vidage :

Surveillance du gonflement : Surveillez régulièrement vos tables pour détecter le gonflement. Vous pouvez utiliser des requêtes SQL pour estimer le gonflement ou utiliser des outils de surveillance.

-- Requête pour estimer le gonflement (nécessite l'extension pgstattuple)
-- CREATE EXTENSION pgstattuple;
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_table_size(oid)) AS index_size,
    CASE WHEN dead_tuples > 0 THEN round(100.0 * dead_tuples / (live_tuples + dead_tuples), 2) ELSE 0 END AS percent_bloat
FROM (
    SELECT
        schemaname,
        relname,
        n_live_tup AS live_tuples,
        n_dead_tup AS dead_tuples,
        c.oid
    FROM pg_stat_user_tables s JOIN pg_class c ON s.relid = c.oid
) AS stats
WHERE live_tuples + dead_tuples > 0
ORDER BY percent_bloat DESC;

-- Requête alternative pour estimer le gonflement sans extensions
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) ELSE 0 END AS percent_bloat
FROM pg_stat_user_tables
ORDER BY percent_bloat DESC;

Maintenance des index : Les index peuvent également devenir gonflés. Utilisez REINDEX pour les reconstruire si nécessaire. REINDEX simple peut bloquer le travail normal ; REINDEX CONCURRENTLY réduit les perturbations mais prend plus de temps et nécessite toujours une planification.
```
REINDEX INDEX CONCURRENTLY votre_nom_index;
```
Prévention du bouclage des identifiants de transaction : PostgreSQL réutilise les identifiants de transaction. Lorsqu'un ID atteint sa valeur maximale, il boucle. Pour éviter la corruption des données, PostgreSQL gèle les anciens tuples. VACUUM (surtout avec FREEZE) joue un rôle clé. Le paramètre freeze_max_age de l'autovacuum dicte l'âge qu'un identifiant de transaction peut atteindre avant que l'autovacuum ne soit forcé de s'exécuter, même si d'autres seuils ne sont pas atteints.
```
-- Surveiller l'âge des identifiants de transaction
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 10;
```
Si vous voyez des âges très élevés, cela indique des problèmes potentiels avec le vidage qui ne suit pas.
Stratégie de partitionnement : Pour les très grandes tables, envisagez le partitionnement. Vider une partition plus petite est beaucoup plus rapide et moins gourmand en ressources que de vider une seule table massive.
Pool de connexions : Bien que ce ne soit pas directement une stratégie de vidage, un pool de connexions efficace (par exemple, en utilisant PgBouncer) peut réduire la surcharge liée à l'établissement de connexions à la base de données, ce qui profite indirectement aux performances globales de la base de données et permet aux tâches de maintenance en arrière-plan comme l'autovacuum de s'exécuter plus facilement.
Contrôle des transactions longues : Une seule ancienne transaction peut empêcher le nettoyage. Vérifiez les sessions qui sont ouvertes depuis longtemps, en particulier les sessions idle in transaction, car elles peuvent maintenir les anciennes versions de lignes visibles et forcer le gonflement à augmenter.
```
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
```

Un flux de travail pratique pour le réglage du vidage

Commencez par la table qui pose problème, pas par l'ensemble du serveur. Si une table de commandes de 900 Go est gonflée et qu'une table de recherche de 20 Mo est propre, les modifications globales peuvent faire du bruit sans résoudre le vrai problème. Regardez d'abord pg_stat_user_tables :

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Comparez ensuite cela avec la charge de travail. Une table de type file d'attente qui met constamment à jour le statut peut nécessiter un autovacuum_vacuum_scale_factor faible, car attendre que 20 pour cent d'une table énorme devienne mort est bien trop tard. Une partition d'archive mensuelle peut ne pas nécessiter de paramètres agressifs du tout. Les paramètres par table vous permettent de traiter ces cas différemment.

Pour les tables à mises à jour lourdes, un modèle courant est :

ALTER TABLE job_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

Ces chiffres sont des points de départ, pas une vérité universelle. Surveillez si les tuples morts cessent de croître entre les exécutions de l'autovacuum, si la latence des requêtes s'améliore et si l'autovacuum crée des E/S inacceptables pendant les heures de pointe.

Lorsque le gonflement est déjà sévère, un VACUUM régulier peut arrêter l'hémorragie mais ne réduit pas le fichier de relation. Cela surprend de nombreuses équipes. Un VACUUM régulier rend l'espace réutilisable à l'intérieur de la table ; il ne rend généralement pas la plupart de l'espace au système d'exploitation. Pour réduire physiquement une grande table, vous choisissez entre des options perturbatrices telles que VACUUM FULL, les reconstructions de table, la rotation des partitions ou des outils tels que pg_repack là où c'est autorisé. Chaque option a des compromis opérationnels en termes de verrouillage, d'espace disque et de fonctionnement.

Choisir la correction la moins douloureuse

Si une table est modérément gonflée mais reçoit toujours des écritures régulières, commencez par le réglage de l'autovacuum et le nettoyage des anciennes transactions. Vous voulez que PostgreSQL réutilise l'espace naturellement au lieu de réécrire une grande table pendant les heures de travail.

Si une table a subi une purge unique et est maintenant beaucoup plus petite, un VACUUM régulier rendra l'espace vide réutilisable pour les futures insertions et mises à jour. Si vous devez rendre cet espace au système d'exploitation, planifiez une option de réécriture. VACUUM FULL est simple mais bloquant. pg_repack peut être moins perturbateur, mais c'est une extension supplémentaire et nécessite toujours suffisamment d'espace disque libre pour construire des structures de remplacement. Les tables partitionnées vous offrent une autre option : supprimer ou détacher les anciennes partitions au lieu de supprimer des millions de lignes d'une seule table géante.

Si les index sont le problème, ne reconstruisez pas chaque index par habitude. Vérifiez quels index sont volumineux, inutilisés ou dupliqués. pg_stat_user_indexes peut montrer les comptes d'analyse d'index, et une révision du schéma peut révéler des index qui se chevauchent, comme (user_id) et (user_id, created_at) où un seul peut être nécessaire. La suppression d'un index vraiment inutilisé peut améliorer les performances d'écriture et réduire le travail de vidage futur.

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

Soyez prudent avec les index "inutilisés" après un redémarrage ou une réinitialisation des statistiques, car les compteurs recommencent à zéro. Regardez suffisamment d'historique avant de supprimer quoi que ce soit.

Une bonne stratégie de vidage est ennuyeuse quand elle fonctionne. L'autovacuum s'exécute assez souvent pour que les tuples morts ne s'accumulent pas, la maintenance manuelle est réservée aux événements connus, et les anciennes transactions sont traitées comme des problèmes de production plutôt que comme des sessions inactives inoffensives. L'objectif n'est pas de vider autant que possible. L'objectif est de maintenir le nettoyage en avance sur le renouvellement sans voler les E/S dont votre application a besoin.