The SQLite FAQ states the following:
(12) I deleted a lot of data but the database file did not get any
smaller. Is this a bug?No. When you delete information from an SQLite database, the unused
disk space is added to an internal "free-list" and is reused the next
time you insert data. The disk space is not lost. But neither is it
returned to the operating system.
Can you explain to me the motives behind that decision (as opposite to returning free space to the OS immediately)? What are the pros and cons of such decision (including non-obvious ones)?
Best Answer
As Phil wrote, freeing pages from the free-list would require that pages are moved around (and that all references to those pages are adjusted).
When data is deleted, it is very likely that some other data will be inserted afterwards, and that data can then just use the pages in the free-list. Returning those pages to the OS only to allocate new ones afterwards would be inefficient, and will increase fragmentation (both of the database file on disk, and of the objects in the database file). But if you really want SQLite to do that, you can enable it with
PRAGMA auto_vacuum.In practice, deleting lots of data usually doesn't happen during normal operation, and if you know that you will not insert new data after deleting, you can run the
VACUUMcommand manually.