VACUUM rimuove le versioni morte delle righe, le vecchie copie che UPDATE e DELETE lasciano in una tabella, e segna il loro spazio come libero per nuove righe. VACUUM ANALYZE aggiorna anche le statistiche usate dal planner. VACUUM FULL riscrive l'intera tabella per restituire spazio al sistema operativo. Autovacuum, attivo per impostazione predefinita, esegue VACUUM e ANALYZE per te in background.
Dopo il DELETE, la tabella contiene 700 righe vive e 300 morte; dopo VACUUM, 0 righe morte. Le viste delle statistiche si aggiornano con un certo ritardo; pg_stat_force_next_flush() (PostgreSQL 15+) le fa aggiornare subito per questa demo.
Perché PostgreSQL lascia righe morte
PostgreSQL tiene le vecchie versioni delle righe così che ogni transazione veda un'istantanea coerente (MVCC). Un UPDATE scrive una nuova versione della riga e segna la vecchia come scaduta; un DELETE si limita a segnarla. La vecchia versione resta finché nessuna transazione in corso può averne bisogno, e a quel punto VACUUM può rimuoverla. ctid, la posizione fisica di una riga, mostra lo spostamento:
La riga 1 parte da (0,1), pagina 0 slot 1, e dopo l'update si trova in (0,3). Lo slot 1 contiene ancora la vecchia versione finché non viene ripulito. Una tabella aggiornata spesso continua a produrre righe morte, quindi autovacuum ci torna di continuo.
VACUUM vs VACUUM FULL
Il semplice VACUUM lascia il file della stessa dimensione: lo spazio libero resta nella tabella per gli insert futuri. VACUUM FULL scrive una copia nuova e compatta ed elimina il vecchio file.
VACUUM | VACUUM FULL | |
|---|---|---|
| Lock | consente letture e scritture | blocca ogni accesso |
| Spazio | riusato dalla stessa tabella | restituito al sistema operativo |
| Disco extra necessario | no | spazio per una copia completa della tabella |
| Quando | di routine (lo fa autovacuum) | di rado, dopo aver eliminato la maggior parte di una tabella grande |
Le opzioni vanno tra parentesi:
VACUUM (VERBOSE, ANALYZE) items;stampa cosa ha fatto e aggiorna le statistiche.VACUUM (PARALLEL 4)(PostgreSQL 13+) passa gli indici con worker paralleli.VACUUM (SKIP_LOCKED)salta le tabelle che non riesce a bloccare subito.
Come autovacuum decide quando partire
Autovacuum controlla ogni tabella a ogni autovacuum_naptime. Esegue il vacuum della tabella quando le sue righe morte superano autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table rows: con i valori predefiniti, 50 righe più il 20 per cento della tabella. Esegue l'analyze quando le righe cambiate dall'ultimo analyze superano 50 più il 10 per cento. Da PostgreSQL 13, anche i soli insert fanno partire un vacuum. I valori predefiniti in postgresql.conf:
autovacuum = on
autovacuum_naptime = 1min
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
Su una tabella con 100 milioni di righe, il 20 per cento vuol dire 20 milioni di righe morte prima che parta un vacuum. Abbassa lo scale factor solo per le tabelle grandi e molto usate:
ALTER TABLE events RESET (autovacuum_vacuum_scale_factor); riporta la tabella all'impostazione del server. Il capitolo sul vacuum di routine copre le altre impostazioni.
Controllare cosa ha fatto autovacuum
pg_stat_user_tables mostra le righe morte e l'ultimo vacuum manuale e automatico di ogni tabella:
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Se le righe morte continuano a crescere anche se autovacuum gira, cerca vecchie transazioni aperte, che gli impediscono di rimuovere qualsiasi cosa più recente della loro istantanea:
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;
Freeze e wraparound degli ID di transazione
Gli ID di transazione sono contatori a 32 bit che ricominciano da capo. Per mantenere visibili le righe vecchie, il vacuum le congela (freeze), segnandole come più vecchie di ogni transazione. Quando l'ID non congelato più vecchio di una tabella supera autovacuum_freeze_max_age (200 milioni di transazioni per impostazione predefinita), autovacuum ci gira sopra anche se è disattivato.
Se il freeze resta comunque molto indietro, PostgreSQL smette di assegnare nuovi ID di transazione, e le scritture falliscono finché un vacuum non recupera. Questa query mostra quanto è vicino ogni database:
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
Domande frequenti
VACUUM blocca la tabella?
Il semplice VACUUM prende un lock che consente ancora SELECT, INSERT, UPDATE e DELETE; va in conflitto solo con modifiche allo schema, creazione di indici, ANALYZE e altri vacuum. VACUUM FULL prende un lock ACCESS EXCLUSIVE, che blocca ogni query sulla tabella finché non ha finito.
Devo disattivare autovacuum?
No. Senza di lui le righe morte si accumulano, le statistiche invecchiano e alla fine il database deve smettere di accettare scritture per evitare il wraparound degli ID di transazione. Regola invece le sue soglie tabella per tabella.
Perché la mia tabella non diventa più piccola dopo VACUUM?
Il semplice VACUUM segna lo spazio delle righe morte come libero per nuove righe nella stessa tabella; restituisce spazio al sistema operativo solo quando le pagine vuote sono in fondo al file. VACUUM FULL riscrive la tabella e restituisce lo spazio.
Perché VACUUM non rimuove le righe morte?
Una riga può essere rimossa solo quando nessuna transazione in corso potrebbe ancora averne bisogno. Una transazione aperta da tanto tempo (spesso una sessione inattiva che ha eseguito BEGIN), un vecchio replication slot o una transazione preparata abbandonata le trattengono.