ANALYZE i VACUUM w SQLite: statystyki i odzyskiwanie miejsca

Dwa różne zadania konserwacyjne

ANALYZE i VACUUM wymienia się jednym tchem, ale naprawiają różne problemy.

• ANALYZE zbiera statystyki o twoich danych, żeby planer zapytań podejmował mądrzejsze decyzje. Zapisuje je do tabeli sqlite_stat1 i nie dotyka właściwych wierszy.
• VACUUM przebudowuje sam plik, aby odzyskać nieużywane strony i zdefragmentować dane. Nie zmienia bezpośrednio planów zapytań.

Jeśli zapytania wybierają zły indeks, potrzebujesz ANALYZE. Jeśli po wielu usunięciach plik jest większy, niż powinien, potrzebujesz VACUUM. Mylenie ich kończy się mnóstwem zmarnowanego czasu na konserwację.

Co naprawdę robi ANALYZE

Planer zapytań musi zgadywać. Gdy widzi WHERE status = 'active', musi oszacować, ile wierszy pasuje (jeden? milion?), aby zdecydować, czy użyć indeksu, czy przeskanować tabelę. Bez statystyk korzysta z prostych heurystyk.

ANALYZE przechodzi przez każdy indeks i zapisuje zbiorcze informacje o rozkładzie wartości:

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;

Wiersz w sqlite_stat1 mówi planerowi mniej więcej, ile wierszy ma indeks i ile duplikatów ma typowy klucz. Następnym razem, gdy zapytasz WHERE status = 'pending', planer wie, że pending występuje rzadko, i sięga po indeks. Dla WHERE status = 'shipped' może uznać, że skan jest tańszy.

Możesz analizować pojedynczą tabelę lub indeks zamiast całej bazy:

ANALYZE orders;
ANALYZE idx_orders_status;

Uruchamiaj ANALYZE po masowych importach, po dużych zmianach schematu albo wtedy, gdy zauważysz, że planer wybiera słabe plany dla tabel, w których zmienił się rozkład danych.

PRAGMA optimize: współczesny domyślny wybór

Uruchamianie ANALYZE na ślepo przy każdym zamknięciu połączenia to marnotrawstwo: zwykle nic nie zmieniło się na tyle, żeby miało to znaczenie. SQLite ma sprytniejszą nakładkę:

PRAGMA optimize;

PRAGMA optimize sprawdza, jak zmieniła się baza od ostatniej analizy, i uruchamia ANALYZE tylko dla tabel, które tego potrzebują. Oficjalne zalecenie to wywoływać je w każdym długo żyjącym połączeniu tuż przed zamknięciem, a okresowo także w połączeniach otwartych przez wiele godzin.

Jest tanie, gdy nic się nie zmieniło, i skuteczne, gdy coś się zmieniło. Najpierw sięgaj po optimize, a po surowe ANALYZE tylko wtedy, gdy musisz wymusić odświeżenie.

Co naprawdę robi VACUUM

Gdy usuwasz wiersze lub tabelę, SQLite oznacza te strony jako wolne, ale nie zmniejsza pliku. Wolne strony są ponownie wykorzystywane przez przyszłe wstawienia, więc zwykle to nie problem. Jednak przez lata intensywnych zmian gromadzą się dwie rzeczy:

1. Wolne miejsce, którego system operacyjny nie widzi. Twój plik .db wciąż ma 2 GB, choć żywych danych jest tylko 800 MB.
2. Fragmentacja. Wiersze tej samej tabeli lądują na rozrzuconych, niesąsiadujących stronach, co spowalnia skany.

VACUUM naprawia oba problemy: kopiuje całą bazę do nowego, ciasno upakowanego pliku i zastępuje nim oryginał:

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;

Po VACUUM plik ma taki rozmiar, jakby od początku wstawiono tylko 100 pozostałych wierszy. Przy okazji wszystkie rowid pozostają takie same, ale układ na dysku znów jest ciągły.

Kilka rzeczy, które warto wiedzieć przed uruchomieniem:

• Przez cały czas trwania potrzebuje blokady wyłącznej na bazie. Żadne inne połączenie nie może zapisywać.
• Potrzebuje wolnego miejsca na dysku mniej więcej dwa razy większego niż baza, bo buduje nowy plik obok starego.
• Nie może działać wewnątrz transakcji i zgłosi błąd, jeśli są otwarte aktywne transakcje.
• Na bazie o rozmiarze kilku GB może trwać długo. Zaplanuj to.

Kiedy naprawdę uruchamiać VACUUM

W większości aplikacji: nie uruchamiaj, chyba że zmieniło się coś konkretnego.

Dobre powody, aby uruchomić VACUUM:

• Właśnie usunięto dużą tabelę lub ogromną partię wierszy i chcesz odzyskać miejsce na dysku.
• Baza przechodzi intensywne zmiany od lat, a zapytania skanujące tabele wydają się wolniejsze niż kiedyś.
• Dostarczasz plik bazy jako część wydania i chcesz, żeby był jak najmniejszy.

Złe powody:

• "Tak na wszelki wypadek." Za każdym razem przepisuje cały plik. Nie ma nic bezpiecznego w robieniu tego na działającym systemie.
• Po każdej partii usunięć. Zwolnione strony i tak zostałyby ponownie wykorzystane.

auto_vacuum i przyrostowe VACUUM

Jeśli chcesz, żeby SQLite automatycznie zarządzał wolnymi stronami, ustawiasz auto_vacuum przy tworzeniu bazy, bo później nie da się tego zmienić bez pełnego vacuum:

PRAGMA auto_vacuum = INCREMENTAL;

Trzy tryby:

• NONE (domyślny): wolne strony zostają w pliku i są ponownie używane przy kolejnych wstawieniach.
• FULL: każdy commit, który zwalnia strony, od razu przycina plik. Wygodne, ale każda transakcja ponosi ten koszt.
• INCREMENTAL: SQLite śledzi wolne strony, ale zwalnia je dopiero na twoją prośbę:

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) oddaje systemowi operacyjnemu do N wolnych stron. Jest szybkie, nie trzyma długo blokady wyłącznej i można je uruchamiać według harmonogramu. To złoty środek dla baz z dużą liczbą zapisów, które mają pozostać zwarte bez kosztu pełnego VACUUM.

VACUUM INTO: eksport zwartej kopii

VACUUM INTO zapisuje świeżą, zwartą kopię do nowego pliku, nie ruszając oryginału:

VACUUM INTO 'backup.db';

To naprawdę przydatne:

• Kopie zapasowe. Wynik to spójna, w pełni odkurzona migawka: żadnych w połowie zapisanych stron, żadnego pliku .wal, o który trzeba się martwić. Lepsze niż kopiowanie pliku przez cp.
• Zmniejszanie bez długiego blokowania zapisów. Robisz vacuum do pliku obok, a potem atomowo podmieniasz pliki. Zapisy nie są blokowane przez cały czas trwania vacuum.
• Dystrybucja. Możesz rozesłać małą, zdefragmentowaną kopię bazy deweloperskiej.

Plik docelowy nie może istnieć. Jeśli istnieje, dostaniesz błąd.

Praktyczny przepis na konserwację

Dla typowej bazy aplikacji:

-- W każdym długo żyjącym połączeniu, przed zamknięciem:
PRAGMA optimize;

-- Po dużym masowym imporcie lub zmianie schematu:
ANALYZE;

-- Po usunięciu dużej ilości danych, aby odzyskać miejsce:
VACUUM;

-- Kopie zapasowe:
VACUUM INTO '/backups/app-2026-04-23.db';

Jeśli baza jest intensywnie zapisywana i czyszczona i działa 24/7, ustaw auto_vacuum = INCREMENTAL przy tworzeniu i okresowo uruchamiaj PRAGMA incremental_vacuum(N), na przykład raz dziennie w czasie małego ruchu.

Diagnoza: "Dlaczego mój plik jest taki duży?"

Dwie pragmy mówią, co się dzieje:

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 = obecny rozmiar pliku.
• freelist_count × page_size = bajty zmarnowane na nieużywane strony.

Jeśli freelist_count to duża część page_count, VACUUM (lub incremental_vacuum) wyraźnie zmniejszy plik. Jeśli to niewiele, plik jest już dobrze upakowany i VACUUM nie pomoże.

Typowe pułapki

• Uruchamianie VACUUM wewnątrz transakcji. Nie da się. Najpierw zrób commit.
• Zapominanie, że VACUUM potrzebuje wolnego miejsca na dysku. Baza o rozmiarze 10 GB potrzebuje kolejnych ~10 GB wolnego miejsca.
• Ustawianie auto_vacuum, gdy dane już istnieją. Nic to nie daje aż do następnego pełnego VACUUM. Jeśli tego chcesz, ustaw to przy tworzeniu bazy.
• Uruchamianie ANALYZE w oczekiwaniu na mniejszy plik. To zadanie VACUUM.
• Uruchamianie VACUUM w oczekiwaniu na lepsze plany zapytań. To zadanie ANALYZE.

Te dwa polecenia się uzupełniają i żadne nie zastępuje drugiego.

Dalej: transakcje

Polecenia konserwacyjne takie jak VACUUM pokazują coś, co dotąd braliśmy za pewnik: model transakcyjny SQLite oraz to, co i kiedy jest blokowane. Od tego zaczyna się następny rozdział: jak działają transakcje, co naprawdę gwarantują BEGIN / COMMIT / ROLLBACK i jak używać ich, aby praca z wieloma instrukcjami była atomowa.