Write-Ahead Logging and Crash Recovery

When a database promises durability — that committed data survives crashes — it's not fsyncing every row to disk. That would be prohibitively slow. Instead, databases use write-ahead logging (WAL) to batch writes into sequential I/O, then apply them lazily. Understanding WAL is understanding how databases balance performance with the promise of "once committed, always durable."

Log the change before applying it

Write-ahead logging states: before any change is applied to the data pages, the change must be written to the log. The log (WAL) is a sequential, append-only file that records all modifications.

The reason is simple: sequential writes are fast (100+ MB/s), random writes are slow (few MB/s). By writing the log sequentially, then applying changes to data pages later, you get the best of both worlds.

plaintext

Transaction modifies page → WAL record written sequentially → Page modified in memory
                                                          ↓
                                              Checkpoint writes dirty pages to disk

PostgreSQL stores WAL in pg_wal/ as 16MB segment files:

sql

-- Check WAL configuration
SHOW wal_level;                    # minimal, replica, logical
SHOW max_wal_size;                 # Default 1GB, can grow up to 2x
SHOW wal_compression;              # on/off, compresses WAL records
 
-- Monitor WAL generation
SELECT pg_current_wal_lsn();       # Current WAL position
SELECT pg_walfile_name(pg_current_wal_lsn());  # Current segment file

Checkpoints bound how much log recovery replays

A checkpoint writes all dirty (modified) data pages to disk and records the WAL position. After a checkpoint, all WAL before that position is no longer needed for crash recovery.

plaintext

Checkpoint timeline:
  [WAL records] [WAL records] [WAL records] [CHECKPOINT] [WAL records]
                  ↓                                        ↓
            Still needed for recovery              Recovery starts here

PostgreSQL checkpoints are triggered by:

max_wal_size reached (default 1GB, grows up to 2×)
checkpoint_timeout (default 5 minutes)
Manual CHECKPOINT command

sql

-- Checkpoint stats (PG ≤ 16; moved to pg_stat_checkpointer in 17+)
SELECT * FROM pg_stat_bgwriter;
 
-- Key fields:
-- checkpoints_timed: Checkpoints triggered by timeout
-- checkpoints_req: Checkpoints triggered by WAL size (too many = bad)
-- buffers_checkpoint: Pages written during checkpoints
-- buffers_backend: Pages written by backends (should be low)
-- buffers_alloc: Pages allocated
 
-- If checkpoints_req >> checkpoints_timed, increase max_wal_size

ARIES replays committed work, then undoes the rest

ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) is the recovery algorithm used by PostgreSQL, SQL Server, and most modern databases. It has three phases:

Phase 1: analysis

Scan the WAL from the last checkpoint to determine:

Which transactions were active at crash time
Which pages were dirty (modified but not yet written to disk)
The starting point for the redo pass

// Simplified ARIES analysis pass
void analysis_pass(const char *wal_dir, LSN checkpoint_lsn){
    LSN lsn = checkpoint_lsn;
    
    while (true){
        WALRecord *rec = read_wal_record(wal_dir, lsn);
        if (rec == NULL) break;
        
        if (rec->type == XLOG_CHECKPOINT){
            // Update checkpoint info
            checkpoint_info = rec->data;
        }
        
        if (rec->type == XLOG_TRANSACTION_START){
            // Mark transaction as active
            active_txns[rec->txn_id] = true;
        }
        
        if (rec->type == XLOG_COMMIT){
            // Mark transaction as committed
            active_txns[rec->txn_id] = false;
        }
        
        // Track dirty pages
        dirty_pages[rec->page_id] = lsn;
        
        lsn += rec->total_length;
    }
}

Phase 2: redo

Replay WAL records to bring the database to the state it was in at crash time. This is the key insight: even committed changes might not have been flushed to disk, so we must redo everything.

// Simplified ARIES redo pass
void redo_pass(const char *wal_dir, LSN start_lsn){
    LSN lsn = start_lsn;
    
    while (true){
        WALRecord *rec = read_wal_record(wal_dir, lsn);
        if (rec == NULL) break;
        
        // Check if the page's last modification is older than this record
        Page page = buffer_pool_get_page(rec->page_id);
        if (page->lsn < rec->lsn){
            // Apply the change
            apply_wal_record(page, rec);
            page->lsn = rec->lsn;
        }
        
        buffer_pool_release_page(page);
        lsn += rec->total_length;
    }
}

Phase 3: undo

Roll back transactions that were active (not committed) at crash time. These transactions' WAL records are undone in reverse order.

// Simplified ARIES undo pass
void undo_pass(ActiveTxnList *active_txns){
    for (int i = active_txns->count - 1; i >= 0; i--){
        TransactionId txn_id = active_txns->list[i];
        
        // Scan WAL backwards for this transaction's records
        LSN lsn = active_txns->last_lsn[txn_id];
        while (lsn > 0){
            WALRecord *rec = read_wal_record_reverse(lsn);
            if (rec->txn_id != txn_id) continue;
            
            if (rec->type == XLOG_TRANSACTION_START){
                // Done undoing this transaction
                break;
            }
            
            // Undo the change
            Page page = buffer_pool_get_page(rec->page_id);
            undo_wal_record(page, rec);
            buffer_pool_release_page(page);
            
            lsn = rec->prev_lsn;
        }
        
        // Write compensation log record (CLR) — never needs to be undone
        write_clr(txn_id, lsn);
    }
}

Redo logs and undo logs split the same job differently

Some databases call this the "redo log" rather than WAL. The concept is the same, but there are differences:

sql

-- PostgreSQL: WAL is the only mechanism
-- MySQL/InnoDB: Has both redo log and undo log
-- Redo log: crash recovery (like WAL)
-- Undo log: transaction rollback and MVCC
 
-- InnoDB redo log configuration (replaced by innodb_redo_log_capacity,
-- default 100MB, in MySQL 8.0.30+)
SHOW innodb_log_file_size;   -- Default 48MB
SHOW innodb_log_files_in_group;  -- Default 2

tradeoff / Durability vs Performance

fsync=on (default)
fsync=off
synchronous_commit=off

For most applications, the default (synchronous_commit=on with fsync=on) is correct. Only consider relaxing durability for bulk loading or analytics workloads where data loss is acceptable.

fsync=on guarantees durability but adds latency. fsync=off risks data loss on power failure but is 10-100× faster. synchronous_commit=off commits without waiting for the WAL flush: a database crash loses nothing, but an OS or power crash can lose up to 3 × wal_writer_delay, roughly 0.6 s of transactions.

Archive and monitor the log like production data

sql

-- Monitor WAL usage
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_used;
 
-- Archive WAL for point-in-time recovery
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'cp %p /archive/%f';
SELECT pg_reload_conf();
 
-- Create a base backup for PITR
SELECT pg_start_backup('base_backup');
-- Copy pg_basebackup to safe location
SELECT pg_stop_backup();
 
-- Recovery: create recovery.signal (recovery.conf was removed in
-- PG 12), set restore_command + recovery_target_time, and restart

Sequential writes buy both speed and durability

WAL is the foundation of database durability. By writing changes sequentially before applying them to data pages, databases achieve both performance and crash safety. ARIES recovery ensures that after a crash, committed transactions are preserved and uncommitted transactions are rolled back — exactly once. Understanding WAL mechanics helps you tune checkpoint frequency, manage archive storage, and diagnose performance bottlenecks.