What does VACUUM do in SQLite and why is it necessary?

Explanation & Concept

When rows or tables are deleted in SQLite, the freed database pages are marked as unused on an internal 'free list' rather than returned to the operating system. Over time, frequent deletions create internal database fragmentation.

The `VACUUM` command rebuilds the entire database file into a clean, compact file, freeing unused space back to the operating system and defragmenting B-Tree pages for faster sequential access.

Code Example

sql

-- Reclaim unused disk space and defragment database
VACUUM;

-- Alternatively, enable auto-vacuuming before initial table creation
PRAGMA auto_vacuum = FULL;

How It Works & Key Takeaways

Running `VACUUM` reduces database file size on disk and reorganizes pages contiguously, improving subsequent read speeds.