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.

... (truncated)

Focusing on Percona Server 8.4.11-11 My previous post (Performance Progression of Percona Server for MySQL 8.4) did a brief review of the performance changes in Percona Server for MySQL 8.4 released in 2026. I recommend reading it first to better understand the material in this post. Version 8.4.11-11 includes patches that deliver significant improvements in … Continued

The post Performance improvements in Percona Server 8.4.11-11 appeared first on Percona.

Percona and HexaCluster have partnered to remove the hardest part of an open source database migration: getting off Oracle, SQL Server, DB2 or Sybase ASE with confidence, on a predictable timeline, without a multi-year consulting program. Percona brings open source expertise, its own distributions, operators and enterprise support. HexaCluster brings the assessment and migration engineering … Continued

The post Percona and HexaCluster: Faster, Safer Oracle Migration appeared first on Percona.

Aleksey and I are back to reading papers live. This paper, Jetpack(OSDI '26), attempts building a universal 1-RTT fast-path framework that bolts onto existing leader-based consensus protocols with minimal modification.

Why would we want this? Classic consensus protocols like Raft, Paxos, or Zab require two round-trip times (2 RTT) to commit a command: one RTT from client to leader, and another to replicate across followers. The extra RTT matters a lot for WAN deployments, so fast-path protocols (such as Fast Paxos, EPaxos, or SwiftPaxos) reduce this to 1 RTT by bypassing leader serialization, but unfortunately they tightly couple the fast path to the core protocol design. Production systems cannot easily swap out their battle-tested bespoke consensus engines, but if there was an add on that helped with latency especially in WAN deployments, that would be useful.

The good news is that Jetpack is truly an add-on portable deal. It provides a shim layer that runs two execution paths in parallel: a 1-RTT fast path and the original 2-RTT consensus path. When a client issues a command, it broadcasts the request concurrently to both paths. The fast path checks for key conflicts, and if none exist and a supermajority quorum ($\sim 3/4$ of nodes) issues a promise, this enables the command to fast-commit in 1 RTT. To guarantee agreement, original path proposers promise not to propose conflicting commands ahead of fast-committed ones.

The bad news is that this design gets wasteful due to keeping two distinct logs (the fast-path log and the original-path log). The original consensus engine runs its full replication cycle in the background, ignoring the fast path replication of commands (because it is completely oblivious to the fast path replication in the name of bolt-on portability). So these commands travel in the network twice, and replicas process commands twice, introducing redundant work and extra CPU/network overhead.

This dual-log architecture also creates a bigger gap between commitment and execution. Jetpack fast-commits in 1 RTT, but actual state-machine execution is driven strictly by the underlying original 2RTT path log ordering. Then, what good is a fast commit in practice? If you are running write-heavy, asynchronous pipelines or "fire-and-forget" ingestion where a client issues a PUT(key, val) and immediately moves on to the next task, a 1-RTT durable commit confirmation is a win. However, the fast path only gives you a fast commit, but it does not accelerate state-machine execution. The moment you run an interactive workload, say a client that issues a PUT(key, val) and immediately follows up with a GET(key) expecting read-your-own-writes or linearizability, the fast-path illusion breaks down. Jetpack's shim detects the unexecuted PUT sitting in its in-flight conflict pool and immediately demotes the GET right back to the slow 2-RTT original path to preserve correctness. So, yes, Jetpack stays linearizable, but you pay the full 2-RTT latency tax and wait for the original consensus engine to catch up to answer the GET.

This connects directly to the principle of nil-externality formalized in Exploiting Nil-Externality for Fast Replicated Storage (SOSP '21). Because write commands like PUT return no system state back to the client (they have "nil externality"), Jetpack can safely grant a 1-RTT fast commit before state-machine execution. But, the moment an operation (like a GET) needs to externalize state, that nil-externality optimization breaks down and forces the system to wait for full state-machine execution.

Despite this execution lag and redundant message overhead, the evaluation section shows gains for write-heavy pipelines (I think due to nil-eternality optimization) by benchmarking Jetpack across six consensus systems deployed on 10 AWS datacenters using YCSB workloads and Facebook's Akkio production traces. Because cross-datacenter write requests (such as remote shard updates in Akkio) primarily wait on durable commit confirmation before responding, Jetpack slashes client-observed end-to-end latency by up to 60%.

The paper's biggest safety contribution is to show how the fast-path protocols may break during leader elections. As I noted in my 2020 blog post review of CURP, mixing witness state with backup replicas makes view changes inherently risky. Jetpack proves this by uncovering a concrete bug in CURP’s Raft extension (used in production by Xline), where a lagging witness ACKs a fast-path command, only for a delayed cleanup message from a new leader to erase it, causing permanent data loss (or reordering) after a crash. This happens because promises made in stable views live in local replica states that a new leader never saw. Jetpack fixes this "view change hazard" with two strict principles: keeping fast-path views independent so ACKs cannot straddle terms (Principle 1), and forcing new leaders to write a "stability marker" that recovers all prior fast-committed commands before accepting new proposals (Principle 2).

As Aleksey and I experienced live during our reading session, untangling Jetpack’s three-phase recovery procedure was the trickiest part of the paper. We  struggled to follow why recovery requires only a standard majority rather than a superquorum. But refreshing our understanding of Fast Paxos later showed that Jetpack's recovery mechanism is similar to that of Fast Paxos. Fast Paxos explicitly requires a supermajority quorum ($Q_2 \approx 3/4$ of nodes) for fast-path commits precisely so that leader election and recovery ($Q_1$) can run on a simple majority. Because any supermajority $Q_2$ is mathematically guaranteed to overlap with any standard majority $Q_1$ by at least one node, a newly elected leader polling a simple majority during recovery will always discover any fast-committed command. Still, the quorum math aside, I'll be damned if anyone can call fast-path recovery simple (and dependable).

One of the most persistent misconceptions about PostgreSQL MVCC is that old row versions accumulate in a chain until VACUUM removes them, making reads increasingly expensive as updates pile up.

That's not how PostgreSQL works. Space amplification is common in MVCC databases because they need to access multiple versions over time, but this doesn't necessarily lead to read amplification. Databases are built to read only the data they need from a larger dataset.

In this article, I'll demonstrate three important facts:

• Scans don't walk version chains across pages — Seq Scans examine heap tuples directly and skip invisible ones; Index Scans follow the HOT chain only within a single page; Bitmap Scans behave like one or the other depending on bitmap losiness.
• Making space reusable in heap and indexes doesn't wait for vacuum — normal reads perform maintenance with hint bits and opportunistic heap pruning, even with autovacuum disabled.
• Index scans pay the visibility cost once, and mark dead entries LP_DEAD to skip future heap visits.

As a result, PostgreSQL can build up dead tuples and index entries, but read amplification doesn't increase proportionally, and space can be reused over time.

Setup

PostgreSQL MVCC is known for space amplification (called bloat), but it doesn't accumulate old versions forever. Some garbage collection (called vacuum) happens in the background. However, this article focuses on read amplification before vacuum. For the purpose of the demo, I created a table and disabled auto-vacuum:

drop table if exists mvcc_demo;

create table mvcc_demo (
 id int,
 a int,
 b int,
 filler text default repeat('x',1000)
);

alter table mvcc_demo set (autovacuum_enabled = off);

create index on mvcc_demo ( a );
create index on mvcc_demo ( b );

insert into mvcc_demo
select n,n,n
from generate_series(1,8) n;

vacuum analyze;

To inspect heap pages, I prepare a helper query that lists all line pointers in a page except those with length zero, which are only small stubs:

create extension if not exists pageinspect;

prepare show_tuples(int,int) as
select page, lp, t_xmin, t_xmax, t_ctid,
  regexp_replace(t_data::text,'^(\\x)(.{8})(.{8})(.{8})(.{8}).*$','id=\\x\2 a=\\x\3 b=\\x\4 filler=\\x\5...'),
  t_infomask, t_infomask2
 from generate_series($1,$2) page, lateral (
  select * from heap_page_items(get_raw_page('mvcc_demo', page)) where lp_len>0
) order by page, lp
;

execute show_tuples(0,1)
;

The initial state shows eight tuples:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  1 |    697 |      0 | (0,1)  |       2306 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
(8 rows)

The value 0 of t_xmax and the t_ctid pointing to itself indicate that the tuple is the current version of the row.

Creating a version chain

I'll update one row several times with the following statement:

postgres=# update mvcc_demo
           set a=a+1
           where id=1
;
UPDATE 1

The first update created a new row version on another page because there was no space on the same page. The original tuple is updated with t_ctid storing the address of the next version, and receives its end of visibility in xmax:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  1 |    697 |    740 | (1,2)  |        258 | id=\x01000000 a=\x01000000 b=\x01000000 filler=\xb00f0000...
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |      0 | (1,2)  |      10242 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
(9 rows)

This t_ctid is what makes people think every read must follow a growing chain of versions. Ordinary visibility checks don't work that way because:

• if the query's read snapshot is between xmin and xmax, this is the right row, and there's no need to get another one
• if the tuple is not visible to the snapshot, it is skipped. The scan continues normally and may encounter another version of the same logical row elsewhere in the heap.

Here, the row with id=1 (\x01000000) has two versions in two pages - it's not a HOT (heap-only tuple) update. The new version was inserted on a page with free space.

After a second update, the same happens, but there is free space on the same page, so the new version is inserted there, with two versions of id=1 (\x01000000) on page 1:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       8450 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |      0 | (1,3)  |      10242 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
(9 rows)

Actually, all versions of id=1 (\x01000000) are on the same page because the initial version has disappeared from the first page even without vacuum, proof that free space is released even before vacuum runs. What happened is that the update has read the first page and did some cleanup while the buffer was pinned.

This is proof that garbage collection can happen without vacuum, simply when UPDATE, DELETE, or SELECT reads after the update. It is called opportunistic pruning: pruning is attempted whenever a page's free space heuristically looks low, or a page previously failed to fit an updated tuple. Space reclamation happens during tuple retrieval when the page is full or nearly full (<10% free or fillfactor target) and a buffer cleanup lock can be acquired.

Additionally, when the UPDATE has to move a new version to a different page because there isn't room, it flags the old page as full. Here, there was space to place the new version on the same page.

Here is a third update that adds another version:

 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |      0 | (0,2)  |       2306 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |    742 | (1,4)  |       8450 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
    1 |  4 |    742 |      0 | (1,4)  |      10242 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(10 rows)

The row id=1 (\x01000000) started with a=1 (\x01000000), then updated to a=2 (\x02000000), a=3 (\x03000000), and a=4 (\x04000000). Because no open transactions need to read those old values, Postgres cleans them up when possible to free space on the page.

In this example, I update an indexed column, so even if the new version lands on the same page, this isn't a HOT update—a separate index entry is created. Because the old version might still be referenced by an index entry, ordinary read-triggered pruning cannot fully discard its line pointer. It is still there with a length of zero, which I filter out with lp_len>0 - so that id=1 (\x01000000), a=1 (\x01000000) disappeared.

However, page defragmentation reclaimed the tuple storage even though the line pointer is retained as a stub, and this space can be reused before any VACUUM runs and removes the line pointer. I update another row, id=2 (\x02000000) in the first page, and the new version fits there in a new line pointer of the same page:

postgres=# update mvcc_demo
           set a=a+1
           where id=2
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |    743 | (0,8)  |        258 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |      0 | (0,3)  |       2306 | id=\x03000000 a=\x03000000 b=\x03000000 filler=\xb00f0000...
    0 |  4 |    697 |      0 | (0,4)  |       2306 | id=\x04000000 a=\x04000000 b=\x04000000 filler=\xb00f0000...
    0 |  5 |    697 |      0 | (0,5)  |       2306 | id=\x05000000 a=\x05000000 b=\x05000000 filler=\xb00f0000...
    0 |  6 |    697 |      0 | (0,6)  |       2306 | id=\x06000000 a=\x06000000 b=\x06000000 filler=\xb00f0000...
    0 |  7 |    697 |      0 | (0,7)  |       2306 | id=\x07000000 a=\x07000000 b=\x07000000 filler=\xb00f0000...
    0 |  8 |    743 |      0 | (0,8)  |      10242 | id=\x02000000 a=\x03000000 b=\x02000000 filler=\xb00f0000...
    1 |  1 |    697 |      0 | (1,1)  |       2306 | id=\x08000000 a=\x08000000 b=\x08000000 filler=\xb00f0000...
    1 |  2 |    740 |    741 | (1,3)  |       9474 | id=\x01000000 a=\x02000000 b=\x01000000 filler=\xb00f0000...
    1 |  3 |    741 |    742 | (1,4)  |       9474 | id=\x01000000 a=\x03000000 b=\x01000000 filler=\xb00f0000...
    1 |  4 |    742 |      0 | (1,4)  |      10498 | id=\x01000000 a=\x04000000 b=\x01000000 filler=\xb00f0000...
(11 rows)

The two versions of id=2 (\x02000000) are on the same page. This page is now full again, and another update, on id =3 (\ x03000000), will need to insert its new version on another page:

postgres=# update mvcc_demo
           set a=a+1
           where id=3
;
UPDATE 1
postgres=# execute show_tuples(0,1)
;
 page | lp | t_xmin | t_xmax | t_ctid | t_infomask |                        regexp_replace
------+----+--------+--------+--------+------------+--------------------------------------------------------------
    0 |  2 |    697 |    743 | (0,8)  |       1282 | id=\x02000000 a=\x02000000 b=\x02000000 filler=\xb00f0000...
    0 |  3 |    697 |    744 | (1,
                                        by Franck Pachot