Log Sequence Number (LSN)

A log sequence number (LSN) identifies a position in a database’s transaction log. PostgreSQL’s documentation defines it as “a pointer to a location in the WAL,” stored internally as a 64-bit integer representing a byte position in the write-ahead log stream and printed as two hexadecimal numbers separated by a slash, such as 16/B374D848. Because it is a position in an append-only stream, LSNs compare and subtract meaningfully: PostgreSQL supports the standard comparison operators on them, and subtracting one from another gives the number of bytes between the two locations.

What a position buys you

• Order. A larger LSN means later in the log. SQL Server’s change data capture records a commit LSN with every captured change; per Microsoft’s documentation it “identifies changes that were committed within the same transaction, and orders those transactions,” with a separate sequence value ordering changes inside one transaction.
• Resumption. A consumer that records the LSN it has processed can restart from there instead of re-reading everything.
• Duplicate rejection. If a target stores the version of the change it last applied to each row, a re-delivered older change can be recognized and ignored rather than overwriting newer data. What to store is narrower than “the LSN” — see the next section.
• A measurable gap. The distance between the log’s newest position and a consumer’s position is lag expressed in bytes — a number that can be alerted on before it becomes a missed retention window.

Other engines have their own spelling of the same idea: MySQL identifies a position by binary log file and offset, and message systems give consumers an offset within a partition. The names differ; the property that matters — a comparable position in an ordered, durable stream — is the same.

Three positions that get called “the LSN”

Row-level de-duplication is where the word starts covering more than one thing, and the difference decides whether a correct change gets thrown away.

• A physical log position. PostgreSQL’s pointer into the WAL is a byte offset. It orders records in the log, and a record’s position is not the same thing as the order in which transactions became visible.
• A commit position. SQL Server’s commit LSN “identifies changes that were committed within the same transaction, and orders those transactions.” This is the one that matches the order a replica must follow — and every change from one transaction carries the same value.
• A sequence within the transaction. Because the commit position is shared, SQL Server pairs it with a separate sequence value ordering changes inside one transaction.

So a row version has to be the pair: commit position first, then the sequence within it. Store only the commit position and a row updated twice inside one transaction becomes indistinguishable — the second change is not greater than the stored value, the merge treats it as already applied, and it is dropped. The target ends up with the first of the two updates and no error anywhere.

Two limits on comparison come with that. Positions are comparable only within one source: two servers’ numbers are unrelated, so a target fed by several databases needs a version per source rather than one column. And a capture that has been re-seeded — a new snapshot, a rebuilt slot, a new capture instance — can issue positions that do not continue the old sequence, which is why the rule to carry is “compare within a source and a capture generation,” not “larger number wins.”

Check what your connector actually emits before designing around this. Some publish the composite version as a single sortable field; others expose the parts separately and leave the combination to you.

What it is not

It is not a timestamp. A commit position orders transactions, which is what a replica must follow. It says nothing about when a business event occurred, and it cannot be compared across databases: two servers’ LSNs are unrelated numbers. SQL Server keeps a separate mapping table pairing commit LSNs with commit times precisely because the two are different things.

A recorded position is not a guarantee of exactly-once delivery. PostgreSQL’s documentation notes that a replication slot’s position is persisted only at checkpoints, so after a crash the slot “might return to an earlier LSN, which will then cause recent changes to be sent again,” and that clients are responsible for handling the same message more than once without ill effects. The position tells you what to discard; it does not discard it for you.

A position outside the retained log is unusable. Logs are pruned. SQL Server’s change data capture exposes this as a validity interval and requires that a requested extraction interval “must be fully covered” by it. A consumer that falls behind retention cannot resume from its stored LSN and must be re-seeded from a snapshot.

How LSNs are used to order, resume, and de-duplicate an incremental feed is worked through in Incremental Loading and CDC: How a Pipeline Knows What Changed. For the logging mechanism the position points into, see write-ahead logging.

References: PostgreSQL 18 documentation, pg_lsn Type; PostgreSQL 18 documentation, Logical Decoding Concepts; Microsoft Learn, What is change data capture (CDC)?.