SQLite - VACUUM

SQLite - The VACUUM Command

In SQLite, when you delete data, the space it occupied is marked as ‘free’ and reused for future inserts, but the database file size does not shrink. The VACUUM command rebuilds the entire database file from scratch to reclaim this unused space, resulting in a smaller, defragmented database file.

This process copies the content to a temporary database and then reloads it into the original file. This cleans up the file structure by eliminating free pages and making table data contiguous. Be aware: VACUUM may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

VACUUM operates on the entire main database. It cannot be used on attached databases. It will also fail if an active transaction is in progress. For in-memory databases, VACUUM has no effect.

Standard VACUUM

To run VACUUM on the entire database, you can execute the command directly in the SQLite shell:

sqlite> VACUUM;

Note: The older syntax VACUUM table_name; is no longer supported in modern SQLite versions. VACUUM always processes the entire database.

Modern Alternative: VACUUM INTO

A newer, often safer, alternative is VACUUM INTO. This command writes a new, vacuumed version of the database to a separate file, leaving the original database untouched. This is excellent for creating clean backups.

Syntax:

sqlite> VACUUM INTO 'new_database_file.db';

This creates a minimal, defragmented copy named new_database_file.db.

Auto-VACUUM Mode

Auto-vacuum is a different mechanism. Instead of rebuilding the database, it moves free pages to the end of the file and truncates the file, preventing it from growing unnecessarily. It does not defragment data like a full VACUUM. This mode must be enabled before tables are created.

You can configure auto-vacuuming using a PRAGMA statement:

-- Disable auto-vacuuming (default)
PRAGMA auto_vacuum = NONE; -- Value: 0

-- Enable full auto-vacuuming
PRAGMA auto_vacuum = FULL; -- Value: 1

-- Enable incremental vacuuming (requires PRAGMA incremental_vacuum)
PRAGMA auto_vacuum = INCREMENTAL; -- Value: 2

You can check the current setting for a database from the command line:

$ sqlite3 your_database.db "PRAGMA auto_vacuum;"

Best Practices and Considerations

• Performance Impact: VACUUM can be a slow and I/O-intensive operation, blocking all other database connections while it runs. Use it during maintenance periods.
• When to Use: It’s most effective after large-scale deletions of data. For databases on embedded systems with limited storage, periodic vacuuming can be critical.
• auto_vacuum vs VACUUM: Use auto_vacuum to keep file sizes in check on frequently changing databases. Use VACUUM for periodic deep cleaning and defragmentation.