SQLite batch atomic writes

[rhashimoto]

SQLite has an optional feature, batch atomic writes, that VFS implementations can use:

Definition: "Batch-atomic write" mean the ability to write an arbitrary number of bytes into arbitrary locations in a single file, and to do so atomically. Following a system crash or power failure, either all the writes are preserved in the file, or none of them are.

It was created for the "Flash-Friendly File System" but it is a good match for the versioned block IndexedDB idea which also allows atomic file changes of arbitrary size. When a batch atomic change is made to a database, no journal data is written to IndexedDB so this is a supported alternative to the "zero-journal" hack of discarding the journal data (saving only page indices) and reconstituting them when necessary.

I modified IDBVersionedVFS in a private branch and it works. I did encounter a few gotchas. Not all transactions can use this feature - e.g. it isn't supported for a transaction that writes to multiple databases - so all transactions start with an in-memory journal which will be transferred to the file system if/when the transaction does not qualify for batch atomic writes. This conversion to a file system journal will also happen if the journal won't fit in the SQLite pager cache, so transactions that modify a lot of database pages may also not qualify for batch atomic writes (cache size can be adjusted with PRAGMA cache_size).

The benefits are mixed but generally favorable. The code for batch atomic writes is simpler and cleaner than the "zero-journal" hack, and should perform at least as well when you get to use it (about the same on commits, faster on rollbacks). It's probably a reasonable choice for applications that use a single database where most transactions fit in the cache, or that use features that "no-journal" doesn't support, or where its performance off the fast-path is still considered adequate.

[rhashimoto]

I've made IDBBatchAtomicVFS the featured IndexedDB VFS in the online demo and benchmarks.

It is the most general of the sample IndexedDB VFS classes, meaning that it should work in some less common usage cases such as changes to the page size, transactions needing a super-journal, and nested savepoints (some of these cases will be slow, e.g. super-journal rollbacks). It benchmarks faster than IDBMinimalVFS but slower than IDBVersionedVFS (PRAGMA tuning could close this gap), and code readability is also somewhere in between.