Am I correct that out of the box it's implementing a full integrity around "commit" -- ie. The data has to fully hit the disk before the call returns?
For most applications I don't want this. Browser history for example is fine if the most recent transactions are lost on a power failure. Of course I do not want the file to be corrupted and lost.
At a previous employer we had similar issues with Chrome's internal SQLite database (Linux) in an environment with network mounted home directories. It was dealt with by a Chrome wrapper to change one of the internal flags on the user's SQLite database.
Possibly we may just have a case of a bad default here. Particularly in the context of other posts eg. "SQLite as a file format"
In the docs. I only find "PRAGMA synchronous=OFF." Did I dream it, or is there a similar setting to preserve the integrity of the file through write barriers, journaling etc., but not have a full fsync on transaction?
Because then on a power failure the file would contain "a stable commit" but not necessarily "the most recent commit" ... And it seems that for most SQLite integrations that is what we want?
Instead we're now all burning through SSDs for our trivial user settings files, a bit like everything is O_SYNC and the page cache was never invented.