Un instantané PostgreSQL de longue durée peut conserver des versions intermédiaires des lignes qu’il ne lit jamais. Vacuum utilise un horizon de transaction prudent pour décider de ce qui peut être supprimé. Ce guide explique pourquoi, puis reproduit le comportement avec des lignes d’articles synthétiques et node-postgres sur Mac.

L’objectif est de distinguer un instantané qui retarde le nettoyage d’un vacuum qui n’a pas été exécuté. L’exemple cible PostgreSQL 18 et utilise deux connexions à la base de données : un lecteur qui conserve un instantané Repeatable Read et un rédacteur qui met à jour un seul article. La documentation de PostgreSQL indique actuellement que la version 18 est la version majeure prise en charge la plus récente ; les détails d’implémentation ci-dessous se rapportent explicitement à sa branche 18. Documentation PostgreSQL 18.

Pourquoi conserver des versions que l’ancien lecteur ne peut pas voir ?

Une mise à jour crée une nouvelle version de ligne. PostgreSQL conserve la version précédente tant qu’une autre transaction peut en avoir besoin. À terme, vacuum peut récupérer les versions obsolètes et rendre leur espace de stockage réutilisable. C’est le cycle de vie de base MVCC décrit dans Routine Vacuuming.

La subtilité est que la visibilité d’un instantané et la possibilité de supprimer physiquement une version répondent à des questions différentes. La visibilité détermine si une requête donnée doit renvoyer une version. Le nettoyage utilise un seuil prudent au lieu de déterminer, pour chaque version, exactement quels instantanés existants pourraient la lire.

Dans PostgreSQL 18, HeapTupleSatisfiesVacuum compare la transaction qui a rendu une version obsolète à OldestXmin. Une suppression ou un remplacement validé ne rend pas immédiatement la version précédente supprimable : les versions dont la transaction de suppression se situe au niveau ou au-delà de ce seuil restent classées comme récemment mortes. Cette règle est documentée directement dans l’implémentation officielle de la visibilité des tuples.

Considérons la séquence illustrative suivante. Chaque mise à jour est validée séparément et aucun autre lecteur n’est impliqué :

Étape Action Ce que voit l’ancien lecteur Dernière version validée
1 Insérer la révision 0 de l’article — Révision 0
2 Le lecteur établit un instantané Repeatable Read Révision 0 Révision 0
3 Le rédacteur valide la révision 1 Révision 0 Révision 1
4 Le rédacteur valide la révision 2 Révision 0 Révision 2
5 Le rédacteur valide la révision 3 Révision 0 Révision 3

La révision 0 est directement nécessaire au lecteur. Les révisions 1 et 2 sont des versions intermédiaires : ce lecteur ne peut pas les voir, car elles ont été créées après son instantané. Pourtant, leurs transactions de remplacement sont elles aussi plus récentes que le seuil de nettoyage conservé. L’application de la règle documentée concernant ce seuil explique pourquoi ces versions peuvent subsister aux côtés de la révision 0. Leur conservation ne signifie pas que le lecteur peut les parcourir comme des résultats de requêtes historiques. Implémentation de la visibilité des tuples dans PostgreSQL 18.

L’horizon couvre davantage que la ligne sélectionnée par le lecteur. PostgreSQL 18 calcule des horizons adaptés aux relations à partir des identifiants de transaction des processus serveur et des horizons d’instantanés ; pour les données des tables ordinaires, il prend généralement en compte les sessions de la même base de données. Il ne suit pas uniquement les lignes renvoyées par un SELECT particulier. Cela explique pourquoi une petite requête peut affecter le nettoyage ailleurs dans cette base de données. Les relations partagées et les autres types de relations obéissent à des règles distinctes ; l’affirmation « un instantané bloque vacuum partout » est donc trop générale. Implémentation des horizons de PostgreSQL 18.

Quelle transaction conserve réellement un instantané de longue durée ?

Repeatable Read établit son instantané lors de la première instruction qui ne contrôle pas la transaction, puis utilise cet instantané pour les requêtes suivantes. Émettre simplement BEGIN ne suffit pas pour cette démonstration ; la première requête sur l’article établit la vue qui doit être conservée. Read Committed, le niveau d’isolation par défaut de PostgreSQL, prend au contraire un nouvel instantané pour chaque commande. Isolation des transactions.

Par conséquent, une transaction Repeatable Read inactive qui a interrogé la table peut conserver son instantané même une fois la requête terminée. Une instruction Read Committed de longue durée peut également conserver un instantané pendant son exécution, mais les instructions distinctes terminées à ce niveau ne partagent pas la même vue fixe. La durée d’une transaction ne suffit donc pas à établir quel instantané est conservé. Isolation des transactions.

Une transaction en lecture seule peut contribuer à l’horizon sans posséder son propre identifiant de transaction de premier niveau. L’implémentation prend en compte les identifiants de transaction des processus serveur ainsi que les horizons d’instantanés. Dans cet exemple, un backend_xid nul associé à un backend_xmin non nul est donc compatible avec un lecteur qui conserve un instantané. Implémentation des horizons de PostgreSQL 18.

Reproduire le comportement depuis Terminal sur Mac

Il vous faut un serveur PostgreSQL 18 local en cours d’exécution, ses outils en ligne de commande et Node.js avec npm. La documentation de compatibilité de node-postgres mentionne Node.js 20 ou 24. Ce guide suppose que vos paramètres de connexion locale existants fonctionnent. Il crée une base de données distincte et n’utilise que du texte d’article synthétique. Présentation et installation de node-postgres.

Dans Terminal, créez un répertoire de travail, installez le pilote et créez la base de données de l’exemple :

mkdir article-snapshot-lab
cd article-snapshot-lab
npm init -y
npm install pg
createdb snapshot_lab

Enregistrez le programme complet ci-dessous sous snapshot-demo.cjs. Ses connexions héritent des paramètres d’environnement PostgreSQL, notamment PGDATABASE, PGHOST et PGPORT, comme indiqué dans Connexion avec node-postgres. Utilisez les mêmes paramètres de connexion locale pour createdb et le programme.

Le lecteur conserve toutes les instructions de la transaction sur un seul client dédié. node-postgres l’exige : une transaction doit utiliser le même client du début à la fin ; envoyer ses instructions via pool.query() ne convient donc pas. Le second client exécute indépendamment les mises à jour et les commandes vacuum. Transactions avec node-postgres.

L’exemple indexe aussi revision, qui change à chaque mise à jour. Cela empêche volontairement ces mises à jour d’utiliser HOT, ce qui facilite l’interprétation de la démonstration. PostgreSQL précise que HOT exige que les mises à jour ne modifient pas les colonnes référencées par les index ordinaires et qu’il y ait suffisamment d’espace sur la page d’origine. Tuples stockés uniquement dans le tas.

const { Client } = require('pg');

async function main() {
  const reader = new Client({
    application_name: 'article_snapshot_reader',
    connectionTimeoutMillis: 5000
  });
  const writer = new Client({
    application_name: 'article_snapshot_writer',
    connectionTimeoutMillis: 5000
  });
  let readerOpen = false;
  writer.on('notice', notice => console.log(notice.message));

  try {
    await reader.connect();
    await writer.connect();
    const version = await writer.query('SHOW server_version_num');
    const major = Math.floor(Number(version.rows[0].server_version_num) / 10000);
    if (major !== 18) throw new Error('This walkthrough targets PostgreSQL 18.');

    await writer.query(
      'CREATE TABLE public.article_snapshot_demo (' +
      'id integer PRIMARY KEY, revision integer NOT NULL, title text NOT NULL)'
    );
    await writer.query(
      'CREATE INDEX article_snapshot_demo_revision_idx ' +
      'ON public.article_snapshot_demo (revision)'
    );
    await writer.query(
      'INSERT INTO public.article_snapshot_demo VALUES (1, 0, $1)',
      ['Article draft']
    );

    await reader.query('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY');
    readerOpen = true;
    const selectRow = 'SELECT id, revision, title FROM public.article_snapshot_demo';
    console.log('Reader before:', (await reader.query(selectRow)).rows);

    for (const revision of [1, 2, 3]) {
      await writer.query(
        'UPDATE public.article_snapshot_demo SET revision = $1, title = $2 WHERE id = 1',
        [revision, 'Article revision ' + revision]
      );
    }

    const pid = (await reader.query('SELECT pg_backend_pid() AS pid')).rows[0].pid;
    console.log('Reader activity:', (await writer.query(
      'SELECT state, backend_xid, backend_xmin FROM pg_stat_activity WHERE pid = $1',
      [pid]
    )).rows);
    console.log('Writer latest:', (await writer.query(selectRow)).rows);
    console.log('Reader still:', (await reader.query(selectRow)).rows);

    console.log('Vacuum with snapshot open:');
    await writer.query('VACUUM (VERBOSE) public.article_snapshot_demo');

    await reader.query('COMMIT');
    readerOpen = false;

    console.log('Vacuum after reader commit:');
    await writer.query('VACUUM (VERBOSE) public.article_snapshot_demo');
    console.log('Reader fresh:', (await reader.query(selectRow)).rows);
  } finally {
    if (readerOpen) await reader.query('ROLLBACK').catch(console.error);
    await Promise.allSettled([reader.end(), writer.end()]);
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Lancez-le en sélectionnant explicitement la base de données :

PGDATABASE=snapshot_lab node snapshot-demo.cjs

La syntaxe JavaScript du programme a été vérifiée avec Node.js. La séquence exécutée sur la base de données a été examinée au regard de la documentation consultée ; elle n’a pas été lancée sur un serveur PostgreSQL pour cet article.

Comment interpréter les deux passages de vacuum

Les résultats attendus des requêtes sont la révision 0 pour Reader before, la révision 3 pour Writer latest et à nouveau la révision 0 pour Reader still. Une fois le lecteur validé, sa prochaine requête autonome devrait renvoyer la révision 3. Il s’agit des résultats attendus de la séquence synthétique, et non de sorties de test enregistrées.

Le résultat de l’activité devrait identifier le lecteur comme idle in transaction pendant que le rédacteur l’inspecte, avec un backend_xmin conservé. PostgreSQL définit cet état comme celui d’une transaction en attente d’une autre commande, et backend_xmin comme l’horizon actuel du processus serveur. Système de statistiques cumulées.

Dans le premier rapport de vacuum, repérez les versions obsolètes qui restent impossibles à supprimer. Une fois le lecteur validé, comparez-le au second rapport pour vérifier la suppression de ces versions. Dans une base de données autrement calme, les trois remplacements validés fournissent trois versions obsolètes à examiner. D’autres horizons conservés ou des opérations de maintenance concurrentes peuvent modifier le rapport exact ; observez donc l’évolution entre les deux passages au lieu d’exiger un texte de sortie identique.

VACUUM (VERBOSE) produit un rapport d’activité détaillé. Le programme affiche les messages du serveur via l’événement notice documenté par node-postgres. Les deux appels à vacuum sont exécutés en dehors de blocs de transaction explicites, car PostgreSQL n’autorise pas VACUUM à l’intérieur d’un tel bloc. PostgreSQL VACUUM, API Client de node-postgres.

Utilisez ce guide de diagnostic si le résultat diffère de vos attentes :

Observation Vérification suivante
Le lecteur voit la révision 3 avant la validation Vérifiez que Repeatable Read a été démarré et que la première requête sur l’article a été exécutée avant les mises à jour.
Le premier vacuum conserve des versions obsolètes Comparez-le au passage effectué après la validation du lecteur ; leur conservation pendant que l’instantané est ouvert est attendue.
Des versions subsistent après la validation du lecteur Recherchez un autre horizon conservé ; la fin de cette transaction de lecture supprime uniquement sa contribution.
Le nombre dans les statistiques de la table semble incohérent Considérez n_dead_tup comme une estimation et comparez les rapports de vacuum.
Le fichier de la table ne diminue pas Vérifiez si l’espace de stockage est devenu réutilisable ; la réduction du fichier est un résultat distinct.

PostgreSQL indique explicitement que n_dead_tup est une valeur estimée. L’heure d’un vacuum enregistrée établit également que la maintenance a été exécutée, sans établir que toutes les versions obsolètes pouvaient être supprimées. Ces champs aident au diagnostic, mais aucun ne remplace l’examen de l’instantané conservé et du rapport de vacuum. Système de statistiques cumulées.

En règle générale, vacuum rend l’espace réutilisable dans la table plutôt que de le restituer à macOS. Les pages de fin entièrement libres constituent une exception documentée. Une taille de fichier stable après le nettoyage ne prouve donc pas que celui-ci a échoué. Routine Vacuuming.

Que faut-il modifier dans l’application ?

Si plusieurs requêtes sur des articles doivent partager une vue cohérente de la base de données, limitez la transaction à ces requêtes et terminez-la lorsqu’elles sont achevées. Comme conséquence de conception de l’application liée à la conservation des instantanés, effectuez les traitements sans rapport et les interactions sur le bureau après la fin de cette transaction, lorsque la tâche le permet.

Si chaque requête peut utiliser indépendamment la dernière vue validée, Read Committed fournit des instantanés au niveau de chaque commande. Changer de niveau d’isolation ou valider entre les lots modifie la sémantique de cohérence : les requêtes ultérieures peuvent voir de nouvelles révisions d’articles validées. Choisissez cette approche uniquement si ces résultats changeants conviennent à la tâche de lecture. Isolation des transactions.

Augmenter la fréquence de vacuum ne permet pas à une version de franchir un seuil de suppression inchangé. Mettre fin à l’instantané inutile réduit sa contribution à la rétention. De même, VACUUM FULL réécrit la table et exige un verrou exclusif ainsi que de l’espace supplémentaire ; ce n’est pas la réponse habituelle à cet exemple. Implémentation de la visibilité des tuples dans PostgreSQL 18, PostgreSQL VACUUM.

Le script laisse sa table synthétique disponible pour inspection. Il échoue volontairement si cette table existe déjà, au lieu de remplacer son contenu. Après inspection, exécutez la commande suivante dans une session de requête connectée à snapshot_lab si vous souhaitez supprimer la table de l’exemple. Si une exécution a échoué pendant la configuration, inspectez cette même table avant de la supprimer et de relancer le programme.

DROP TABLE public.article_snapshot_demo;

Le diagnostic utile consiste à observer la transition : établir un ancien instantané, valider plusieurs remplacements, exécuter vacuum, mettre fin à l’instantané puis exécuter vacuum à nouveau. Voir la révision d’origine rester lisible tandis que les versions intermédiaires résistent elles aussi au nettoyage rend la distinction concrète. Les règles de visibilité de PostgreSQL déterminent ce que renvoie la requête ; son horizon de nettoyage détermine quand l’espace obsolète peut être réutilisé.

Sources