Write-Ahead Logs and Recovery

28.1.1 PLAIN — in simple words#

1. An application can change several pages before the storage system finishes writing all of them. A crash in the middle can leave a mixture of old and new data.
2. Recovery information records enough about changes and their acceptance boundaries to reconstruct a valid state under the engine’s rules.
3. A log is useful only when its own validity, ordering and persistence are protected. An incomplete or corrupted note cannot be treated as trustworthy merely because it is called a log.

28.1.2 PLAIN — a picture in your head#

1. Before updating several filing cabinets, a clerk records the required changes in a protected journal. After an interruption, another clerk uses the journal to finish or resolve the interrupted work.
2. The journal must distinguish an accepted instruction from a draft that was never approved.
3. Where the comparison breaks: real logs often describe physical or physiological engine changes, not ordinary business sentences. Their records, checksums and transaction metadata must be interpreted by the matching engine.

28.1.3 PLAIN — a worked example#

1. A toy operation changes stock from 5 to 2 and records a reservation. If the stock page reaches storage but the reservation page does not, a crash leaves an incomplete local operation.
2. A recovery protocol can avoid forcing both pages at the exact same instant by preserving sufficient log evidence and a defined commit boundary.
3. After restart, the engine follows its recovery rules rather than guessing from whichever page happens to look newest.
4. A business audit log containing “reservation accepted” may lack the physical information needed to rebuild pages. Conversely, a WAL record may lack the human explanation needed for an audit. The two logs serve different purposes.

28.1.4 PLAIN — what is really happening inside#

1. The engine coordinates data-page modifications, log generation and transaction completion. Log persistence and page writeback obey a required order.
2. Recovery can encounter work from committed, aborted or incomplete transactions. How it handles each depends on the engine’s storage and visibility design.
3. A correct recovery process must also recognize incomplete or invalid log tails. It cannot blindly execute every byte remaining in a file as a valid operation.

28.1.5 TECHNICAL — the engineer’s version#

1. PostgreSQL’s WAL supports redo-based recovery of data-file changes. SQLite WAL uses page frames and commit information under its own format; SQLite rollback journaling is a different mechanism. [S129] [S132] [S133]
2. The logical model in this chapter replays only complete committed teaching transactions. That is not a description of PostgreSQL’s exact redo algorithm: physical recovery can replay changes whose transaction visibility is subsequently governed by transaction-status and MVCC rules.
3. Keep recovery logs, application event logs and audit records distinct in a system inventory. Similar append-only shapes do not imply identical contents, retention or authority.

28.1.6 WORDS — remember these#

1. Recovery log: information needed to reconstruct a valid stored state — engine records supporting recovery after incomplete persistence or interruption. Log tail: the final portion of the recorded sequence — a region that can be incomplete or require validity checks after failure. Recovery protocol: the rules for interpreting surviving evidence — a specified procedure connecting log validity, transaction status and stored data.

28.2.1 PLAIN — in simple words#

1. Write-ahead means that the required recovery information reaches the promised persistent boundary before the corresponding data-file change can make recovery depend on it.
2. This ordering is more important than the fact that a log file exists. A log written too late cannot explain a data change after the only in-memory explanation disappears.
3. Commit acknowledgement adds another condition: the engine must meet the configured durability promise before telling the caller that the transaction is accepted under that promise.

28.2.2 PLAIN — a picture in your head#

1. A clerk must secure the correction instruction before altering the official cabinet copy. Otherwise an interruption can leave a changed cabinet and no reliable account of the intended operation.
2. Securing the instruction can allow the cabinet changes to be completed later in a more efficient batch.
3. Where the comparison breaks: “secure” means a precise storage contract, not merely placing a note in another memory buffer. Operating-system and device caches may still be volatile.

28.2.3 PLAIN — a worked example#

1. Consider four milestones: create the log record in memory, persist the required log record, flush the changed data page, and acknowledge commit under the selected contract.
2. A valid WAL design can persist the log and required commit information, acknowledge a durable commit, and flush some data pages later. Recovery uses the surviving log if the later flush never happened.
3. An invalid ordering flushes a changed page while its required recovery record exists only in volatile memory. Losing that memory can make the page change impossible to interpret or complete safely.
4. This is an ordering argument, not a claim that every engine uses exactly four system calls or one log record per business action.

28.2.4 PLAIN — what is really happening inside#

1. Log records are often appended in a sequence, making it possible to synchronize a contiguous prefix efficiently.
2. Several commits can share one lower-level synchronization when the engine ensures that the required records for each are included. This is group commit, not a relaxation of each acknowledged transaction’s selected promise.
3. Durability settings can intentionally change when acknowledgement occurs. Such a change belongs in the application contract and benchmark report, not hidden in a performance tweak.

28.2.5 TECHNICAL — the engineer’s version#

1. PostgreSQL’s WAL ordering allows data pages to be written after the necessary log records have been flushed. Its documentation explains that a WAL synchronization can cover multiple concurrent commits. [S129]
2. PostgreSQL asynchronous commit can acknowledge before the transaction’s WAL reaches durable storage, allowing recent acknowledged transactions to be lost after a crash while preserving database consistency under the documented design. This differs from disabling fsync, which can threaten consistency. [S136]
3. An experiment comparing commit latency must record these settings. A faster result obtained by weakening the durability contract is not a like-for-like optimization.

28.2.6 WORDS — remember these#

1. Write-ahead ordering: recovery evidence must precede dependent data persistence — the ordering constraint linking log durability and data-page writes. Group commit: one synchronization supports several commits — batching log persistence while meeting each transaction’s selected acknowledgement condition. Asynchronous commit: acknowledge before full local log persistence — a configured contract that can trade recent-transaction durability for lower latency.

28.3.1 PLAIN — in simple words#

1. A log position identifies a place in the ordered recovery stream. It lets the engine say how far it has generated, written, flushed or replayed information.
2. Those progress points are different. A record can be generated but not yet persisted, or persisted on a replica but not yet applied there.
3. A position is not automatically a timestamp, a transaction number or a count of rows. Interpret it within the log stream and timeline to which it belongs.

28.3.2 PLAIN — a picture in your head#

1. A long instruction roll has marked positions. One clerk has written through position 100, another has secured through 90, and a recovery clerk has applied through 80.
2. Saying “we are at 100” hides which clerk and which boundary you mean.
3. Where the comparison breaks: real log sequence numbers may refer to byte positions rather than numbered instructions. Comparing positions across unrelated streams or timelines needs additional identity information.

28.3.3 PLAIN — a worked example#

1. Our toy log numbers records 1 through 11 for easy hand tracing. Suppose records through 9 survive a simulated failure; records 10 and 11 exist only in the discarded model memory.
2. Recovery must use the surviving prefix ending at 9. It cannot infer the contents of later records from the fact that the application intended to append them.
3. A checkpoint at position 6 supplies a known base state. Replaying valid records 7 through 9 can advance that state without rereading the model’s earlier completed prefix.
4. These small integers are record ordinals in the teaching model, not actual PostgreSQL LSN encodings.

28.3.4 PLAIN — what is really happening inside#

1. Engine metadata can associate pages with log positions so recovery knows which changes a page already reflects.
2. The engine must make redo safe under its actual record semantics. Simply replaying an increment twice would be wrong unless the protocol recognizes that the corresponding change is already applied.
3. Position continuity matters. A missing required segment is not repaired by skipping to the next available filename and hoping the data agrees.

28.3.5 TECHNICAL — the engineer’s version#

1. PostgreSQL page headers include a WAL-related LSN field. Recovery and replication use log positions with engine-specific meanings; the toy ordinal notation deliberately avoids imitating their byte-level format. [S128] [S131]
2. Distinguish generated, written, flushed and replayed progress. A lag measurement must name both endpoints and whether the difference is expressed in bytes, time or another unit. [S144]
3. Retain stream identity and timeline context with recovery positions. A numerically similar offset in an unrelated history is not interchangeable evidence.

28.3.6 WORDS — remember these#

1. Log sequence number: a position in a named recovery stream — an engine-defined offset used to identify and compare WAL progress. Durable prefix: the surviving accepted beginning of a log — the valid ordered region known to have reached the required persistence boundary. Replay position: how far recovery has applied the stream — progress measured under the engine’s interpretation of log records.

28.4.1 PLAIN — in simple words#

1. A checkpoint records a recovery starting boundary so the engine need not reconstruct everything from the beginning of time after every crash.
2. It coordinates data-page persistence and recovery metadata. It is not simply the same thing as a user transaction committing.
3. Older logs may still be needed for backups, replicas or other consumers even after local crash recovery can start later. “Checkpoint complete” is not permission to delete arbitrary recovery files.

28.4.2 PLAIN — a picture in your head#

1. A clerk periodically produces a verified cabinet state and marks where the journal continues from that state. A later recovery starts from the cabinet and reads the remaining instructions.
2. An archive keeper may still need older instructions to reconstruct an earlier date.
3. Where the comparison breaks: real checkpoints can coordinate work over an interval rather than freeze the entire system at one instant. Their correctness depends on the engine’s detailed ordering rules.

28.4.3 PLAIN — a worked example#

1. In the toy model, start with P=5. T1 commits P=2 and T2 aborts its proposed P=9. After position 6, no teaching transaction is active and the accepted state is P=2.
2. Save that state as a model checkpoint with position 6. Later T3 commits P=4 at position 9; T4 remains incomplete at position 11.
3. Recovery from the checkpoint and valid suffix returns P=4. The checkpoint does not make T4’s incomplete proposed P=1 committed.
4. This model deliberately checkpoints only at a simple quiescent boundary. It is not an implementation of a real fuzzy checkpoint algorithm.

28.4.4 PLAIN — what is really happening inside#

1. More frequent checkpoints can reduce later redo work but increase current writeback and related log overhead.
2. Less frequent checkpoints can improve some write workloads while increasing recovery work and retained log volume. The best interval depends on recovery objectives and actual measurements.
3. Long readers can also affect checkpoint progress in engines such as SQLite WAL. A checkpoint that cannot complete under its mode should not be reported as a fully reclaimed log merely because the call returned.

28.4.5 TECHNICAL — the engineer’s version#

1. PostgreSQL checkpoints establish redo boundaries and spread writeback work under configurable limits. Full-page-image behaviour interacts with checkpoint frequency, so tuning one parameter can change WAL volume as well as recovery time. [S135]
2. SQLite WAL checkpointing transfers eligible page content back to the main database and has modes with different blocking/completion behaviour. Active readers can prevent complete reset of the WAL. [S132]
3. Checkpoint retention and archive retention are different. Keep every segment required by the actual backup, recovery and replication contracts. [S131]

28.4.6 WORDS — remember these#

1. Checkpoint: a recorded starting boundary for recovery — coordinated data and metadata progress that limits required redo under an engine’s protocol. Redo horizon: the earliest recovery position still needed — the log boundary from which reconstruction must begin for a particular purpose. Checkpoint pressure: current work caused by reaching that boundary — I/O and related overhead generated by checkpoint activity.

28.5.1 PLAIN — in simple words#

1. Redo means reconstructing changes that the recovery protocol says must be represented. Rollback means preventing an unsuccessful transaction’s changes from becoming its accepted result.
2. Engines can achieve those goals differently. Some recovery designs use explicit undo information; others combine physical redo with transaction visibility so incomplete transactions do not become visible committed work.
3. Do not assume that every database literally runs an opposite SQL statement for every failed statement. Recovery operates at the engine’s own representation and transaction rules.

28.5.2 PLAIN — a picture in your head#

1. A recovery clerk can finish approved corrections and exclude unapproved drafts. The exact paperwork determines how each is recognized.
2. One office keeps before-and-after copies; another preserves new pages with approval metadata. Both need a coherent protocol, but their recovery steps differ.
3. Where the comparison breaks: “undo” is not necessarily a business compensation. Refunding a delivered purchase is a new real-world action, not erasing an uncommitted database write.

28.5.3 PLAIN — a worked example#

1. The toy model stages SET records by transaction identity. A COMMIT applies that transaction’s staged after-values; an ABORT discards them. Incomplete staged work at the end is ignored.
2. For T1: BEGIN, SET P=2, COMMIT changes accepted state from 5 to 2. For T2: BEGIN, SET P=9, ABORT leaves accepted state at 2.
3. Replaying the same complete log from the same initial state produces the same result. That is determinism of the model, not permission to apply arbitrary increments repeatedly to an already advanced state.
4. The lab validates record ordering and transaction state. A COMMIT for an unknown transaction is malformed input, not a harmless record to skip silently.

28.5.4 PLAIN — what is really happening inside#

1. Recovery must know both the base state’s provenance and the log region being applied. Replaying a suffix against the wrong checkpoint can produce plausible but incorrect records.
2. Physical redo may rebuild pages containing versions that are not visible as committed rows. Visibility and physical reconstruction are related but separate engine responsibilities.
3. A recovery tool should fail clearly when required evidence is absent or incompatible, rather than inventing a clean state by dropping unexplained records.

28.5.5 TECHNICAL — the engineer’s version#

1. The toy recovery function uses logical after-values and serial transaction fixtures. It does not model page LSN checks, torn pages, interleaved conflicting writers, transaction-ID wraparound or an engine’s log parser.
2. PostgreSQL crash recovery is redo-oriented; MVCC and transaction-status information govern visibility of tuple versions. Do not equate the toy’s “apply only committed transactions” loop with the exact PostgreSQL implementation. [S129] [S119]
3. SQLite rollback journals preserve original page content, whereas WAL frames record revised page content. These mechanisms illustrate why recovery terminology must be attached to a concrete format. [S133]

28.5.6 WORDS — remember these#

1. After-value: the state an accepted update should leave — a logical replacement used by this teaching recovery model. Undo information: evidence for reversing unaccepted changes — recovery data used by designs that require explicit restoration of earlier state. Recovery base: the known state from which replay begins — a checkpoint or backup identified together with its matching log boundary.

28.6.1 PLAIN — in simple words#

1. A useful recovery trace lists what survived, what did not, and what the protocol can conclude. It does not fill missing evidence with the application’s intention.
2. Testing different surviving prefixes exposes the importance of commit boundaries. An update record alone is not necessarily an accepted transaction.
3. A simulated prefix is a teaching experiment. A real crash test additionally needs control over processes, storage, flush settings and the failure being injected.

28.6.2 PLAIN — a picture in your head#

1. Imagine the journal is cut at a chosen line. The recovery clerk may use only the lines still present and the known starting cabinet state.
2. A line that would have been written next is not evidence merely because the clerk remembers planning it.
3. Where the comparison breaks: real failures do not always leave a neat cut at a record boundary. Checksums, framing, partial writes and device behaviour determine which bytes are valid.

28.6.3 PLAIN — a worked example#

1. Use the following complete synthetic log. SET means a staged after-value, and the initial state is P=5.

1 BEGIN T1       2 SET T1 P=2       3 COMMIT T1
4 BEGIN T2       5 SET T2 P=9       6 ABORT T2
7 BEGIN T3       8 SET T3 P=4       9 COMMIT T3
10 BEGIN T4     11 SET T4 P=1       [no commit]

2. A surviving prefix ending at 2 recovers P=5. Ending at 3 recovers P=2. Ending at 5 or 6 still recovers P=2.
3. Ending at 9 recovers P=4. Ending at 11 also recovers P=4 because T4 lacks a commit. Recovering from the position-6 checkpoint P=2 plus the remaining suffix also yields P=4.
4. Every expected result follows from the model’s explicit rules. The example makes no claim that a real device preserved exactly those prefixes during a power cut.

28.6.4 PLAIN — what is really happening inside#

1. A real recovery investigation preserves the original files, relevant log segments, configuration and diagnostic output before attempting changes.
2. A successful restart proves that the engine reached a state it accepted under its checks. Business reconciliation and expected-record checks are still needed to establish the recovery target’s meaning.
3. Failure to find a record can mean it was never committed, not included in the surviving history, restored to an earlier target or queried incorrectly. The trace must distinguish these possibilities rather than assigning a convenient cause.

28.6.5 TECHNICAL — the engineer’s version#

1. The companion tests every listed prefix and malformed transaction ordering in memory. No process is killed, no power is cut, and no hardware durability claim follows from those tests.
2. Before a real failure experiment, state the fault model: process termination, operating-system crash, abrupt power loss, device loss or a network partition. Each exercises different layers of the persistence chain. [S130]
3. For PostgreSQL recovery from backups, use matching physical base-backup and continuous WAL evidence under the documented procedure. A logical SQL dump is not a physical WAL-replay base. [S131]

28.6.6 WORDS — remember these#

1. Crash trace: the ordered evidence surrounding an interruption — a record of persisted state, surviving log material and recovery decisions. Fault model: the failures an experiment assumes or injects — the specific process, operating-system, device or communication conditions being tested. Recovery target: the intended state or boundary to restore — a chosen time, log position or other engine-supported objective with matching evidence.