Our app's SQLite file is 4GB after we purged 80 percent of old rows, yet the file size has not dropped a single byte. Why does deletion not shrink SQLite and what is the right way to reclaim the space?
How to shrink a SQLite database file?
Solved SQL & Databases
MS
Michael Scofield December 12, 2019
2 replies
4,960 views
Reviewed by moderators
SQLite never returns space to the OS on delete, it just marks pages free for reuse. Reclaiming:
1
Run
VACUUM; on the database. It rebuilds the file compactly. Needs free disk roughly equal to the final size and locks the database during the run, so schedule it.2
Ongoing churn:
PRAGMA auto_vacuum=INCREMENTAL; set before tables exist, then periodic PRAGMA incremental_vacuum; keeps it trimmed without full locks.Your 4GB file should land near 800MB after the first VACUUM.
VACUUM took 6 minutes, file went 4GB to 760MB. Incremental mode enabled for the future. Solved.