Should a SQLite migration run VACUUM?
VACUUM in a migration
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
VACUUM rewrites the entire database file into a temporary copy and swaps it in: it needs up to twice the database's size in free disk space, holds the write lock for the whole rewrite, and fails outright if a transaction is open on the connection, so inside a migration runner's transaction it is an error, and outside one it is an outage-sized pause.
Why it is dangerous on a populated table
SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.
Fires on
VACUUM;The safe pattern
Vacuum from an operational job in a maintenance window, never from a migration. If space reclaim must be continuous, PRAGMA auto_vacuum=INCREMENTAL with incremental_vacuum in small steps.
-- operational runbook, not a migration:
-- sqlite3 app.db 'VACUUM;'Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (2)
BEGIN;
VACUUM;
COMMIT;VACUUM;Stays silent (2)
PRAGMA incremental_vacuum(100);VACUUM INTO '/backups/app.db';How to check locally
SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.sql --engine=sqlite