SQLite's Durability Settings Are a Mess

Summary

One of the most important properties of a database is durability. Durability means that after a transaction commits, you can be confident that, absent catastrophic hardware failure, the changes made by the commit won't be lost. This should remain true even if the operating system crashes or the system loses power soon after the commit. On Linux, and most other Unix operating systems, durability is ensured by calling the fsync system call at the right time. Durability comes at a performance cost, and sometimes applications don't need durability. Some applications can tolerate losing the last several seconds of commits in the event of a power failure, as long as the database doesn't end up corrupted. Thus, databases typically provide knobs to configure if and when they call fsync. This is fine, but it's essential that the database clearly documents what its default durability properties are, and what each configuration setting guarantees. Unfortunately, SQLite's documentation about its durability properties is far from clear. I cannot tell whether SQLite is durable by default, and if not, what are the minimal settings you need to use to ensure durability. The two relevant configuration options are journal_mode and synchronous. journal_mode has several possible values, but most people use either DELETE or WAL. synchronous has four possible values: EXTRA, FULL, NORMAL, and OFF. This is how I interpret SQLite's documentation after a careful reading: The default value of journal_mode is DELETE:The DELETE journaling mode is the normal behavior (source; archived)The default value of synchronous is FULL:If not overridden at compile-time, the default setting is 2 (FULL) (source; archived)The default value of synchronous is FULL even in WAL mode:If not overridden at compile-time, this value is the same as SQLITE_DEFAULT_SYNCHRONOUS. (source; archived)When journal_mode is DELETE, you need to set synchronous to EXTRA to get durability:EXTRA synchronous is like FULL with the addi...