Is there any disadvantage of vacuuming sqlite dbs?

Are there any disadvantages of vacuuming sqlite databases? If not, why don't applications vacuum their dbs?

Max Ried

Posted 2011-06-29T14:34:43.633

Reputation: 1 521

Answers

Vacuuming recreates the db file from the existing file, and as such it needs extra disk space to run. It also takes some time to do so, and requires a lock to keep things consistent.

So, people chose to not have to deal with the lock and space issues and let the db grow, hoping it doesn't grow too big.

You can do an auto_vacuum which doesn't clean as much on deletes, but doesn't lock the db for any time either.

Rich Homolka

Posted 2011-06-29T14:34:43.633

Reputation: 27 121

Mail.app's "Envelope Index" sqlite3 db gets really large. This also impacts Mail.app's startup time. – Max Ried – 2011-06-29T21:10:23.793

@bot47 Yeah, mail is a particularly bad usage model for dbs. A lot of deletes. I'd write Mail.app so that it would vacuum on app shutdown. MacOS X comes with command line tools for sqlite2, you could probably wrine script to vacuum it yourself. – Rich Homolka – 2011-06-29T21:44:04.363

Vacuuming a SQLite DB basically just saves/"compacts" disk space -- it has very little effect on performance. Most apps would not benefit much from vacuuming unless they delete large amounts of data.

Daniel R Hicks

Posted 2011-06-29T14:34:43.633

Reputation: 5 783

Be aware that vacuuming an sqlite db can recreate the _id column with new id's. So if you are heavily relying on data integrity or data matching based on _id, then make sure you can accomodate this scenario.

IgorGanapolsky

Posted 2011-06-29T14:34:43.633

Reputation: 101