Write-Ahead Log

A write-ahead log is an append-only file that records the intent to change data before the change touches the actual table or page on disk. The rule it enforces is in the name: write to the log ahead of writing to the data. Postgres calls this the WAL, MySQL InnoDB calls it the redo log, SQLite calls it WAL mode, and SQL Server calls it the transaction log. They are all the same idea.

The reason it exists is that random writes to data files are slow and a crash in the middle of one leaves the file half-updated. Appending to a log is fast because it is sequential, and the log is the source of truth for what was supposed to happen. As long as the log entry made it to durable storage, the database can always finish or redo the work later.

Each entry usually describes a physical or logical change: page 4012 byte offset 80 changed from this to that, or this row was inserted into this table. Entries carry a monotonically increasing sequence number, called the LSN in Postgres and SQL Server, so recovery knows the exact order to replay them.

When a transaction modifies data, the database first writes the change into an in-memory log buffer and updates the page in the shared buffer cache. The actual data file on disk is not touched yet. When the transaction commits, the database calls fsync on the log file so the log records are physically on disk. Only after that fsync returns does the commit report success to the client.

The dirty data pages stay in memory and get written to the data files later, in the background, by a checkpoint process. A checkpoint flushes all dirty pages up to a known log position and records that position, so on recovery the database only needs to replay log entries after the last checkpoint instead of the entire history.

Recovery after a crash is a replay. The database reads the log from the last checkpoint forward, reapplies every committed change to the data files (redo), and rolls back any transaction that was in flight but never committed (undo). This is the ARIES algorithm that most relational engines follow. Because commit only depends on a sequential append plus one fsync, a write-ahead log also lets many transactions group their commits into a single disk flush, which raises throughput a lot.

You get a write-ahead log for free inside any serious database, so the real decisions are about tuning and about reusing the log. The big win beyond crash recovery is replication: the same log that recovers a crash can be streamed to replicas, which is exactly how Postgres streaming replication and MySQL binlog replication work. Change data capture tools like Debezium read the log to feed Kafka, so downstream systems see every change in order.

The cost is write amplification. Every change is written twice, once to the log and once to the data files, so a busy system does roughly double the disk writes. The commit fsync also adds latency, often a few milliseconds per commit on spinning disks, which is why group commit and fast SSDs matter. If you set the log to flush less aggressively, for example Postgres synchronous_commit off or MySQL innodb_flush_log_at_trx_commit set to 2, you trade a small window of possible data loss on a crash for much higher throughput.

Logs also need management. They grow until a checkpoint lets old segments be recycled or archived, and if archiving falls behind, the log can fill the disk and stall the database. A long-running transaction or a stuck replica can pin old log segments and cause the same problem.

Imagine a bank transfer that debits account A by 100 and credits account B by 100. Inside one transaction the database writes two log records, one for each row change, then a commit record, and fsyncs the log. At this point the data pages for A and B may still be sitting in memory only.

Now the power fails before those pages reach disk. On restart, the database finds the last checkpoint, reads forward, sees the commit record for the transfer, and redoes both row changes against the data files. The money moved exactly once and the books balance. If instead the crash happened after the debit record but before the commit record, recovery sees no commit, so it discards the partial work and account A keeps its 100. Either way the database is consistent, which is the whole point.