Write-Ahead Logging (WAL)

The Durability Problem

When you COMMIT a transaction, the database promises that your data is safe. But writing data to disk is slow (random I/O). Writing to memory is fast but volatile (lost on power failure).

Write-Ahead Logging (WAL) solves this by writing changes to a sequential log file on disk before applying them to the main data files.

[!IMPORTANT] Rule: The WAL record describing a page change must be durable before that changed page is allowed to reach durable storage. Dirty pages can already be modified in memory.

How WAL Works

1. Transaction Start: User begins modifying data.
2. Log Record: DB creates a log entry describing the change (e.g., "Page 42, Offset 10: Change 'A' to 'B'").
3. Append to WAL: This small record is appended to the wal_buffer in memory.
4. Commit: User commits.
5. Flush: The wal_buffer is fsync()ed to disk. Transaction is now durable.
6. Apply to Data: Later (background process), the change is written to the actual data file (checkpointing).

Why is this faster?

Append-only writes are sequential I/O, which is significantly faster than the random I/O required to update data pages scattered across the disk.

Crash Recovery

What happens if the server crashes right after Step 5 but before Step 6?

1. Restart: DB starts up and sees the data file is old.
2. Replay: It reads the WAL from the last Checkpoint.
3. Redo: It reapplies all changes found in the WAL to the data pages.
4. Consistent State: Recovery restores a transactionally valid state according to durable log records and commit status; uncommitted work does not become visible just because a physical change is replayed.

Key Concepts

LSN (Log Sequence Number)

Every WAL record has a unique ID, the LSN. It strictly increases. Data pages store the LSN of the last change that modified them.

• If PageLSN < WAL_LSN, the page is stale and must be updated.
• If PageLSN >= WAL_LSN, the page is up-to-date.

Checkpointing

The WAL grows forever. We can't replay everything on startup. Checkpointing is the process of:

1. Flushing all dirty pages from memory to the data file.
2. Recording a "Checkpoint" record in the WAL.
3. Recycling/removing older segments only when recovery, replication slots, and archiving no longer need them.

Tradeoff:

• Frequent Checkpoints: Fast recovery, but slower runtime performance (more disk I/O).
• Infrequent Checkpoints: Fast runtime, but slow recovery (scanning GBs of logs).

WAL in Replication

WAL isn't just for crash recovery. It's the standard way to replicate data.

Primary-Replica Replication:

1. Primary writes to local WAL.
2. Primary sends WAL records to Replica.
3. Replica applies WAL records to its own data files.

This is basically "Continuous Recovery" on the Replica.

Code Example: Simplified WAL Implementation

import os
import struct

class WriteAheadLog:
    def __init__(self, log_file="db.wal"):
        self.log_file = log_file
        self.file = open(self.log_file, "ab") # Append binary mode
    
    def log_change(self, transaction_id, key, value):
        # Create a log record: (TransID, KeyLen, Key, ValLen, Value)
        # Using struct to pack binary data
        key_bytes = key.encode('utf-8')
        val_bytes = value.encode('utf-8')
        
        record = struct.pack(
            f"I I {len(key_bytes)}s I {len(val_bytes)}s", 
            transaction_id, 
            len(key_bytes), 
            key_bytes, 
            len(val_bytes), 
            val_bytes
        )
        
        # 1. Write to OS buffer
        self.file.write(record)
        
        # 2. Force flush to disk (fsync) - Critical for durability!
        self.file.flush()
        os.fsync(self.file.fileno())
        
        print(f"Logged Transaction {transaction_id}: {key}={value}")

    def close(self):
        self.file.close()

# Usage
wal = WriteAheadLog()
wal.log_change(101, "user:1:balance", "500")
# This writes one record; recovery parsing and transaction commit markers are not implemented.

Production Tuning

PostgreSQL wal_sync_method

• fsync: One supported synchronization method; the default depends on the operating system.
• fdatasync: Synchronizes data and metadata required to retrieve it; performance depends on the platform and storage.
• open_datasync: Uses O_DSYNC flag.
• open_sync: Uses O_SYNC flag.

MySQL (InnoDB) innodb_flush_log_at_trx_commit

• 1 (Default): Flush to disk on every commit. ACID compliant. Slowest.
• 0: Writes/flushes occur periodically. A crash can lose recent transactions; scheduling and configuration affect the interval.
• 2: Write to OS cache on commit, flush every 1s. Can lose data on OS crash, but safe if DB crashes.

Interview Tips 💡

• "Why is WAL faster than writing directly?" — Sequential I/O vs Random I/O.
• "What is a Checkpoint?" — Explain it as a way to truncate the log and speed up recovery.
• "Can I disable WAL?" — Yes (e.g., Unlogged Tables in Postgres), for temporary data where performance > durability.
• "How does replication relate to WAL?" — It's just shipping the log. Log Shipping.

Related Concepts

• ACID vs BASE
• Database Replication
• LSM Trees (Use WAL for MemTable durability)

Source and practical check

PostgreSQL WAL introduction explains write-ahead ordering; WAL configuration explains durability settings. The Python sketch omits checksums, framing, torn-write handling, commit records, and replay. Test crashes at record boundaries and mid-record before calling a custom log recoverable.