Skip to main content

Write-Ahead Log (WAL) Explained: Database Durability, Recovery and Replication

Calculating read time…

Every database you have ever used — PostgreSQL, MySQL, SQLite, MongoDB, Cassandra, etcd — has one thing in common. They all rely on a mechanism called the Write-Ahead Log.

It is the single most important concept in database engineering. Yet most developers have never heard of it. They just trust that when they commit a transaction, the data is safe. WAL is why that trust is justified.

WAL is also the backbone of replication, point-in-time recovery, distributed consensus (Raft/Paxos), event streaming (Kafka), and change data capture (CDC). Once you understand WAL deeply, you understand how the entire data world works. Let's go!

💡 WAL Everywhere — Real Systems That Use It

🐘 PostgreSQL: WAL lives in pg_wal/ — every transaction is WAL-logged
🐬 MySQL InnoDB: "Redo Log" is MySQL's WAL implementation
📦 Apache Kafka: The entire Kafka architecture IS a distributed WAL
🔵 etcd: Uses WAL + Raft consensus for Kubernetes state
🪨 RocksDB: WAL protects its LSM-tree memtable writes
☁️ Amazon DynamoDB, Google Spanner: Both use WAL internally
🎯 SQLite: WAL mode is its recommended write strategy

If you have ever used any of these systems, WAL has silently saved your data.

📓 Section 1: Think of WAL Like a Surgeon's Notes + Diary

Before we dive into bits and bytes, let's build a perfect mental model using two simple real-world analogies.

✏️ Analogy 1: The Surgeon's Checklist

Imagine a surgeon about to perform an operation. Before making a single incision, they write every planned step in a checklist: "Step 1: anaesthesia. Step 2: incision at point X. Step 3: remove appendix. Step 4: stitch."

If the power goes out mid-surgery, the surgical team can read the checklist and know exactly what was done, what wasn't done, and what needs to happen next. The checklist is written before the action happens. That's WAL.

📖 Analogy 2: The Bank Ledger

A bank cashier never directly modifies a customer's account balance without first writing the transaction in the ledger: "Transfer ₹10,000 from Account A to Account B — 14:32:07"

If the cashier's computer crashes at 14:32:08, the bank opens the ledger, sees the committed entry, and applies it correctly. The ledger was written before the balance was updated. That is exactly Write-Ahead Log.

❌ Without WAL — Dangerous
1️⃣ Transaction begins
2️⃣ Data written directly to disk
💥 CRASH mid-write!
❓ Was the data written? Partially? Fully? We don't know!

😱 Data corruption. No recovery possible.

✅ With WAL — Safe
1️⃣ Transaction begins
📒 Write intent to WAL first (synced to disk!)
3️⃣ Apply changes to actual data pages
💥 CRASH here? No problem!
🔄 On restart: replay WAL → perfect state restored!

🎉 Zero data loss. Always recoverable.

✅ The Golden Rule of WAL — Commit to the Log Before the Data

"Write Ahead" means: write the log entry AHEAD of (before) writing the actual data. Only after the log is safely on disk can the actual data write proceed.

This one rule guarantees that the database can always recover to a consistent state — no matter when a crash occurs. Even mid-write. Even mid-transaction. The log is truth. The data files are derived from it.

🔥 Section 2: The Core Problem — Why Databases Need WAL

To truly appreciate WAL, we need to understand the fundamental problem it solves: the gap between what's in memory and what's on disk.

💡 The Memory vs Disk Problem

RAM is fast but volatile — it loses everything when power is cut.
Disk is slow but durable — data survives power loss.

Databases need speed, so they work primarily in RAM (buffer pool / page cache). But they also need durability, so they must eventually write to disk.

The terrifying question: what if the power goes out DURING that disk write?
A disk write is not atomic — it can be interrupted mid-page (a 8KB or 16KB page is written in multiple 512-byte or 4096-byte disk sectors). A partial page write = corrupted data. WAL solves this entirely.

⚡ The Memory ↔ Disk Gap — Where Data Lives in a Running Database

⚡ RAM (Buffer Pool)
📄 Page 42 (modified) 🔴
📄 Page 17 (clean) ✅
📄 Page 88 (modified) 🔴

⚠️ 🔴 = "dirty pages" — modified in RAM, NOT yet on disk.
If power cuts here → lost forever without WAL!

📒 WAL
(on disk, sequential)
"I got the intent!"
WAL is ALWAYS written before the dirty page
💾 Disk (Data Files) 💿
📄 Page 42 (old version) ⌛
📄 Page 17 (current) ✅
📄 Page 88 (old version) ⌛

✅ WAL covers the gap.
Crash + replay WAL → disk catches up to the committed truth.


⚙️ Section 3: How WAL Works — Every Step Explained

Let's trace exactly what happens when you run: UPDATE accounts SET balance = 5000 WHERE id = 42;

🔄 WAL Transaction Flow — Step by Step

1
Client sends UPDATE statement
The database parses the SQL and begins a transaction. A unique Transaction ID (XID) is assigned: e.g., XID = 7823.
⬇️
2
WAL record written to WAL buffer in memory
A log record is created containing: the old value (balance = 3000), the new value (balance = 5000), the page ID affected, the transaction ID, and a Log Sequence Number (LSN). This goes into the WAL buffer — an in-memory staging area.
⬇️
3
Data page modified in the Buffer Pool (RAM only!)
The actual data page (holding account 42's record) is loaded into the buffer pool if not already there, and the balance is updated in memory: 3000 → 5000. This page is now "dirty" — changed in RAM but not yet on disk.
⬇️
4
🔑 COMMIT issued — WAL buffer flushed to disk (fsync!)
This is the most critical moment. When the client says COMMIT, the WAL buffer is flushed to the WAL file on disk using fsync() — which forces the OS and disk to physically write the data (bypassing caches). Only after fsync succeeds does the database tell the client: "Committed ✅". The dirty data page does NOT need to be on disk yet — just the WAL record.
⬇️ (later, asynchronously)
5
Checkpoint: dirty pages eventually flushed to data files
At a checkpoint, the database flushes all dirty pages from the buffer pool to the actual data files on disk. This is expensive (random I/O), so it happens periodically — not on every commit. After a checkpoint, the WAL records before that point are no longer needed for recovery.
🚫 The Crucial Rule: WAL Must Be Durable Before Acknowledging Commit

The sequence is non-negotiable: WAL on disk → then say "committed".
If you acknowledge the commit before WAL hits disk and then crash — you've lied to the client. Their transaction appeared to succeed but the data is gone.

This is why fsync() is so important (and why some misconfigured databases that skip fsync for speed are dangerous — they can lose committed data on crash).

🔢 Section 4: Log Sequence Numbers — The Heartbeat of WAL

Every WAL record is identified by a Log Sequence Number (LSN) — a monotonically increasing integer that uniquely identifies each position in the WAL. Think of it as the page number in that surgeon's checklist.

🔢 WAL as an Append-Only Log — Each Record Has an LSN

LSN 1001
BEGIN XID=7820
LSN 1002
UPDATE page 42
old:3000 → new:5000
LSN 1003
COMMIT XID=7820
LSN 1004
BEGIN XID=7821
LSN 1005
INSERT page 17
row id=9999
LSN 1006 ← current
COMMIT XID=7821
LSN 1007
next write
goes here →

↑ WAL is ALWAYS append-only — records are only ever added to the end, never modified. This makes WAL writes sequential (fast!) unlike random data file writes (slow!).

LSNs are used everywhere in database internals:

📄 Each data page stores the LSN of the last WAL record that modified it.
Before writing a dirty page to disk, the database checks: is the corresponding WAL record already durable? If not, flush WAL first. This guarantees WAL always leads data.
🔄 Replicas track their "replay LSN" — how far they've applied the primary's WAL.
The replication lag = Primary's current LSN − Replica's replay LSN. Monitor this to know how far behind a replica is.
📦 Checkpoints record the LSN up to which data files are consistent.
On crash recovery, the database only needs to replay WAL from the last checkpoint LSN — not from the very beginning of time.

💥 Section 5: Crash Recovery — WAL's Greatest Superpower

The true test of WAL is what happens when everything goes wrong. Your database server crashes. Power cuts. Hardware fails. How does WAL bring it back to a perfectly consistent state?

💥 Crash Recovery — The 3-Phase Protocol (ARIES Algorithm)

🔍
Phase 1: Analysis

Database scans the WAL forward from the last checkpoint. Builds a list of: which transactions were committed, which were in-flight (not committed) when the crash happened, and which dirty pages needed to be written.

➡️
▶️
Phase 2: Redo

Replay ALL WAL records from the checkpoint, even for transactions that were already committed. This re-applies any changes that were in RAM but not yet flushed to disk files. All committed changes are restored.

➡️
↩️
Phase 3: Undo

Roll back all incomplete transactions (those that had no COMMIT in the WAL). Uses the "old value" stored in each WAL record to reverse the change. The database returns to the last consistent committed state.

✅ Result after 3 phases: Database is in exactly the state it was in at the moment of the last committed transaction. All committed data is present. All in-flight data is rolled back cleanly. This algorithm is called ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) and is used by PostgreSQL, SQL Server, and many others.

⏱️ Recovery Timeline — What the Database Sees After a Crash

📦 Checkpoint
(LSN 1000)
All pages flushed
→
✅ XID 7820
COMMIT
(LSN 1003)
→
🔄 XID 7821
BEGIN
(LSN 1004)
→
🔄 XID 7821
UPDATE
(LSN 1005)
→
💥 CRASH
here!
(no commit)
→
🔄 RECOVERY
Redo 7820 ✅
Undo 7821 ↩️

After recovery: XID 7820's changes are present. XID 7821's changes are gone. Database is consistent.


📦 Section 6: Checkpoints — Bounding Recovery Time

Without checkpoints, recovery after a crash would mean replaying the entire WAL from the beginning of time. That could take hours or days. Unacceptable. Checkpoints solve this.

💡 The "Save Game" Analogy

In a video game, a checkpoint is where you save your progress. If you die after a checkpoint, you don't start from the very beginning — you restart from the last save point.

Database checkpoints work exactly the same way. At a checkpoint, all dirty pages are flushed to disk. The checkpoint LSN is recorded. On crash recovery, replay starts from the last checkpoint LSN — not from LSN 0. Recovery time is bounded!

📦 What Happens During a Checkpoint

1. Database scans the buffer pool for all dirty (modified) pages.
↓
2. Before flushing each dirty page, confirm its WAL record is already on disk (WAL-before-data rule).
↓
3. Write all dirty pages to their data files on disk (this is the expensive part — lots of I/O).
↓
4. Write a special CHECKPOINT record to the WAL: "As of LSN 2000, all data files are consistent."
↓
5. WAL records older than the checkpoint LSN can now be safely deleted (they're no longer needed for recovery). WAL files are recycled.
⚙️ Checkpoint Frequency ⚡ Write Performance 🔄 Recovery Time After Crash 💾 WAL Disk Usage
Very Frequent (every 1 min) ❌ Slow (constant I/O) ✅ Very fast recovery ✅ Small WAL
Balanced (every 5 min) ✅ Good ✅ Reasonable ✅ Manageable
Infrequent (every 60 min) ✅ Fastest writes ❌ Slow recovery (60 min WAL replay) ❌ Large WAL accumulation

🔄 Section 7: WAL for Replication — How Replicas Stay in Sync

One of the most powerful secondary uses of WAL is database replication. Instead of building a separate change-tracking system, replicas simply consume the primary's WAL and replay it to stay in sync.

💡 The "Carbon Copy" Analogy

Remember carbon copy paper? When you wrote on the top sheet, an identical copy appeared below. The WAL stream is exactly like this for replicas.

Every change the primary commits is recorded in its WAL. Replica servers subscribe to that WAL stream and apply the same changes in the same order. The replica's data becomes an exact copy of the primary — continuously, with a delay of milliseconds.

🔄 Streaming Replication via WAL — Live Animation

🖥️ Primary DB
LSN 1006: COMMIT
LSN 1007: INSERT → (writing)

Current LSN: 1007

WAL stream
📦
📦
~5ms lag
🖥️ Replica 1 (Read)
Replaying LSN 1006 ✅

Lag: ~5ms

🖥️ Replica 2 (Standby)
Replaying LSN 1005 ✅

Lag: ~8ms

💡 PostgreSQL Streaming Replication works exactly like this: Replicas connect to the primary's WAL sender process. The primary streams WAL records as they're written. Replicas apply them immediately. Replication lag is typically milliseconds on a healthy network — the replica is almost a real-time mirror.

⚡ Synchronous vs Asynchronous Replication

🔵 Synchronous Replication

Primary waits for at least one replica to confirm it has received AND written the WAL before telling the client "committed". Zero data loss — even if primary crashes, replica has everything. Trade-off: adds replica's network latency to every commit.

🟢 Asynchronous Replication

Primary commits and tells the client immediately, then ships WAL to replicas asynchronously. Faster commits — no waiting for network roundtrip. Trade-off: if primary crashes before replica receives WAL, a tiny amount of data (seconds) may be lost. Most systems use this — the risk is usually acceptable.


🐘 Section 8: WAL in PostgreSQL — A Deep Technical Look

PostgreSQL's WAL implementation is one of the most studied and well-documented. Let's look at how PostgreSQL specifically implements WAL.

🐘 PostgreSQL WAL Internals

📁 WAL Files

Stored in $PGDATA/pg_wal/. Each file is exactly 16MB (configurable). Named by their starting LSN (e.g., 000000010000000000000001). Written sequentially — append only!

💾 WAL Writer

A dedicated background process that periodically flushes the WAL buffer to disk. Runs every wal_writer_delay (default: 200ms). Transactions don't always have to wait for this — they can force a flush on COMMIT.

🔄 WAL Sender

One WAL Sender process per connected replica. Reads WAL records and streams them over the network to the replica. The replica has a WAL Receiver process that receives and writes them locally.

📦 Checkpointer

Background process that performs checkpoints every checkpoint_timeout (default: 5 minutes) or every max_wal_size of WAL generated. Writes dirty pages to disk and records the checkpoint LSN in the control file.

⚙️ PostgreSQL Setting 🎯 Default 📝 What It Controls
wal_level replica How much info in WAL. logical needed for CDC/logical replication
synchronous_commit on fsync WAL before acknowledging commit. Set off for speed (risky!)
checkpoint_timeout 5min Max time between checkpoints. Higher = better write performance but slower recovery
max_wal_size 1GB Forces a checkpoint if WAL exceeds this size. Prevent disk from filling
wal_keep_size 0 How much WAL to retain for slow replicas. Increase if replicas are far behind

⏰ Section 9: Point-in-Time Recovery (PITR) — Time Travel with WAL

WAL gives databases a superpower that most people don't know exists: the ability to restore a database to any point in time in the past — not just the last backup.

💡 The "Rewind" Analogy

Imagine you accidentally ran DROP TABLE orders; at 2:47 PM today. With a traditional backup, you'd restore the backup from last night — losing all of today's work. With PITR (Point-in-Time Recovery), you can restore to 2:46:59 PM — one second before the accident. All of today's work (except that one command) is preserved! 🎯

⏰ How PITR Works — The Process

Step 1 — Regular base backups: Take a full snapshot of the database daily/weekly. Archive it to S3, GCS, or similar.
↓
Step 2 — Continuous WAL archiving: After each WAL file is completed, copy it to the archive (S3/GCS). These WAL files capture every change since the backup.
↓
Step 3 — Disaster strikes: DROP TABLE orders at 2:47 PM.
↓
Step 4 — Restore base backup: Bring the database to last night's snapshot.
↓
Step 5 — Replay archived WAL up to target time: Apply all WAL records from the backup until timestamp 2:46:59 PM. Stop exactly there. The database is now in the state just before the DROP!

🌐 Section 10: WAL Beyond Databases — Distributed Systems

WAL's principles extend far beyond individual databases. The most important distributed systems in the world are fundamentally just clever implementations of the WAL concept.

📨
Apache Kafka — A Distributed WAL for Events

Kafka is a WAL. Its entire architecture is an append-only, distributed, replicated log. Producers append records to the log (like WAL records). Consumers replay from any offset (like recovery from a checkpoint LSN). Partitions are replicated across brokers (like WAL-based database replication). Kafka makes the WAL model the primary product rather than an internal implementation detail.

🔵
etcd / Raft — WAL for Distributed Consensus

etcd (used by Kubernetes) uses WAL + the Raft consensus algorithm. In Raft: the leader writes a log entry (WAL record), replicates it to a majority of followers, and only applies it once a majority confirms they have it. This is exactly WAL semantics applied across multiple machines — guaranteeing that all nodes agree on the same history of changes, even if some nodes crash.

🪨
RocksDB / LevelDB — WAL Protecting LSM-Trees

RocksDB (used by Cassandra, TiKV, MyRocks) writes to an in-memory MemTable for speed. MemTable data is volatile — a crash would lose it. WAL protects the MemTable: every write is also appended to WAL on disk. On crash, replay WAL to rebuild the MemTable. Same pattern, different data structure.

📡
Change Data Capture (CDC) — WAL as an Event Stream

Tools like Debezium read the WAL of PostgreSQL or MySQL and convert each WAL record into a structured event stream (usually into Kafka). Every INSERT, UPDATE, DELETE in your database becomes a real-time event that downstream services can consume. WAL becomes the source of truth for event-driven architecture — without any application code changes!


⚡ Section 11: WAL and Performance — The Trade-offs

WAL is not free. Understanding its performance implications is critical for designing high-throughput data systems.

✍️ Write Amplification

Every write to a database is actually written twice: once to the WAL and once (eventually) to the data page. This is called write amplification. It's unavoidable — it's the price of durability.

✍️ Write Amplification — One Logical Write = Multiple Physical Writes

💭 Logical write:
UPDATE balance = 5000
→
📒 Physical write 1:
WAL record (sequential)
~0.1ms (fast!)
+
📄 Physical write 2:
Data page (random I/O)
~1–10ms (slow)
+
📦 Eventually:
Checkpoint flush
batch (amortised)

The WAL write is fast because it's sequential (append to end of file). The data page write is slow because it's random I/O (write to arbitrary page on disk). WAL lets you delay (and batch) the slow random writes — only the fast sequential write is on the critical path.

🎯 Group Commit — Batching WAL Writes for Speed

Problem: fsync() is expensive — it stalls until the OS and disk confirm the write. If each transaction does its own fsync, high-concurrency workloads are bottlenecked.

Solution — Group Commit: Instead of each transaction doing its own fsync, the database batches multiple transactions' WAL records together and does ONE fsync for all of them.

❌ Without Group Commit:
T1 fsync → T2 fsync → T3 fsync = 3 expensive fsyncs
✅ With Group Commit:
T1 + T2 + T3 WAL records → ONE fsync = 3x throughput improvement!

PostgreSQL, MySQL, and most databases implement group commit automatically. It's one of the most impactful WAL performance optimisations.


💻 Section 12: A Peek at the Code — WAL in Action

Let's look at simplified code that illustrates WAL concepts. We'll go from a naive (unsafe) approach to a proper WAL implementation, and then look at real PostgreSQL WAL inspection commands.

📌 What This Code Does (Read Before The Code!)

This is a naive (WRONG) database write function that does NOT use WAL. It writes data directly to the data file without any logging. This code looks simple and works fine under normal conditions. But if the process crashes between lines 5 and 6, the data file is left in a partially-written, corrupted state. There is no way to recover. This is the exact problem WAL was invented to solve. Read this first so the correct version (below) makes sense.

# ❌ NAIVE APPROACH — NO WAL (DANGEROUS — DO NOT USE)
# This is how NOT to write a database.
# Purely for educational comparison purposes.

def naive_write(data_file, page_id, new_data):
    # Load the page from disk into memory
    page = data_file.read_page(page_id)

    # Modify the page in memory
    page.update(new_data)

    # Write the modified page directly to disk
    # ⚠️ If the process CRASHES HERE (during this write),
    # the page is partially written = CORRUPTED DATA. Forever.
    data_file.write_page(page_id, page)  # 💥 danger zone

    # Tell the caller: done!
    return "committed"  # Was it though? We can't guarantee it!

# Problem: a crash between write_page() starting and finishing
# leaves an 8KB or 16KB page in a half-written state.
# There is NO WAY to know what the correct data should be.
# The database is corrupted. Game over.
📌 What This Code Does (Read Before The Code!)

This is a correct WAL-based write function. It implements the Write-Ahead Log principle from scratch. The critical insight is that it writes to the WAL log file FIRST and flushes it to disk (using fsync) BEFORE touching the actual data file. This means: even if the system crashes between the WAL flush and the data file write, the WAL record exists on disk. Recovery can replay the WAL and restore the correct state. Notice also that it stores BOTH the old value (for undo) and the new value (for redo) — this is how the database can roll back uncommitted transactions during recovery.

# ✅ WAL-BASED WRITE — CORRECT APPROACH (Simplified Pseudocode)
# This is the pattern used by PostgreSQL, MySQL InnoDB, and most serious databases.

class WALManager:
    def __init__(self):
        self.wal_buffer = []             # in-memory staging area
        self.current_lsn = 0            # Log Sequence Number counter
        self.wal_file = open("wal.log", "ab")  # append-only WAL file

    def next_lsn(self):
        self.current_lsn += 1
        return self.current_lsn


def wal_write(wal, data_file, transaction_id, page_id, old_data, new_data):

    # ── STEP 1: Create the WAL record ───────────────────────────
    # The record contains BOTH old and new values:
    #   → old_data used for UNDO (roll back uncommitted transactions)
    #   → new_data used for REDO (replay committed transactions after crash)
    lsn = wal.next_lsn()
    wal_record = {
        "lsn":            lsn,
        "transaction_id": transaction_id,
        "page_id":        page_id,
        "old_data":       old_data,   # for undo on crash/rollback
        "new_data":       new_data,   # for redo on crash recovery
        "timestamp":      now()
    }

    # ── STEP 2: Append WAL record to in-memory buffer ───────────
    wal.wal_buffer.append(serialize(wal_record))

    # ── STEP 3: Modify data page in buffer pool (RAM only!) ─────
    # The data file on disk is NOT touched yet.
    buffer_pool.load_page(page_id)    # bring page to RAM if not already there
    buffer_pool.modify(page_id, new_data)
    buffer_pool.mark_dirty(page_id, lsn)  # tag page with the WAL LSN

    # ── STEP 4: On COMMIT — flush WAL buffer to disk ─────────────
    # THIS IS THE CRITICAL LINE. We fsync the WAL before telling
    # the client their transaction succeeded.
    # After this line, the commit is durable — even if we crash.
    wal.wal_file.write(b"".join(wal.wal_buffer))
    wal.wal_file.flush()
    os.fsync(wal.wal_file.fileno())   # force OS + disk to physically write!
    wal.wal_buffer.clear()

    # ── STEP 5: NOW safe to tell the client ─────────────────────
    # Data page on disk may still be "dirty" (old version).
    # That's fine — WAL is on disk. We can always recover.
    return {"status": "committed", "lsn": lsn}

    # Data page written to disk later by background checkpoint process.
    # WAL entries up to the checkpoint LSN can then be safely deleted.
📌 What These SQL Commands Do (Read Before The Code!)

These are real PostgreSQL SQL commands you can run right now on any PostgreSQL database to inspect your WAL's state. They are incredibly useful for: understanding replication lag, knowing how much WAL is being generated per second, finding the current LSN position, and monitoring checkpoint frequency. Each command is explained in plain English before you run it — no more mysterious pg_ functions!

-- ✅ Real PostgreSQL commands to inspect WAL health
-- Run these on your own PostgreSQL database!

-- 1. What is the current WAL write position (LSN)?
-- Like asking: "what page are we on in our logbook?"
SELECT pg_current_wal_lsn() AS current_wal_position;
-- Output example: 0/1A2B3C4D

-- 2. How much WAL is being generated right now? (bytes per second)
-- Useful for capacity planning — is WAL filling up your disk?
SELECT
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        pg_current_wal_flush_lsn()
    )) AS wal_pending_flush;

-- 3. How far behind is each replica? (replication lag in bytes AND seconds)
-- Critical for monitoring replication health!
SELECT
    client_addr                              AS replica_ip,
    state                                    AS replication_state,
    sent_lsn                                 AS lsn_sent_to_replica,
    replay_lsn                               AS lsn_applied_on_replica,
    pg_size_pretty(
        pg_wal_lsn_diff(sent_lsn, replay_lsn)
    )                                        AS lag_bytes,
    now() - pg_last_xact_replay_timestamp()  AS lag_time
FROM pg_stat_replication;

-- 4. How many checkpoints have happened? Were any forced (bad for performance)?
-- "Requested" = on schedule. "Timed" = great. "Requested" too often = investigate!
SELECT
    checkpoints_timed       AS scheduled_checkpoints,
    checkpoints_req         AS forced_checkpoints,     -- too many = max_wal_size too small
    checkpoint_write_time   AS write_time_ms,
    checkpoint_sync_time    AS sync_time_ms,
    buffers_checkpoint      AS pages_written_at_checkpoint,
    stats_reset
FROM pg_stat_bgwriter;

-- 5. List WAL files currently on disk and their sizes
SELECT
    name,
    size,
    modification
FROM pg_ls_waldir()
ORDER BY modification DESC
LIMIT 10;
✅ Pro Tip — Monitor These Three Things in Production:

🔹 Replication lag in seconds from pg_stat_replication — alert if it exceeds 30 seconds
🔹 Forced checkpoints from pg_stat_bgwriter.checkpoints_req — if non-zero, increase max_wal_size
🔹 WAL directory size from pg_ls_waldir() — if growing unboundedly, check replica connectivity or archiving health

These three signals catch 90% of WAL-related production issues before they become outages! 🎯

⚛️ Section 13: WAL and ACID — How WAL Delivers Database Guarantees

ACID (Atomicity, Consistency, Isolation, Durability) is the set of guarantees every transactional database promises. WAL is the engineering mechanism that makes two of these — Atomicity and Durability — possible at scale.

⚛️ ACID Property 📝 What It Means 📒 How WAL Delivers It
⚛️ Atomicity All-or-nothing: either the whole transaction succeeds or none of it does WAL records contain old values (undo log). Crash during transaction → Undo phase replays old values. Incomplete transaction disappears cleanly.
🔗 Consistency Database moves from one valid state to another WAL + Undo ensures half-applied transactions are fully rolled back. Constraints and invariants are never violated across crashes.
🔒 Isolation Concurrent transactions don't interfere with each other Handled by MVCC (Multi-Version Concurrency Control) — a separate mechanism. WAL records include transaction IDs that MVCC uses to determine visibility.
💾 Durability Committed transactions survive crashes permanently This is WAL's primary job. fsync WAL to disk before commit acknowledgement. The committed WAL record outlives any crash. Redo phase restores the committed state.

🗺️ Section 14: Everything Together — The Complete WAL Architecture

📒 WAL — Complete Architecture Map

── CLIENT ──
👤 Application: BEGIN; UPDATE ...; COMMIT;
⬇️
🐘 Database Engine (PostgreSQL / MySQL / SQLite)
1️⃣ Parse SQL → Generate WAL record (LSN + old + new + XID)
2️⃣ Append to WAL buffer (in RAM) → fsync to WAL file on disk (on COMMIT)
3️⃣ Modify data page in Buffer Pool (dirty — RAM only)
4️⃣ Checkpointer (background): flush dirty pages to data files periodically
5️⃣ WAL Sender: stream WAL records to Replica WAL Receivers
⬇️
fsync
⬇️
replicate
⬇️
archive
📒 WAL Files
pg_wal/ sequential
append-only
📄 Data Files
base/ tables
random I/O
🖥️ Replica 1
Streaming WAL
Read workloads
🖥️ Replica 2
Streaming WAL
Standby / HA
☁️ WAL Archive
S3 / GCS
PITR recovery
📡 CDC (Debezium)
WAL → Kafka
Event streaming

📐 Section 15: Core Design Principles WAL Teaches Us

📝 Append-Only Logs Are the Foundation of Reliable Systems

WAL is append-only — you never modify it, only add to the end. This makes WAL writes sequential (fast!) and makes the log an authoritative, tamper-proof record of history. This principle appears everywhere: Kafka, event sourcing, audit logs, Git commits. Append-only = trustworthy.

🛡️ Design for Crash Safety from Day One

WAL teaches the engineering discipline of designing for crashes as a first-class concern — not an afterthought. Every write operation must be crash-safe. Before your system says "done", the intent must be durably recorded. This same discipline applies to distributed systems: Raft and Paxos are WAL for multiple nodes.

🔀 Separate Fast Sequential I/O from Slow Random I/O

WAL's biggest performance insight: write sequentially to the log (fast!), and batch random data-file writes into checkpoints (amortised!). This is why SSDs and spinning disks both benefit from WAL. Sequential write performance is always dramatically better than random writes — design your systems accordingly.

♻️ The Log Is the Truth — Data Files Are a Cache

This is the deepest insight WAL gives us. The WAL IS the database. The data files are merely a materialised view of the log — a cache that avoids replaying the full log on every query. You can rebuild the entire database from the WAL alone (given enough time). Martin Kleppmann calls this "the log is the truth" in his landmark book Designing Data-Intensive Applications.


🎓 Section 16: System Design Interview Cheat Sheet

WAL comes up in system design interviews in several forms. Here is how to handle every variant:

❓ "How does a database ensure durability?"

Answer: WAL — write the log record to disk (fsync) before acknowledging commit. Even if the process crashes, the WAL record survives and is replayed on restart. Mention: Redo log restores committed changes. Undo log reverses uncommitted changes.

❓ "How does database replication work?"

Answer: The primary ships WAL records to replicas in real time. Replicas replay the WAL to stay in sync. Lag is measured in bytes (WAL bytes not yet applied) and time. Synchronous replication: primary waits for replica ACK before committing. Async: primary commits immediately and ships WAL in the background.

❓ "How would you implement point-in-time recovery?"

Answer: Base backup + continuous WAL archiving to durable storage (S3). To recover to time T: restore base backup, replay archived WAL until timestamp T. This is how AWS RDS, Google Cloud SQL, and Azure Database all offer PITR.

❓ "What is Kafka, really?" or "How is Kafka similar to a database?"

Answer: Kafka IS a WAL — a distributed, replicated, append-only log. Producers = transactions writing to WAL. Consumers = replica readers replaying WAL. Offsets = LSNs. Kafka makes the WAL the product rather than the internal mechanism.

❓ "How does the Raft consensus algorithm relate to WAL?"

Answer: Raft is WAL for distributed systems. The Raft log IS the WAL. The leader appends entries to its log and replicates them to a majority of followers before applying — exactly like a database writing WAL before acknowledging commit. etcd, CockroachDB, and TiKV all use Raft over WAL.

❓ "What is Change Data Capture (CDC)?"

Answer: Reading the database WAL as a stream of change events. Tools like Debezium connect to PostgreSQL's logical replication slot (which exposes the WAL) and convert each WAL record into a structured event (Kafka message). Every INSERT/UPDATE/DELETE in your database becomes a real-time event — without any application code changes.


🎉 Final Summary

📒 Write-Ahead Log — Write the log record to disk BEFORE modifying the actual data. Log is truth. Data files are derived cache.
🔢 Log Sequence Numbers (LSN) — Monotonically increasing IDs for every WAL record. Enable crash recovery, replication tracking, and checkpoint management.
💥 Crash Recovery (ARIES) — 3 phases: Analysis (what was in flight?), Redo (replay all committed changes), Undo (roll back incomplete transactions). Always consistent.
📦 Checkpoints — Periodically flush dirty pages to disk. Mark a safe "replay from here" point. Bound recovery time. Allow WAL recycling.
🔄 Replication via WAL — Primary streams WAL records to replicas. Replicas replay to stay in sync. Lag = primary LSN − replica replay LSN.
⏰ Point-in-Time Recovery (PITR) — Base backup + archived WAL = ability to restore to any moment in history. The ultimate undo button.
📨 Kafka = Distributed WAL — Kafka makes the WAL the product. Producers append, consumers replay. Offsets = LSNs. Same idea, distributed.
🔵 Raft = WAL for Distributed Consensus — Raft log IS the WAL. Leader appends and replicates before applying — WAL semantics across N machines.
⚡ Group Commit — Batch multiple transactions' WAL flushes into one fsync. Dramatically improves high-concurrency write throughput.
📡 CDC via WAL — Debezium reads WAL → converts to Kafka events. Every DB change becomes a real-time stream without touching application code.
✅ The Most Profound Insight from WAL:

WAL reveals a universal truth about reliable systems: the log IS the system. Everything else is a materialised view of the log.

Your database's data files? A materialised view of the WAL. Your Kafka consumer's local state? A materialised view of the Kafka log. Your distributed system's state machine? A materialised view of the Raft log.

Once you see this pattern, you see it everywhere. And once you see it everywhere, you design much more reliable systems. This is WAL's true gift to engineers. 🎁


Happy Learning! Keep Building! 🔥

Comments