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."

The WAL Principle

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

Checkpointing: Flushing Dirty Pages

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
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: The Recovery Algorithm

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);
    }
}

WAL vs Redo Log

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
SHOW innodb_log_file_size;   -- Default 48MB
SHOW innodb_log_files_in_group;  -- Default 2

tradeoff / Durability vs Performance

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 to WAL but not disk, losing at most ~3 seconds of transactions.

Practical WAL Management

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.conf and start PostgreSQL
-- recovery_target_time = '2026-08-18 12:00:00'

Synthesis

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.