SQL 102 🛢️ Write-Ahead Logging (WAL) and Redo/Undo Logs - KandZ

⏱️ Estimated read: 13 min | |

Every relational database that claims ACID compliance has to solve the same problem: how do you guarantee that a committed transaction survives a crash, and how do you undo a transaction that was interrupted mid-flight? The answer, across every major engine, is the same: write the change to a log before you write it to the data file. This is the write-ahead logging rule, and it is the foundation of durability and atomicity . The log is sequential, which makes it fast; the data pages are scattered, which makes them slow. By flushing the log at commit time and deferring the data page writes, the database gets durability without paying the cost of random I/O on every transaction .

The two log types serve opposite purposes. The redo log records what changed, so that a committed transaction can be replayed after a crash. The undo log records what the data looked like before the change, so that an uncommitted transaction can be rolled back, and so that concurrent readers can see a consistent snapshot of the data while a writer is modifying it. The recovery process is always the same sequence: roll forward through the redo log to reconstruct the state at the moment of the crash, then roll back the uncommitted transactions using the undo log . The checkpoint is the mechanism that bounds how much redo log must be replayed; it marks the point up to which all dirty pages have been flushed to disk .

Key point: WAL requires that the redo log record be flushed to disk before the corresponding data page. The redo log enables roll-forward recovery of committed changes; the undo log enables roll-back of uncommitted changes and MVCC. A checkpoint advances the point from which recovery must start, bounding the amount of redo that must be replayed.

Why WAL and the two logs exist

The durability problem. A transaction that commits must survive a crash. If the database wrote the data page directly to disk at commit time, it would have to wait for the random I/O to complete, which is orders of magnitude slower than a sequential log write. WAL solves this by writing a compact log record sequentially and flushing that, while the data page stays in the buffer pool as a “dirty page” until a checkpoint or buffer pressure forces it out . The commit is durable because the log record is on disk; the data page can be written later because the log can reconstruct it.

The atomicity problem. A transaction that fails halfway through must be undone. If the database has already written some of its changes to data pages, those changes must be reversed. The undo log stores the before-image of every modified row, and the rollback process applies those before-images to restore the original state . Without the undo log, there would be no way to reverse a partial transaction after a crash.

The consistency problem. Before WAL, a crash during a page write could leave the page half-written and corrupted. WAL solves this by logging the full page content on the first modification after a checkpoint (full-page writes), so that recovery can restore a consistent page even if the on-disk version is torn . The log is the source of truth; the data page is a cache that can be reconstructed.

The checkpoint problem. If the database had to replay the entire log from the beginning of time, recovery would take as long as the database has existed. The checkpoint is the mechanism that bounds this. A checkpoint flushes dirty pages to disk and records the log sequence number (LSN) up to which everything is durable. Recovery starts from the last checkpoint, not from the beginning .

a. The redo log: roll-forward recovery

The redo log is a sequential record of every change made to the database. In InnoDB, it is a circular set of files (#ib_redo* in MySQL 8.0.30+, or ib_logfile0/ib_logfile1 before) that record physical page modifications . In PostgreSQL, it is the WAL segment files in pg_wal, each 16 MB, containing records that describe the changes . In SQL Server, it is the transaction log file, with log records that describe the modification and the LSN .

The WAL rule is enforced by the engine: a dirty page cannot be flushed to disk until the redo log records that describe its changes have been flushed. InnoDB implements this by tracking the LSN of each page and comparing it to the LSN of the flushed log. If the page’s LSN is greater than the flushed log LSN, the page cannot be written . SQL Server has explicit logic that prevents a dirty page from being flushed before its log record .

The recovery process starts at the last checkpoint. InnoDB reads the checkpoint LSN, scans the redo log from that point forward, and applies every record to the corresponding page in the buffer pool . PostgreSQL reads pg_control to find the checkpoint position, then scans the WAL from that point . SQL Server rolls forward from the Minimum Recovery LSN (MinLSN) recorded in the checkpoint . After the roll-forward, the data pages are in the state they were at the moment of the crash, including changes from transactions that had not committed.

b. The undo log: roll-back and MVCC

The undo log stores the before-image of every modified row. InnoDB stores it in undo tablespaces, organized into rollback segments and undo slots . Oracle stores it in undo tablespaces with undo segments . PostgreSQL does not have a separate undo log in the same sense; it uses MVCC with tuple versions in the heap, and the “undo” is the old tuple version that is retained until vacuumed . The effect is the same: the database can reconstruct the previous state of a row.

The undo log serves two purposes. The first is rollback: when a transaction aborts or when recovery finds an uncommitted transaction, the undo records are applied to reverse the changes . The second is MVCC: a concurrent reader can see the old version of a row while a writer is modifying it, because the undo log (or the old tuple version) contains the before-image . This is how databases provide consistent reads without blocking writers.

The undo log is itself protected by redo. Every change to the undo log is recorded in the redo log, so that after a crash, the undo log can be reconstructed before it is used to roll back uncommitted transactions . The sequence is: roll forward the redo (which restores both data pages and undo pages), then use the restored undo to roll back the uncommitted transactions.

c. Checkpoints and the recovery boundary

A checkpoint is the operation that flushes dirty pages to disk and records the LSN up to which everything is durable. InnoDB writes a checkpoint record to the redo log header, marking the point before which the redo log is no longer needed for recovery . PostgreSQL writes the checkpoint position to pg_control and flushes the WAL . SQL Server writes a checkpoint record with the MinLSN, which is the minimum of the checkpoint start LSN, the oldest active transaction LSN, and the oldest replication transaction LSN .

The checkpoint interval determines the recovery time. A more frequent checkpoint flushes dirty pages more often, which means less redo log to replay after a crash, which means faster recovery . But more frequent checkpoints also mean more I/O during normal operation, because the dirty pages are being written more often. The checkpoint_timeout and max_wal_size parameters in PostgreSQL control this balance; the innodb_redo_log_capacity in MySQL controls the size of the circular log and therefore how often checkpoints are forced .

The checkpoint also determines the point from which the log can be truncated or recycled. InnoDB reuses the circular redo log files once the checkpoint has advanced past them . SQL Server truncates the log in the simple recovery model at checkpoint, but in the full recovery model the log is only truncated by a log backup . PostgreSQL recycles WAL segments once they are no longer needed for recovery or archiving .

Complete Example Session

The following session traces a transaction through the WAL and recovery process in InnoDB, showing the redo log, the undo log, and the checkpoint.

// ============================================
// PART 1: THE TRANSACTION
// ============================================
// T1: BEGIN
// T1: UPDATE accounts SET balance = balance - 100 WHERE id = 1
// T1: UPDATE accounts SET balance = balance + 100 WHERE id = 2
// T1: COMMIT

// ============================================
// PART 2: THE WRITE PATH
// ============================================
// 1. The changes are written to the log buffer in memory
// 2. The undo log records the before-image:
//    id=1, balance=500 (was 500, now 400)
//    id=2, balance=200 (was 200, now 300)
// 3. The redo log records the physical changes to the pages
// 4. At COMMIT, the redo log is flushed to disk (fsync)
// 5. The data pages in the buffer pool are now "dirty"
//    but they are not flushed to disk yet

// ============================================
// PART 3: THE CRASH
// ============================================
// After T1 commits, a second transaction T2 begins:
// T2: BEGIN
// T2: UPDATE accounts SET balance = balance - 50 WHERE id = 1
// T2: (crash before COMMIT)

// ============================================
// PART 4: RECOVERY — ROLL FORWARD
// ============================================
// InnoDB reads the checkpoint LSN
// It replays the redo log from the checkpoint to the end
// T1's changes are applied (committed)
// T2's changes are applied (uncommitted, but redo replays everything)
// The data pages in memory now match the crash state

// ============================================
// PART 5: RECOVERY — ROLL BACK
// ============================================
// InnoDB examines the undo log and the transaction table
// T1 is committed: no rollback needed
// T2 is uncommitted: the undo log is applied
// T2's change to id=1 (balance - 50) is reversed
// The database is now consistent

// ============================================
// PART 6: THE CHECKPOINT
// ============================================
// A checkpoint flushes the dirty pages to disk
// The checkpoint LSN advances
// The redo log before the checkpoint can be recycled
// Recovery after this checkpoint starts from the new LSN
// It does not need to replay T1 or T2

The six parts cover the transaction, the write path, the crash, the roll-forward, the roll-back, and the checkpoint. What the session demonstrates is that the redo log replays everything (committed and uncommitted), and the undo log is what distinguishes the committed transactions from the uncommitted ones.

Quick Reference

WAL Rule

Rule	Consequence
Log before data page	Durability without random I/O
Sequential log write	Fast commit
Deferred data page write	Batch I/O at checkpoint
Full-page write after checkpoint	Torn-page protection

Redo vs Undo

Aspect	Redo Log	Undo Log
Content	After-image (what changed)	Before-image (what it was)
Purpose	Roll-forward committed changes	Roll-back uncommitted changes
MVCC	No	Yes (old versions)
Recovery phase	First	Second

Checkpoint

Engine	Checkpoint Record	Recovery Start
InnoDB	Redo log header	Last checkpoint LSN
PostgreSQL	`pg_control`	Checkpoint LSN
SQL Server	Checkpoint log records	MinLSN
Oracle	Control file	Checkpoint SCN

Key Configuration

Engine	Parameter	Effect
InnoDB	`innodb_redo_log_capacity`	Redo log size, checkpoint frequency
PostgreSQL	`max_wal_size`	WAL size before checkpoint
PostgreSQL	`checkpoint_timeout`	Maximum time between checkpoints
SQL Server	`recovery interval`	Target recovery time

Best Practices

✅ Do This:

# InnoDB: size the redo log for the write workload
innodb_redo_log_capacity = 2G

# PostgreSQL: balance checkpoint frequency and recovery time
max_wal_size = 4GB
checkpoint_timeout = 15min

❌ Don’t Do This:

# InnoDB: a tiny redo log forces frequent checkpoints
innodb_redo_log_capacity = 64M # write stalls

# PostgreSQL: an enormous max_wal_size means long recovery
max_wal_size = 64GB # slow crash recovery

Common Pitfalls

Pitfall	Why It Happens	Fix
Long crash recovery	Redo log too large, checkpoints too infrequent	Reduce `max_wal_size` or `innodb_redo_log_capacity`
Write stalls	Redo log too small, checkpoints too frequent	Increase log size
Torn page corruption	Full-page writes disabled	Enable `full_page_writes`
Disk lies about fsync	Hardware cache without battery	Use durable storage

Real-World Examples

1. InnoDB redo log size

SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

2. PostgreSQL WAL directory

$PGDATA/pg_wal/

3. SQL Server checkpoint

CHECKPOINT;

4. Oracle undo tablespace

CREATE UNDO TABLESPACE undotbs_01 DATAFILE '...' SIZE 2M;

5. PostgreSQL checkpoint

CHECKPOINT;

6. InnoDB crash recovery log

[InnoDB] Starting crash recovery.
[InnoDB] Applying a batch of 1234 redo log records...
[InnoDB] Rolling back 2 incomplete transactions

7. Full-page writes

full_page_writes = on

8. SQL Server recovery model

ALTER DATABASE db SET RECOVERY FULL;

9. InnoDB flush at commit

innodb_flush_log_at_trx_commit = 1

10. PostgreSQL synchronous commit

synchronous_commit = on

Visual

The WAL Write Path

┌──────────────────────────────────────────────┐
│  UPDATE                                   │
│         │                                    │
│         ▼                                    │
│  Log buffer (redo record)                    │
│  Undo log (before-image)                     │
│         │                                    │
│         ▼                                    │
│  COMMIT → flush redo log to disk             │
│         │                                    │
│         ▼                                    │
│  Data page stays dirty in buffer pool        │
│  (flushed later at checkpoint)               │
└──────────────────────────────────────────────┘

Recovery Sequence

┌──────────────────────────────────────────────┐
│  CRASH                                       │
│         │                                    │
│         ▼                                    │
│  1. Read checkpoint LSN                      │
│         │                                    │
│         ▼                                    │
│  2. Roll forward (redo log)                  │
│     → data pages match crash state           │
│         │                                    │
│         ▼                                    │
│  3. Roll back (undo log)                     │
│     → uncommitted transactions reversed      │
│         │                                    │
│         ▼                                    │
│  4. Consistent state                         │
└──────────────────────────────────────────────┘

Checkpoint Boundary

┌──────────────────────────────────────────────┐
│  Redo log:                                   │
│  [checkpoint LSN] ──── redo records ────▶    │
│         │                                    │
│         ▼                                    │
│  Recovery starts here                        │
│  Records before this point are recycled      │
└──────────────────────────────────────────────┘

Summary

Item	Value
WAL rule	Log before data page
Redo log	After-image, roll-forward
Undo log	Before-image, roll-back and MVCC
Recovery	Roll forward, then roll back
Checkpoint	Flushes dirty pages, bounds recovery
InnoDB redo	Circular files, `#ib_redo*`
PostgreSQL WAL	`pg_wal` segments, 16 MB each
SQL Server log	Transaction log, LSN, MinLSN
Oracle undo	Undo tablespace, undo segments

Key takeaways:

WAL is the durability mechanism. The redo log is flushed at commit, and the data page is written later. The log is sequential and fast; the data page is random and slow. This is why a committed transaction survives a crash without waiting for the data page .
The redo log is for roll-forward. It records every change, committed or not. Recovery replays it from the last checkpoint to reconstruct the state at the moment of the crash .
The undo log is for roll-back and MVCC. It stores the before-image of modified rows, so that uncommitted transactions can be reversed and concurrent readers can see a consistent snapshot .
The checkpoint bounds recovery. It flushes dirty pages and records the LSN up to which everything is durable. Recovery starts from the checkpoint, not from the beginning of time .
The undo log is protected by redo. Every change to the undo log is itself logged in the redo log, so that after a crash, the undo log can be reconstructed before it is used to roll back .
The redo log size is a trade-off. A larger log reduces checkpoint frequency and write stalls, but increases recovery time. A smaller log does the opposite. The right size depends on the write workload and the acceptable recovery window .

Remember: Write-ahead logging is not a single log. It is a system of three components—the redo log, the undo log, and the checkpoint—that together provide durability, atomicity, and consistency. The redo log records what changed and is replayed after a crash. The undo log records what the data was and is used to reverse uncommitted changes and to provide consistent reads. The checkpoint marks the boundary between what must be replayed and what has been safely written. Every major database implements this same architecture with different names and different file formats, but the logic is identical: write the log first, replay it on recovery, undo what did not commit, and start from the last checkpoint.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!