Uno snapshot PostgreSQL di lunga durata può conservare versioni intermedie delle righe che non legge mai. Vacuum usa un orizzonte transazionale prudenziale per decidere cosa può essere rimosso. Questa guida spiega perché, poi riproduce il comportamento con righe di articoli sintetiche e node-postgres su Mac.

L’obiettivo è distinguere uno snapshot che trattiene la pulizia da un vacuum che non è stato eseguito. L’esempio è pensato per PostgreSQL 18 e usa due connessioni al database: un lettore che mantiene uno snapshot Repeatable Read e uno scrittore che aggiorna un singolo articolo. La documentazione di PostgreSQL indica attualmente la versione 18 come la versione principale supportata più recente; i dettagli di implementazione qui sotto si riferiscono esplicitamente al suo ramo 18. Documentazione di PostgreSQL 18.

Perché conservare versioni che il vecchio lettore non può vedere?

Un aggiornamento crea una nuova versione della riga. PostgreSQL conserva la versione precedente finché un’altra transazione potrebbe averne bisogno. In seguito, vacuum può recuperare le versioni obsolete e rendere riutilizzabile lo spazio che occupano. Questo è il ciclo di vita di base dell’MVCC descritto in Pulizia ordinaria.

La sottigliezza sta nel fatto che la visibilità dello snapshot e la rimovibilità fisica rispondono a domande diverse. La visibilità stabilisce se una determinata query debba restituire una versione. La pulizia usa un limite prudenziale invece di determinare, per ogni versione, quali snapshot esistenti potrebbero leggerla.

In PostgreSQL 18, HeapTupleSatisfiesVacuum confronta la transazione che ha reso obsoleta una versione con OldestXmin. Una cancellazione o sostituzione confermata non rende subito rimovibile la versione precedente: le versioni la cui transazione di cancellazione è pari o successiva a quel limite restano classificate come recentemente morte. Questa regola è documentata direttamente nell’implementazione ufficiale della visibilità delle tuple.

Considera questa sequenza illustrativa. Ogni aggiornamento viene confermato separatamente e non sono coinvolti altri lettori:

Passaggio Azione Cosa vede il vecchio lettore Ultima versione confermata
1 Inserimento della revisione 0 dell’articolo — Revisione 0
2 Il lettore stabilisce uno snapshot Repeatable Read Revisione 0 Revisione 0
3 Lo scrittore conferma la revisione 1 Revisione 0 Revisione 1
4 Lo scrittore conferma la revisione 2 Revisione 0 Revisione 2
5 Lo scrittore conferma la revisione 3 Revisione 0 Revisione 3

La revisione 0 è necessaria direttamente al lettore. Le revisioni 1 e 2 sono versioni intermedie: questo lettore non può vederle perché sono state create dopo il suo snapshot. Tuttavia, anche le loro transazioni di sostituzione sono più recenti del limite di pulizia mantenuto. Applicando la regola documentata del limite si spiega perché queste versioni possono restare insieme alla revisione 0. La loro conservazione non significa che il lettore possa attraversarle come risultati storici delle query. Implementazione della visibilità delle tuple in PostgreSQL 18.

L’orizzonte è più ampio della riga selezionata dal lettore. PostgreSQL 18 calcola orizzonti appropriati per ciascuna relazione a partire dagli ID di transazione dei backend e dagli orizzonti degli snapshot; per i dati delle tabelle ordinarie, in genere tiene conto delle sessioni nello stesso database. Non tiene traccia solo delle righe restituite da una particolare SELECT. Questo spiega perché una query di piccole dimensioni può influire sulla pulizia in altre parti dello stesso database. Le relazioni condivise e altri tipi di relazione seguono regole distinte, quindi dire che «uno snapshot blocca ogni vacuum ovunque» è un’affermazione troppo ampia. Implementazione degli orizzonti in PostgreSQL 18.

Quale transazione mantiene davvero uno snapshot di lunga durata?

Repeatable Read stabilisce il proprio snapshot alla prima istruzione che non sia di controllo della transazione, poi usa quello snapshot per le query successive. Emettere soltanto BEGIN non basta per questa dimostrazione: è la query iniziale sull’articolo a stabilire la vista da mantenere. Read Committed, il livello di isolamento predefinito di PostgreSQL, acquisisce invece un nuovo snapshot per ogni comando. Isolamento delle transazioni.

Di conseguenza, una transazione Repeatable Read inattiva che ha interrogato la tabella può mantenere il proprio snapshot anche dopo la fine della query. Anche un’istruzione Read Committed di lunga durata può trattenere uno snapshot mentre è in esecuzione, ma le istruzioni completate separatamente a quel livello non condividono la stessa vista fissa. La sola durata della transazione, quindi, non stabilisce quale snapshot venga mantenuto. Isolamento delle transazioni.

Una transazione di sola lettura può contribuire all’orizzonte senza avere un proprio ID di transazione di primo livello. L’implementazione considera sia gli ID di transazione dei backend sia gli orizzonti degli snapshot. In questo esempio, un backend_xid nullo insieme a un backend_xmin non nullo è quindi compatibile con un lettore che mantiene uno snapshot. Implementazione degli orizzonti in PostgreSQL 18.

Riprodurre il comportamento da Terminale su Mac

Servono un server PostgreSQL 18 locale in esecuzione, i relativi strumenti da riga di comando e Node.js con npm. La documentazione sulla compatibilità di node-postgres elenca Node.js 20 o 24. Questa guida presuppone che le impostazioni di connessione locale esistenti funzionino. Crea un database separato e usa solo testo sintetico per gli articoli. Introduzione e installazione di node-postgres.

In Terminal, crea una directory di lavoro, installa il driver e crea il database di esempio:

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

Salva il programma completo qui sotto come snapshot-demo.cjs. Le sue connessioni ereditano le impostazioni dell’ambiente PostgreSQL, tra cui PGDATABASE, PGHOST e PGPORT, come descritto in Connessione con node-postgres. Usa le stesse impostazioni di connessione locale per createdb e per il programma.

Il lettore mantiene tutte le istruzioni della transazione su un unico client dedicato. node-postgres lo richiede: una transazione deve usare lo stesso client dall’inizio alla fine, quindi inviarne le istruzioni tramite pool.query() non è adatto. Il secondo client esegue indipendentemente gli aggiornamenti e i comandi vacuum. Transazioni con node-postgres.

L’esempio indicizza anche revision, che cambia a ogni aggiornamento. In questo modo si impedisce deliberatamente che questi aggiornamenti usino HOT, rendendo la dimostrazione più facile da interpretare. PostgreSQL documenta che HOT richiede che gli aggiornamenti lascino inalterate le colonne referenziate dagli indici ordinari, oltre a richiedere spazio sufficiente sulla pagina originale. Tuple solo heap.

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;
});

Eseguilo selezionando esplicitamente il database:

PGDATABASE=snapshot_lab node snapshot-demo.cjs

La sintassi JavaScript del programma è stata verificata con Node.js. La sequenza relativa al database è stata esaminata rispetto alla documentazione recuperata; per questo articolo non è stata eseguita su un server PostgreSQL.

Come interpretare i due passaggi di vacuum

I risultati attesi delle query sono la revisione 0 per Reader before, la revisione 3 per Writer latest e di nuovo la revisione 0 per Reader still. Dopo il commit del lettore, la sua successiva query autonoma dovrebbe restituire la revisione 3. Sono risultati attesi della sequenza sintetica, non output acquisiti da un test.

Il risultato dell’attività dovrebbe identificare il lettore come idle in transaction mentre lo scrittore lo esamina, con un backend_xmin mantenuto. PostgreSQL definisce questo stato come una transazione in attesa di un altro comando e backend_xmin come l’orizzonte corrente del backend. Sistema delle statistiche cumulative.

Esamina il primo rapporto di vacuum per individuare le versioni obsolete che restano non rimovibili. Dopo il commit del lettore, confronta il secondo rapporto per verificare la rimozione di quelle versioni. In un database altrimenti inattivo, le tre sostituzioni confermate forniscono tre versioni obsolete da esaminare. Altri orizzonti mantenuti o attività di manutenzione concorrenti possono influire sul rapporto esatto, quindi usa il confronto prima e dopo invece di pretendere un testo di output identico.

VACUUM (VERBOSE) produce un rapporto dettagliato sull’attività. Il programma stampa le notifiche del server tramite l’evento notice di node-postgres documentato. Entrambe le chiamate vacuum vengono eseguite fuori da blocchi di transazione espliciti, perché PostgreSQL non consente VACUUM al loro interno. VACUUM di PostgreSQL, API Client di node-postgres.

Usa questa guida decisionale quando il risultato differisce dalle aspettative:

Osservazione Verifica successiva
Il lettore vede la revisione 3 prima del commit Verifica che Repeatable Read sia stato avviato e che la prima query sull’articolo sia stata eseguita prima degli aggiornamenti.
Il primo vacuum conserva versioni obsolete Confrontalo con il passaggio successivo al commit del lettore; è previsto che vengano mantenute mentre lo snapshot è aperto.
Le versioni restano anche dopo il commit del lettore Cerca un altro orizzonte mantenuto; terminare questa transazione di lettura elimina solo il suo contributo.
Un conteggio nelle statistiche della tabella sembra incoerente Considera n_dead_tup una stima e confronta i rapporti di vacuum.
Il file della tabella non diventa più piccolo Verifica se lo spazio è diventato riutilizzabile; la riduzione delle dimensioni del file è un esito distinto.

PostgreSQL descrive esplicitamente n_dead_tup come un conteggio stimato. Anche un orario di vacuum registrato dimostra che la manutenzione è stata eseguita, ma non che ogni versione obsoleta fosse rimovibile. Questi campi aiutano la diagnosi, ma nessuno dei due sostituisce l’esame dello snapshot mantenuto e del rapporto di vacuum. Sistema delle statistiche cumulative.

Di norma, vacuum rende lo spazio riutilizzabile all’interno della tabella anziché restituirlo a macOS. Le pagine finali completamente libere costituiscono un’eccezione documentata. Una dimensione del file invariata dopo la pulizia, quindi, non dimostra che la pulizia sia fallita. Pulizia ordinaria.

Cosa dovrebbe cambiare nell’applicazione?

Se diverse query sugli articoli devono condividere una vista coerente del database, limita la transazione a quelle query e concludila quando terminano. Come conseguenza progettuale della conservazione degli snapshot, quando l’attività lo consente esegui le elaborazioni non correlate e le interazioni con il desktop dopo la fine della transazione.

Se ogni query può usare autonomamente la vista confermata più recente, Read Committed fornisce snapshot a livello di comando. Cambiare il livello di isolamento o eseguire il commit tra i blocchi modifica le garanzie di coerenza: le query successive possono vedere revisioni degli articoli confermate nel frattempo. Scegli questo approccio solo se risultati soggetti a variazioni sono compatibili con il compito del lettore. Isolamento delle transazioni.

Aumentare la frequenza di vacuum non può far superare a una versione un limite di rimovibilità che non è cambiato. Terminare lo snapshot non necessario risolve il contributo che dà alla conservazione. Allo stesso modo, VACUUM FULL riscrive la tabella e richiede un lock esclusivo, oltre a ulteriore spazio; non è la risposta abituale a questo esempio. Implementazione della visibilità delle tuple in PostgreSQL 18, VACUUM di PostgreSQL.

Lo script lascia disponibile la tabella sintetica per l’ispezione. Se quella tabella esiste già, lo script si interrompe deliberatamente senza sostituirne i contenuti. Dopo averla ispezionata, esegui il comando seguente in una sessione di query connessa a snapshot_lab se vuoi rimuovere la tabella di esempio. Se un’esecuzione si è interrotta durante la configurazione, ispeziona la stessa tabella prima di rimuoverla e riprovare.

DROP TABLE public.article_snapshot_demo;

La diagnosi utile consiste nel seguire la transizione: stabilire uno snapshot precedente, confermare diverse sostituzioni, eseguire vacuum, terminare lo snapshot e rieseguire vacuum. Vedere la revisione originale restare leggibile mentre anche le versioni intermedie resistono alla pulizia rende concreta la distinzione. Le regole di visibilità di PostgreSQL determinano cosa restituisce la query; l’orizzonte di pulizia determina quando lo spazio obsoleto può essere riutilizzato.

Fonti