SQLite ANALYZE et VACUUM : stats et espace disque

Deux opérations de maintenance bien distinctes

On parle souvent de ANALYZE et VACUUM dans la même phrase, pourtant ces deux commandes règlent des problèmes complètement différents.

• ANALYZE collecte des statistiques sur vos données pour que le planificateur de requêtes prenne de meilleures décisions. Cette commande écrit dans une table nommée sqlite_stat1 sans jamais toucher à vos lignes.
• VACUUM reconstruit le fichier lui-même pour récupérer les pages inutilisées et défragmenter le stockage. Elle n'a aucun impact direct sur les plans de requêtes.

Si vos requêtes choisissent le mauvais index, c'est ANALYZE qu'il vous faut. Si votre fichier a pris trop d'embonpoint après des suppressions massives, c'est VACUUM. Confondre les deux, c'est perdre énormément de temps en maintenance inutile.

Ce que fait réellement ANALYZE

Le planificateur de requêtes avance un peu à l'aveugle. Quand il voit WHERE status = 'active', il doit estimer combien de lignes correspondent — une seule ? un million ? — pour décider s'il vaut mieux passer par un index ou parcourir la table entière. Sans statistiques, il se rabat sur des heuristiques assez grossières.

ANALYZE parcourt chaque index et enregistre un résumé de la façon dont les valeurs sont réparties :

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 ligne sqlite_stat1 indique au planificateur le nombre approximatif de lignes que contient l'index, ainsi que le nombre moyen de doublons par clé. Lors de votre prochaine requête WHERE status = 'pending', il saura que pending est rare et utilisera l'index ; pour WHERE status = 'shipped', il jugera peut-être qu'un parcours complet revient moins cher.

Vous pouvez aussi cibler une seule table ou un seul index plutôt que toute la base :

ANALYZE orders;
ANALYZE idx_orders_status;

Lancez ANALYZE après un chargement massif, après une modification importante du schéma, ou dès que vous remarquez que le planificateur choisit de mauvais plans sur des tables dont la distribution a évolué.

PRAGMA optimize : le réflexe moderne

Déclencher ANALYZE aveuglément à chaque fermeture de connexion est un gaspillage — la plupart du temps, rien n'a suffisamment bougé pour que ça vaille le coup. SQLite fournit un wrapper plus malin :

PRAGMA optimize;

PRAGMA optimize regarde ce qui a changé dans la base depuis la dernière analyse, puis lance ANALYZE uniquement sur les tables qui en ont besoin. La recommandation officielle : l'appeler sur chaque connexion à longue durée de vie juste avant de la fermer, et de temps en temps sur les connexions qui restent ouvertes pendant des heures.

C'est quasi gratuit quand rien n'a bougé, et utile dès qu'il y a eu des changements. Privilégiez optimize en premier ; ne sortez ANALYZE brut que si vous avez vraiment besoin de forcer un rafraîchissement.

À quoi sert vraiment SQLite VACUUM

Quand vous supprimez des lignes ou que vous droppez une table, SQLite marque les pages concernées comme libres, mais ne réduit pas la taille du fichier pour autant. Ces pages libres seront réutilisées par les futurs INSERT, donc dans la plupart des cas ça suffit largement. Sauf qu'au fil du temps, deux choses s'accumulent :

1. De l'espace libre invisible pour l'OS. Votre fichier .db reste à 2 Go alors que les données réelles n'en occupent que 800 Mo.
2. De la fragmentation. Les lignes d'une même table se retrouvent éparpillées sur des pages non contiguës, ce qui plombe les performances en lecture séquentielle.

VACUUM règle les deux problèmes : il recopie toute la base dans un fichier neuf, bien tassé, puis remplace l'original :

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 'entrée ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Après un VACUUM, le fichier retrouve la taille qu'il aurait eue si vous n'aviez inséré que les 100 lignes survivantes depuis zéro. Effet de bord intéressant : tous les rowids restent identiques, mais l'agencement sur disque redevient contigu.

Quelques points à connaître avant de le lancer :

• Il pose un verrou exclusif sur la base pendant toute son exécution. Aucune autre connexion ne peut écrire.
• Il faut compter environ deux fois la taille de la base en espace disque libre — il construit le nouveau fichier à côté de l'ancien.
• Il ne peut pas s'exécuter à l'intérieur d'une transaction, et il échoue si une transaction est active.
• Sur une base de plusieurs Go, ça peut prendre du temps. Anticipez.

Quand lancer VACUUM SQLite

Pour la majorité des applications : ne le faites pas, sauf si quelque chose de précis a changé.

Bonnes raisons de lancer un VACUUM :

• Vous venez de supprimer une grosse table ou un énorme lot de lignes, et vous voulez récupérer l'espace disque.
• La base tourne depuis des années et les requêtes qui parcourent les tables semblent plus lentes qu'avant.
• Vous distribuez un fichier de base de données dans une release et vous le voulez le plus compact possible.

Mauvaises raisons :

• « Au cas où. » VACUUM réécrit tout le fichier à chaque fois. Faire ça sur un système en production, ça n'a rien de prudent.
• Après chaque batch de suppressions. Les pages libérées auraient de toute façon été réutilisées.

auto_vacuum et VACUUM incrémental

Si vous voulez que SQLite gère les pages libres automatiquement, il faut définir auto_vacuum à la création de la base — impossible de le changer après coup sans passer par un vacuum complet :

PRAGMA auto_vacuum = INCREMENTAL;

Trois modes existent :

• NONE (par défaut) : les pages libérées restent dans le fichier et seront réutilisées lors des prochains INSERT.
• FULL : chaque COMMIT qui libère des pages tronque automatiquement le fichier. Pratique, mais chaque transaction en paie le prix.
• INCREMENTAL : SQLite garde la trace des pages libres, mais ne les restitue que lorsque vous le demandez explicitement :

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) libère jusqu'à N pages libres en les rendant à l'OS — c'est rapide, le verrou exclusif est très bref, et vous pouvez le planifier tranquillement. C'est le compromis idéal pour les bases avec beaucoup d'écritures qui doivent rester compactes sans subir le coût d'un VACUUM complet.

VACUUM INTO : exporter une copie compacte

VACUUM INTO écrit une copie neuve et compacte dans un nouveau fichier, sans toucher à l'original :

VACUUM INTO 'backup.db';

C'est vraiment pratique :

• Sauvegardes. Le fichier produit est un instantané cohérent et entièrement compacté — aucune page à moitié écrite, pas de .wal à gérer. Bien mieux qu'une simple copie avec cp.
• Réduire la taille sans bloquer les écritures trop longtemps. Vous lancez le VACUUM vers un fichier annexe, puis vous faites un swap atomique. Les processus en écriture ne sont pas bloqués pendant toute la durée du VACUUM.
• Distribution. Vous pouvez livrer une copie miniature et défragmentée d'une base de développement.

Attention : le fichier de destination ne doit pas déjà exister, sinon vous obtenez une erreur.

Une recette de maintenance qui marche en pratique

Pour la base d'une application classique :

-- Sur chaque connexion de longue durée, avant la fermeture :
PRAGMA optimize;

-- Après un chargement en masse important ou une modification de schéma :
ANALYZE;

-- Après avoir supprimé beaucoup de données et vouloir récupérer de l'espace disque :
VACUUM;

-- Pour les sauvegardes :
VACUUM INTO '/backups/app-2026-04-23.db';

Si la base de données encaisse beaucoup d'écritures et de suppressions tout en restant en ligne 24/7, activez auto_vacuum = INCREMENTAL dès la création, puis lancez PRAGMA incremental_vacuum(N) à intervalles réguliers — typiquement une fois par jour pendant les heures creuses.

Diagnostiquer « pourquoi mon fichier SQLite est-il si gros ? »

Deux pragmas suffisent pour y voir clair :

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 = taille actuelle du fichier.
• freelist_count × page_size = octets gaspillés sur des pages inutilisées.

Si freelist_count représente une part importante de page_count, un VACUUM (ou incremental_vacuum) va réduire la taille du fichier de manière visible. Si la valeur est faible, le fichier est déjà compact et VACUUM ne servira à rien.

Pièges classiques à éviter

• Lancer VACUUM à l'intérieur d'une transaction. Impossible. Faites un commit d'abord.
• Oublier que VACUUM a besoin d'espace disque libre. Pour une base de 10 Go, il faut environ 10 Go supplémentaires de libre.
• Activer auto_vacuum alors que la base contient déjà des données. Ça ne fera rien tant que vous n'aurez pas lancé un VACUUM complet. À configurer dès la création de la base si vous y tenez.
• Lancer ANALYZE en espérant réduire la taille du fichier. C'est le boulot de VACUUM.
• Lancer VACUUM en espérant de meilleurs plans d'exécution. C'est le boulot d'ANALYZE.

Les deux commandes sont complémentaires : aucune ne remplace l'autre.

La suite : les transactions

Les commandes de maintenance comme VACUUM mettent en lumière un sujet qu'on avait laissé de côté : le modèle transactionnel de SQLite, ce qu'il verrouille et à quel moment. Le prochain chapitre attaque ça de front — comment fonctionnent les transactions, ce que garantissent réellement BEGIN / COMMIT / ROLLBACK, et comment s'en servir pour qu'un enchaînement d'instructions reste atomique.