SQLite VACUUM

When you delete or update rows in SQLite, the database file does not automatically shrink. The space previously used by deleted data stays in the file as empty pages. The VACUUM command reclaims that space, rebuilds the database file, and compacts it to the smallest possible size.

Why Empty Space Stays Behind

BEFORE DELETES (database file: 10 MB)
──────────────────────────────────────────────────────
[ Page 1 ] [ Page 2 ] [ Page 3 ] [ Page 4 ] [ Page 5 ]
[ data    ] [ data    ] [ data    ] [ data    ] [ data    ]

After DELETE removes 60% of rows:
──────────────────────────────────────────────────────
[ Page 1 ] [ Page 2 ] [ Page 3 ] [ Page 4 ] [ Page 5 ]
[ data    ] [  EMPTY  ] [ data    ] [  EMPTY  ] [  EMPTY  ]

File is still 10 MB — empty pages stay allocated.

After VACUUM:
──────────────────────────────────────────────────────
[ Page 1 ] [ Page 2 ]
[ data    ] [ data    ]

File shrinks to ~4 MB. Empty pages are released.

Running VACUUM

-- Full VACUUM: rebuilds entire database
VACUUM;

-- VACUUM INTO: compact copy to a new file (original untouched)
VACUUM INTO 'compacted_backup.db';

What VACUUM Does

1. Creates a new database file
2. Copies all live data into it in compact form
3. Rebuilds all indexes cleanly
4. Replaces the original file with the new one
5. Reports success

Side effects:
  - Defragments all tables
  - All rowids become sequential (fragmented IDs are renumbered... 
    UNLESS table has INTEGER PRIMARY KEY AUTOINCREMENT)
  - All pages are full (no free pages left)

Measuring Space Before and After

-- Check current database size
PRAGMA page_count;       -- number of pages in use
PRAGMA page_size;        -- bytes per page (default 4096)
PRAGMA freelist_count;   -- number of empty (free) pages

-- Example:
sqlite> PRAGMA page_count;
2560
sqlite> PRAGMA page_size;
4096
sqlite> PRAGMA freelist_count;
1400

Wasted space = 1400 × 4096 = 5.7 MB
Total file  = 2560 × 4096 = 10.0 MB
Useful data = 1160 × 4096 = 4.3 MB

After VACUUM:
sqlite> PRAGMA freelist_count;
0
-- File now ~4.3 MB

Auto-VACUUM Mode

Instead of running VACUUM manually, you can enable auto-vacuum. SQLite then returns free pages to the OS automatically when rows are deleted.

-- Enable auto-vacuum (must be set before any data is written)
PRAGMA auto_vacuum = FULL;

-- Or incremental mode (manually trigger page reclaim):
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);   -- release up to 100 pages

-- Disable (default):
PRAGMA auto_vacuum = NONE;

-- Check current setting:
PRAGMA auto_vacuum;

When to Run VACUUM

RUN VACUUM WHEN:
──────────────────────────────────────────────────────
✔ After bulk DELETE operations
✔ After major data migrations or imports
✔ Database file is noticeably larger than expected
✔ Before distributing or archiving a database
✔ Scheduled maintenance (e.g. weekly on low-traffic hours)

DO NOT RUN VACUUM:
──────────────────────────────────────────────────────
✘ During active writes (locks the file for the duration)
✘ On very large databases without adequate disk space
   (VACUUM needs ~2× the DB size in temp space)

VACUUM INTO — Safe Compact Backup

-- Creates a fresh, compact copy without touching the original
VACUUM INTO '/backups/mydb_compact.db';

-- Use cases:
-- ✔ Create a minimal distribution copy
-- ✔ Backup and compact in one step
-- ✔ Test VACUUM result before applying to production

Key Takeaways

• Deleted rows leave empty pages — VACUUM reclaims that space
• Run VACUUM; to compact and rebuild the database file
• PRAGMA freelist_count shows how many empty pages exist before vacuuming
• VACUUM INTO 'file.db' creates a compacted copy without modifying the original
• Enable PRAGMA auto_vacuum = FULL to reclaim space automatically after deletes