SQLite VACUUM
Also known as: vacuum sqlite, compact database
VACUUM is a SQLite command that rebuilds a database file from scratch, repacking pages to remove free space left by deletions and updates. It shrinks the file on disk, defragments it, and can reduce fragmentation, but requires extra temporary space to run.
- Rebuilds the database to free pages left by deletes and updates.
- Needs temporary space up to about twice the database size.
- Cannot run inside a transaction; VACUUM INTO writes a clean copy.
How VACUUM works
When rows are deleted or updated, SQLite marks pages as free but does not return that space to the operating system, so the file stays large even as data shrinks. VACUUM rebuilds the whole database into a new file with the free pages removed, then replaces the original.
The rebuild also defragments the file: tables and indexes are written back in a more contiguous, sequential layout, which can improve scan performance. Running VACUUM needs free disk space up to roughly twice the database size because it builds a temporary copy.
`PRAGMA auto_vacuum` can return freed pages incrementally instead, and `VACUUM INTO` writes a clean copy to a new file without disturbing the original. VACUUM cannot run inside a transaction and takes a write lock for its duration.
Why a cleaner cares
Long-lived app databases, including a cleaner's own Room scan index, accumulate free pages as records are added and removed across thousands of scans. That bloat counts as app data even though it holds no live rows.
Running VACUUM reclaims that wasted space and tightens the file, so the cleaner's footprint stays small. The same principle explains why some app caches and databases keep growing on disk until compacted or cleared.