# Write-Ahead Logging (WAL) Write-Ahead Logging is a durability and crash-recovery technique used by most modern databases. The rule is simple: before any change to the database file is committed, a record of that change is appended to a separate log file and persisted to disk. The actual data file is updated later. If the process crashes mid-write, the database can replay the log on restart and reach a consistent state. ## Why It Exists Writing directly to a data file in place is fast but fragile; a crash mid-update leaves the file half-written and unrecoverable. WAL makes durability cheap and crash recovery automatic by separating "intent to change" (the log) from "the change itself" (the data file). The log is append-only, sequential, and easy to fsync; the data file can be updated lazily. ## Core Properties - **Append-only**: writes go to the end of the log; sequential I/O is fast on every storage medium - **Durable on commit**: an `fsync` on the log is enough to guarantee the change survives a crash - **Replayable**: on restart, the engine replays unflushed log records to bring the data file up to date - **Concurrent reads**: readers can keep reading the data file at the last consistent snapshot while writers append to the log ## Where It Shows Up - **[[SQLite]]**: `PRAGMA journal_mode=WAL` switches the engine from rollback journal mode to WAL, enabling concurrent reads while a writer is active - **[[PostgreSQL]]**: WAL is core; streaming replication, point-in-time recovery, and crash recovery all build on it - **MySQL/InnoDB**: redo log plays the same role - **[[Litestream]]**: piggybacks on SQLite's WAL to replicate changes to object storage ## Trade-offs - **Two writes per change** (log first, data file later) but the log writes are sequential and the data writes are batched, so throughput usually goes up, not down - **Log must be checkpointed**: at some point the log entries must be applied to the data file and the log truncated, otherwise it grows unbounded - **Slight crash-recovery latency**: replay on startup takes time proportional to un-checkpointed log size ## The Bigger Idea WAL is the canonical example of a broader pattern: separate the *intent* of a state change from its *application*, persist the intent first, and apply lazily. The same idea drives event sourcing, message queues, and (more loosely) Git's reflog. ## References - SQLite WAL documentation: https://www.sqlite.org/wal.html - PostgreSQL WAL: https://www.postgresql.org/docs/current/wal-intro.html - https://en.wikipedia.org/wiki/Write-ahead_logging ## Related - [[ACID]] - [[Atomicity]] - [[SQLite]] - [[PostgreSQL]] - [[Litestream]] - [[Database]]