On 9 Dec 2014, at 1:36am, David Barrett <dbarr...@expensify.com> wrote:
> *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. Okay. I have some great news for you. You can completely ignore VACUUMing without any time or space drawbacks. You're wasting your time and using up the life of your SSD for no advantage. Fragmentation ceases to become a problem when you move from rotating disks to SSD. SSD is a truly random access medium. It's no faster to access block b then block b+1 than it is block b then block b+1000. Two contiguous blocks used to be faster in rotating disks only because there is a physical read/write head and it will already be in the right place. SSDs have no read/write head. It's all solid state and accessing one block is no faster than another. Delete old rows and you'll release space. Insert new rows and they'll take up the space released. Don't worry about the internal 'neatness' of the file. Over a long series of operations you might see an extra block used from time to time. But it will be either zero or one extra block per table/index. No more than that. A messy internal file structure might niggle the OCD side of your nature but that's the only disadvantage. Also, SSD drives wear out fast. We don't have good figures yet for mass-produced drives (manufacturers introduce new models faster than the old ones wear out, so it's hard to gather stats) but typical figures show a drive failing in from 2,000 to 3,000 write cycles of each single block. Your drive does something called 'wear levelling' and it has a certain number of blocks spare and will automatically swap them in when the first blocks fail, but after that your drive is smoke. And VACUUM /thrashes/ a drive, doing huge amounts of reading and writing as it rebuilds tables and indexes. You don't want to do something like that on an SSD without a good reason. So maybe once every few years, or perhaps if you have another more complicated maintenance routine which already takes up lots of time, do a VACUUM then. But it doesn't really matter if you never VACUUM. (Which is entirely unlike my home, dammit.) Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
