SQLite WAL Mode Default Settings Compromise Durability by Skipping Fsync on Commits
By
Bogdanp
9mo ago· 2 min readenInsight
65/100
Toasty
Bagelometer↗
Toasted just enough. A reliable bake, gently seasoned.
Score65TypeanalysisSentimentneutral
Summary
SQLite's WAL (Write-Ahead Logging) mode with default synchronous=NORMAL setting does not perform fsync operations on each commit, which can result in potential data loss during power failures or system crashes. The article explains that while WAL mode improves write throughput, it sacrifices durability under default configurations, with fsync operations only occurring during checkpoints rather than every transaction commit.
Key quotes
· 4 pulledSQLite has a WAL mode (the default is journal mode), but you're likely using it if you want higher write throughput
SQLite also has a PRAGMA called synchronous which configures how fsync is called. The default is NORMAL
WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash
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
SQLite when used with WAL doesn’t do fsync unless specified.
