ANALYZE и VACUUM в SQLite: статистика и освобождение места

Две разные задачи обслуживания

ANALYZE и VACUUM упоминаются в одном ряду, но решают разные проблемы.

• ANALYZE собирает статистику о ваших данных, чтобы планировщик принимал более удачные решения. Он пишет в таблицу sqlite_stat1 и реальные строки не трогает.
• VACUUM пересобирает сам файл, освобождая неиспользуемые страницы и дефрагментируя хранилище. На планы запросов он напрямую не влияет.

Если запросы упорно выбирают не тот индекс — вам нужен ANALYZE. Если файл стал заметно толще, чем должен быть, после кучи удалений — вам нужен VACUUM. Перепутать их легко, и тогда время на обслуживание уходит впустую.

Что на самом деле делает ANALYZE

Планировщик вынужден гадать. Когда он видит WHERE status = 'active', ему нужно прикинуть, сколько строк подойдёт — одна? миллион? — чтобы решить, лезть в индекс или сканировать таблицу. Без статистики он скатывается на грубые эвристики.

ANALYZE обходит каждый индекс и записывает сводную информацию о распределении значений:

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;

Запись в sqlite_stat1 сообщает планировщику, сколько примерно строк в индексе и сколько в среднем дубликатов на ключ. В следующий раз, когда вы спросите WHERE status = 'pending', он увидит, что pending встречается редко, и пойдёт в индекс; для WHERE status = 'shipped' он, может быть, решит, что скан дешевле.

Можно проанализировать и одну таблицу или индекс вместо всей базы:

ANALYZE orders;
ANALYZE idx_orders_status;

Запускайте ANALYZE после массовых загрузок, после крупных изменений схемы или когда заметили, что планировщик упорно выбирает плохие планы на таблицах, где сильно сместилось распределение.

PRAGMA optimize: современный дефолт

Гонять ANALYZE на каждом закрытии соединения — расточительно: чаще всего ничего настолько не поменялось, чтобы это имело значение. У SQLite есть умная обёртка:

PRAGMA optimize;

PRAGMA optimize смотрит, как база изменилась с прошлого анализа, и запускает ANALYZE только на тех таблицах, которым это действительно нужно. Официальная рекомендация — вызывать его на каждом долгоживущем соединении прямо перед закрытием, и периодически — на соединениях, которые висят часами.

Когда ничего не менялось, он почти бесплатный; когда что-то изменилось — эффективен. Берите optimize первым делом; до сырого ANALYZE опускайтесь только когда нужно принудительно обновить статистику.

Что на самом деле делает VACUUM

Когда вы удаляете строки или таблицу, SQLite помечает страницы как свободные, но файл не уменьшает. Свободные страницы потом переиспользуются под новые вставки, так что в обычной жизни всё нормально. Но за долгую жизнь базы с активной записью накапливаются две неприятности:

1. Свободное место, которого ОС не видит. Файл .db так и весит 2 ГБ, хотя живых данных там уже только 800 МБ.
2. Фрагментация. Строки одной и той же таблицы расползаются по неподряд идущим страницам, и сканы начинают тормозить.

VACUUM чинит и то, и другое: копирует всю базу в свежий файл, плотно упакованный, и подменяет оригинал:

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;

После VACUUM файл занимает столько же места, как если бы вы с нуля вставили только 100 переживших строк. Побочный эффект: rowid-ы сохраняются прежними, а размещение на диске снова становится сплошным.

Перед запуском полезно знать пару вещей:

• На время работы он берёт эксклюзивный лок. Никакое другое соединение не сможет писать.
• Ему нужно примерно вдвое больше свободного места, чем размер базы — он строит новый файл рядом со старым.
• Внутри транзакции он не работает и упадёт, если есть открытые транзакции.
• На многогигабайтной базе он может идти долго. Планируйте.

Когда реально стоит запускать VACUUM

Большинству приложений: не запускайте, пока что-то конкретное не изменилось.

Хорошие поводы запустить VACUUM:

• Только что выкинули большую таблицу или удалили огромный пакет строк — и хочется вернуть место на диске.
• База перемалывает данные годами, и сканирующие запросы стали ощутимо медленнее, чем были.
• Вы кладёте файл базы в релиз и хотите, чтобы он был как можно компактнее.

Плохие поводы:

• «Так, на всякий случай.» Каждый раз он переписывает весь файл — на боевой системе ничего безопасного в этом нет.
• После каждого пакета удалений. Освободившиеся страницы и так бы переиспользовались.

auto_vacuum и инкрементальный VACUUM

Если хочется, чтобы SQLite сам управлял свободными страницами, задайте auto_vacuum на этапе создания базы — позже без полного VACUUM его уже не поменять:

PRAGMA auto_vacuum = INCREMENTAL;

Три режима:

• NONE (по умолчанию): свободные страницы остаются в файле, переиспользуются при последующих вставках.
• FULL: каждый коммит, освобождающий страницы, ещё и обрезает файл. Удобно, но за это платит каждая транзакция.
• INCREMENTAL: SQLite ведёт учёт свободных страниц, но отдаёт их обратно ОС только по запросу:

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) отдаёт ОС до N свободных страниц — быстро, без долгого эксклюзивного лока, и его можно дёргать по расписанию. Это золотая середина для записе-нагруженных баз, которым нужно оставаться компактными без накладных расходов полного VACUUM.

VACUUM INTO: компактная копия отдельным файлом

VACUUM INTO пишет свежую, компактную копию базы в новый файл, не трогая оригинал:

VACUUM INTO 'backup.db';

Это правда полезно:

• Резервные копии. На выходе — целостный, полностью «провакуумленный» снимок: ни недописанных страниц, ни проблем с .wal. Лучше, чем копировать файл через cp.
• Уменьшение без долгой блокировки записи. Вакуум идёт в отдельный файл, потом можно атомарно подменить. Писатели не блокируются на всё время операции.
• Раздача. Положить в дистрибутив крошечную, дефрагментированную копию dev-базы.

Файл назначения не должен существовать. Если уже есть — получите ошибку.

Практический рецепт обслуживания

Для типичной прикладной базы:

-- На каждом долгоживущем соединении, перед закрытием:
PRAGMA optimize;

-- После большой массовой загрузки или изменения схемы:
ANALYZE;

-- После удаления большого объёма данных, когда хочется вернуть место:
VACUUM;

-- Для бэкапов:
VACUUM INTO '/backups/app-2026-04-23.db';

Если база живёт под высокой записью/удалением и держится онлайн 24/7, поставьте auto_vacuum = INCREMENTAL ещё при создании и периодически вызывайте PRAGMA incremental_vacuum(N) — например, раз в сутки в часы низкой нагрузки.

Диагностика «почему файл такой большой?»

Два прагмы расскажут, что происходит:

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 = текущий размер файла.
• freelist_count × page_size = сколько байт уходит на неиспользуемые страницы.

Если freelist_count — заметная доля page_count, VACUUM (или incremental_vacuum) ощутимо ужмёт файл. Если доля небольшая — файл уже упакован эффективно, и VACUUM тут не поможет.

Типичные грабли

• Запуск VACUUM внутри транзакции. Не получится. Сначала закоммитьте.
• Забыли, что VACUUM нужен запас места. Базе на 10 ГБ нужно ещё ~10 ГБ свободного диска.
• Включаете auto_vacuum, когда данные уже есть. До следующего полного VACUUM это no-op. Хотите включить — задавайте при создании базы.
• Запустили ANALYZE и ждёте, что файл станет меньше. Это работа VACUUM.
• Запустили VACUUM и ждёте лучших планов. Это работа ANALYZE.

Эти команды друг друга дополняют, ни одна не заменяет другую.

Дальше: транзакции

Команды обслуживания вроде VACUUM подсвечивают то, что мы до этого принимали как данность: транзакционную модель SQLite и то, что и когда она блокирует. Следующая глава начинается именно с этого — как устроены транзакции, что на самом деле гарантируют BEGIN / COMMIT / ROLLBACK, и как ими пользоваться, чтобы многошаговая работа оставалась атомарной.