AUTO-VACUUM or not?

[brody4hire]

Unfortunately the DRAFT https://www.w3.org/TR/webdatabase/ does not explicitly deal with manual VACUUM vs AUTO-VACUUM. The following sections may be relevant:

https://www.w3.org/TR/webdatabase/#sensitivity-of-data:

[...] user agents should ensure that when deleting data, it is promptly deleted from the underlying storage.

https://www.w3.org/TR/webdatabase/#disk-space:

User agents should limit the total amount of space allowed for databases.

I found the following resources from a quick search:

• http://stackoverflow.com/questions/6763981/how-to-compress-websql-database-from-javascript
• https://bugs.chromium.org/p/chromium/issues/detail?id=122522
• http://lifehacker.com/5344418/make-firefox-faster-by-vacuuming-your-database
• db.remove not decreasing file size of Cordova SQLite DB pouchdb/pouchdb#3725

The following resources describe the various AUTO-VACUUM options:

• https://www.sqlite.org/pragma.html#pragma_auto_vacuum
• https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/

I would personally favor FULL AUTO-VACUUM since it seems to avoid indeterminate interruptions and (along with #645: secure delete) keep things cleaned up in general.

Another idea would be to make this an openDatabase option.

[brody4hire]

In many cases JavaScript app authors do not consider memory & disk resource management at the earliest prototyping & delivery stages. I would highly favor enabling auto-vacuum by default in the next major (if not minor) release to deal with these cases.

An excellent resource about auto-vacuum on Android: http://stackoverflow.com/questions/25135463/where-we-need-use-vacuum

According to http://www.sqlite.org/pragma.html#pragma_auto_vacuum there are some tricks needed to enable auto-vacuum on existing sqlite database files.

An alternative may be to make this more explicit by introducing a mandatory setting in the next major release.

[brody4hire]

From http://www.sqlite.org/pragma.html#pragma_auto_vacuum with bold emphasis added to a few relevant items:

The default setting for auto-vacuum is 0 or "none", unless the SQLITE_DEFAULT_AUTOVACUUM compile-time option is used. The "none" setting means that auto-vacuum is disabled. When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. Unused database file pages are added to a "freelist" and reused for subsequent inserts. So no database file space is lost. However, the database file does not shrink. In this mode the VACUUM command can be used to rebuild the entire database file and thus reclaim unused disk space.

So if no form of VACUUM or AUTO-VACUUM is used:

• no disk space will be lost since disk space for deleted data will be used to store new data
• database file will never shrink

I hope to document this sometime later.