How Write-Ahead Logging WAL Works: Database Performance

1. Introduction and Core Concepts

Write-ahead logging (WAL) is one of the most fundamental durability techniques in modern database systems. At its core, WAL is a simple but powerful rule: before any change is applied to the persistent database pages on disk, a description of that change must first be recorded in a sequential, append-only log and forced to stable storage. This ordering guarantee ensures that even if the system crashes mid-operation, the database can recover to a consistent state by replaying the log.

The principle behind write-ahead logging wal solves a critical tension in database performance. Updating data pages in place is expensive because it requires random I/O, while durability demands that committed transactions survive crashes. Without WAL, a database would need to synchronously flush every modified data page to disk before acknowledging a commit, which would cripple throughput. WAL decouples commit durability from data page persistence by making the log the single source of truth for committed changes.

To understand WAL, you must understand three key concepts. First, the log is append-only and sequential, which makes writes extremely fast on both HDDs and SSDs compared to random page updates. Second, each log record is assigned a monotonically increasing Log Sequence Number (LSN), which establishes a total order of all operations and enables precise recovery. Third, the WAL protocol enforces two rules: the undo rule and the redo rule. The undo rule states that if a data page is flushed to disk before a transaction commits, the undo information for that page must already be on disk. The redo rule states that a transaction cannot be considered committed until its commit record is durably on disk in the log.

Why WAL Matters for Performance and Durability

WAL transforms random writes into sequential writes. A transaction that updates ten different pages scattered across the database file would otherwise require ten random disk seeks. With WAL, those ten changes are coalesced into a single sequential append to the log file, followed by an fsync. The actual data pages can be written back lazily, in the background, in batches, and in an order optimized for I/O efficiency. This is the primary reason nearly every high-performance OLTP system relies on WAL.

Beyond performance, WAL provides atomicity and durability, two properties of ACID. Atomicity is achieved because uncommitted changes can be undone using the log during recovery, while durability is achieved because committed changes are guaranteed to be replayable from the log even if the data pages were never flushed. Without WAL, a crash that occurs between updating some pages but not others would leave the database in an inconsistent, partially applied state.

The WAL Lifecycle at a Glance

The lifecycle of a WAL-protected operation follows a predictable path: the database generates a log record describing the before-image and after-image of the change, appends it to an in-memory log buffer, forces the log buffer to disk on commit, applies the change to the in-memory buffer pool page, and eventually flushes the dirty page to disk during a checkpoint or background writer activity. Recovery, when needed, scans the log from the last checkpoint forward, redoes committed operations and undoes uncommitted ones.

2. How It Works

The mechanism of write-ahead logging wal rests on the interplay between three storage components: the in-memory buffer pool, the WAL buffer, and the two persistent files on disk — the log file and the data file. When a transaction modifies a row, it does not immediately touch the data file. Instead, it acquires a latch on the affected buffer pool page, generates a log record containing the transaction ID, page ID, LSN, previous LSN for that transaction, and the physical or logical description of the change, and copies that record into the WAL buffer.

Each log record is tightly structured. A typical record contains a header with LSN and transaction ID, a type field (UPDATE, INSERT, DELETE, COMMIT, ABORT, CHECKPOINT), the page identifier, the offset and length of the change, a before-image for undo and an after-image for redo, and a pointer to the previous log record of the same transaction to form a per-transaction chain. This chaining allows the system to efficiently roll back a single transaction by walking its log records backward without scanning the entire log.

The commit path is where the WAL rule is strictly enforced. When a client issues COMMIT, the database must ensure all log records for that transaction up to and including the COMMIT record are flushed to disk with an fsync or fdatasync call. Only after that flush succeeds can the database acknowledge success to the client. If the flush fails or the system crashes before it completes, the transaction is considered not committed and will be undone during recovery. This is often called force-at-commit for the log, but no-force for data pages.

LSNs, Log Buffers, and PageLSN

The Log Sequence Number is the backbone of WAL ordering. Every data page header stores a PageLSN — the LSN of the most recent log record that modified that page. Before a dirty page can be flushed to disk, the system checks its PageLSN against the flushedLSN, which is the highest LSN known to be durable on disk. If PageLSN > flushedLSN, the flush must be delayed until the log is flushed further. This check, known as Write-Ahead Logging enforcement, prevents a page from reaching disk with changes that cannot be recovered.

Log buffers are typically small, contiguous memory regions, often 4MB to 64MB, that batch log records to amortize the cost of fsync. Multiple concurrent transactions can append to the WAL buffer under a lightweight latch, and a group commit optimization allows one fsync to durably commit many transactions at once. This batching is critical: without it, each commit would pay the full latency of a disk sync, limiting throughput to roughly 100-200 transactions per second on a single disk.

Checkpoints and Log Truncation

Without checkpoints, the log would grow forever and recovery would require scanning from the beginning of time. A checkpoint is a periodic operation that flushes all dirty buffer pool pages to disk and writes a checkpoint record to the log containing the list of active transactions and the LSN where redo should start. Once the checkpoint completes, all log records before the checkpoint's redo point are no longer needed for crash recovery and can be truncated or recycled. Modern systems use fuzzy checkpoints that do not block concurrent transactions, at the cost of a slightly more complex recovery process.

3. Implementation

Implementing a correct WAL requires careful attention to ordering, durability, and concurrency. The following Python examples illustrate the core abstractions in a simplified, single-threaded form suitable for learning. A production implementation would add latching, concurrent log buffers, and integration with a buffer pool manager, but the logical flow remains identical.

Basic WAL Manager with LSN Tracking

import os
import json
import struct

class WALRecord:
    def __init__(self, lsn, tx_id, page_id, op_type, before, after, prev_lsn):
        self.lsn = lsn
        self.tx_id = tx_id
        self.page_id = page_id
        self.op_type = op_type  # 'UPDATE', 'COMMIT', 'ABORT', 'CHECKPOINT'
        self.before = before
        self.after = after
        self.prev_lsn = prev_lsn

    def serialize(self):
        # Simple JSON serialization for clarity; production uses binary format
        return json.dumps(self.__dict__).encode('utf-8')

    @staticmethod
    def deserialize(data):
        d = json.loads(data.decode('utf-8'))
        return WALRecord(**d)

class WriteAheadLog:
    def __init__(self, log_path):
        self.log_path = log_path
        self.next_lsn = 1
        self.flushed_lsn = 0
        self.buffer = []  # in-memory WAL buffer
        # Transaction table: tx_id -> last LSN
        self.tx_table = {}
        # Dirty page table: page_id -> recLSN (first LSN that dirtied page)
        self.dirty_pages = {}
        # Open log file in append-binary mode
        self.file = open(log_path, 'ab+', buffering=0)

    def append(self, tx_id, page_id, op_type, before, after):
        prev_lsn = self.tx_table.get(tx_id, 0)
        rec = WALRecord(self.next_lsn, tx_id, page_id, op_type, before, after, prev_lsn)
        self.buffer.append(rec)
        self.tx_table[tx_id] = self.next_lsn
        if page_id not in self.dirty_pages:
            self.dirty_pages[page_id] = self.next_lsn
        self.next_lsn += 1
        return rec.lsn

    def flush(self, up_to_lsn=None):
        # Flush WAL buffer to stable storage (fsync)
        if not self.buffer:
            return self.flushed_lsn
        target = up_to_lsn if up_to_lsn is not None else self.buffer[-1].lsn
        for rec in self.buffer:
            if rec.lsn > target:
                break
            data = rec.serialize()
            # Prefix with length for framing
            self.file.write(struct.pack('>I', len(data)))
            self.file.write(data)
        self.file.flush()
        os.fsync(self.file.fileno())
        # Remove flushed records from buffer
        self.buffer = [r for r in self.buffer if r.lsn > target]
        self.flushed_lsn = max(self.flushed_lsn, target)
        return self.flushed_lsn

    def commit(self, tx_id):
        # WAL Rule: log must be durable before commit ack
        commit_lsn = self.append(tx_id, None, 'COMMIT', None, None)
        self.flush(up_to_lsn=commit_lsn)
        # After flush, transaction is durably committed
        self.tx_table.pop(tx_id, None)
        return commit_lsn

Group Commit Optimization

import threading
import time

class GroupCommitWAL(WriteAheadLog):
    def __init__(self, log_path, group_interval_ms=5):
        super().__init__(log_path)
        self.group_interval_ms = group_interval_ms
        self.lock = threading.Lock()
        self.waiters = []  # list of (target_lsn, event)

    def append_and_wait_group_commit(self, tx_id, page_id, before, after):
        with self.lock:
            lsn = self.append(tx_id, page_id, 'UPDATE', before, after)
            commit_lsn = self.append(tx_id, None, 'COMMIT', None, None)
            event = threading.Event()
            self.waiters.append((commit_lsn, event))
        # Wait for group flush instead of flushing immediately
        event.wait()
        return commit_lsn

    def group_commit_daemon(self):
        # Background thread that flushes every N milliseconds
        while True:
            time.sleep(self.group_interval_ms / 1000.0)
            with self.lock:
                if not self.waiters:
                    continue
                max_lsn = max(lsn for lsn, _ in self.waiters)
                self.flush(up_to_lsn=max_lsn)
                for _, ev in self.waiters:
                    ev.set()
                self.waiters.clear()

    def can_flush_page(self, page_id, page_lsn):
        # Enforce WAL rule: page cannot be flushed if its change is not durable
        # Returns True only if page_lsn <= flushed_lsn
        return page_lsn <= self.flushed_lsn

Fuzzy Checkpointing

class CheckpointManager:
    def __init__(self, wal):
        self.wal = wal

    def fuzzy_checkpoint(self, buffer_pool):
        # 1. Capture dirty page table and active transactions without blocking
        dirty_snapshot = dict(self.wal.dirty_pages)
        active_tx_snapshot = dict(self.wal.tx_table)
        checkpoint_lsn = self.wal.next_lsn

        # 2. Write BEGIN_CHECKPOINT record
        begin_lsn = self.wal.append(0, None, 'BEGIN_CHECKPOINT', None, None)
        self.wal.flush(up_to_lsn=begin_lsn)

        # 3. Flush dirty pages in background (no latch on entire pool)
        for page_id, rec_lsn in dirty_snapshot.items():
            page = buffer_pool.get(page_id)
            if page is not None and page.lsn >= rec_lsn:
                # Ensure log is flushed before page
                if not self.wal.can_flush_page(page_id, page.lsn):
                    self.wal.flush(up_to_lsn=page.lsn)
                buffer_pool.flush_page(page_id)

        # 4. Write END_CHECKPOINT with snapshots
        end_rec = {
            'dirty_pages': dirty_snapshot,
            'active_tx': active_tx_snapshot,
            'redo_start_lsn': min(dirty_snapshot.values()) if dirty_snapshot else checkpoint_lsn
        }
        end_lsn = self.wal.append(0, None, 'END_CHECKPOINT', None, end_rec)
        self.wal.flush(up_to_lsn=end_lsn)
        return end_lsn

ARIES-Style Crash Recovery (Redo and Undo)

class RecoveryManager:
    def __init__(self, wal_path):
        self.wal_path = wal_path

    def scan_log(self):
        # Read all records from log file in order
        records = []
        with open(self.wal_path, 'rb') as f:
            while True:
                header = f.read(4)
                if not header or len(header) < 4:
                    break
                length = struct.unpack('>I', header)[0]
                data = f.read(length)
                rec = WALRecord.deserialize(data)
                records.append(rec)
        return sorted(records, key=lambda r: r.lsn)

    def recover(self, buffer_pool):
        records = self.scan_log()
        if not records:
            return

        # Analysis phase: find last checkpoint and build tables
        last_checkpoint = None
        for rec in reversed(records):
            if rec.op_type == 'END_CHECKPOINT':
                last_checkpoint = rec
                break

        redo_start = last_checkpoint.after['redo_start_lsn'] if last_checkpoint else 1
        committed = set()
        active = set()

        for rec in records:
            if rec.op_type == 'COMMIT':
                committed.add(rec.tx_id)
            elif rec.op_type == 'UPDATE':
                active.add(rec.tx_id)

        committed_active = committed.intersection(active) if last_checkpoint else committed

        # Redo phase: replay all updates from redo_start, even for losers
        for rec in records:
            if rec.lsn < redo_start:
                continue
            if rec.op_type == 'UPDATE':
                page = buffer_pool.get(rec.page_id)
                # Only redo if page LSN < record LSN (idempotent)
                if page is None or page.lsn < rec.lsn:
                    buffer_pool.apply_update(rec.page_id, rec.after, rec.lsn)

        # Undo phase: rollback loser transactions in reverse LSN order
        losers = active - committed
        for rec in reversed(records):
            if rec.tx_id in losers and rec.op_type == 'UPDATE':
                # Write compensation log record (CLR) and undo
                buffer_pool.apply_update(rec.page_id, rec.before, rec.lsn)
                print(f"Undo TX {rec.tx_id} LSN {rec.lsn} on page {rec.page_id}")

        print(f"Recovery complete. Redo from {redo_start}, committed={committed}, losers={losers}")

4. Step-by-Step Example

Consider a simple banking database with two accounts, A with balance 1000 and B with balance 500, stored on pages P1 and P2. Two transactions execute concurrently: T1 transfers 100 from A to B, and T2 deposits 200 into A. The buffer pool initially has both pages in memory with PageLSN = 10, and the log's next LSN is 11.

Step 1: T1 begins and updates A. The system generates log record LSN 11: T1, P1, UPDATE, before=1000, after=900, prevLSN=0. This record is appended to the WAL buffer but not yet flushed. The buffer pool page P1 is updated in memory to 900 and its PageLSN is set to 11. The dirty page table now records P1 -> recLSN 11. At this point, if the system crashed, T1 would be undone because no COMMIT record exists.

Step 2: T2 updates A as well, creating LSN 12: T2, P1, UPDATE, before=900, after=1100, prevLSN=0. Page P1 in memory becomes 1100 with PageLSN 12. Note that P1 now reflects the interleaving of two uncommitted transactions, which is allowed under WAL with proper latching. The log buffer now holds LSN 11 and 12, neither flushed.

Step 3: T1 updates B, generating LSN 13: T1, P2, UPDATE, before=500, after=600, prevLSN=11. Page P2 becomes 600 with PageLSN 13. The transaction chain for T1 is now 11 -> 13, allowing efficient rollback.

Step 4: T1 commits. The system appends LSN 14: T1, COMMIT, and immediately flushes the WAL buffer up to LSN 14 to disk with fsync. This flush writes LSN 11, 12, 13, and 14 sequentially. Only after fsync succeeds does the system acknowledge T1's commit to the client. Now T1 is durable even though P1 and P2 are still dirty in memory and have not been written to the data file. T2 remains active and its LSN 12 is now durable as well, but T2 is not yet committed.

Crash and Recovery Walkthrough

Suppose a crash occurs immediately after T1's commit flush, before any data pages are flushed and before T2 commits. On restart, the recovery manager scans the log. During the Analysis phase, it finds that T1 is committed and T2 is active. The Redo phase starts from the last checkpoint's redo point, say LSN 11, and replays LSN 11, 12, and 13. It checks each page's PageLSN on disk, which is still 10, so all three updates are reapplied, bringing P1 to 1100 and P2 to 600.

Next, the Undo phase processes loser transactions in reverse order. T2 is a loser because it never committed, so its update LSN 12 is undone by restoring P1 to its before-image of 900. However, careful ARIES recovery recognizes that P1 was also modified by the committed T1, so the final value after undo must reflect only committed changes. The compensation logic restores P1 to 900, which is exactly A = 1000 - 100 + 200 undone = 900, while P2 remains 600. The database is now consistent and reflects only T1's committed transfer.

If the crash had occurred before T1's flush, neither T1 nor T2 would have a COMMIT record on disk, and recovery would undo both transactions, leaving A=1000 and B=500. This demonstrates the all-or-nothing guarantee: a transaction's effects become visible durably only when its commit record reaches the log.

5. Complexity Analysis

The performance of write-ahead logging wal is best understood by separating the cost of logging from the cost of data page I/O. Logging itself is sequential and benefits from batching, while recovery cost depends on log volume and checkpoint frequency.

Appending a log record is O(1) in-memory work plus amortized O(1) I/O when group commit is used. Without group commit, each commit pays O(1) fsync latency, which dominates. Flushing a data page is O(1) per page but can be deferred and batched. Recovery is linear in the number of log records that must be scanned.

Interpreting the Costs

In steady-state operation, WAL adds negligible CPU overhead. The dominant cost is the fsync on commit. With group commit batching 50 transactions per fsync on an SSD with 0.5ms fsync latency, average commit latency drops from 0.5ms to 0.01ms of fsync time plus queuing delay. This is why high-throughput systems can achieve 10,000+ transactions per second with WAL, whereas synchronous data page flushing would limit them to a few hundred.

Space complexity is proportional to log volume. Each update generates a log record whose size is roughly the size of the changed bytes plus ~50-100 bytes of header. For a workload that updates 1KB rows, log volume is approximately 1.05KB per update. The log must retain at least all records since the last checkpoint's redo point, typically a few hundred megabytes to a few gigabytes, depending on checkpoint interval. More frequent checkpoints reduce recovery time but increase runtime I/O.

Amortized Analysis and Checkpoint Tuning

Checkpoint frequency presents a classic trade-off. Frequent checkpoints, for example every 30 seconds or every 1GB of log, minimize recovery time to O(checkpoint interval) but increase write amplification because pages that are updated repeatedly will be flushed multiple times. Infrequent checkpoints, for example every 10 minutes, reduce runtime overhead but make recovery scan more log, potentially minutes of redo. Most production systems use an adaptive policy based on both time and log size, targeting recovery times under 1-2 minutes.

6. Variations and Optimizations

While the basic WAL protocol is simple, production databases employ numerous optimizations to maximize throughput and minimize latency.

Group commit, as shown in the implementation, is the single most impactful optimization. Instead of each committer issuing its own fsync, transactions queue their commit records and a single fsync makes them all durable. Modern implementations use an adaptive window: if load is low, commit latency is prioritized and the window is small; if load is high, throughput is prioritized and the window grows to batch more transactions. PostgreSQL, MySQL InnoDB, and SQL Server all implement variants of group commit.

Log buffer sizing and double buffering also matter. A single WAL buffer can become a contention point under high concurrency. Systems like Oracle and SQL Server use multiple log buffers or a circular log buffer with careful LSN allocation via atomic fetch-and-add, allowing dozens of threads to append concurrently without blocking. Some systems also use non-volatile memory or battery-backed write caches to make log flushes nearly free.

Physical vs Logical vs Physiological Logging

The content of log records can vary. Physical logging records byte-level changes to a page, which makes redo fast but generates large log volume and is sensitive to page format changes. Logical logging records high-level operations like INSERT INTO table VALUES (...), which generates small logs but requires more work during redo and can be non-deterministic. Most modern systems use physiological logging, a hybrid that logs logical operations within a physical page, for example B-tree insert of key K into page P at slot S. This balances log size, redo speed, and concurrency.

Advanced Techniques

WAL compression reduces I/O by compressing log records before flushing, trading CPU for bandwidth, which is beneficial on I/O-bound systems. Log striping and partitioning distribute the log across multiple disks or files to increase aggregate bandwidth. On multi-core systems, per-core log buffers or partitioned logs avoid global contention, with a global LSN assigned at flush time.

Another important variation is the use of write-ahead logging for replication. Logical WAL records can be streamed to replicas, which replay them to stay in sync with the primary. PostgreSQL's streaming replication, MySQL's binlog, and MongoDB's oplog are all WAL-derived replication logs. This unifies durability and replication into a single mechanism, simplifying the architecture.

7. Real-World Applications

Write-ahead logging wal is ubiquitous in systems that require durability without sacrificing performance. Its applications span databases, file systems, and distributed systems.

In relational databases, WAL is the default durability mechanism. PostgreSQL writes all changes to its WAL (pg_wal) before touching heap pages, and uses WAL for both crash recovery and streaming replication. MySQL's InnoDB uses a redo log that is fundamentally a WAL, plus a separate undo log for MVCC. SQLite offers WAL mode as an alternative to its traditional rollback journal, allowing concurrent readers and writers with dramatically better write performance. Oracle's redo log and SQL Server's transaction log are both WAL implementations that have been refined over decades.

NoSQL and NewSQL systems also rely heavily on WAL. RocksDB and LevelDB, which underpin many modern storage engines, use a WAL to protect their in-memory memtables before flushing to SSTables. When a write arrives, it is appended to the WAL and inserted into the memtable; if the system crashes before the memtable is flushed, the WAL is replayed. Apache Cassandra, HBase, and etcd use similar commit logs for durability. In distributed consensus systems like Raft and Paxos, the replicated log is essentially a distributed WAL that ensures all nodes agree on the order of operations.

File Systems and Beyond

Journaling file systems such as ext4, NTFS, and XFS use WAL-like journaling to protect metadata. Before updating directory structures or allocation bitmaps, they write a journal transaction describing the changes. After a crash, the journal is replayed to restore consistency, avoiding lengthy fsck scans. This is directly analogous to database WAL, applied to file system metadata.

Message queues and streaming platforms use WAL for durable messaging. Apache Kafka's partitioned commit log is a WAL that durably stores messages before they are consumed. Apache Pulsar and Amazon Kinesis use similar logs to ensure messages survive broker failures. Even web browsers use WAL: IndexedDB and some local storage engines use WAL to protect user data against sudden tab or process termination.

8. Comparison with Alternatives

WAL is not the only way to achieve atomicity and durability. Understanding its trade-offs against alternatives clarifies when WAL is the right choice.

Shadow paging maintains two copies of each page: a shadow copy on disk that represents the last consistent state, and a current copy in memory. On commit, a new page table is atomically swapped to make the current copies the new shadow. This avoids logging but requires remapping pages on every write and suffers from scattered I/O and poor locality. It also makes recovery conceptually simple but complicates garbage collection of old pages.

In-place updates with no logging are the fastest in the absence of crashes but provide no guarantees. A crash can leave the database torn, with some pages updated and others not, requiring external backups. Doublewrite buffering, used by InnoDB as a complement to WAL, writes pages first to a contiguous doublewrite buffer before writing them to their final location, protecting against torn pages caused by partial page writes, but it does not replace WAL for transaction durability.

Copy-on-write and log-structured merge trees take a different approach: they never overwrite data in place, instead writing new versions sequentially and garbage collecting old ones. While this is log-structured like WAL, it treats the log as the database itself rather than as a separate recovery structure, which has different performance characteristics for reads.

When to Choose WAL

WAL is the preferred choice for any system that needs high write throughput, strong durability, and fast recovery with concurrent access. Its sequential write pattern is optimal for modern storage, and its separation of logging from page flushing allows sophisticated I/O scheduling. Shadow paging is rarely used today except in systems like LMDB or CouchDB that leverage copy-on-write for MVCC snapshots. In practice, most production databases combine WAL with complementary techniques like doublewrite buffers for torn-page protection.

9. Common Mistakes and Best Practices

Implementing or operating a WAL-based system incorrectly can negate its benefits or, worse, silently lose data. The most dangerous mistake is violating the WAL rule itself: flushing a dirty data page to disk before its log records are durable. This can happen if the buffer pool manager flushes pages without checking PageLSN against flushedLSN, or if the log flush uses buffered I/O without fsync. On a crash, the page on disk would contain changes that cannot be undone or redone, leading to corruption. Always enforce that page flush waits for log flush, and always use fsync, fdatasync, or O_SYNC for log writes.

Another common pitfall is forgetting to handle partial log writes. If a crash occurs mid-log-append, the tail of the log may contain a torn record with a valid header but incomplete body. Recovery must detect this via checksums or length validation and truncate the log at the last valid record. Without checksums, a torn record could be misinterpreted as valid and cause incorrect redo. Every log record should include a CRC32 or similar checksum covering its entire content.

Improper checkpointing is also frequent. Taking checkpoints too infrequently causes unbounded log growth and multi-minute recovery times, while checkpointing too aggressively causes write amplification and stalls. A related mistake is truncating the log before the checkpoint is fully durable. The log should only be truncated after the END_CHECKPOINT record and all dirty pages it covers are durably flushed. Truncating early can make recovery impossible.

Best Practices for Production

• Use group commit and batch flushes: Never fsync per transaction in high-throughput systems. Batch commits to amortize fsync cost and monitor commit latency percentiles.
• Separate log and data storage: Place the WAL on its own disk or SSD, ideally with low-latency fsync, to avoid contention with random data page I/O. This also isolates log bandwidth.
• Enable checksums and validate on recovery: Every log record should carry a checksum, and recovery should verify it before applying. This detects both torn writes and bit rot.
• Monitor log growth and checkpoint lag: Alert on log size, checkpoint age, and the distance between flushedLSN and current LSN. Unbounded lag indicates the checkpoint or background writer is falling behind.
• Test crash recovery regularly: Use fault injection to simulate crashes at various points, including mid-flush and mid-checkpoint, and verify that recovery produces a consistent state. Tools like Jepsen or custom crash tests are invaluable.
• Size WAL buffers appropriately: Too small causes frequent flushes and contention; too large increases memory pressure and recovery window. Start with 16-64MB and tune based on write rate.

Operational Pitfalls to Avoid

Do not place the WAL on a file system mounted with write-back caching that ignores fsync, such as some NFS configurations or disks with volatile write caches without proper barriers. This silently breaks durability. Also avoid using buffered I/O for the log without explicit flushes, and never delete or recycle log files that may still be needed for replication or point-in-time recovery. Finally, ensure that log archiving for point-in-time recovery does not fall behind; a lagging archiver can cause the primary log directory to fill and stall the database.

10. Interview Questions

Write-ahead logging is a favorite topic in database and systems interviews because it tests understanding of durability, concurrency, and I/O. Expect questions that probe both the theory and the practical trade-offs.

Q1: What is the Write-Ahead Logging rule and why is the order important?

Answer: The WAL rule states that the log record describing a change must be forced to stable storage before the changed data page is allowed to reach stable storage, and that a transaction's COMMIT record must be durable before the transaction is acknowledged as committed. The order is critical because the log is the only durable record of unflushed changes. If a page were flushed before its log, a crash would leave the page on disk with changes that cannot be recovered or undone, violating atomicity and durability. Conversely, forcing the log first ensures that any page state on disk can be reconstructed by replaying the log.

Q2: How does WAL enable group commit and why does it improve throughput?

Answer: Group commit exploits the fact that a single fsync can make multiple transactions durable at once. Instead of each transaction issuing its own fsync, transactions append their commit records to the WAL buffer and wait. A background thread periodically flushes the buffer with one fsync, waking all waiters. This amortizes the fixed cost of fsync, which is typically 0.2-1ms on SSDs, across many transactions. Without group commit, throughput is limited to roughly 1/fsync_latency transactions per second, about 1000 TPS. With group commit batching 50 transactions, throughput can exceed 50,000 TPS with only a small increase in average latency.

Q3: Explain the three phases of ARIES recovery and what each does.

Answer: ARIES recovery has Analysis, Redo, and Undo phases. Analysis scans the log from the last checkpoint forward to reconstruct the dirty page table and active transaction table at the time of the crash, and determines the redo start point. Redo replays all updates from the redo point forward, reapplying changes to pages whose PageLSN is less than the log record's LSN, making the operation idempotent and bringing the database to the exact state at crash time, including uncommitted changes. Undo rolls back loser transactions, those that were active but not committed at crash, by walking their log chains backward and applying before-images, writing compensation log records to ensure idempotence if a crash occurs during recovery.

Q4: What is the difference between physical, logical, and physiological logging?

Answer: Physical logging records byte-level after-images of pages, which makes redo simple but creates large logs and couples the log format to the page layout. Logical logging records high-level operations like SQL statements, which creates small logs but makes redo complex and potentially non-deterministic, especially with concurrent operations. Physiological logging is a hybrid used by most systems: it logs logical operations within a specific page, for example insert key K into B-tree page P at offset O. This gives a balance of moderate log size, efficient redo that can be applied to the specific page without re-traversing the B-tree, and support for high concurrency.

Q5: How do checkpoints affect recovery time and runtime performance? How would you tune them?

Answer: Checkpoints bound recovery time by flushing dirty pages and writing a marker that tells recovery where to start redo. Without checkpoints, recovery would scan the entire log. Frequent checkpoints shorten recovery but increase runtime I/O because hot pages that are updated repeatedly get flushed multiple times, causing write amplification. Infrequent checkpoints reduce runtime overhead but lengthen recovery and increase log retention. Tuning involves balancing these: set a checkpoint interval based on both time, for example every 5 minutes, and log volume, for example every 1GB, and monitor recovery time and write amplification. Adaptive policies that trigger checkpoints when the dirty page count or log size exceeds thresholds are common in production.