SQLite (with WAL) doesn't do `fsync` on each commit under default settings
17 days ago
- #Durability
- #WAL
- #SQLite
- SQLite's WAL mode (default is journal mode) is used for higher write throughput.
- SQLite has a PRAGMA called synchronous which configures how fsync is called, with the default being NORMAL.
- In WAL mode with synchronous=NORMAL, the WAL file is synchronized before each checkpoint, but no sync operations occur during most transactions.
- synchronous=NORMAL in WAL mode loses durability; transactions might roll back after a power loss or system crash.
- For durability, use synchronous=FULL in WAL mode, which ensures an fsync on each commit.
- synchronous=FULL adds an extra WAL sync after each transaction to ensure durability across power loss.