How safe is SQLite WAL on power failures?

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 reb开发者_开发技巧oot"".

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

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.

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."

As long as sync-calls to your OS work perfectly, you have zero risk of a corrupted db. However, this may or may not be the case - see the sqlite documentation for a more lengthy explanation about this.

To correct Doug Currie (see Post of MattR): You only risk loosing transactions if @synchronous=NORMAL@. If @synchronous=FULL@ (which is default) you do not sacrifice durability. See http://www.sqlite.org/draft/wal.html for more details on this.

I believe WAL journaling is in fact safer than 'classic' journaling (in case of imperfect system sync), because the chance that the db does something critical in a given moment is lower as far as I understand WAL journaling. However I currently have no hard data to support this.