SQLite's WAL Mode: Durability vs. Performance Trade-off
SQLite's WAL (Write-Ahead Log) mode, often used for higher write throughput, compromises data durability compared to the default journal mode. The `synchronous` pragma controls how often fsync is called; the default is NORMAL. In NORMAL mode, WAL files are synced before each checkpoint, and the database file after, but most transactions lack sync operations. For applications where durability isn't critical, NORMAL is sufficient. For guaranteed durability across power loss, `synchronous=FULL` adds a WAL file sync after each transaction commit, increasing durability at the cost of write speed. This explanation, prompted by concerns about SurrealDB potentially sacrificing durability for benchmark performance, clarifies SQLite's approach.