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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
