> Use WAL mode
(For bonus points, also bump cache_size up to 20000 or so and set synchronous=NORMAL)
3 new SQLite databases each with 1 text column, inserting 1000 rows of 1 char per. Testing using .NET6 and latest copy of System.Data.SQLite.
Default / No Pragmas:
8806ms
WAL Mode Only:
2819ms
WAL Mode + Synchronous Normal:
44msOur reasoning goes something like - whatever happened approximately at the edge of the power-loss abyss is considered part of the explosion. We have never held out hope that we'd be able to get information to disk up until the last microsecond.
Letting WAL roll us back a few transactions is not a huge deal for our customers. Even if this breaks logical consistency with regard to external systems. We have many degrees of "redo" and the back office is always going to be a thing in our line of business.
We file this under the "edge cases not economically worth targeting" bucket and take our 2 ~free orders of magnitude.
Right, a power cut or kernel panic. It's certainly something to be aware of, but there are probably much more likely ways to lose some data (programming errors, etc).
Some loss around the edges (and between snapshots) is understood and accepted by all. We've made it clear to our customers that by making this trade-off, we can vastly simplify operational costs around the system (i.e. we only need 1 self-contained VM per environment).
I'm not sure this is true with all filesystems. I think there are some filesystems in which a crash during append can end up with the file enlarged, but the data not written (IIRC I saw something like this with XFS when I was working on a kernel module that kept crashing the kernel).
The SQLite WAL file itself has a running checksum that starts from the beginning so if you had an enlarged file or even a block of zero'd out bytes in the middle, SQLite would still recover gracefully. It recovers up to the last valid WAL frame that contains a set "commit" flag.
I love databases that choose safe defaults.
That said, many real-life applications of databases don't need to block for confirmation that the transaction has been durably committed -- ETL jobs, integration tests, most logging, etc.
When you discover that you can get a 20-50x speedup for basically free, it's like Christmas.