VACUUM, autovacuum e bloat delle tabelle

Aggiornato il 5 luglio 2026

PostgreSQL non aggiorna mai una riga sul posto. Ogni UPDATE scrive una nuova versione della riga e marca come morta quella vecchia; ogni DELETE si limita a marcare. Lo spazio viene recuperato più tardi, in modo asincrono, da VACUUM. Quando quel ciclo funziona, non ci pensi mai. Quando resta indietro, tabelle e indici crescono silenziosamente — è il bloat — e ogni query ne paga il prezzo.

Perché esistono le righe morte: MVCC

Sotto MVCC (multi-version concurrency control), i lettori non bloccano mai gli scrittori e viceversa, perché ogni transazione vede uno snapshot consistente: le vecchie versioni delle righe vengono conservate finché qualche transazione potrebbe ancora averne bisogno. Nel momento in cui nessuna transazione attiva può vedere una versione morta, essa diventa spazzatura — ma PostgreSQL non la recupera inline. È il lavoro di VACUUM.

Due conseguenze immediate:

• Un carico di lavoro con molti UPDATE scrive all'incirca quanto uno con molti INSERT, più il turnover degli indici.
• Una singola transazione a lunga durata trattiene la pulizia per l'intero database — VACUUM non può rimuovere alcuna versione di riga che lo snapshot di quella transazione potrebbe ancora vedere, indipendentemente da quali tabelle abbia toccato. Una sessione dimenticata idle in transaction di venerdì significa un database gonfio lunedì.

Cosa fa davvero VACUUM (e cosa non fa)

• Il VACUUM semplice cerca le tuple morte, rende il loro spazio riutilizzabile per scritture future nella stessa tabella, aggiorna la free space map e la visibility map, e congela le tuple vecchie per prevenire il wraparound dei transaction ID. Gira insieme al traffico normale.
• Non riduce il file su disco (tranne il caso speciale di pagine completamente vuote proprio in fondo alla tabella). Una tabella che una volta si è gonfiata a 50 GB resta 50 GB su disco anche se dentro è vuota al 90%.
• VACUUM FULL riscrive la tabella in un nuovo file compatto e restituisce lo spazio al SO — ma prende un lock ACCESS EXCLUSIVE per tutta la durata della riscrittura. Su una tabella grande e trafficata è un'interruzione di servizio; guarda pg_repack per le ricostruzioni online.
• ANALYZE (spesso eseguito insieme) aggiorna le statistiche del planner — un lavoro diverso che pure conta, vedi leggere EXPLAIN ANALYZE.

Come autovacuum decide di eseguire

Il launcher di autovacuum controlla periodicamente ogni tabella rispetto a una soglia:

vacuum when:  n_dead_tup  >  autovacuum_vacuum_threshold
                             + autovacuum_vacuum_scale_factor × reltuples
-- defaults:  50 + 0.20 × reltuples  (20% of the table)

Lo scale factor di default del 20% va bene per le tabelle piccole ed è terribile per quelle grandi: una tabella da 100 milioni di righe accumula 20 milioni di righe morte prima ancora che autovacuum parta. Per tabelle grandi e calde, imposta un override per tabella:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000
);

Autovacuum è anche deliberatamente frenato da ritardi basati sul costo (autovacuum_vacuum_cost_delay / autovacuum_vacuum_cost_limit) in modo da non saturare l'I/O. Sull'hardware moderno i default sono conservativi; se autovacuum gira di continuo ma non recupera mai, alzare autovacuum_vacuum_cost_limit è di solito la prima leva.

Rilevare il bloat prima che faccia danni

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

• n_dead_tup grande e in crescita, con last_autovacuum vecchio o NULL → autovacuum non tiene il passo (oppure una transazione lunga sta bloccando l'orizzonte: controlla in pg_stat_activity gli xact_start vecchi).
• Sintomo lato query: conteggi dei buffer sproporzionati rispetto alle righe restituite in EXPLAIN (ANALYZE, BUFFERS) — leggere 40.000 pagine per 500 righe significa scansionare per lo più spazio morto.
• Controllo lato dimensione: confronta pg_total_relation_size() nel tempo, oppure usa l'estensione pgstattuple per una percentuale esatta di spazio morto su una tabella sospetta.

Aggiornamenti HOT: il pasto gratis per cui vale la pena progettare

Se un UPDATE modifica solo colonne che non sono indicizzate, e la nuova versione entra nella stessa pagina, PostgreSQL esegue un aggiornamento HOT (heap-only tuple): non viene scritta alcuna voce di indice, e la versione morta può essere ripulita a basso costo all'interno della pagina. Ecco perché aggiungere "solo un altro indice" su una colonna aggiornata di frequente può rovinare le performance di scrittura — trasforma ogni aggiornamento HOT in uno completo. Un fillfactor più basso (es. 80–90 per le tabelle con molti update) lascia spazio su ogni pagina e alza il tasso di HOT.

Regole pratiche

• Non disabilitare mai autovacuum. Se fa male, è mal configurato, non superfluo — e ti protegge anche dal wraparound dei transaction ID, che è un evento capace di fermare il database.
• Termina o metti un timeout alle transazioni idle: idle_in_transaction_session_timeout è un'assicurazione a basso costo.
• Regola per tabella, non globalmente: una manciata di tabelle calde di solito causa la maggior parte dei problemi.
• Il bloat che hai già non se ne andrà da solo: il VACUUM semplice ferma la crescita; recuperare disco richiede VACUUM FULL o una ricostruzione online.