WAL architecture, in depth: the two logging rules, LSNs, checkpoints, ARIES-style recovery worked by hand, torn pages and fsync failure

The problem a log solves

Consider a transfer that debits one account and credits another. The two rows live on different 8 KB pages. If the machine loses power after the first page is written and before the second, the disk holds a state that never existed logically: money has vanished. Even a single page is not safe, because a disk or file system may persist only part of an 8 KB write, producing a torn page.

A log turns this into an append problem. The database writes a record saying "page 5, offset 40: 10 becomes 20" to the end of one file. Sequential appends are cheap, many transactions share one flush, and after a crash the log can reconstruct whatever the pages are missing. The data files become a cache of the log's effect.

The two rules

Every WAL implementation enforces the same two ordering rules. Everything else is optimisation.

1. Log before data. A dirty page may not be written to the data file until every log record describing changes to it is durable. Each page carries a pageLSN, the log sequence number of the last record that changed it, so the check is a single comparison: flush the log up to pageLSN, then write the page.
2. Log before acknowledgement. A transaction is committed when its commit record is durable in the log, not when its pages are. The client is told "committed" only after that flush returns.

def commit(txn):
    lsn = wal.append(encode("COMMIT", txn.id, prev_lsn=txn.last_lsn))
    wal.flush_to(lsn)          # rule 2: commit record durable before we answer
    locks.release(txn)
    return "COMMITTED"

def write_page(page):          # called by the background writer or on eviction
    wal.flush_to(page.page_lsn)  # rule 1: log up to this page's last change first
    datafile.pwrite(page.bytes, page.offset)

These rules permit the policy textbooks call steal, no-force. Steal means an uncommitted transaction's dirty page may be written to disk, for example when the buffer pool needs the frame; the log holds the before-image needed to undo it. No-force means commit does not force the transaction's pages to disk; the log holds the after-image needed to redo them. Steal gives the buffer pool freedom and no-force gives cheap commits, and the price is that recovery needs both undo and redo information.

Records and LSNs

A log record typically carries its LSN, the transaction id, the previous LSN of the same transaction (so a transaction's records form a backward chain), the target page, and either a physical description of the bytes that changed or a logical description of an operation. PostgreSQL uses the byte position in the log as the LSN, which makes the LSN both a name and an address. It stores the log in segment files, 16 MB each by default.

Each record also carries a checksum. That is how recovery finds the end of the log: after a crash the final record may be half written, so the reader stops at the first record that is short, has a bad checksum, or has an LSN that does not match its position. The toy below implements exactly that.

import os, struct, zlib

HDR = struct.Struct("<QII")          # lsn (byte offset), payload length, crc32 of payload


class Wal:
    def __init__(self, path):
        self.f = open(path, "ab+")
        self.next_lsn = self._recover_tail()
        self.durable_lsn = self.next_lsn          # everything that survived is durable

    def append(self, payload: bytes) -> int:
        """Buffer a record; return its LSN. Not durable yet."""
        lsn = self.next_lsn
        self.f.write(HDR.pack(lsn, len(payload), zlib.crc32(payload)) + payload)
        self.next_lsn += HDR.size + len(payload)
        return lsn

    def flush_to(self, lsn: int):
        """Make every record up to and including lsn durable (one fsync covers many)."""
        if lsn < self.durable_lsn:
            return
        self.f.flush()
        os.fsync(self.f.fileno())
        self.durable_lsn = self.next_lsn

    def _recover_tail(self) -> int:
        """Scan from the start; stop at the first record that is short or fails its CRC."""
        self.f.seek(0)
        good = 0
        while True:
            hdr = self.f.read(HDR.size)
            if len(hdr) < HDR.size:
                break
            lsn, n, crc = HDR.unpack(hdr)
            body = self.f.read(n)
            if lsn != good or len(body) < n or zlib.crc32(body) != crc:
                break                               # torn or garbage tail from a crash
            good += HDR.size + n
        self.f.truncate(good)                       # never append after garbage
        return good

Three details matter. The truncate stops a later append from landing behind garbage, which would make every future record unreachable. The LSN check rejects stale bytes from a recycled file that happen to carry a valid checksum. And a real implementation must fsync the directory after creating a segment, or the file can vanish in a crash even though its contents were synced.

Group commit and log throughput

One fsync per commit caps a single log at a few hundred to a few thousand commits per second, depending on the device. The standard fix is group commit: while one flush is in progress, other committing transactions append their records and wait, and the next flush covers all of them. The flush_to method above already has the right shape, since one call makes everything appended so far durable. The batching, leader election among waiters and tuning knobs are covered in group commit architecture.

Systems also offer to relax the second rule for workloads that can lose the last moments of work. PostgreSQL's synchronous_commit = off acknowledges before the flush; a crash can lose recently acknowledged transactions but cannot corrupt the database, because the first rule still holds. InnoDB's innodb_flush_log_at_trx_commit values 0 and 2 make a similar trade. Use these per session or per transaction for data you can regenerate, never globally for money.

Checkpoints bound recovery time

Without checkpoints, recovery would replay the log from the beginning of time. A checkpoint records where recovery may safely start. A fuzzy checkpoint, the kind every production system uses, does not stop the world: it writes a record listing the transactions in flight with their last LSNs and the dirty pages with their recLSN, the LSN of the first change that dirtied each page since it was last written. Redo can start at the smallest recLSN, and log older than that can be recycled or archived.

Frequent checkpoints mean more page writes and more full-page images (below); rare ones mean longer replay and more WAL on disk. PostgreSQL triggers them on time and on WAL volume, through checkpoint_timeout and max_wal_size, and spreads the page writes across the interval to avoid I/O spikes.

Recovery worked by hand

ARIES, described by Mohan and colleagues in 1992, is the recovery design most engines follow in outline. It runs three passes. Here is a small log and the state on disk at the crash.

LSN  txn  record                               prevLSN
100  T1   BEGIN
110  T1   UPDATE P5  A: 10 -> 20                100
120  T2   BEGIN
130  T2   UPDATE P7  B: 5 -> 6                  120
140  --   CHECKPOINT  active={T1:110, T2:130}  dirty={P5:110, P7:130}
150  T1   COMMIT                                110
     (P5 is flushed to disk here: pageLSN 110)
160  T2   UPDATE P5  A: 20 -> 25                130
     *** crash ***   on disk: P5 pageLSN 110 (A=20), P7 pageLSN 90 (B=5)

Analysis starts at the checkpoint and rebuilds the two tables. It sees T1's commit at 150, so T1 leaves the active set. It sees T2's update at 160, so T2's last LSN becomes 160. P5 is already in the dirty page table with recLSN 110. At the end, T2 is the only loser.

Redo starts at the smallest recLSN, 110, and repeats history, including the loser's changes. Record 110 targets P5, whose pageLSN is 110, so it is already applied and skipped. Record 130 targets P7 with pageLSN 90, so B becomes 6 and pageLSN becomes 130. Record 160 targets P5 with pageLSN 110, so A becomes 25 and pageLSN becomes 160. The database is now exactly as it was at the crash.

Undo walks the loser's chain backwards. It reverses 160, setting A back to 20, and logs a compensation log record (CLR) at 170 whose undo-next pointer is 130. It reverses 130, setting B back to 5, logs a CLR at 180, and writes an END record for T2 at 190. Final state: A is 20 from committed T1, B is 5, and T2 never happened.

The CLRs are what make recovery safe to interrupt. If the machine crashes again during undo, redo replays the CLRs and undo resumes from their undo-next pointers instead of undoing the same change twice. The pageLSN comparison is what makes redo idempotent: applying the log twice gives the same pages as applying it once.

Torn pages and full-page writes

Redo assumes the page it reads is internally consistent, only old. A torn page breaks that: half of it is new and half old, and applying a small delta to it produces garbage. Engines defend against this in one of two ways. PostgreSQL writes a full-page image into the WAL the first time a page is modified after each checkpoint, controlled by full_page_writes, which is on by default; recovery restores the whole image and then applies later deltas. InnoDB instead writes pages first to a doublewrite buffer and then to their home location, so a torn home copy can be restored from the intact one.

Both cost write volume. Full-page images are why WAL volume spikes after each checkpoint, and why spacing checkpoints further apart often reduces total WAL. Turning the protection off is safe only on storage that guarantees atomic page-sized writes, a guarantee you should get in writing from the vendor.

When fsync fails

The whole design rests on fsync meaning what it says. In 2018 PostgreSQL developers found that on Linux a failed fsync could clear the kernel's error state and drop the dirty pages, so a retry would report success while the data was gone. Retrying a failed fsync is therefore unsafe. PostgreSQL's response, added after that discussion, was the data_sync_retry setting, default off, which makes the server PANIC on a data-file fsync failure and recover from the WAL instead of trusting the retry.

The operational lessons generalise to any engine. Treat an fsync error as fatal. Do not place the log on storage that lies about flushes, such as consumer disks with volatile write caches or virtual disks configured for write-back without protection. And test durability by pulling power, not by killing the process, because a process kill leaves the operating system's cache intact.

Beyond recovery: the log as a product

Once a log exists, other components read it. Physical replicas replay it byte for byte, which is how streaming replication works; see database replication. Logical decoding turns it into row-level change events for downstream systems, covered in CDC via logical decoding. Point-in-time recovery restores a base backup and replays archived segments up to a chosen LSN. Log-structured engines go one step further and make the log, plus sorted runs derived from it, the primary storage; see LSM trees.

Each reader pins WAL. A replica that falls behind or a replication slot whose consumer has died prevents the segments it still needs from being recycled, and the log volume fills. This is the most common WAL outage in PostgreSQL shops.

Operating a WAL

• Measure volume. Sample the current LSN twice and subtract to get bytes per second. Watch the full-page image count to see what checkpoints cost.
• Give the log its own fast, honest device where the platform allows it; commit latency is flush latency.
• Alert on retained WAL, per replication slot and per replica, well before the volume is full.
• Tune checkpoints so that most are triggered by time, not by volume; frequent volume-triggered checkpoints mean the WAL budget is too small.
• Rehearse recovery: restore a base backup, replay to a timestamp, and time it. Recovery time is a product requirement.

-- How far has the log advanced, and how much WAL did a workload generate?
SELECT pg_current_wal_lsn();                                   -- e.g. 3/2A0019C8
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '3/20000000'));

-- WAL volume, full-page images and buffer-full events (PostgreSQL 14 and later)
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes), wal_buffers_full FROM pg_stat_wal;

The dirty pages the log protects live in the buffer pool, whose eviction policy decides when the first rule is exercised; buffer pool architecture covers that side.

What to do next

1. Write down, for your database, what makes a commit durable: which setting controls the flush, and whether any session relaxes it.
2. Measure WAL bytes per second and full-page images per checkpoint on a normal day, and record them as a baseline.
3. Check every replication slot and replica for retained WAL, and add an alert on the largest.
4. Confirm your storage honours flushes, and that torn-page protection is on unless the vendor guarantees atomic page writes.
5. Run the toy WAL above, kill it mid-append, truncate the file at a random byte, and watch recovery find the tail.
6. Time a restore and replay from backup, and compare it with your recovery objective.