WAL
How it works
1. Each modification produces a log record (WAL record) describing the change. 2. Log records are appended sequentially to the log file and flushed to durable storage (fsync) BEFORE the actual data files are changed. 3. A transaction commits the moment its commit record is durably written to the log — the changed data pages may reach disk later. 4. A single log fsync can commit many small, concurrent transactions. 5. A checkpoint transfers applied changes from the log into the actual data files and lets the old log be truncated (or archived). 6. After a crash the recovery manager replays committed changes from the log (REDO) and rolls back uncommitted ones (UNDO). WAL also enables online backups and point-in-time recovery (PITR) by archiving and replaying the log.
Problem solved
How to guarantee durability and atomicity of committed transactions despite a crash, without the costly flushing of every changed data page to disk on each commit. The naive approach (writing changes in place) loses data on a mid-write crash or forces many random disk writes.
Components
Sequentially appended entries describing each data change; flushed to durable storage before the data files are modified.
Operation that transfers applied changes from the log into the actual data files, allowing the old log to be truncated or archived.
After a crash, replays changes of committed transactions from the log (REDO) and rolls back changes of uncommitted transactions (UNDO).
Implementation
Shared-memory-based implementations (e.g. SQLite) require a local filesystem; WAL does not work correctly over NFS/network filesystems.
If checkpoints cannot run (e.g. long-running readers hold an old snapshot), the log grows without bound, consuming space and lengthening recovery.
Durability guarantees assume storage honors fsync faithfully. Lying/buffered fsync (e.g. in cheap hardware or some filesystems) can lose committed data after a power failure.
WAL works best for small transactions; for transactions on the order of hundreds of MB the rollback journal mode can be faster (SQLite).
Evolution
Mohan et al. (IBM) formalize a Write-Ahead Logging-based recovery method with fine-granularity locking and partial rollbacks. Used in IBM Db2, Microsoft SQL Server and others.
SQLite 3.7.0 (2010-07-21) introduces WAL mode as an alternative to the rollback journal: more concurrency (readers do not block the writer), more sequential I/O and fewer fsync() calls.
Hyperparameters (configurable axes)
How often the log is flushed into the data files. Less frequent checkpoints = less background I/O but a larger log and longer recovery.
Whether a commit waits for the log to be durably flushed. Synchronous commit gives full durability at the cost of latency; asynchronous increases throughput at the risk of losing the most recent transactions.
Maximum log size that triggers a checkpoint/truncation. Prevents excessive growth of the WAL file.
Parallelism
The log is appended sequentially, giving writes a sequential character (cheap fsync) but serializing the log stream. A single log fsync can, however, commit many concurrent transactions (group commit).
Hardware requirements
WAL is a software technique independent of accelerators; it benefits mainly from fast sequential writes to durable storage (SSD/NVMe) and efficient fsync.