SQLite's Durability Settings Are a Mess
12 days ago
- #Database
- #Durability
- #SQLite
- SQLite's durability settings are confusing and poorly documented.
- Durability in databases ensures committed transactions are not lost even after crashes or power failures.
- SQLite provides configuration options like `journal_mode` and `synchronous` to control durability.
- Default `journal_mode` is DELETE, and default `synchronous` is FULL, but FULL in DELETE mode does not guarantee durability.
- For durability in DELETE mode, `synchronous` must be set to EXTRA.
- In WAL mode, `synchronous=FULL` provides durability, but the documentation and creator's statements contradict this.
- Libraries wrapping SQLite may override defaults, e.g., some Go drivers set `synchronous=NORMAL` in WAL mode, which lacks durability.
- macOS's `fsync` is not reliable by default; SQLite requires enabling `fullfsync` for proper durability.
- Programmers must explicitly set durability settings due to unclear defaults and potential breaking changes.
- SQLite should improve documentation by clearly mapping durability guarantees for each `journal_mode` and `synchronous` combination.