On 2014/12/09 03:36, David Barrett wrote:
Hi all, great questions:
*Re: Why VACUUM.* We vacuum weekly. This particular database is a
"rolling journal" -- we are constantly adding new rows to the end of the
table, and every week we truncate off the head of the journal to only keep
3M rows at the "tail". Given that we're truncating the "head", without
vacuuming we'd be inserting the new rows at the "front" of the database
with the old rows at the "end" -- and then each truncation would leave the
database more and more fragmented. Granted, this is on SSDs so the
fragmentation doesn't matter a *ton*, but it just adds up and gets worse
over time. Anyway, agreed it's not the most important thing to do, but all
things being equal I'd like to do it if I can to keep things clean and
snappy.
Hi David - this is a completely unneeded step. SQLite will maintain it's internal structure, it doesn't do 'rolling' data usage and
it will do so using the minimum IO. SQLite knows about people deleting and inserting, it will re-use and not waste, so no need to
try and accomplish that. Only use Vacuum maybe after some months or such when you are doing all your proper DB maintenance -
otherwise the performance gain will be negligible (and if you do experience some or other significant performance gain after a
vacuum in your use-case, please let us know because something might be broken then).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
