Build1 publisher3 min readPublished
At least five ordering questions hide inside one updated_at column
A dev.to walkthrough of PostgreSQL snapshots, LSNs and Oracle SCNs sets out why a row version has no commit coordinate to record when it is written, and what that indirection costs the replication and audit consumers that read it later.
The Engineer · Build desk
What happened
- A dev.to post argues there is no universal updated_at in a nonblocking MVCC database, because a row version is created, stays private, becomes visible at commit, reaches replicas and is shown to users later.
- It breaks the question into at least five: when the transaction began, which state a statement read, when the row version was created, when the commit became visible, and which durable log position protects it.
- Its generic sequence separates commit, making the log durable, making changes visible and replying to the client, and notes some databases run those steps in a different order.
- None of the three coordinates PostgreSQL exposes is a wall-clock timestamp: xmin:xmax:xip_list for visibility, an LSN for write-ahead log position, and a transaction ID for tuple versions.
- Oracle's SCN covers read consistency, commits, checkpoints and recovery, but the post says a transaction ID, undo address and redo position are needed too.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- cost Indirection keeps commit cheap by moving work to whoever reads later: every reader resolves a marker through transaction or log metadata until cleanup copies the outcome into the block.
- decision Replication resumption, migration validation and audit reconstruction each ask a different ordering question of the same write, so a schema that offers one timestamp column has already answered for all three.
- exposure A downstream service that trusts a statement-time stamp reports a moment when no other session could read the row, and in the post's example the acknowledged commit is five minutes later.
A row version cannot carry its own commit coordinate because the coordinate does not exist yet. When a transaction modifies its first row, the final commit coordinate has not been assigned [11]. By commit time, one transaction may have changed millions of rows, and many of its dirty pages may already have left memory [12]. Going back to stamp all of them would make commit latency a function of transaction size. It would also undermine write-ahead logging's no-force rule: commit should make the log durable, not force every data page [13].
The answer engines reach for is indirection. A row version records a transaction marker or a provisional time. The commit then publishes the outcome and the final coordinate in transaction or log metadata, and readers resolve the marker through that metadata [14]. In PostgreSQL the tuple retains xmin and xmax, and commit status lives in pg_xact [15]. Cleanup later may copy enough information into blocks or final versions to avoid the lookup [14].
The visibility test the post writes down is two lines. A version v is visible to a query q if q's own transaction created it, or if v is committed and commitPoint(v) is at or before readPoint(q) [16]. Neither PostgreSQL nor InnoDB stores commitPoint as a per-row scalar; both derive it from the transaction ID, its status, and the transactions active in the snapshot [17]. Oracle and YugabyteDB make the logical commit more explicit, and every engine still needs the own-transaction exception for uncommitted changes [18].
Which coordinate you want depends on the question being asked. For application-log correlation the post says the request or statement time may be the correct one. It also says the same value misdescribes the user's experience, because users distinguish "I edited", "I saved", and "I published" as separate business events [8]. For replication resumption the coordinate has to be the durable log position, and PostgreSQL exposes that as an LSN [3].
The comparison the post proposes is "which coordinator answers which ordering question?" [19]. Its disclosure says the piece was written with a lot of help from GitHub Copilot, used to make the comparison more thorough and to check each equivalence against the original documentation and code. The disclosure adds: "Any interpretation and remaining errors are my own" [20].
For a mapping like that to transfer to your system, the coordinate names have to be the ones your engine version exposes. The ordering guarantee you depend on also has to be the one that coordinator actually provides. The post says SQL Server, MySQL/InnoDB and MongoDB/WiredTiger split these responsibilities [6], and that YugabyteDB uses HybridTime as its MVCC read and commit coordinator while provisional writes initially carry different timestamps [5].
What to watch
- A per-engine table that names which coordinate answers each of the five questions in SQL Server, InnoDB and WiredTiger, so the equivalences can be checked rather than assumed.
- Documentation on how YugabyteDB replaces a provisional write's timestamp with the committed HybridTime, and what readers see in between.
- Coordinate names that changed between engine versions; a mapping copied from a blog post then points at something your build does not expose.