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 addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss. (source; archived)
When journal_mode is WAL, FULL is sufficient for durability: With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction helps ensure that transactions are durable across a power loss (source; archived) Note that this is not mentioned under the definition of FULL, but rather further down in the documentation for synchronous .
Based on the above, I conclude that:
By default, SQLite is not durable, because the default value of journal_mode is DELETE, and the default value of synchronous is FULL, which doesn't provide durability in DELETE mode.
If you change journal_mode to WAL, then SQLite is durable, because synchronous=FULL provides durability in WAL mode.
However, a recent Hacker News comment by a user who credibly claims to be Richard Hipp, the creator of SQLite, says:
"In its default configuration, SQLite is durable."
"If you switch to WAL mode, the default behavior is that transactions ... are not necessarily durable across OS crashes or power failures"
That's literally the opposite of what the documentation seems to say!
A Hacker News commenter who agrees with my reading of the documentation asked Hipp how his comment is consistent with the documentation, but received no reply.
Hipp also says that WAL mode used to be durable by default, but it was changed after people complained about poor performance. This surprised me, since I had the impression that SQLite cared deeply about backwards compatibility, and weakening the default durability setting is a nasty breaking change for any application which needs durability.
There are a couple other pitfalls around SQLite durability that you should be aware of, though I don't necessarily blame the SQLite project for these:
Libraries that wrap SQLite can override the default value of synchronous . For example, the most popular Go driver for SQLite sets it to NORMAL when in WAL mode, which does not provide durability.
On macOS, fsync is nerfed to make macOS appear faster. If you want a real fsync, you have to make a different, macOS-specific system call. SQLite can do this, but it's off by default.
My takeaway is that if you need durability, you'd better set the synchronous option explicitly because who knows what the default is, or what it will be in the future. With WAL mode, FULL seems to suffice. As for DELETE mode, who knows if FULL is enough, so you'd better go with EXTRA to be safe. And if your application might be used on macOS, enable fullfsync .
The SQLite project ought to clarify their documentation. Since the meaning of synchronous depends on the value of journal_mode , I think it would be quite helpful to document the values of synchronous separately for each possible journal_mode , rather than mixing it all together. A table with synchronous values on one axis and journal_mode on the other which tells you if the combination provides durability would do wonders.
By the way, there are definitely many applications for which losing a few seconds of data in exchange for better performance is a great tradeoff, which is why SQLite and macOS have made the choices they have made. But programmers need to know what guarantees their tools provide, which is why unclear documentation and breaking previously-held assumptions is not cool.