Hi, So if my database grew to 20Mb and later lots of records were deleted amounting to 50% of the database size, the size on disk will still be 20Mb. Correct?
Now if I start inserting records, sqlite will use the remaing 50% of disk space until it reaches a point where no more free pages or space in pages are available to insert data. Is this inference valid? Sreekumar On Nov 18, 2011 9:09 PM, "Simon Slavin" <[email protected]> wrote: > > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
