How safe is SQLite WAL on power failures?

Question

In the SQLite documentation on the write-ahead-log feature introduced in version 3.7, there are some comments which confused me a bit.

The linked page says "syncing the content to the disk is not required, as long as the application is willing to sacrifice durability following a power loss". Then a couple of paragraphs down, it says "Checkpointing does require sync operations in order to avoid the possibility of database corruption following a power loss or hard reboot"".

So is my database at greater risk of corruption on power loss if I use WAL?

Answer 1

To answer fully, we need to know what you have PRAGMA synchronous set to, as this affects when fdatasync()is called and thus when the buffers flushed out on the physical drive.

When you quote "as long as the application is willing to sacrifice durability following a power loss", this is referring to having synchronous=NORMAL. Here the WAL is only synchronized to disk when checkpointing happens (one fdatasync() for the WAL and one for the main DB after it's merged). You should be protected against corruption pretty well, but there may be some writes that never made it to the platter and are thus lost: hence the lost durability. The upside though is much less of the slow fdatasync() to actually sync the data.

To have best resiliance against data being lost, you might want synchronous=FULL. This re-gains durability, but the cost is one fdatasync() per write transaction. However, this is still better than non-WAL mode where there would be two fdatasync() calls -- one for the transaction journal and one for the main DB.

Answer 2

There is no increased risk of corruption with WAL (since it uses sync operations when checkpointing).

However, if there is a crash (power loss or hard reboot) you will lose any transactions since the last checkpoint; that's what is meant by "sacrificing durability."