Write-Ahead Logs and Durability Before Mutating Indexed State

Learning outcomes

AtlasMart's checkout service acknowledges an order update in 12 ms, but after a host crash the order returns to its previous state. The application log says “success”; the recovered database says otherwise. This lesson makes the missing boundary visible: acknowledgement is a durability claim only when the required recovery information has crossed the storage system's durable boundary.

1. The WAL rule: log durable information before dependent data pages

A write-ahead log is an append-oriented record of changes sufficient for recovery. The core ordering rule is not “write everything twice”; it is that recovery information describing a change reaches the required durable medium before a data/index page whose correctness depends on that change is considered safely persistent. A dirty page is an in-memory page that differs from its persisted copy. A checkpoint records a recovery boundary so replay need not start from the beginning of history.

For an AtlasMart order transition PENDING → PAID, a simplified path is: validate the invariant; assign transaction/log identity; append a WAL record; flush/synchronize that WAL according to the configured durability contract; update buffer/index state; acknowledge; later write dirty pages/checkpoint. After a crash, the engine replays durable log records not yet reflected in data pages. This is the REDO intuition described by PostgreSQL's WAL documentation.

2. Logical and physical logging are recovery strategies, not API slogans

Physical logging describes low-level changes to storage structures/pages; logical logging describes higher-level operations such as inserting or updating a record. Real engines may mix approaches, log physiological changes, or maintain multiple log types. The useful design question is: what information is needed to redo/undo or reconstruct a valid state after interruption, and what ordering constraints exist between that information and data pages?

Do not infer log semantics from a file named “wal”. A storage engine may use separate commit logs, manifests, journals, double-write buffers, or copy-on-write structures. The mechanism must be verified from implementation documentation and crash tests.

3. Group commit amortizes synchronization without erasing durability semantics

Group commit lets several transactions share one durable synchronization. If transactions T3 and T4 both append log records and one subsequent fsync makes both durable, both can be acknowledged after that common boundary. This can reduce synchronization overhead, but it may add queueing latency while the group forms. The tail-latency effect depends on device latency, scheduler behavior, group policy, and concurrent load.

Relaxed settings may acknowledge after an OS write, after a local memory copy, or with WAL disabled. Those policies can be intentional for reconstructible caches or ingestion buffers, but they create a documented data-loss window. “Fast commit” is therefore incomplete unless paired with “what failure may erase it?”

4. Wrong approach: acknowledge before the recoverable state is durable

A practitioner may optimize checkout latency by replying immediately after copying the change into an in-memory page or unsynchronized log buffer. The request succeeds, then power is lost before the log is durable. On restart, recovery has no durable record for the acknowledged write. This is not a stale read; it is an acknowledged-write durability violation relative to the intended contract.

The safe repair is contract-first: decide the required failure domain, move acknowledgement after the corresponding durable boundary, instrument sync latency, and test crash recovery. If a lower-durability mode is deliberately chosen, expose that fact in the service-level objective and make the lost interval reconstructible.

5. AtlasMart lab — durable offset, unsafe acknowledgement, and group commit

The lab uses a temporary local directory. It performs real Python file writes and os.fsync, but the “power loss” is deliberately modeled by truncating bytes after the last remembered fsync offset. That makes the teaching deterministic; it does not claim a particular operating system or SSD would lose exactly those bytes.

import json, os, tempfile

records = [
    {"tx": 1, "key": "order:501", "value": "PAID"},
    {"tx": 2, "key": "order:502", "value": "PAID"},
    {"tx": 3, "key": "inventory:sku-9", "value": 41},
    {"tx": 4, "key": "inventory:sku-10", "value": 18},
]

def append_record(f, rec):
    line = (json.dumps(rec, sort_keys=True) + "\n").encode()
    f.write(line)
    f.flush()
    return line

def recover(path):
    state = {}
    with open(path, "rb") as r:
        for line in r:
            rec = json.loads(line)
            state[rec["key"]] = rec["value"]
    return state

with tempfile.TemporaryDirectory() as d:
    wal = os.path.join(d, "atlasmart.wal")
    with open(wal, "w+b", buffering=0) as f:
        append_record(f, records[0])
        os.fsync(f.fileno())
        durable_offset = f.tell()
        print("tx1 ACK after fsync; durable_offset=", durable_offset)

        append_record(f, records[1])
        print("tx2 WRONG ACK before fsync; volatile_end=", f.tell())

        # Controlled crash model: bytes after the last fsync boundary are treated
        # as lost. This is pedagogical and does not predict a specific OS/device.
        f.truncate(durable_offset)
        f.flush()
        os.fsync(f.fileno())

    state = recover(wal)
    print("after crash recovery:", state)
    print("tx2 survived?", "order:502" in state)

    with open(wal, "ab", buffering=0) as f:
        append_record(f, records[2])
        append_record(f, records[3])
        os.fsync(f.fileno())
        print("tx3+tx4 group commit: one fsync for two log records")

    state = recover(wal)
    checkpoint = os.path.join(d, "checkpoint.json")
    with open(checkpoint, "w", encoding="utf-8") as c:
        json.dump(state, c, sort_keys=True)
        c.flush()
        os.fsync(c.fileno())
    print("checkpoint state:", state)
    print("recovery source remains WAL until checkpoint policy says otherwise")

Run with python wal_durability.py. No cleanup is required because the temporary directory is removed automatically.

tx1 ACK after fsync; durable_offset= 47
tx2 WRONG ACK before fsync; volatile_end= 94
after crash recovery: {'order:501': 'PAID'}
tx2 survived? False
tx3+tx4 group commit: one fsync for two log records
checkpoint state: {'order:501': 'PAID', 'inventory:sku-9': 41, 'inventory:sku-10': 18}
recovery source remains WAL until checkpoint policy says otherwise

Verification

• Transaction 1 survives because the model records a durable WAL boundary before acknowledgement.
• Transaction 2 is acknowledged by the intentionally wrong path and then disappears after the controlled crash.
• Transactions 3 and 4 share one fsync, demonstrating group commit rather than one-sync-per-transaction.
• The checkpoint contains recovered state, but the WAL remains the source of recovery truth until the checkpoint policy permits older log removal.

6. Production judgment

Use strict WAL-before-data ordering when acknowledged state must survive process/host/power failures covered by the storage contract. Relax durability only for explicitly reconstructible state and document the maximum loss window. In replicated systems, combine local WAL semantics with replica acknowledgement semantics; “quorum committed” and “durably persisted on each quorum member” are separate questions.

Bridge to Lesson 2: WAL explains recoverability, but an engine still needs an indexed structure for point/range access. We next inspect B/B+ tree pages, splits, locality, and update behavior.

Authoritative references

• PostgreSQL 18 — Write-Ahead Logging (WAL) — current official explanation of the WAL-before-data rule and REDO recovery
• SQLite — Write-Ahead Logging — official WAL-mode architecture and checkpoint discussion; implementation-specific
• ARIES paper overview entry — foundational recovery work by Mohan et al.; useful for deeper logging/recovery study
• RocksDB FAQ — implementation example noting durability consequences when WAL is disabled