A couple of days ago, I commented on the vacuum-your-mail-to-make-it-go-faster meme passing through the Mac weblogging community.
I wouldn’t do that. It will work. Most of the time. And then it won’t. While there is very little data loss risk — the Envelope Index is just that, an index of all of your Mail — there is a risk and it was clearly left off in Tiger for a reason.
First, auto_vacuum was new to SQLite 3.1.0 and Tiger ships with SQLite 3.1.3, which won’t be updated unless a serious security hole is found. There have been a number of bug fixes to vacuum and vacuum related functionality since 3.1.3. In particular, there have been a handful of fixes that prevent relatively esoteric situations from corrupting a database. Some of these were directly related to auto_vacuum and some were simply catalyzed by auto_vacuum.
Secondly, it isn’t going to buy you that much performance over periodically vacuuming the database. Most of you who have manually vacuumed your database are seeing a performance boost related to the removal of 10s of months of garbage built up in the database. Running vacuum after only a week or two of Mail activity is going to yield very little benefit.
That is, the additional risk of running with auto_vacuum enabled with a 3.1.3 based SQLite database isn’t worth the very slight additional performance you’ll see on a day to day basis. Better to simply manually vacuum the database every six months or so.