PostgreSQL VACUUM et ANALYZE : entretenir les tables et vérifier les statistiques

Dans PostgreSQL, VACUUM rend réutilisable l’espace occupé par des versions de lignes devenues inutiles. ANALYZE actualise les statistiques utilisées par le planificateur pour estimer les résultats. Ces commandes peuvent être exécutées séparément ou réunies avec VACUUM (ANALYZE). Leurs objectifs diffèrent : vérifiez les effets de chacune.

Ce guide suit une table de 10 000 lignes après des mises à jour, des suppressions, une analyse, un vacuum et de nouvelles insertions. Le SQL a été exécuté sur PostgreSQL 18.6 dans une base locale jetable. Nous avons mesuré les nombres de lignes, les statistiques de colonne, les compteurs de maintenance et la taille du fichier de données principal. Ces observations ne constituent pas un test de performances en production.

Comprendre les besoins de maintenance

PostgreSQL conserve des versions de lignes pour offrir aux transactions une vue cohérente des données. Une mise à jour ou une suppression peut laisser une ancienne version. Si une transaction peut encore en avoir besoin, elle ne peut pas être supprimée immédiatement. La documentation sur l’isolation des transactions explique ces règles de visibilité.

Distinguez trois questions : l’espace réutilisable dans la table, la précision des statistiques du planificateur et la place occupée par les fichiers sur disque. Une seule mesure ne répond pas aux trois. Un fichier volumineux après un vacuum réussi ne suffit pas à conclure à un échec.

Vacuum entretient aussi les informations de visibilité et gèle les anciens identifiants de transaction pour prévenir leur bouclage. Peu de lignes mortes ne suppriment pas ces besoins de maintenance.

Partez d’un symptôme observé dans la charge de travail. Notre guide pg_stat_statements (en anglais) aide à repérer les requêtes coûteuses. Après avoir vérifié les statistiques, utilisez EXPLAIN ANALYZE (en anglais) pour examiner une requête choisie. Ici, nous nous concentrons sur la maintenance et ses effets observables.

Préparer une base jetable

Connectez-vous directement avec psql à une nouvelle base de test dont vous êtes propriétaire. Conservez l’autocommit, n’entourez pas l’exercice de BEGIN et arrêtez-vous en cas d’erreur. Vérifiez la base avant toute création ou suppression :

SELECT current_database(), current_user, version();
SET statement_timeout = '15s';
SET lock_timeout = '2s';
SHOW autovacuum;

Les deux délais limitent les instructions et l’attente des verrous dans ce laboratoire ; ce ne sont pas des valeurs conseillées pour la production. Gardez la même connexion. Avec un client SQL graphique, vérifiez s’il ouvre automatiquement des transactions.

Arrêtez-vous si le schéma vacuum_lab existe déjà. Créez une seule fois le jeu de données :

CREATE SCHEMA vacuum_lab;
CREATE TABLE vacuum_lab.jobs (
    id integer PRIMARY KEY,
    state text NOT NULL,
    payload text NOT NULL
) WITH (autovacuum_enabled = false);

INSERT INTO vacuum_lab.jobs
SELECT i,
       CASE WHEN i % 5 = 0 THEN 'done' ELSE 'queued' END,
       repeat('x', 200)
FROM generate_series(1, 10000) AS g(i);

ANALYZE vacuum_lab.jobs;

La table contient 8 000 tâches queued et 2 000 tâches done. Toutes les charges utiles ont la même largeur. La maintenance automatique est suspendue uniquement sur cette table jetable afin de rendre visibles les étapes manuelles. L’autovacuum reste activé au niveau du serveur. Le nettoyage rétablit le réglage par défaut de la table avant de la supprimer.

Ne recopiez pas ce réglage de table dans une migration applicative. Les paramètres d’autovacuum décrivent le vacuum et l’analyse automatiques ; conservez cette protection pour les charges normales. Un exercice manuel ne la remplace pas.

Modifier les lignes puis examiner les statistiques

UPDATE vacuum_lab.jobs SET state = 'done' WHERE id <= 4000;
DELETE FROM vacuum_lab.jobs WHERE id % 2 = 0;

SELECT state, count(*)
FROM vacuum_lab.jobs
GROUP BY state ORDER BY state;

La mise à jour touche 4 000 lignes ; la suppression retire les 5 000 identifiants pairs. Il reste 2 600 lignes done et 2 400 lignes queued, soit 52 % et 48 %. La répartition diffère donc nettement de celle du départ.

Examinez maintenant les statistiques de la colonne state :

SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'vacuum_lab'
  AND tablename = 'jobs'
  AND attname = 'state';

Avant un nouvel ANALYZE, notre requête renvoyait toujours {queued,done} et les fréquences {0.8,0.2}. Ces valeurs décrivent l’analyse précédente, pas un comptage en direct de la table modifiée. Dans pg_stats, chaque valeur correspond à la fréquence située à la même position dans l’autre tableau.

ANALYZE vacuum_lab.jobs;

Relancez la requête de statistiques. Notre résultat est devenu {done,queued} et {0.52,0.48}, conformément à ce petit jeu de données. L’ordre a changé parce que done est désormais la valeur la plus fréquente. Lire les fréquences sans le tableau des valeurs inverserait l’interprétation.

La commande ANALYZE collecte des statistiques pour le planificateur ; elle diffère de l’option ANALYZE qui exécute une requête dans EXPLAIN. Les grandes tables sont généralement échantillonnées : n’attendez pas des fréquences exactes ni des estimations identiques à chaque exécution. Des statistiques précises aident la planification sans garantir un plan ou une latence donnés.

Relever la maintenance et la taille du fichier principal

SELECT n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relid = 'vacuum_lab.jobs'::regclass;

SELECT pg_relation_size('vacuum_lab.jobs') AS heap_bytes;

La première requête donne les estimations de lignes vivantes et mortes ainsi que les dates des dernières maintenances manuelles et automatiques. Notre table indiquait 5 000 lignes vivantes et 9 000 lignes mortes avant le vacuum. La seconde requête indiquait 3 481 600 octets.

Ces mesures ont des sens différents. Les statistiques d’activité des tables sont des estimations qui peuvent être retardées, pas une mesure exacte de l’espace inutile. Une date nulle ne constitue pas un historique permanent. Les statistiques peuvent être réinitialisées et les lectures répétées dans une transaction peuvent utiliser des valeurs en cache.

Par défaut, pg_relation_size mesure le fichier principal de la relation. Notre mesure du heap exclut les index séparés, les données TOAST et les fichiers auxiliaires. Elle ne représente pas l’occupation totale de la table ou de la base.

Exécuter VACUUM et comparer les résultats

VACUUM (ANALYZE, VERBOSE, TRUNCATE FALSE) vacuum_lab.jobs;

Cette commande lance un VACUUM ordinaire, inclut l’analyse et affiche un compte rendu. TRUNCATE FALSE désactive la suppression des pages vides à la fin du fichier pour isoler la réutilisation interne. Cette option évite aussi le verrou exclusif nécessaire à cette troncature. Consultez les options de VACUUM avant de l’adapter.

Relancez les requêtes de maintenance et de taille. Dans notre test, le compteur de vacuums manuels a augmenté, l’estimation des lignes mortes est passée à zéro et le heap est resté à 3 481 600 octets. Une comparaison séparée de toutes les lignes vivantes triées par identifiant a confirmé que leurs valeurs étaient inchangées.

Cette taille inchangée était attendue. Insérez maintenant 5 000 nouvelles lignes de même largeur :

INSERT INTO vacuum_lab.jobs
SELECT i, 'queued', repeat('x', 200)
FROM generate_series(10001, 15000) AS g(i);

SELECT count(*) AS live_rows,
       pg_relation_size('vacuum_lab.jobs') AS heap_bytes
FROM vacuum_lab.jobs;

La table est revenue à 10 000 lignes vivantes et le heap est resté à 3 481 600 octets. Les nouvelles lignes ont utilisé l’espace disponible après le vacuum. C’est une observation directe dans ce jeu de données, pas une garantie pour une autre charge, largeur de ligne, structure d’index ou configuration de stockage. Le heap initial occupait 2 490 368 octets avant les mises à jour et suppressions ; le vacuum ne l’a pas ramené à cette taille.

Vérifier transactions, verrous et droits

Notre test négatif a confirmé que VACUUM échoue dans un bloc de transaction explicite. Si votre client signale cette erreur, terminez la transaction de manière appropriée et lancez la maintenance en dehors. Lisez aussi les avertissements : PostgreSQL 18 exige les droits de maintenance appropriés et peut ignorer une table si ces droits manquent.

Le vacuum ordinaire peut coexister avec les lectures et écritures courantes, mais consomme des ressources et peut entrer en conflit avec d’autres maintenances ou modifications de schéma. La documentation des verrous PostgreSQL distingue ces cas. D’anciens instantanés peuvent retenir des versions de lignes : répéter VACUUM ne dispense pas d’examiner une transaction longue.

VACUUM FULL réécrit la table, exige de l’espace de travail supplémentaire et bloque les autres accès avec un verrou exclusif. Un heap encore volumineux ne justifie pas d’en faire l’étape suivante par défaut. Le guide de maintenance courante explique ce compromis. Nous n’avons testé ni VACUUM FULL ni les verrous en production.

Observer un vacuum en cours

Pour une opération plus longue, lancez cette requête depuis une seconde connexion à la même base :

SELECT pid, relid::regclass AS table_name, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum
WHERE datname = current_database();

pg_stat_progress_vacuum décrit la phase et la progression sur les blocs du heap d’un vacuum ordinaire. VACUUM FULL utilise la vue de progression de cluster. Le rapport des blocs parcourus ne représente pas le pourcentage de tout le travail restant : les phases d’index et de nettoyage ont leur propre travail.

Notre petit vacuum était déjà terminé quand nous avons interrogé la vue, qui n’a renvoyé aucune ligne. Le test valide donc la requête, pas un suivi en cours d’exécution. Un résultat vide après la fin ne signifie pas que le vacuum n’a jamais eu lieu.

Nettoyer puis appliquer les observations avec méthode

ALTER TABLE vacuum_lab.jobs RESET (autovacuum_enabled);
DROP TABLE vacuum_lab.jobs;
DROP SCHEMA vacuum_lab;

Exécutez ce nettoyage uniquement dans la base jetable. Il rétablit l’option de table, supprime la table nommée puis son schéma vide, sans CASCADE. Fermez ensuite la connexion du laboratoire.

L’exercice a passé 15 contrôles sur PostgreSQL 18.6 : évolution des statistiques, conservation des données vivantes, réutilisation d’espace, refus en transaction et nettoyage. Le serveur privé a ensuite été arrêté. Aucun débit de production, charge concurrente, déploiement cloud ou comportement d’instantané ancien n’a été testé.

Sur une vraie table, comparez l’historique de maintenance, les changements de données, l’activité des transactions et les plans avant de modifier les paramètres. Gardez l’autovacuum activé et ne faites un ajustement ciblé que si ces observations le justifient. Reprenez les mêmes mesures après le changement pour vérifier son effet sur le problème visé.