How different is the Berkeley DB VACUUM from SQLites?
SQLite implements the VACUUM command as a database dump followed by a complete reload from that dump. It is an expensive operation, locking the entire database for the duration of the operation. It is also an all or nothing operation. Either it works, or it fails and you have to try again sometime. When SQLite finishes, the database is frequently smaller in size (file size is smaller) and the btree is better organized (shallower) than before due to in-order key insertion of the data from the dump file. SQLite, when it works and when you can afford locking everyone out of the database, does a good job of VACUUM. Berkeley DB approaches this in a completely different way. For many releases now Berkeley DB’s B-Tree implementation has had the ability to compact while other oprations are in-flight. Compacting is a process wherein the B-Tree nodes are examined and, when less than optimal, they are re-organized (reverse split, etc.). The more shallow your B-Tree, the fewer lookups required to
