I have a SQLite database my application uses to store some data. It can get very large (a few GBs in size), it has only 3 columns: an auto incrementing counter, a UUID, and a binary BLOB. My application keeps track of how many rows are in the database and removes the oldest ones (based on the increment) when it has exceeded the row limit.
On startup I also run VACUUM to compress the database in case the row limit has changed and the space in the database is mostly free allocated space.
My understanding is that a DELETE command will simply mark the deleted pages as "free pages" which can be written over again. Despite this, I see that the file size is continuing to grow (albeit slower) when inserting new rows after the row limit has been reached. Is this due to fragmentation of the free pages? Can I expect this fragmentation accretion to stop after a long enough time has passed? My application is intended to run uninterrupted for a very long time and if the file size increases on every INSERT the hard drive of the machine will fill up.
