Search code examples
sqlitewal

WAL synchronization NORMAL vs. OFF

In the original documents for WAL the difference between FULL and NORMAL synchronization is clearly stated1:

Write transactions are very fast since they only involve writing the content once (versus twice for rollback-journal transactions) and because the writes are all sequential. Further, syncing the content to the disk is not required, as long as the application is willing to sacrifice durability following a power loss or hard reboot. (Writers sync the WAL on every transaction commit if PRAGMA synchronous is set to FULL but omit this sync if PRAGMA synchronous is set to NORMAL.)

But I can't find anywhere the effect of PRAGMA synchronous = OFF in WAL mode. I suspect it is the same as NORMAL. Anyone has an informed answer?

Solution

  • The documentation also says:

    In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint.

    So with OFF, there is no difference for normal transactions, but any checkpoint will be unsafe.