VACUUM e ANALYZE in SQLite: statistiche e recupero di spazio

Due lavori di manutenzione diversi

ANALYZE e VACUUM vengono citati sempre insieme, ma risolvono problemi diversi.

• ANALYZE raccoglie statistiche sui tuoi dati perché il query planner faccia scelte più intelligenti. Scrive in una tabella chiamata sqlite_stat1 e non tocca le tue righe.
• VACUUM ricostruisce il file stesso per recuperare le pagine inutilizzate e deframmentare lo spazio. Non cambia direttamente i piani di esecuzione.

Se le query scelgono l'indice sbagliato, ti serve ANALYZE. Se il file è più grande del dovuto dopo tante cancellazioni, ti serve VACUUM. Confonderli fa sprecare parecchio tempo in manutenzione.

Cosa fa davvero ANALYZE

Il query planner deve tirare a indovinare. Quando vede WHERE status = 'active', deve stimare quante righe corrispondono (una? un milione?) per decidere se usare un indice o scansionare la tabella. Senza statistiche, ripiega su euristiche grossolane.

ANALYZE percorre ogni indice e registra informazioni di sintesi su come sono distribuiti i valori:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

La riga di sqlite_stat1 dice al planner quante righe ha all'incirca l'indice e quanti duplicati ha una chiave tipica. La prossima volta che interroghi WHERE status = 'pending', sa che pending è raro e usa l'indice; per WHERE status = 'shipped', potrebbe decidere che una scansione costa meno.

Puoi analizzare una singola tabella o un singolo indice invece dell'intero database:

ANALYZE orders;
ANALYZE idx_orders_status;

Esegui ANALYZE dopo caricamenti massivi, dopo grandi modifiche allo schema, o quando noti che il planner sceglie piani scadenti su tabelle la cui distribuzione è cambiata.

PRAGMA optimize: la scelta predefinita moderna

Eseguire ANALYZE alla cieca a ogni chiusura di connessione è uno spreco: la maggior parte delle volte non è cambiato abbastanza da fare differenza. SQLite offre un'alternativa più furba:

PRAGMA optimize;

PRAGMA optimize controlla quanto è cambiato il database dall'ultima analisi ed esegue ANALYZE solo sulle tabelle che ne hanno bisogno. La raccomandazione ufficiale è chiamarlo su ogni connessione di lunga durata subito prima di chiuderla, e periodicamente sulle connessioni che restano aperte per ore.

Costa poco quando non è cambiato nulla ed è efficace quando qualcosa è cambiato. Usa prima optimize; ricorri ad ANALYZE puro solo quando devi forzare un aggiornamento.

Cosa fa davvero VACUUM

Quando elimini righe o fai il drop di una tabella, SQLite segna quelle pagine come libere ma non riduce il file. Le pagine libere vengono riutilizzate dagli insert futuri, quindi di solito va bene così. Ma nel corso di una lunga vita di modifiche si accumulano due cose:

1. Spazio libero che il sistema operativo non vede. Il tuo file .db resta a 2 GB anche se i dati vivi sono solo 800 MB.
2. Frammentazione. Le righe della stessa tabella finiscono sparse su pagine non adiacenti, peggiorando le prestazioni delle scansioni.

VACUUM risolve entrambe copiando l'intero database in un file nuovo, compattato, e sostituendo l'originale:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Dopo VACUUM, il file ha la dimensione che avrebbe se avessi inserito da zero solo le 100 righe rimaste. Come effetto collaterale, tutti i rowid restano uguali ma la disposizione su disco torna contigua.

Alcune cose da sapere prima di eseguirlo:

• Richiede un lock esclusivo sul database per tutta la durata. Nessun'altra connessione può scrivere.
• Richiede circa il doppio della dimensione del database in spazio libero su disco: costruisce il nuovo file accanto al vecchio.
• Non può essere eseguito dentro una transazione, e dà errore se ci sono transazioni attive aperte.
• Su un database di diversi GB può richiedere molto tempo. Pianificalo.

Quando eseguire davvero VACUUM

Per la maggior parte delle applicazioni: non farlo, a meno che non sia cambiato qualcosa di specifico.

Buoni motivi per eseguire VACUUM:

• Hai appena fatto il drop di una tabella grande o eliminato un enorme blocco di righe e vuoi recuperare spazio su disco.
• Il database subisce modifiche da anni e le query che scansionano le tabelle sembrano più lente di prima.
• Distribuisci un file di database come parte di una release e lo vuoi il più piccolo possibile.

Cattivi motivi:

• "Per sicurezza." Riscrive l'intero file ogni volta. Non c'è niente di sicuro nel farlo su un sistema in produzione.
• Dopo ogni blocco di cancellazioni. Le pagine liberate sarebbero state riutilizzate comunque.

auto_vacuum e VACUUM incrementale

Se vuoi che SQLite gestisca in automatico le pagine libere, imposta auto_vacuum alla creazione del database: non si può cambiare dopo senza un vacuum completo:

PRAGMA auto_vacuum = INCREMENTAL;

Tre modalità:

• NONE (predefinita): le pagine libere restano nel file e vengono riutilizzate dagli insert successivi.
• FULL: ogni commit che libera pagine tronca anche il file. Comodo, ma ogni transazione ne paga il costo.
• INCREMENTAL: SQLite tiene traccia delle pagine libere ma le rilascia solo quando glielo chiedi:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) restituisce al sistema operativo fino a N pagine libere: è veloce, non tiene un lock esclusivo a lungo e puoi eseguirlo a intervalli regolari. È il compromesso ideale per database con molte scritture che devono restare compatti senza il costo di un VACUUM completo.

VACUUM INTO: esportare una copia compatta

VACUUM INTO scrive una copia nuova e compatta in un altro file senza toccare l'originale:

VACUUM INTO 'backup.db';

È davvero utile:

• Backup. L'output è uno snapshot coerente e completamente compattato: niente pagine scritte a metà, nessun .wal di cui preoccuparsi. Meglio che copiare il file con cp.
• Ridurre il file senza bloccare a lungo chi scrive. Fai il vacuum su un file a parte, poi lo scambi in modo atomico. Chi scrive non resta bloccato per tutta la durata del vacuum.
• Distribuzione. Distribuisci una copia piccola e deframmentata di un database di sviluppo.

Il file di destinazione non deve esistere. Se esiste, ottieni un errore.

Una ricetta pratica di manutenzione

Per un tipico database applicativo:

-- Su ogni connessione di lunga durata, prima di chiuderla:
PRAGMA optimize;

-- Dopo un grande caricamento massivo o una modifica allo schema:
ANALYZE;

-- Dopo aver eliminato molti dati, quando vuoi recuperare spazio su disco:
VACUUM;

-- Per i backup:
VACUUM INTO '/backups/app-2026-04-23.db';

Se il database è dominato da scritture e cancellazioni e resta online 24 ore su 24, imposta auto_vacuum = INCREMENTAL alla creazione ed esegui PRAGMA incremental_vacuum(N) periodicamente, magari una volta al giorno nelle ore di traffico basso.

Diagnosi: "perché il mio file è così grande?"

Due pragma ti dicono cosa sta succedendo:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = dimensione attuale del file.
• freelist_count × page_size = byte sprecati in pagine inutilizzate.

Se freelist_count è una frazione consistente di page_count, un VACUUM (o incremental_vacuum) ridurrà visibilmente il file. Se è piccolo, il file è già compattato in modo efficiente e VACUUM non servirà.

Errori comuni

• Eseguire VACUUM dentro una transazione. Non si può. Fai prima il commit.
• Dimenticare che VACUUM richiede spazio libero su disco. Un database da 10 GB ha bisogno di altri 10 GB circa liberi per il vacuum.
• Impostare auto_vacuum quando i dati esistono già. Non ha effetto fino al prossimo VACUUM completo. Impostalo alla creazione del database se ti serve.
• Eseguire ANALYZE aspettandosi file più piccoli. Quello è il lavoro di VACUUM.
• Eseguire VACUUM aspettandosi piani di esecuzione migliori. Quello è il lavoro di ANALYZE.

I due comandi si completano a vicenda; nessuno dei due sostituisce l'altro.

Prossimo passo: le transazioni

Comandi di manutenzione come VACUUM mettono in luce qualcosa che abbiamo dato per scontato: il modello transazionale di SQLite e cosa blocca, e quando. Il prossimo capitolo parte da lì: come funzionano le transazioni, cosa garantiscono davvero BEGIN / COMMIT / ROLLBACK e come usarle per rendere atomico un lavoro fatto di più istruzioni.