Log Sequence Number (LSN)

An LSN is not a timestamp; it is a total order token tied to physical or logical position within a single, append-only log stream produced by one authoring node (typically the primary). Internally it is often represented as a 64-bit value, sometimes split into a segment/file identifier and a byte offset (e.g., PostgreSQL’s WAL segment : offset pairing, or MySQL’s binlog file : position before GTIDs). Because the log is append-only and single-writer, LSN comparison gives a cheap, unambiguous < or > relation without needing clock synchronization or vector-based causality tracking — it is strictly local ordering, not distributed causal ordering.

LSNs are the backbone of several mechanisms: crash recovery replays log records from the last checkpoint LSN forward; streaming replication replicas report the LSN they have received, flushed to disk, and applied, letting the primary compute replication lag in bytes rather than wall-clock time; point-in-time recovery (PITR) restores a base backup and replays WAL up to a target LSN; and logical decoding systems (e.g., Debezium, pglogical) use LSNs as resumable cursors, storing the last-consumed position so a consumer can restart exactly where it left off after a crash. In synchronous replication configurations, synchronous_commit semantics are frequently expressed as “commit does not return until replica LSN ≥ primary’s commit LSN,” making LSN the currency of durability guarantees.

Edge cases matter here. LSN space is finite and can wrap or exhaust in extreme-throughput systems, forcing operators to monitor consumption rate the same way they’d monitor disk space. Log segment recycling and WAL retention policies must account for the slowest consumer’s reported LSN — a stalled logical replication slot holding a low watermark LSN will prevent WAL cleanup indefinitely, silently filling disk until the primary halts writes. Failover complicates LSN semantics further: a promoted replica starts a new timeline (PostgreSQL explicitly models this with timeline IDs alongside LSNs) because the old primary’s LSN sequence is no longer authoritative once split-brain risk is introduced; naively comparing LSNs across timelines without timeline-awareness produces corrupted recovery decisions.

Architecturally, LSN exposes the boundary between single-node total ordering and distributed system design: it is trivial and cheap precisely because it assumes one log author, which is why systems needing multi-writer ordering must layer consensus (Raft terms/indices) or vector clocks on top rather than relying on LSN alone. Understanding LSN semantics is a prerequisite for correctly reasoning about replication lag alerts, safe WAL retention tuning, replica promotion procedures, and CDC pipeline exactly-once resumption — getting any of these wrong is one of the most common causes of silent data loss or unbounded storage growth in production database fleets.