According to the SQLlite.org, the purpose of vacuum is as follows, VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.
I am trying to resolving an issue, I am keeping the record count in each row for the table with 200000 row, After the table fill up 200,000 record, when I deleting the data and inserting new data. my record count can get all over the place, the record count some time can be incremented up to 200 from one record to the next. I am thinking it might be related to vacuum. I am vacuum when freelist_count reaches to 1000. Thank you, Liang -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Richard Hipp Sent: Thursday, December 5, 2019 3:57 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? On 12/5/19, Simon Slavin <slav...@bigfraud.org> wrote: > > VACUUM should not be saving you any space. It might, depending on what he is doing. If a single page of the database holds (say) 30 rows of data, and the OP deletes 10 rows from that page, that leaves some empty space on the page. That empty space is reused later, but only if new rows are inserted that have keys that belong on the page in question. If new content is appended to the table (for example, if this is a ROWID table with automatically chosen rowids and new rows are inserted) then the empty space freed up by deleted rows on interior pages will continue to go unused. Once a sufficient number of rows are removed from a page, and the free space on that page gets to be a substantial fraction of the total space for the page, then the page is merged with adjacent pages, freeing up a whole page for reuse. But as doing this reorganization is expensive, it is deferred until a lot of free space accumulates on the page. (The exact thresholds for when a rebalance occurs are written down some place, but they do not come immediately to my mind, as the whole mechanism *just works* and we haven't touched it in about 15 years.) So, if the OP is adding rows to the end of a table, intermixed with deleting random rows from the middle of the table, then the table will grow in size and VACUUM will restore it to the minimum size. But the OP is wrong on this point: The table does not grow *without bound*. There is an upper bound on the amount of free space within a table. If you go above that bound, then space is automatically reclaimed. But, it might be that the upper bound is larger than what the OP can tolerate. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=1ENRPbn-L24iZuWFyfkwR0RwAWnSwaNCCh_MMvJN4Fs&s=UfFmqi9FEBt_M45aGHanxTL2DeM19UgHVighO3_nqKo&e= _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
