SQLite (with WAL) doesn't do `fsync` on each commit under default settings
13 points by av
13 points by av
Man I was just about to correct you for being wrong on the default mode, it looks like you discovered that =D
But yes, NORMAL has not been the default since SQLite3
thanks! your post inspired me to write this, but I wasn’t aware it was a compile time flag and different on macOS
Indeed, I don’t know if this is actually the case or not, but it might be NORMAL on the macOS shipped version because technically the behaviour on FULL would not be any different without setting the full fsync option because macOS decided to cheat a bit with fdatasync. That is just a guess though tbh.
Isn’t this exactly why you would run with WAL enabled (ie trade off some durability for higher performance)?
edit: WAL mode is apparently faster even with synchronous=FULL..
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.)