Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
1  
To do what you require SQLite would have to move DB objects (rows) to the beginning of the file so that it could shrink it - this is an expensive operation so is not something that happens automatically. I don't know of a RDBMS that does this automatically. –  FreshPhilOfSO Sep 19 '12 at 9:39
add comment

1 Answer

up vote 3 down vote accepted

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 VACUUM command manually.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.