Evaluating WAL for Cordova SQLite plugin solutions

[brodycj]

For redesign of SQLite plugin for Apache Cordova (https://github.com/brodybits/ask-me-anything/issues/3), I would like to start evaluating whether or not to consider using WAL (https://www.sqlite.org/wal.html).

Use case: I think the primary benefit of WAL would be to alleviate possible blocking between writer(s) and readers, under the following kinds of circumstances:

• Custom native Cordova plugin does read or write access, parallel to JavaScript
• JavaScript code can initiate parallel read and write access, from different modules for example. (It should not be very hard to imagine multiple JavaScript modules starting multiple read and write operations in parallel, given that JavaScript was designed to be event-driven from its very beginning.) Discussed in the past here: https://github.com/litehelpers/cordova-sqlite-evmax-ext-workers-legacy-build-free/issues/8

Major drawbacks (as discussed in https://github.com/xpbrew/cordova-sqlite-storage/issues/643#issuecomment-285372916 and https://github.com/xpbrew/cordova-sqlite-storage-help/issues/10):

• need for checkpointing which could be a blocking, long-running operation after a large number of DELETE operations, as discussed in:
  • multiple places in https://www.sqlite.org/wal.html
  • http://sqlite.1065341.n5.nabble.com/Atomic-DELETE-index-optimisation-td99277.html - where someone encountered multiple issues with WAL and large DELETE operations
  • hint given to do manual checkpointing here (these refs look the same, giving both in case one goes down someday):
  • https://blog.xojo.com/2015/12/21/speed-up-sqlite-with-write-ahead-logging/
  • https://dev.to/lefebvre/speed-up-sqlite-with-write-ahead-logging-wal-do
• possible issues with attached databases, as discussed in:
  • https://www.sqlite.org/wal.html
  • https://stackoverflow.com/questions/39149065/sqlite-write-ahead-logging-wal-journal-mode-with-attached-database
• potential privacy issues:
  • https://www.digitalforensics.com/blog/forensic-analysis-of-sqlite-write-ahead-log-wal/
  • https://sqliteforensictoolkit.com/forensic-examination-of-sqlite-write-ahead-log-wal-files/
• some other drawbacks listed in https://www.sqlite.org/wal.html

Major advantages of using WAL (from https://github.com/xpbrew/cordova-sqlite-storage-help/issues/10):

• should alleviate possible blocking between readers and writers (most of the time), as discussed https://www.sqlite.org/wal.html
• could provide improved protection against possible database corruption as discussed in the following threads (see discussion and current disposition in https://github.com/xpbrew/cordova-sqlite-storage-help/issues/34):
  • http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
  • http://sqlite.1065341.n5.nabble.com/concurrent-writes-and-reads-to-from-DB-td42513.html
• seems to have proven itself in "across containers" ref: https://medium.com/@rbranson/sharing-sqlite-databases-across-containers-is-surprisingly-brilliant-bacb8d753054

I discovered some more resources from: https://www.google.com/search?q=how+does+sqlite+checkpoint+work

At this point I think that using WAL should be optional and should come with some warnings.

[brodycj]

To keep the comparison as fair as possible, I would like to add that semantics of VACUUM (https://sqlite.org/lang_vacuum.html), where space for deleted data is not always cleaned up right away, seems to apply with and without WAL. But without WAL, SQLite would keep the deleted data pages in some kind of a "free list" where they could be used again. My understanding is that there are multiple forms of "auto vacuum", but a full "manual" vacuum would be better.

[brodycj]

Here are some interesting SQLite WAL+VACUUM links that I found:

• https://www.google.com/search?q=sqlite+wal+vacuum
• https://stackoverflow.com/questions/13434128/vacuum-database-and-wal-mode
• http://sqlite.1065341.n5.nabble.com/VACUUM-and-WAL-td50196.html
• https://www.sqlite.org/tempfiles.html (plenty of info in this link!)