Log records in a write-ahead log are stamped with a monotonically increasing log sequence number, and data pages carry one too. What is an LSN used for?

## What an LSN is A log sequence number is a value assigned to each log record such that later records always get larger values. In most engines it is literally the record's byte position in the logical log stream, which makes it both an ordering and an address: given an LSN you can seek straight to the record. Because it is monotonic, comparisons are meaningful: LSN A < LSN B means the change described by A happened before the change described by B. ## pageLSN: the link between log and data The crucial trick is that the LSN is also written into the data page. Each page header holds a **pageLSN** — the LSN of the latest log record whose effect is present in that page's bytes. When a transaction modifies the page, the engine writes the log record, then stamps its LSN into the page header. This is what welds the two structures together. ### Job 1 — enforcing the write-ahead rule mechanically The write-ahead protocol says a page may not be written to disk before the log records describing its changes. With pageLSN, that becomes a one-line check in the buffer manager: before writing page P, ensure `flushedLSN >= P.pageLSN`; otherwise flush the log first. No bookkeeping of which transactions touched which pages is needed. ### Job 2 — idempotent redo Recovery replays the log forward. But recovery itself can be interrupted — the machine can crash again halfway through — so replay must be repeatable without double-applying anything. Consider a redo record with LSN 5000 for page 42. The engine reads page 42 from disk and inspects its pageLSN: - pageLSN = 4800 → the page predates this change; apply the redo and set pageLSN to 5000. - pageLSN = 5200 → the page already reflects this change and later ones; skip it. This matters because operations are not naturally idempotent. \"Decrement the balance by 60\" applied twice is wrong; the LSN comparison is what prevents it. It also means recovery does not need to know which pages were flushed before the crash — the pages tell it themselves. ### Job 3 — a coordinate system for the whole system Because LSNs are positions in a single ordered stream, they become the vocabulary for everything built on the log: - **Commit durability**: a commit is durable once the flushed LSN reaches its commit record's LSN. Sessions waiting on commit wait on that threshold, which is what enables group commit — one flush satisfies every waiter below the new flushed LSN. - **Replication**: a replica reports the LSN it has received, written, flushed and applied. Lag is the difference between the primary's current LSN and the replica's, measured in bytes of log — a far more meaningful number than seconds. - **Point-in-time recovery**: restore a base backup, then replay archived log up to a chosen LSN (or the timestamp mapped to one). - **Checkpoints**: a checkpoint record notes the LSN from which redo must start, since everything before it is guaranteed present in the data files. ## Related bookkeeping Undo processing needs its own chain, so log records typically also carry a **prevLSN** pointing to the previous record of the same transaction — a linked list that lets the engine walk one transaction's changes backwards without scanning the whole log. Recovery designs that log the undo work itself add a pointer to the next record still to be undone, so that even rollback is restartable. ## Practical implications Monotonicity is a real constraint: LSNs cannot be reused or rewound, which is why you cannot simply hand-edit or truncate a log and why a replica promoted ahead of its former primary creates a timeline divergence that must be tracked explicitly. And operationally, LSN arithmetic is how you answer real questions: how much log a lagging replica still has to consume, how much log a stalled archiver is pinning on disk, how far behind a standby is in bytes rather than guesses. ## Interview framing \"It is a monotonic address into the log, and because the page carries the LSN of its latest change, the engine can cheaply enforce log-before-data and skip redo the page already contains.\"