SQLite ANALYZE & VACUUM: Statistiken und Speicher freigeben

Zwei grundverschiedene Wartungsaufgaben

ANALYZE und VACUUM werden gerne in einen Topf geworfen, dabei lösen sie ganz unterschiedliche Probleme.

• ANALYZE sammelt Statistiken über deine Daten, damit der Query Planner bessere Entscheidungen treffen kann. Die Ergebnisse landen in einer Tabelle namens sqlite_stat1 – an deinen eigentlichen Datensätzen wird nichts angerührt.
• VACUUM baut dagegen die Datenbankdatei selbst neu auf, gibt ungenutzte Pages frei und entfragmentiert den Speicher. Die Ausführungspläne deiner Queries beeinflusst es nicht direkt.

Wenn Queries den falschen Index nehmen, brauchst du ANALYZE. Wenn die Datei nach vielen Löschvorgängen aufgebläht ist, ist VACUUM das richtige Werkzeug. Wer beides verwechselt, verschwendet schnell jede Menge Zeit mit der falschen Art von Wartung.

Was ANALYZE genau macht

Der Query Planner muss schätzen. Bei WHERE status = 'active' muss er abschätzen, wie viele Zeilen wohl matchen – eine? eine Million? – um zu entscheiden, ob sich ein Index lohnt oder ein voller Table Scan schneller ist. Ohne Statistiken bleiben ihm nur grobe Faustregeln.

ANALYZE läuft jeden Index durch und legt zusammenfassende Infos darüber ab, wie die Werte verteilt sind:

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;

Die Zeile in sqlite_stat1 verrät dem Query Planner ungefähr, wie viele Zeilen der Index enthält und wie viele Duplikate ein typischer Schlüssel hat. Wenn du das nächste Mal WHERE status = 'pending' abfragst, weiß SQLite, dass pending selten vorkommt, und greift zum Index. Bei WHERE status = 'shipped' kann es dagegen zum Schluss kommen, dass ein Full Scan günstiger ist.

Statt die ganze Datenbank zu analysieren, kannst du gezielt eine einzelne Tabelle oder einen einzelnen Index untersuchen:

ANALYZE orders;
ANALYZE idx_orders_status;

Setze ANALYZE ein, nachdem du große Datenmengen geladen hast, nach umfangreichen Schemaänderungen oder wenn du merkst, dass der Query Planner für Tabellen mit veränderter Datenverteilung schlechte Pläne wählt.

PRAGMA optimize: der moderne Standard

ANALYZE stumpf bei jedem Verbindungsende laufen zu lassen, ist Verschwendung — meistens hat sich nichts Relevantes verändert. SQLite bringt deshalb einen schlaueren Wrapper mit:

PRAGMA optimize;

PRAGMA optimize prüft, was sich seit der letzten Analyse in der Datenbank verändert hat, und führt ANALYZE nur dort aus, wo es wirklich nötig ist. Die offizielle Empfehlung lautet: Auf jeder langlebigen Verbindung kurz vor dem Schließen aufrufen – und bei Verbindungen, die stundenlang offen bleiben, regelmäßig zwischendurch.

Wenn sich nichts geändert hat, kostet es kaum etwas; wenn doch, bringt es genau das, was nötig ist. Greif also zuerst zu optimize und nur dann direkt zu ANALYZE, wenn du eine Aktualisierung erzwingen musst.

Was VACUUM in SQLite wirklich macht

Wenn du Zeilen löschst oder eine Tabelle droppst, markiert SQLite die betroffenen Pages zwar als frei, aber die Datei selbst wird nicht kleiner. Die freien Pages werden bei späteren Inserts wiederverwendet – meistens ist das also völlig in Ordnung. Über die Zeit sammeln sich aber zwei Probleme an:

1. Freier Speicher, den das Betriebssystem nicht sieht. Deine .db-Datei bleibt bei 2 GB, obwohl nur noch 800 MB tatsächliche Daten drinstecken.
2. Fragmentierung. Zeilen derselben Tabelle landen verstreut auf nicht zusammenhängenden Pages, was die Scan-Performance ausbremst.

Mit sqlite vacuum lassen sich beide Probleme lösen: SQLite kopiert die komplette Datenbank in eine frische, dicht gepackte Datei und ersetzt das 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 'Eintrag ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

Nach einem VACUUM ist die Datei genauso groß, als hättest du nur die verbleibenden 100 Zeilen frisch eingefügt. Als Nebeneffekt bleiben alle Rowids erhalten, und das Layout auf der Festplatte ist wieder zusammenhängend.

Ein paar Dinge solltest du vorher wissen:

• VACUUM braucht für die gesamte Laufzeit einen exklusiven Lock auf die Datenbank. Keine andere Verbindung darf währenddessen schreiben.
• Du brauchst etwa doppelt so viel freien Speicherplatz wie die Datenbank groß ist — die neue Datei wird parallel zur alten aufgebaut.
• Innerhalb einer Transaktion läuft es nicht, und sobald aktive Transaktionen offen sind, gibt es einen Fehler.
• Bei einer Datenbank im Multi-GB-Bereich kann das richtig lange dauern. Plan das ein.

Wann lohnt sich VACUUM wirklich?

Für die meisten Anwendungen gilt: lieber nicht — außer es hat sich konkret etwas geändert.

Sinnvolle Gründe für ein VACUUM:

• Du hast gerade eine große Tabelle gelöscht oder einen riesigen Batch an Zeilen entfernt und willst den Speicher zurück (klassischer Fall von SQLite Speicher freigeben nach DELETE).
• Die Datenbank läuft seit Jahren unter Last, und Queries mit Table Scans fühlen sich spürbar träger an als früher.
• Du lieferst eine SQLite-Datei als Teil eines Releases aus und willst sie so klein wie möglich halten.

Schlechte Gründe:

• „Sicher ist sicher." VACUUM schreibt jedes Mal die komplette Datei neu. Auf einem Live-System ist daran überhaupt nichts sicher.
• Nach jedem Lösch-Batch. Die freigewordenen Pages wären sowieso wiederverwendet worden.

auto_vacuum und incremental vacuum

Wenn SQLite die freien Pages automatisch verwalten soll, musst du auto_vacuum bei der Erstellung der Datenbank setzen — nachträglich lässt sich das ohne ein vollständiges VACUUM nicht mehr ändern:

PRAGMA auto_vacuum = INCREMENTAL;

Drei Modi stehen zur Auswahl:

• NONE (Standard): Freie Pages bleiben in der Datei und werden bei späteren Inserts wiederverwendet.
• FULL: Bei jedem Commit, der Pages freigibt, wird die Datei automatisch gekürzt. Bequem, aber jede Transaktion zahlt dafür.
• INCREMENTAL: SQLite führt Buch über freie Pages, gibt sie aber erst frei, wenn du es ausdrücklich anforderst:

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) gibt bis zu N freie Seiten ans Betriebssystem zurück – das geht schnell, hält den Exclusive-Lock nur kurz und lässt sich problemlos zeitgesteuert ausführen. Genau die richtige Wahl für schreibintensive Datenbanken, die kompakt bleiben sollen, ohne dass du jedes Mal die Kosten eines vollständigen VACUUM in Kauf nehmen musst.

VACUUM INTO: Eine kompakte Kopie exportieren

Mit VACUUM INTO schreibst du eine frische, kompakte Kopie der Datenbank in eine neue Datei, ohne das Original anzufassen:

VACUUM INTO 'backup.db';

Das ist wirklich praktisch:

• Backups. Das Ergebnis ist ein konsistenter, vollständig vakuumierter Snapshot – keine halb geschriebenen Pages, kein lästiges .wal. Deutlich besser, als die Datei einfach mit cp zu kopieren.
• Datenbank verkleinern, ohne Writer lange zu blockieren. Du vakuumierst in eine separate Datei und tauschst sie dann atomar aus. Writer werden also nicht über die gesamte Dauer des Vacuums ausgesperrt.
• Verteilung. Schick eine kleine, defragmentierte Kopie deiner Entwicklungsdatenbank an andere weiter.

Wichtig: Die Zieldatei darf noch nicht existieren. Sonst bekommst du einen Fehler.

Ein praxistaugliches Wartungsrezept

Für eine typische Anwendungsdatenbank:

-- Bei jeder langlebigen Verbindung, vor dem Schließen:
PRAGMA optimize;

-- Nach einem großen Bulk-Load oder einer Schemaänderung:
ANALYZE;

-- Nachdem du viele Daten gelöscht hast und Speicherplatz zurückgewinnen willst:
VACUUM;

-- Für Backups:
VACUUM INTO '/backups/app-2026-04-23.db';

Bei Datenbanken mit hohem Schreib-/Löschaufkommen, die im 24/7-Betrieb laufen, solltest du beim Anlegen auto_vacuum = INCREMENTAL setzen und dann regelmäßig PRAGMA incremental_vacuum(N) ausführen — zum Beispiel einmal täglich in einer Phase mit wenig Last.

Warum ist meine SQLite-Datei so groß? Ursachen finden

Zwei Pragmas verraten dir, was im Inneren los ist:

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 = aktuelle Dateigröße.
• freelist_count × page_size = Bytes, die durch ungenutzte Seiten verschwendet werden.

Wenn freelist_count einen großen Anteil von page_count ausmacht, wird ein VACUUM (oder incremental_vacuum) die Datei spürbar verkleinern. Ist der Wert klein, ist die Datei schon kompakt gepackt und VACUUM bringt nichts.

Typische Stolperfallen

• VACUUM innerhalb einer Transaktion ausführen. Geht nicht. Erst committen.
• Vergessen, dass VACUUM freien Speicher braucht. Eine 10 GB große Datenbank braucht zusätzlich rund 10 GB freien Plattenplatz für den Vorgang.
• auto_vacuum erst setzen, wenn schon Daten da sind. Das bleibt wirkungslos, bis das nächste vollständige VACUUM läuft. Wenn du es willst, setze es direkt beim Anlegen der Datenbank.
• ANALYZE ausführen und kleinere Dateien erwarten. Dafür ist VACUUM zuständig.
• VACUUM ausführen und bessere Query-Pläne erwarten. Dafür ist ANALYZE zuständig.

Die beiden Befehle ergänzen sich – keiner ersetzt den anderen.

Weiter geht's: Transaktionen

Wartungsbefehle wie VACUUM machen etwas sichtbar, das wir bisher als selbstverständlich hingenommen haben: das Transaktionsmodell von SQLite und die Frage, was wann gesperrt wird. Genau da setzt das nächste Kapitel an – wie Transaktionen funktionieren, was BEGIN / COMMIT / ROLLBACK wirklich garantieren und wie du damit mehrere Statements atomar zusammenfasst.