Database field guide · 07
Database Time and Ordering
Clocks, commits, snapshots, and sort order
A precise vocabulary for the different kinds of time and order that applications ask databases to provide.
Franck Pachot7 chapterstime · ordering · consistency
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
There is no single database time
Wall-clock time, transaction time, commit order, log position, and snapshot visibility answer different questions. Treating one as a substitute for another creates bugs that appear only under concurrency, failover, or clock adjustment.
- A timestamp is a value, not proof of causality.
- Commit sequence can disagree with transaction start time.
- State which order a business requirement actually needs.
02
Logical clocks order database events
Oracle SCNs, PostgreSQL transaction identifiers and log sequence positions, and distributed hybrid clocks help engines coordinate visibility and recovery. They are implementation coordinates with specific scope and lifecycle, not universal application timestamps.
- Use commit or log positions for replication progress.
- Account for identifier wraparound and epochs.
- Do not expose engine counters as permanent business identity.
03
A snapshot defines visible history
MVCC evaluates row versions against a snapshot. Read Committed may acquire a new snapshot for each statement, while repeatable-read modes hold a stable transaction view. Both can be correct while returning different answers after another transaction commits.
- Name the snapshot boundary in consistency tests.
- Current does not mean globally latest in a replicated system.
- Long snapshots retain old versions and operational debt.
04
Serializable is about outcomes
Serializable execution guarantees a result equivalent to some serial order; it does not necessarily run one transaction at a time or preserve wall-clock order. Engines enforce this through locking, conflict detection, or aborts with different anomaly boundaries.
- Build retries around the whole transaction.
- Keep external effects outside retryable database work.
- Test invariants, not only individual statements.
05
SQL ordering must be requested
Rows have no guaranteed presentation order without ORDER BY. An index can provide a useful physical order, but ties remain nondeterministic unless the query names a unique final key. Parallel and distributed execution make accidental order especially fragile.
- Add a stable tie-breaker to user-visible lists.
- Match index prefixes to filter and order clauses.
- Never infer commit order from unordered query output.
06
Distributed clocks add uncertainty
Across regions, message delay and clock skew prevent a process from instantly knowing a universal present. Systems pay with coordination latency, expose bounded-staleness reads, or weaken which order is promised. The application must choose consciously.
- Define maximum acceptable staleness per read path.
- Place coordination near the transactions that require it.
- Observe clock health and replication lag independently.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Keep four clocks separate
Transaction start time orders beginnings; wall-clock timestamps represent a node's clock; commit order defines visibility publication; WAL LSN or Oracle SCN places changes in an engine history. None is a universal substitute for another. PostgreSQL now() is fixed at transaction start, statement_timestamp() at statement start, and clock_timestamp() reads the clock on each call.
SELECT now() AS transaction_time,
statement_timestamp() AS statement_time,
clock_timestamp() AS wall_clock,
pg_current_wal_lsn() AS wal_position;
Use engine positions for replication progress
A timestamp comparison cannot prove that a replica has applied a write because clocks may differ and commits can share or reorder timestamps. Compare replay positions in the same log coordinate system. PostgreSQL exposes current, received, and replay LSNs; Oracle exposes SCNs and redo sequence/apply positions. Convert byte lag to time only as an operational approximation.
SELECT pg_current_wal_lsn() AS primary_lsn;
-- On a standby:
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
now() - pg_last_xact_replay_timestamp() AS replay_delay;
Make result ordering total
ORDER BY created_at alone is not deterministic when timestamps tie. The executor may emit ties in heap order, index order, worker arrival order, or shard merge order. Add an immutable unique key as the final ordering term and mirror it in the index. The same tuple becomes a pagination cursor and a reproducible test boundary.
SELECT id, created_at, payload
FROM event
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 50;
Serializable order is not wall-clock order
Serializable guarantees equivalence to some serial execution, but the chosen order can differ from transaction start order unless the system also promises strict serializability. PostgreSQL SSI tracks read/write dependencies and aborts a transaction when a dangerous structure could complete a cycle. Applications must replay the whole transaction from a fresh snapshot on SQLSTATE 40001.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- read the complete invariant, then perform writes
COMMIT;
-- On 40001: rollback, back off, and rerun the transaction.
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
