VACUUM Command

VACUUM rebuilds the database file and can reclaim unused space.

It is useful after large delete operations or periodic maintenance.

Core Concepts

flowchart LR
  A[Deletes/fragmentation over time] --> B[VACUUM]
  B --> C[Rebuilt compact file]
  C --> D[Potentially smaller and cleaner layout]

Command	Purpose
VACUUM;	Rebuild current database file
VACUUM INTO 'new.db';	Write compacted copy to new file

Code Examples

-- Rebuild the currently open database.
VACUUM;

Expected output
Command completes with no result rows.

-- Create compacted copy into a new file.
VACUUM INTO 'app_compacted.db';

Expected output
New compacted database file is created.

# Optional size check example (before/after).
ls -lh app.db app_compacted.db

Expected output
File sizes displayed; compacted copy is often smaller after heavy deletes.

SQLite-Specific Nuances

SQLite Nuance

VACUUM needs temporary extra disk space during rebuild.

Plan capacity before running it on large databases.

Common Pitfalls / Best Practices

Pitfall

Running VACUUM during write-heavy peak periods without considering lock/IO impact.

Best Practice

Schedule vacuum maintenance during low-traffic windows and verify free disk space first.

Quick Challenge

Run a compaction into clean_copy.db from your current database.

View Solution

VACUUM INTO 'clean_copy.db';

Expected output
clean_copy.db created as compacted copy.