LSNs

LSNs, short for log sequence numbers, are identifiers assigned to entries in a database's transaction log or write-ahead log. Each log record receives a unique, monotonically increasing LSN as it is written. The LSN serves as a location pointer within the log and provides a stable point to reference for recovery and replication operations.

How they are used: During crash recovery, the database uses LSNs to determine which log records must be redone or rolled back. Point-in-time recovery uses an end LSN to restore to a specific state. In replication and standby configurations, replica databases apply changes in LSN order to stay synchronized. LSNs also mark the progress of backups, especially log backups or incremental backups, by the LSN range covered.

Scope and examples: The term LSN is used in several systems, notably SQL Server, PostgreSQL, and MySQL. In SQL Server, LSNs identify log records within the transaction log; in PostgreSQL, the WAL location is tracked with LSNs; in MySQL, redo and binary logs also rely on LSN-like positions. Although the concept is similar, the exact format and semantics of LSNs are system-specific, and LSN values are not directly interchangeable between database platforms.

Limitations: LSNs reflect log positions, not wall-clock time; they are themselves not meaningful outside the local system. They can only be compared within the same engine or their specific replication configuration. Restores or upgrades can reset or rebase LSNs; when restored, LSN continuity depends on backup strategy and recovery processes.