When Did This Transaction Happen? PostgreSQL Snapshots, LSNs, Oracle SCNs, and More

When did a database change happen?

This isn't just academic. Incremental replication must order committed changes and resume exactly without locking writers. Migration validation checks if source and target are the same state, even as both change. Audit and incident analysis reconstruct who changed data, when, and when others could see it. Application-log correlation links request timestamps with database transactions and resulting commits.

It has business meaning. An editor changes a document at 10:00🕐, saves at 10:04🕐 to publish it, and gets confirmation at 10:05🕐. Other sessions can't see the uncommitted change at 10:00🕐, but later services might see this timestamp. Which moment should an updated_at field report?

If correlating with an application log, the request or statement time may be correct. If describing the user's experience, it may be wrong: users distinguish "I edited", "I saved", and "I published". These are separate business events. Commit visibility, durability, client acknowledgment, and downstream publication are separate system events.

In a nonblocking MVCC database, there is no universal updated_at. A row version can be created, stay private, become visible at commit, reach replicas, and be shown to users later. The correct timestamp depends on the application's question.

That sounds like one question, but it is at least five:

1. When did the transaction begin?
2. Which committed state did a statement read?
3. When was a new row version created?
4. When did the transaction become committed and visible?
5. Which durable log position protects that commit?

PostgreSQL presents different coordinates: xmin:xmax:xip_list for transaction visibility, an LSN for write-ahead log position, and a transaction ID for tuple version changes. None are wall-clock timestamps.

Oracle appears more unified, using the System Change Number (SCN) for read consistency, transaction commits, checkpoints, and recovery. However, claiming "Oracle has only an SCN" is inaccurate, as it also requires a transaction ID, undo address, and redo position. YugabyteDB employs HybridTime as the MVCC read and commit coordinator, but provisional writes initially have different timestamps. SQL Server, MySQL/InnoDB, and MongoDB/WiredTiger split these responsibilities.

The key comparison isn't "which database has the best clock?" but rather: "which coordinator answers which ordering question?"

AI disclaimer: I wrote this with a lot of help from GitHub Copilot. I used it to make the comparison more thorough and to check each equivalence against the original documentation and code. Any interpretation and remaining errors are my own.

One transaction has several times

Consider this deliberately generic sequence:

BEGIN
 -> choose read point
 -> UPDATE
 -> COMMIT (some databases may run the following in different order)
      -> make log durable (disk or replicas)
      -> make changes visible
 -> reply (successful commit)

Some of these events can coincide in one implementation, but they remain different promises:

The logical visibility rule is similar in all MVCC systems. Here, v is a candidate row or document version, and q is the query evaluating whether it can see that version:

visible (v,q) = 
  ownTransaction(v,q)
  OR
  ( committed(v) AND ( commitPoint(v) <= readPoint(q) ) )

This is a model, not a specific product implementation. PostgreSQL and InnoDB don't store commitPoint as a per-row scalar. They determine it from transaction ID, status, and active transactions in a snapshot. Oracle and YugabyteDB clarify the logical commit process, but all engines require the ownTransaction exception for uncommitted changes.

Why the commit coordinator is usually somewhere else

This separation exists for a physical reason. The final commit coordinate does not exist when the transaction modifies its first row. By commit time, one transaction may have changed millions of rows, and many dirty pages may already have left memory. Revisiting all of them would make commit latency a function of transaction size and would undermine write-ahead logging's no-force rule: commit should make the log durable, not force every data page.

The common answer is indirection. A row version records a transaction marker or provisional time. Commit publishes the outcome and final coordinate in transaction or log metadata. Readers resolve the marker through that metadata; cleanup later may copy enough info into blocks or final versions to avoid lookup.

The implementations differ, but the pressure is the same:

• PostgreSQL tuples retain xmin/xmax. Commit status lives in pg_xact, and the optional pg_commit_ts side data maps XID to wall-clock commit time.
• Oracle rows refer through an ITL entry and XID to transaction-table metadata where commit records the SCN. Block cleanout can happen later.
• YugabyteDB first writes provisional intents. The status tablet records one final commit HybridTime, and asynchronous apply later creates regular records at that time.
• InnoDB rows retain DB_TRX_ID and DB_ROLL_PTR. An internal transaction serialization number is assigned near commit for purge ordering, but it is not copied into each row version.

This explains retention limits. If an engine keeps the XID-to-commit-time mapping as auxiliary metadata, it can age independently of the business row. Recovering an exact commit time years later differs from deciding visibility while the version history is still live.

PostgreSQL: pg_current_snapshot() is a visibility boundary

PostgreSQL documents the text representation of a pg_snapshot as xmin:xmax:xip_list. For example:

select pg_current_snapshot();

 pg_current_snapshot
---------------------
 10:20:10,14,15

The three components have precise meanings:

• xmin is the lowest transaction ID that was still active. Lower IDs have completed, either by committing or rolling back.
• xmax is one past the highest transaction ID that had completed. IDs at or above it had not completed at snapshot time and are invisible to this snapshot.
• xip_list contains the top-level transactions that were still in progress between the two horizons. It does not list subtransaction IDs.

An ID between xmin and xmax that is absent from xip_list has completed. Its commit status then says whether it is visible or dead. This is why the snapshot is a visibility boundary over transaction identities, not a timestamp and not a list of all committed transactions.

There is also an unfortunate name collision. Snapshot xmin and xmax are horizons. Tuple xmin and xmax are transaction IDs in a row-version header: the inserting transaction and, normally, the deleting or superseding transaction. The visibility algorithm relates them, but they are not the same field.

XID order is first-write order, not commit order

A PostgreSQL transaction initially has a virtual transaction ID. A normal 32-bit XID is allocated from a cluster-wide counter when the transaction first writes to the database. A read-only transaction may never get one. Calling pg_current_xact_id() forces allocation; pg_current_xact_id_if_assigned() does not.

The documentation makes the ordering guarantee narrow: a lower XID started writing before a higher XID. It may have started the SQL transaction later, and it may commit much later.

This schedule is possible:

T1 first write   ->   XID 100    ->   remains open
T2 first write   ->   XID 101    ->   commits
Reader snapshot  ->   100:102:100

The reader can see committed work from 101 while 100 is still invisible. A single high-water mark could not describe that state; the exception list is the important part.

This ordering also explains PostgreSQL's famous transaction ID wraparound problem. The XID stored in tuple headers is only 32 bits. Normal XIDs are compared with modulo-2³² arithmetic, so any XID has about two billion values considered older and two billion considered newer. VACUUM must freeze sufficiently old tuple versions before they cross that half-range and appear to come from the future. PostgreSQL's public xid8 adds an epoch for observation, but ordinary heap tuple headers still carry the compact 32-bit XID.

Read time

At READ COMMITTED, each command starts with a new snapshot. Two SELECT statements in one transaction can therefore see different commits. At REPEATABLE READ and SERIALIZABLE, the transaction keeps the snapshot chosen for its first non-transaction-control statement. In all cases, the current transaction's earlier commands require additional self-visibility and command ID rules that are not serialized in the public xmin:xmax:xip_list string.

PostgreSQL can export this read point with pg_export_snapshot() and import it in another transaction with SET TRANSACTION SNAPSHOT. The token remains valid only while the exporting transaction stays open. Parallel pg_dump uses synchronized snapshots so all workers see identical contents, and pg_dump --snapshot can align a dump with another session or a logical replication slot. This is often the right coordinate for comparing a source and target during migration: first agree on the state being compared, then compare the rows.

Update time

An UPDATE normally marks the old tuple with the updater's XID in xmax and creates a replacement tuple with that XID in xmin. The transaction also emits WAL records for WAL-logged storage. At this point, another transaction cannot infer a commit time from the tuple. It sees an XID whose status may still be in progress, committed, or aborted.

Commit time and WAL time

PostgreSQL's pg_lsn is a 64-bit byte position in the WAL stream. WAL records are appended, and their insert positions increase monotonically. The following three positions are deliberately distinct:

select pg_current_wal_insert_lsn(),
             pg_current_wal_lsn(),
             pg_current_wal_flush_lsn();

• The insert LSN is the logical end after records have been inserted into shared WAL buffers.
• The write LSN is how far those buffers have been written out.
• The flush LSN is how far PostgreSQL knows the WAL is on durable storage.

An LSN sampled after an UPDATE does not identify the visibility of that update. Other backends write to the same WAL stream, so their records can be between this transaction's records. The tuple itself does not store its WAL LSN.

There is an important qualification to the slogan "an LSN is only a byte position." For a write transaction, the position of its commit record determines its order among other records in the WAL stream. PostgreSQL's logical decoding API provides a commit_lsn, and the documentation states that concurrent transactions are decoded in commit order.

So these are both true:

• A generic current LSN is not a transaction snapshot or a commit time.
• The LSN of a specific commit record is a useful order for committed change streams.

That order still does not say which client received its success response first. Group commit can flush several commit records together, and process or network scheduling can reorder the replies. With synchronous_commit set to off, PostgreSQL can report success before that commit record reaches durable storage. Logical decoding waits until the transaction has safely been flushed.

PostgreSQL marks the XID committed in pg_xact. If track_commit_timestamp is on, which is not the default, it also retains a wall-clock commit timestamp that can be queried with pg_xact_commit_timestamp(). The mapping is stored separately under pg_commit_ts and is WAL-logged for recovery and physical replication. It is not added to tuple headers, and vacuum routinely removes old entries once their XIDs are no longer needed. This is optional historical metadata, not the MVCC snapshot coordinate and not a permanent audit trail.

Replication adds more positions, not a global clock

Physical streaming replication turns one WAL position into a pipeline. On the primary, it inserts, writes, and flushes a record. A standby then receives, writes, flushes, and replays it. pg_stat_replication exposes the standby's write_lsn, flush_lsn, and replay_lsn as reported to the sender.

flowchart LR
    I[Primary insert] --> W[Primary write]
    W --> F[Primary flush]
    F --> R[Standby receive]
    R --> SW[Standby write]
    SW --> SF[Standby flush]
    SF --> A[Standby replay]
    A --> V[Visible to standby queries]

The synchronous_commit mode selects which boundary a committing session must wait for. In the usual synchronous-standby configuration:

These modes change acknowledgment and durability, not the transaction's MVCC snapshot. They also explain why "committed" needs a subject: committed in the primary's transaction state, durable locally, durable remotely, and visible on a standby are distinct observations.

PostgreSQL 19, still in beta as I write this, makes those boundaries directly waitable:

WAIT FOR LSN '0/306EE20';
WAIT FOR LSN '0/306EE20' WITH (MODE 'standby_flush', TIMEOUT '5s');

The default standby_replay mode is useful for read-your-writes on an asynchronous replica. Other modes wait for standby write, standby flush, or primary flush. This does not turn the LSN into an MVCC snapshot: the client must capture the relevant primary LSN, and WAIT FOR compares its numeric value without identifying the timeline. Promotion therefore requires the application to reconsider whether the token still belongs to the expected history.

After failover, an LSN alone is not a universal history identifier. PostgreSQL creates a new timeline when recovery diverges; positions before the fork share history, while post-fork records are identified by their timeline and LSN. Logical replication has another namespace: replication origins can remember a source LSN and source timestamp for replayed transactions, but those values remain coordinates of that source, not a new global commit clock.

Two-phase commit does not create one either. PREPARE TRANSACTION preserves a local XID under a caller-supplied global transaction identifier (GID); its changes remain invisible until COMMIT PREPARED. An external coordinator can use matching GIDs at several databases to obtain an atomic outcome, but each PostgreSQL cluster still has its own XIDs, WAL timelines, LSNs, and clocks.

Finally, now() is not commit time either. It is transaction_timestamp(), fixed at transaction start. statement_timestamp() marks receipt of the current command, and clock_timestamp() reads the changing wall clock. A default such as updated_at default now() therefore records neither the physical update instant nor the commit instant of a long transaction.

Oracle: one SCN family, but not one coordinate

Oracle's SCN is much closer to the single logical clock people often look for. Oracle defines it as a monotonically increasing logical timestamp that orders database events. The same concept appears in several places:

• a query SCN identifies the consistent point a statement must read;
• a transaction has a start SCN and change SCNs;
• commit generates and records a commit SCN;
• block and data-file checkpoint SCNs bound recovery work;
• Flashback and point-in-time recovery accept SCNs.

Those are related SCN values, not one value assigned at BEGIN and reused for every purpose.

Read time

At Oracle's default READ COMMITTED, a query is consistent to the SCN at which the statement opens. At SERIALIZABLE or READ ONLY, queries use the transaction's read point. If a current block contains changes that are too new, Oracle copies the block and applies undo to build a consistent-read clone.

The SCN still cannot be the entire visibility rule. A session must see its own uncommitted update and exclude another session's uncommitted update, even if both are reading with the same query SCN.

The useful difference is that Oracle exposes the SCN as a historical read coordinate. AS OF SCN asks for the committed state at one point, while VERSIONS BETWEEN SCN returns committed row versions over an interval:

select * from orders as of scn :read_scn;

select versions_startscn, versions_endscn, versions_xid, status
from orders versions between scn :scn_a and :scn_b;

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER can set the same read point for ordinary queries in a session. These features depend on retained undo, or on Flashback Archive when configured for longer history.

Update time

Oracle allocates a transaction ID at the first DML statement, when it assigns an undo segment and a transaction-table slot. The XID encodes:

undo segment number : slot number : sequence number

An update stores the old values in undo and records transaction information in the data block's interested transaction list (ITL). Rows changed by that transaction refer to its ITL entry. The ITL points through the XID and undo block address (UBA) to the transaction table and undo chain. The number of ITL entries is limited per block. Applying undo to restore a consistent read snapshot also restores previous ITLs, so the list virtually covers undo retention.

Commit time

At commit, Oracle generates a commit SCN and records the committed state in the undo segment's transaction table. LGWR writes the remaining redo and the transaction SCN to the online redo log. By default, the client waits for that redo to be durable; asynchronous commit options can weaken that coupling. Data blocks do not all have to be written at commit.

Oracle may clean transaction information from modified blocks during commit. If it does not clean a block, a later reader finds the XID in the ITL, checks the undo segment header for the transaction's status and commit SCN, and performs delayed block cleanout.

We can summarize it as: The Oracle transaction ID identifies the transaction-table entry from which a reader can discover commit status and commit SCN.

But "just an identifier" hides useful work. The XID also locates the undo segment and slot, distinguishes slot reuse through its sequence number, identifies row-lock ownership, and lets a transaction recognize its own changes. Once the transaction is committed, the commit SCN supplies the logical ordering test.

Putting a commit SCN in a table is a special operation

This indirection is what keeps Oracle commit fast. The database can publish the outcome in the undo-segment transaction table and make the redo durable without visiting every changed row or forcing every dirty block. Transaction-table slots and undo are reusable, and delayed cleanout is visibility machinery, not an indefinite audit history of exact row commit times.

Oracle does have a fascinating exception: USERENV('COMMITSCN'). It is absent from the general USERENV parameter list, but Oracle's current error reference documents two unusually strict rules:

• it may be invoked only once in a transaction (ORA-01721);
• it must be a top-level expression in an INSERT ... VALUES clause or the right-hand side of an UPDATE assignment (ORA-01725).

While COMMIT_SCN was used for trigger-based logical replication before Oracle acquired redo-based GoldenGate, Oracle Database 23.26.2's DBVERIFY executable is still a concrete consumer:

create table SYS_DBV<pid>$ (myscn number);
insert into SYS_DBV<pid>$ values (userenv('COMMITSCN'));
-- OCITransCommit
select myscn from SYS_DBV<pid>$;
drop table SYS_DBV<pid>$;

It reads the committed value back as an Oracle NUMBER, converts it to a packed SCN, and puts it in the verification context used by block checks. A live SQL trace and TKPROF run confirmed the lifecycle. Between the insert and commit, the server recursively executed UPDATE SYS_DBV<pid>$ SET MYSCN=:1 WHERE ROWID=:2, affecting the inserted row; DBVERIFY then selected the value, locked the table exclusively, and dropped it. This is an operational SCN boundary, not a feature self-test.

The 23.26.2 server binary also names a precommit ... commit scn patch callback. Together, those clues describe something very different from evaluating SYSDATE: one selected stored value is patched with the SCN known on the commit path. The restrictions are also the point. Oracle can arrange this for one explicit target; doing it implicitly to every ordinary row a transaction touched would destroy the fast-commit design.

Commit-SCN materialized-view logs apply the same idea at a system-maintained boundary. CREATE MATERIALIZED VIEW LOG ... WITH COMMIT SCN chooses them over timestamp-based logs. Current server strings show log rows carrying XID$$ and refresh SQL joining that XID to SYS.SNAP_XCMT$, whose observed columns map XID to COMMIT_SCN:

many MLOG$ change rows --XID$$--> one XID / COMMIT_SCN mapping

That is a scalable commit-time join, not a rewrite of the base-table rows. The mapping is maintained and purged for materialized-view refresh. It should not be treated as a permanent application audit table.

This is also separate from XStream and GoldenGate-style capture. XStream was introduced in 11g Release 2, and mines redo into logical change records delivered in committed transaction order. Commit-SCN materialized-view logs were added in 12c Release 1 for fast refresh. One did not simply replace the other: they are different consumers of commit ordering through different capture paths.

Oracle also has redo addresses

SCN is not a byte address in redo. V$LOGMNR_CONTENTS exposes the separation particularly well. For one mined change, it can report:

• SCN, START_SCN, and COMMIT_SCN for logical database time;
• XIDUSN, XIDSLT, and XIDSQN for transaction identity;
• UBAFIL, UBABLK, and UBAREC for the undo record;
• RBASQN, RBABLK, and RBABYTE for the redo byte address (RBA).

Oracle's term "log sequence number" names the generation of a redo log file. The RBA adds a block and byte offset to locate an individual redo record. In this sense, PostgreSQL's pg_lsn is closer to an Oracle RBA than to an Oracle SCN.

An SCN is also not an exact wall-clock timestamp. SCN_TO_TIMESTAMP() returns an approximation, usually with three-second precision, and the database retains the mapping for a limited period. ORA_ROWSCN is another trap: it is block-level unless the table was created with ROWDEPENDENCIES; only then does Oracle maintain row-level dependency information. Even in that fine-grained mode, ORA_ROWSCN is only a conservative value greater than or equal to the last modifying transaction's commit SCN, not necessarily that exact SCN. Flashback Version Query uses its own VERSIONS_* pseudocolumns instead.

RAC and distributed databases solve different clock problems

Oracle RAC has several instances opening one database. They must coordinate one database SCN domain across the cluster interconnect. Oracle has changed the implementation over time: older releases exposed MAX_COMMIT_PROPAGATION_DELAY, while current binary strings still name a "broadcast-on-commit SCN mode."

Database links connect independent databases, and the documented guarantee is weaker. Oracle says each system has its own SCN. The systems synchronize their SCNs at the end of each remote SQL statement and at the start and end of each transaction, but cannot keep them absolutely synchronized. A gap can therefore produce a remote read that is consistent yet slightly out of date. A dummy remote query or a transaction boundary forces another synchronization point.

Distributed two-phase commit adds common identity and outcome, not a permanent global clock for all work at all sites. The global transaction ID is the same across participants, and a commit-point site records the decisive outcome. For an in-doubt transaction, DBA_2PC_PENDING.COMMIT# exposes what the documentation calls its global commit number; COMMIT FORCE can even reuse the SCN observed at a site that already committed. That synchronizes the resolution of one distributed transaction. It does not give unrelated transactions in independent databases a total order.

A monotonic coordinate still has a finite representation

There is also a naming fossil. Modern Oracle documentation expands SCN as System Change Number, but the 23.26.2 Instant Client still carries the old ORA-08209 explanation: "The System Commit Number has not yet been initialized." This directly shows that System Commit Number existed in Oracle's terminology and survived in old error text. It is not enough to date a formal rename or to claim that early SCNs represented commits only.

Monotonic does not mean infinite. Oracle 12.2 increased the SCN capability: the documented ORA-24442 is raised when a newer database tries to transfer an SCN that exceeds what a pre-12.2 database or client can represent. Current binaries call this BigSCN, track SCN headroom, and include compatibility rollover paths. This differs from PostgreSQL XID wraparound because the SCN is a forward-moving logical time rather than a circular tuple identity. But RAC coordination and database-link synchronization can propagate higher observed SCNs, so capacity and compatibility are distributed-system concerns, not merely local counters.

YugabyteDB: commit HybridTime becomes the MVCC time

I include YugabyteDB because I have also worked with it, and its more modern distributed architecture makes time part of the consistency protocol, not just a diagnostic label. DocDB stores versions in an LSM tree whose key ends in a HybridTime. A hybrid logical clock (HLC) has a physical and a logical component. It follows causal order and is monotonic on each node, but its physical component should not be confused with a perfectly synchronized wall clock.

Read time

A distributed read chooses a hybrid time ht_read. Each tablet waits until that point is safe to read and normally includes a version when ht_record <= ht_read. Clock uncertainty can reveal a record that might have preceded the request even though its HybridTime is above the first read point; YugabyteDB then advances the read time and restarts the read. This is why the read protocol also carries safe time and local/global limits.

YSQL can also synchronize read points across sessions with the PostgreSQL-compatible pg_export_snapshot() and SET TRANSACTION SNAPSHOT syntax. The exporting transaction must remain open, and current YugabyteDB documentation limits export/import to REPEATABLE READ. This shares one distributed snapshot; it is not arbitrary historical time travel.

Update time: provisional HybridTimes

A distributed transaction does not put uncommitted values directly beside regular visible values. It writes provisional records to a separate RocksDB instance named IntentsDB. The documented primary-intent shape is:

DocumentKey, SubKeys..., LockType, ProvisionalRecordHybridTime
        -> TxnId, Value

The provisional HybridTime is not the commit time. Different intents in one transaction generally have different provisional HybridTimes. The transaction UUID ties them to one status record and lets the transaction see its own intents; other readers do not treat pending intents as committed values.

Commit time: one final HybridTime

The transaction manager asks a transaction status tablet to commit. That tablet chooses the current HybridTime while appending the committed status to its Raft log. Once the status change is replicated, the transaction has one commit HybridTime and all its provisional records become logically visible.

A reader that encounters a not-yet-cleaned intent asks the status tablet. If the transaction committed, the reader treats the intent as if it were already a regular record at the final commit HybridTime.

Cleanup is asynchronous. Each participant later Raft-replicates an apply record containing the transaction ID and commit HybridTime, removes the provisional records, and writes regular records to RegularDB with that final HybridTime. We can summarize it as: YugabyteDB stores a timestamp in both provisional and regular records, but the provisional write timestamp and the final commit timestamp are different.

Raft log positions remain a separate coordinate. Each tablet, including the status tablet, has its own Raft log and operation order. No single cluster-wide Raft byte position exists, analogous to a PostgreSQL WAL LSN. HybridTime provides the cross-tablet MVCC coordinate, while Raft provides replicated order and durability inside each tablet group.

HybridTime orders causality, not an omniscient wall clock

The HLC guarantee is precise. Events on one server receive increasing HybridTimes. If event A sends an RPC that leads to event B on another server, the clock value travels with the message, and B receives a greater HybridTime. That covers causal chains.

The implementation packs HybridTime into an unsigned 64-bit value: physical microseconds occupy the high bits, and 12 low bits hold the logical component. If the logical component fills, YugabyteDB carries it into the physical part. The source notes microsecond accuracy through 2100 and beyond. The 64-bit width provides headroom, but the clock algorithm is what guarantees monotonicity: when the physical clock goes backward, the last physical component is retained and the logical component advances.

Two nodes that have exchanged no relevant messages can still have physical clock skew. Their HLC values are comparable as tuples, but that numeric order does not prove a causal relationship or exact wall-clock order between the independent events. Once they communicate, the lower clock advances to the higher observed value.

This is why the read protocol cannot simply sample any node's HLC and declare the result complete. It calculates a global_limit from physical time plus the configured maximum clock skew, waits for tablet safe time, and restarts when it encounters a possibly earlier event above the chosen read point. Together, the timestamp and the uncertainty protocol provide the guarantee.

SQL Server: XSN, LSN, and rowversion

SQL Server exposes almost every possible source of naming confusion.

First, its traditional READ COMMITTED uses locks. Statement snapshots appear when READ_COMMITTED_SNAPSHOT (RCSI) is enabled, and transaction snapshots appear at SNAPSHOT isolation.

For row versioning, SQL Server assigns a transaction sequence number (XSN) when a participating transaction first accesses the version store. For a SNAPSHOT transaction, the engine also records the transactions active at the snapshot. It follows a row's version chain to the newest version whose XSN is below the reader's sequence and was not in that active set. This is conceptually close to PostgreSQL's horizons plus in-progress transactions.

RCSI chooses a new sequence point for each statement. SNAPSHOT keeps the transaction-level view. On update, SQL Server stores the previously committed row image in the version store and links the current row to it. Row-version metadata includes a transaction sequence number and a version pointer. Writers still use write locks, or transaction-ID locks with optimized locking.

SQL Server keeps these identifiers separate:

• transaction_id primarily identifies the transaction for locking and is unique only within an instance;
• transaction_sequence_num (XSN) identifies a transaction in row versioning and participates in snapshot visibility tests;
• LSN identifies a record in one database's transaction log.

Every new log record has a higher LSN than the preceding record. The DMV sys.dm_tran_database_transactions exposes begin, latest, savepoint, and commit LSNs for a database transaction. Change Data Capture makes the meaning especially explicit: __$start_lsn is the commit LSN, groups changes from one transaction, and orders transactions; __$seqval orders changes inside it. CDC stores a separate mapping from commit LSN to commit wall-clock time.

This is a more directly exposed version of the PostgreSQL qualification: an LSN is a log position, and the commit record's position can be used as commit order. It is still not the XSN snapshot boundary or a wall-clock time. A fully durable commit flushes the log before completion; delayed durability can return before it hardens the log.

Finally, the SQL Server rowversion data type is unrelated to all of this. It is an eight-byte database counter placed in rows that declare such a column. It advances on INSERT or UPDATE, even when values are unchanged. It is useful as an optimistic concurrency token, but it is neither a timestamp nor a commit sequence, and the deprecated synonym timestamp makes the name worse.

MySQL/InnoDB: a read view, an undo chain, and two logs

"MySQL" can use different storage engines, so this comparison focuses on InnoDB.

An InnoDB consistent read sees changes committed before its read point, excludes later and uncommitted transactions, and includes its own earlier statements. At the default REPEATABLE READ, the first consistent read establishes the transaction's snapshot. At READ COMMITTED, each consistent read gets a fresh snapshot.

Internally, the read view records transaction-ID limits and the active write transactions. It is the same broad strategy as PostgreSQL and SQL Server snapshot versioning: transaction assignment order plus an active exception set, not a commit timestamp in each row.

InnoDB updates clustered-index records in place. Each record has:

• DB_TRX_ID, the six-byte ID of the last transaction to insert or update it;
• DB_ROLL_PTR, a seven-byte pointer to the undo record from which an older version can be reconstructed.

At update time, the writer has an InnoDB transaction ID, creates undo, generates redo, changes the current record, and holds the conflicting lock. The ID does not become a commit timestamp when the transaction commits. Commit changes its status and makes the version eligible for new read views.

There is a second internal number, but it is easy to over-translate it. MySQL 8.4 source defines trx->no as a transaction serialization number, initially TRX_ID_MAX, assigned shortly before the transaction moves to COMMITTED_IN_MEMORY. InnoDB puts update undo into history in this order, and a read view's m_low_limit_no tells purge which older transaction histories no view still needs.

This is a commit-near ordering horizon, not a miniature Oracle SCN. It is not stored in clustered records, exposed as a stable application token, or used as wall-clock time. The source even notes that transaction numbers need not follow commit LSN order exactly when transactions use different rollback segments, although causal visibility still preserves the necessary order.

InnoDB's redo log has an ever-increasing LSN. MySQL 8.4 exposes current, flushed-to-disk, and checkpoint LSNs. As in PostgreSQL, redo from concurrent transactions can interleave. The LSN tracks recovery progress, not the read view or a row's commit time.

MySQL then adds a second log at the server layer. The binary log is used for replication and point-in-time recovery. MySQL caches a transactional workload and writes it there as a unit at commit. With the default binlog_order_commits=ON, storage-engine commits are serialized in binary-log order. If it is disabled, transactions in one group may commit in an order different from their binary-log positions.

When GTIDs are enabled, a binary-logged client transaction receives a value of the form:

source_uuid:sequence_number

The sequence number follows commit order on that source. It is an excellent replication identity and ordering coordinate, but it is not DB_TRX_ID, is not stored in each InnoDB row version, and does not define one scalar order across unrelated source UUIDs. original_commit_timestamp is separate wall-clock metadata propagated by replication.

The MySQL server coordinates its binary log and InnoDB through internal two-phase commit. Durability therefore depends on both sides, notably sync_binlog and innodb_flush_log_at_trx_commit, rather than on the MVCC transaction ID.

MongoDB/WiredTiger: three time domains in one stack

MongoDB belongs in this comparison because it was designed around replication, while WiredTiger offers a distinct OLTP storage choice underneath it. The database server and its storage engine do not expose the same time abstraction. At least three time domains coexist:

1. $clusterTime and operationTime are logical causal tokens returned to clients;
2. oplog OpTime orders replicated operations within a replica-set history;
3. WiredTiger transaction IDs and timestamps determine storage-engine visibility and history.

They often carry related BSON Timestamp values, but their roles are not interchangeable.

Read and update time

MongoDB's logical clock is Lamport-like. Servers and drivers gossip $clusterTime, advancing it when they observe a later value. Its BSON Timestamp contains seconds plus an ordinal, but it is an ordering token, not an elapsed-time measurement. operationTime lets a client carry the logical time of an acknowledged operation into a causally dependent one.

A read with read concern "snapshot" uses one atClusterTime. Outside a multi-document transaction, a client may supply it; otherwise, mongos, or a single-member replica set, selects a recent majority-committed snapshot. The storage engine implements that point using a WiredTiger read timestamp and a transaction snapshot.

WiredTiger first gives a writing transaction an internal transaction ID and puts each modification on an in-memory update chain. Snapshot visibility checks both that ID and, for timestamped data, the update's commit timestamp. An ordinary update is initially uncommitted, not automatically prepared. Prepare timestamp and durable timestamp are additional states used only when a transaction actually enters the prepared protocol.

For ordinary unprepared transactions, WiredTiger is no-steal at the transaction level: writes first live in memory and are not written to disk before the whole transaction commits. Rollback can mark those in-memory updates aborted instead of physically undoing pages. The tradeoff is a hard cache constraint. MongoDB aborts an uncommitted transaction that creates excessive WiredTiger cache pressure, and returns TransactionTooLargeForCache for a transaction too large to ever fit. Prepared transactions are a separate protocol with additional persistence rules; they should not be used to describe every ordinary update.

The visible BSON document contains none of this metadata. It has no automatic transaction ID, read timestamp, or commit timestamp field. An ObjectId may encode approximate client-side creation time, and an application may add updatedAt, but neither is database commit time.

Commit, oplog, and durability

On a replica set, the oplog is the ordered history of logical writes. Its ts field is a BSON Timestamp; MongoDB guarantees oplog timestamp uniqueness within one mongod. An OpTime pairs that timestamp with the election term:

OpTime = { ts: Timestamp(seconds, ordinal), t: election_term }

MongoDB supplies logical timestamps from this domain to WiredTiger as commit timestamps for replicated changes. WiredTiger then installs the timestamp on the transaction's internal updates; reconciliation can persist it in an on-disk time window. Multi-document transactions may package many changes into applyOps records, so an oplog entry is not necessarily one BSON document change.

A transaction spanning shards adds distributed prepare and commit coordination. Participants can prepare at different timestamps, and the coordinator chooses one commit timestamp that makes the transaction visible across its participants. Each shard still has its own replica-set oplog; no single byte position covers the whole sharded cluster.

Replica-set status makes the pipeline visible through distinct applied, written, durable, and majority-committed OpTime values. WiredTiger also has a journal LSN for local crash recovery. That LSN is not the oplog token used by replication or change streams. The oplog's separate wall dates and status fields such as lastCommittedWallTime are wall-clock observations, not substitutes for OpTime.

What survives later?

WiredTiger does retain timestamp metadata internally while versions need it. In-memory updates have transaction and timestamp fields. The current on-disk value can carry a time window, and the history store keys older values by B-tree, record key, start timestamp, and a uniqueness counter; its value also carries stop and durable timestamps. None of that becomes a queryable field in the BSON document.

The retention boundaries have different names:

• the oldest timestamp is the earliest point at which the application may start a new timestamped read;
• the pinned timestamp also accounts for already-running readers and is the real garbage-collection floor;
• the stable timestamp is the upper boundary of the state considered stable. Rollback to stable removes updates beyond it after rollback or recovery.

History-store versions disappear when no supported read can need them. Oplog entries disappear when the capped oplog rolls past its retention window. A regular document can therefore outlive every system-maintained path from that document to its original commit OpTime. Long-term audit still requires an application field or a separately retained change history.

The same questions, side by side

The table deliberately avoids forcing one-to-one equivalence. Oracle's commit SCN and YugabyteDB's commit HybridTime participate directly in MVCC time. PostgreSQL's commit-record LSN, SQL Server's CDC LSN, and MySQL's GTID are useful for change streams, but they don't make the snapshot stored by a reader.

Can a regular row tell me its exact commit coordinate later?

Usually not. "The engine used this metadata" and "the application can recover it forever from the current row" are very different statements.

The "later" in that question matters. MVCC metadata is retained to serve active or supported historical reads; logs are retained to satisfy recovery and replication policy. Neither lifetime automatically matches an audit requirement.

Wall-clock time is another coordinate

Wall-clock time is useful for audit and diagnosis, but it is a poor substitute for transaction order:

• PostgreSQL now() is transaction start; optional commit timestamps are separate and retained only for a limited transaction-ID horizon.
• Oracle SCN-to-timestamp conversion is approximate and retained for a limited time.
• YugabyteDB HybridTime embeds a physical component but also a logical counter and clock-uncertainty protocol.
• SQL Server CDC maps commit LSN to tran_end_time rather than pretending the LSN is a date.
• MySQL propagates an original commit timestamp separately from GTID, binlog position, InnoDB transaction ID, and redo LSN.
• MongoDB exposes wall-clock dates beside oplog and replica-status OpTime values; the BSON timestamp's seconds-plus-ordinal representation remains a logical replication coordinate.

Two wall-clock readings can be equal, and clocks can be corrected. A client can receive commit responses in an order different from the log's commit records. An updated_at value is normally evaluated while the statement runs, before commit. If an application needs both explanation and deterministic processing, store the timestamp for the intended business event and consume changes with the engine's transactional ordering coordinate.

The practical rule

Before comparing two database numbers, name the promise you need:

1. For repeatable visibility, keep or export a database snapshot.
2. For change-stream order and restart, keep a commit LSN, binlog position/GTID, or the database's CDC token.
3. For durability, wait for the relevant WAL, redo, or Raft flush/apply position required by the configured policy.
4. For optimistic application updates, use an explicit version token and do not call it commit time.
5. For human audit time, store a timestamp, but keep a transactional token as the tie-breaker when order matters.
6. For ordering across independent systems, use a protocol that propagates source identity and causality. Do not compare unrelated XIDs, LSNs, SCNs, oplog positions, or wall clocks as if they shared a namespace.

PostgreSQL's snapshot and WAL LSN are kept separate because visibility and recovery are two distinct processes. This isn’t just about missing metadata. It’s fundamental to how PostgreSQL is designed. WAL recovery updates the physical database by reapplying logged page changes, while MVCC visibility is determined afterward based on heap tuple transaction markers, transaction status, and the reader's snapshot. Importantly, recovery doesn’t need to process every unfinished transaction or undo heap changes before the database can show a consistent state.

That separation benefits the conservative recovery approach. It ensures recovery happens only after a system failure, making a smaller contract more valuable—especially in an open-source database that runs across various operating systems, filesystems, storage solutions, extensions, and support models. It also reduces dependencies for extensions. Usually, a new data type or operator class can reuse existing heap MVCC and index access methods without creating new transaction visibility or crash recovery mechanisms. PostgreSQL indexes typically point to heap tuples and do not contain visibility data themselves. Instead, index-only scans refer to the heap's visibility map.

The boundary is not magic. A genuinely new table or index access method may need its own WAL and visibility work. PostgreSQL provides generic WAL records and custom WAL resource managers for that purpose. The extensibility benefit is that these responsibilities are explicit and localized, not that recovery is free.

Oracle handles many logical ordering challenges in the SCN domain, but XID, undo, and redo addresses are still important. YugabyteDB keeps a final temporal coordinate with committed versions because distributed MVCC requires it, while Raft order stays local to each tablet. SQL Server and InnoDB demonstrate even more valid combinations of these elements. MongoDB/WiredTiger presents them all together in a single stack: logical cluster time, replication OpTime, internal MVCC timestamps, and a separate journal position.

A transaction does not happen at one time. It crosses several boundaries, and each database gives those boundaries different names.

References

This article has been heavily reviewed by AI from the following sources.

PostgreSQL

• Transaction ID and snapshot information functions
• Transactions and identifiers
• Preventing transaction ID wraparound failures
• Transaction isolation
• pg_lsn data type
• WAL internals
• Backup control functions and WAL positions
• Logical decoding output callbacks
• track_commit_timestamp
• synchronous_commit
• Streaming replication monitoring
• Replication origin functions
• PREPARE TRANSACTION
• PostgreSQL 19 WAIT FOR LSN
• Snapshot synchronization and pg_export_snapshot()
• pg_dump --snapshot
• Generic WAL records for extensions
• Custom WAL resource managers
• Heap visibility in index-only scans
• Current date and time functions

Oracle Database

• Oracle AI Database 26ai: Transactions and SCNs
• Data concurrency and read consistency
• V$TRANSACTION
• V$LOGMNR_CONTENTS
• ORA_ROWSCN
• SCN_TO_TIMESTAMP
• Oracle Flashback Query and Version Query
• ORA-24442: SCN exceeds a pre-12.2 target's capability
• ORA-01721: one USERENV('COMMITSCN') per transaction
• ORA-01725: allowed USERENV('COMMITSCN') expression positions
• DBVERIFY utility and HIGH_SCN
• CREATE MATERIALIZED VIEW LOG
• Oracle 11g Release 2 XStream introduction
• Managing distributed transactions and SCN synchronization
• Managing the redo log
• PostgreSQL Multi-Version: From Time Travel to Concurrency Control

YugabyteDB

• Fundamentals of distributed transactions and HybridTime
• Distributed transactions and provisional records
• Transactional read and write paths
• YSQL synchronized snapshots
• YugabyteDB 64-bit HybridTime representation
• YugabyteDB HybridClock monotonicity and logical overflow

SQL Server

• Transaction locking and row versioning guide
• Transaction log architecture and LSNs
• sys.dm_tran_active_snapshot_database_transactions
• sys.dm_tran_database_transactions
• Change Data Capture commit LSNs
• rowversion

MySQL/InnoDB

• InnoDB consistent nonlocking reads
• InnoDB multi-versioning
• InnoDB redo log and LSNs
• The MySQL binary log
• GTID format and source commit order
• Binary log ordering and durability options
• MySQL 8.4 trx_t source and trx->no definition
• MySQL 8.4 transaction commit and serialization source
• MySQL 8.4 ReadView source and purge horizon

MongoDB and WiredTiger

• MongoDB read concern "snapshot" and atClusterTime
• MongoDB causal consistency
• MongoDB BSON timestamps
• MongoDB replica-set oplog
• MongoDB transaction cache limits
• replSetGetStatus replication positions
• WiredTiger transactions
• WiredTiger timestamp model and time windows
• WiredTiger history store
• WiredTiger logging and LSNs
• WiredTiger rollback to stable

My level of knowledge is much higher for Oracle Database, PostgreSQL, YugabyteDB and MongoDB than for Microsoft SQL Server and MySQL InnoDB, so AI review was crucial to avoid biases. Please comment if you see anything not correct or where explanation can be improved. This is not an academic exercise, I've seen many queries falling into the trap of thinking that an updated_at column can easily provide incremental changes. I've left the best reference for this at the end: the Ask Tom Question Selecting rows that have changed via timestamp problematic with non-blocking reads with Tom Kyte's explanations.