Write-Ahead Log (WAL)

The Write-Ahead Log is, in concept, beautifully simple: before any change is made to the data files, write a record describing the change to a separate append-only log, and fsync that log. On crash, replay the log to reconstruct the data files.

This solves a fundamental storage problem: data files are large, structured, and randomly accessed. Writing a small update to a 1TB file is expensive — you have to seek to the right page, modify it, and write it back, ideally atomically. But random I/O with crash safety is hard.

The WAL turns this into sequential I/O, which is dramatically faster and crash-safe:

1. Transaction begins; the engine stages changes in memory.
2. As changes are made, they're appended to the WAL (an append-only file).
3. On COMMIT, the WAL is fsynced — the changes are now durable on disk.
4. The data files are updated later, in the background ('checkpointing').
5. If a crash happens, the WAL is replayed on restart to bring the data files up to date.

The key insight: durability is guaranteed by the WAL, not by the data files. The data files can lag behind the WAL — that's fine, because the WAL is the source of truth that can always be replayed. As long as the WAL is fsynced before COMMIT returns, the transaction is durable.

The single most important setting in any transactional database is the fsync policy on the WAL. The guarantee chain is:

• The application calls COMMIT.
• The database appends the commit record to the WAL.
• The database calls fsync() on the WAL file.
• The kernel flushes the WAL to physical storage.
• Only after fsync returns does the database tell the application 'committed.'

If fsync is honored, the transaction is durable — a power loss immediately after COMMIT returns will not lose the transaction. The WAL is on disk; on restart, it's replayed and the data files are brought up to date.

If fsync is disabled (e.g., synchronous_commit = off in PostgreSQL), COMMIT can return before the WAL is on disk. This is dramatically faster but trades durability for speed: a power loss can lose the last few committed transactions. Acceptable for some workloads (analytics, caches) — unacceptable for others (payments).

Related settings that affect durability:

• wal_level (PostgreSQL) — minimal, replica, or logical. Determines how much info is in the WAL (more for replication, even more for logical decoding).
• synchronous_standby_names — if set, COMMIT waits for at least one synchronous standby to confirm receipt of the WAL. Provides durability across machines.
• full_page_writes — writes the full page image on first modification after a checkpoint, defending against partial page tears. Slightly slower but safer.

The lesson: tune the fsync / synchronous_commit knobs deliberately, knowing exactly which guarantee you're trading for speed.

If every change is in the WAL, why do we need data files at all? Because the WAL is optimized for appends, not for queries. To answer SELECT * FROM users WHERE id = 42, you need an indexed, structured data file — not a sequential log.

The bridge between WAL and data files is the checkpoint:

• Periodically (every few minutes by default), the database writes all dirty buffers from shared_buffers to the data files.
• It records the WAL position (LSN — Log Sequence Number) at which the checkpoint occurred.
• WAL records older than the checkpoint LSN are no longer needed for crash recovery and can be recycled.

On crash recovery:

1. The database reads the last checkpoint record from the WAL.
2. It replays the WAL forward from that point, applying each change to the data files.
3. Uncommitted transactions (those without a commit record) are rolled back via the undo log (in PostgreSQL, the abort is recorded in the WAL itself).

The checkpoint bounds recovery time: you only need to replay WAL from the last checkpoint. If checkpoints are far apart, recovery takes longer; if too frequent, they slow down normal operation. PostgreSQL auto-tunes this with checkpoint_timeout (default 5 minutes) and max_wal_size.

This is also why pg_start_backup() and pg_stop_backup() for backups interact with the WAL — they create a checkpoint and ensure WAL archiving captures everything needed to make the backup consistent.

Different databases call the WAL by different names, but the concept is identical:

• PostgreSQL — pg_wal/ (formerly pg_xlog/). Records every change. wal_level=logical enables logical decoding for CDC.
• MySQL/InnoDB — separate redo log (InnoDB's WAL) and binlog (MySQL server-level, used for replication). They're different logs and reconciled by an internal XA.
• Oracle — redo log files, in groups for rotation. Archive logs (saved redo logs) enable PITR.
• SQL Server — transaction log (.ldf file).
• Cassandra — CommitLog, similar concept; SSTables are the equivalent of data files. Same LSM pattern.
• RocksDB / LevelDB — also use a WAL, similar to Cassandra.

The architectural pattern is universal: sequential log for durability, structured files for queries, background process to bridge them. Whether you're debugging PostgreSQL recovery, MySQL replication, or Cassandra compaction, the underlying mechanism is the same.

This is also why the WAL is the foundation of the transactional outbox pattern: write your business data AND an event-to-publish row in the same transaction (so they're in the same WAL fsync), then a separate process reads the outbox table and publishes events to Kafka. Because the outbox write is in the same WAL as the business write, either both survive or neither does — atomicity across the database and the message queue, without 2PC.

Mental model. Think of Write-Ahead Log (WAL) as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Write-Ahead Log (WAL) mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”

Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.

Answer this without notes: When would you choose Write-Ahead Log (WAL), and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.

Numerical sanity check

A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.