VACUUM e autovacuum su PostgreSQL — Perché le tue query rallentano e come risolverlo subito

Il database PostgreSQL sta diventando lento. Le query che fino a ieri partivano in millisecondi ora impiegano secondi. Il disco si riempie senza motivo. E tu hai già controllato indici, cache e hardware. Il problema è quasi sempre lo stesso: bloat da transazioni non raccolte. Noi di Meteora Web lo vediamo ogni volta che ci chiamano per un'analisi delle performance. E la soluzione si chiama VACUUM e autovacuum.

Perché il database PostgreSQL si gonfia e rallenta?

PostgreSQL usa il meccanismo del MVCC (Multi-Version Concurrency Control). Ogni volta che modifichi o cancelli una riga, la vecchia versione resta nel file di dati fino a che tutte le transazioni che potrebbero vederla non siano terminate. Questi record obsoleti si chiamano dead tuples. Accumulandosi, producono bloat: il tavolo fisico cresce, le scansioni diventano più lente, gli indici si degradano. Senza manutenzione, anche una tabella con pochi record può occupare gigabyte.

Il costo del bloat non è solo in spazio disco. Ogni query scan deve leggere pagine piene di tuple morte. L'analizzatore statistico si basa su dati sporchi, e il query planner prende decisioni sbagliate. Il risultato è un progressivo degrado delle performance.

Esempio concreto: un cliente e-commerce con catalogo di 10.000 prodotti aveva una tabella ordini di 8 GB, ma dopo un VACUUM FULL lo spazio è sceso a 2,5 GB. Le query di reportistica sono passate da 45 secondi a 1 secondo.

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_percent DESC
LIMIT 10;

Come funziona VACUUM rispetto ad autovacuum?

VACUUM è il processo che recupera lo spazio occupato dalle tuple morte, aggiorna le statistiche (se usi VACUUM ANALYZE) e segnala al sistema che quelle pagine possono essere riutilizzate. Esistono due modalità principali:

• VACUUM standard (o VACUUM ANALYZE): non blocca le scritture, ma non restituisce spazio al sistema operativo. Le pagine libere restano allocate alla tabella.
• VACUUM FULL: riscrive l'intera tabella compattandola e restituendo spazio al SO, ma blocca le scritture durante l'operazione. Da usare con cautela.

Autovacuum è il demone integrato che esegue VACUUM automaticamente in base a soglie configurabili. Di default è attivo, ma le impostazioni predefinite sono pensate per carichi medi. Per applicazioni ad alta concorrenza o con frequenti UPDATE/DELETE, i parametri vanno calibrati.

La differenza pratica è semplice: VACUUM manuale lo lanci tu quando vedi il problema, autovacuum lo fa da solo… se lo configuri bene. Noi abbiamo visto decine di server dove autovacuum è in esecuzione ma non tiene il passo perché la soglia di dead tuple è troppo alta o il costo del vacuum è troppo basso.

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

Quali parametri configurare per autovacuum su PostgreSQL?

I parametri più critici sono nel file postgresql.conf o modificabili con ALTER SYSTEM. Ecco quelli su cui intervenire per un database con carico medio-alto:

• autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold — Determinano quando scatta il vacuum. Per tabelle piccole, la soglia fissa è importante; per tabelle grandi, il fattore di scala evita scatti troppo frequenti. Di default threshold=50 e scale_factor=0.2 (20% di tuple modificate). Su tabelle da milioni di righe, il 20% significa centinaia di migliaia di dead tuple prima di intervenire.
• autovacuum_vacuum_cost_limit e autovacuum_vacuum_cost_delay — Controllano quanto workload può generare il vacuum per non impattare le altre query. Valori bassi rendono il vacuum lento e poco incisivo. Su server con risorse dedicate, alza il cost_limit (es. a 1000) e abbassa il delay (es. a 10ms).
• autovacuum_max_workers — Numero massimo di processi vacuum concorrenti. Default 3; se hai molte tabelle attive, aumenta a 5-6.

Consiglio pratico: per una tabella critica puoi impostare parametri specifici per tabella con ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 1000);.

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 1000;
SELECT pg_reload_conf();

Come monitorare lo stato di VACUUM e i dead tuple?

Non puoi gestire ciò che non misuri. PostgreSQL mette a disposizione diverse viste per monitorare l'efficacia della manutenzione:

• pg_stat_user_tables — contiene n_live_tup, n_dead_tup, last_autovacuum, last_vacuum.
• pg_stat_progress_vacuum — mostra i vacuum in corso con dettagli (tabella, fase, percentuale).
• pg_stat_all_tables — versione completa con anche le tabelle di sistema.

Un ottimo indicatore di allerta è n_mod_since_analyze che dice quante modifiche sono state fatte dall'ultimo ANALYZE. Se supera la soglia, le statistiche sono obsolete e il planner può sbagliare.

Noi usiamo un piccolo script per ricevere avvisi via email quando il rapporto dead/live supera il 10% su una tabella significativa. Lo trovi nella documentazione ufficiale di PostgreSQL VACUUM.

SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST
LIMIT 10;

Come forzare un VACUUM manuale quando autovacuum non basta?

Ci sono situazioni in cui autovacuum non riesce a tenere il passo: carichi batch notturni che generano milioni di dead tuple in poco tempo, tabelle con lock pesanti, o configurazioni errate. In quei casi serve un intervento manuale.

La sequenza operativa è:

1. VACUUM ANALYZE sulla tabella specifica: recupera spazio, aggiorna statistiche, non blocca.
2. VACUUM FULL se il bloat è estremo e puoi permetterti un downtime breve (blocco scrittura).
3. REINDEX dopo un VACUUM FULL, perché gli indici possono restare sparsi.

Attenzione: VACUUM FULL riscrive l'intera tabella – serve spazio su disco pari alla dimensione corrente. Su tabelle multi-GB, pianifica una finestra di manutenzione.

Un esempio di esecuzione controllata:

-- Analizza prima lo stato
SELECT * FROM pg_stat_user_tables WHERE relname = 'ordini';

-- VACUUM standard + statistiche
VACUUM (VERBOSE, ANALYZE) ordini;

-- Se serve il FULL (programma in bassa affluenza)
VACUUM (FULL, VERBOSE) ordini;
REINDEX TABLE ordini;

Noi consigliamo di schedulare VACUUM FULL solo su tabelle che lo richiedono esplicitamente, magari tramite un job cron notturno. Per tutto il resto, autovacuum ben configurato basta.

In sintesi

• Controlla subito i dead tuple – se superano il 5-10%, agisci.
• Regola i parametri di autovacuum – scale_factor, threshold, cost_limit, max_workers.
• Monitora con regolarità – last_autovacuum, last_analyze, n_mod_since_analyze.
• Usa VACUUM manuale solo per casi estremi – preferisci VACUUM ANALYZE bloccante a VACUUM FULL.
• Non trascurare le statistiche – ANALYZE separato se VACUUM non lo include.

Se vuoi approfondire tutto il mondo PostgreSQL, consulta la nostra guida pillar su PostgreSQL Avanzato.

Per riferimenti tecnici: Documentazione ufficiale PostgreSQL – Manutenzione del database.