Sorry for the slow response -- yes, this is great logic. We're just disabling vacuum. Thanks!
-david On Mon, Dec 8, 2014 at 6:18 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
