On 18 Nov 2011, at 3:18pm, Sreekumar TP wrote: > I have auto vacuum switched off and do not perform a manual vacuum. In this > scenario will the database size keep increasing when records are inserted > even as records are deleted.
No, you're okay. Deleting records makes space available in the database file and SQLite will know about it. If SQLite wants to store more data, and it fits in the space which is now available, then SQLite will store it in the existing free space rather than making the file longer. > As I understand from the documentation, > > "When auto-vacuum is disabled and data is deleted data from a database, the > database file remains the same size. Unused database file pages are added > to a "freelist <http://www.sqlite.org/fileformat2.html#freelist>" and > reused for subsequent inserts. So no database file space is lost. However, > the database file does not shrink. " WIthout VACUUM, if you INSERT 200 rows, then DELETE 100 of them, the filesize won't shrink. The space will be available, but only to SQLite when it's trying to store data in that same database file. VACUUM (including auto-vacuum) will shrink the database file down and return any free space so it can be used by other files. Since databases rarely shrink, VACUUM doesn't usually matter very much. But if you have a routine which deletes many records (e.g. you have an end-of-year procedure which deletes a year's worth of expired data) then it can be worth doing a 'VACUUM' after your 'DELETE FROM ...'. (The above explanation is slightly simplified since SQLite splits a file into pages, and each page can only be used for certain kinds of data. And even 'UPDATE' can free up space if the new data is shorter than the old data. But the effects of this are small and you get the general idea.) Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
