Time-Travel Queries - Quick Reference

SQL Syntax

AS OF TIMESTAMP

SELECT * FROM orders AS OF TIMESTAMP '2025-11-15 06:00:00';
SELECT * FROM orders AS OF TIMESTAMP '2025-11-15T06:00:00';

AS OF TRANSACTION

SELECT * FROM orders AS OF TRANSACTION 987654;

AS OF SCN

SELECT * FROM orders AS OF SCN 123456789;

AS OF NOW

SELECT * FROM orders AS OF NOW;

Configuration

Automatic versioning is on by default. Turn it off in config.toml for write-heavy tables that never need history:

[storage]
time_travel_enabled = true   # default; false disables AS OF queries

Old versions are removed by background garbage collection; a timestamp, transaction or SCN older than the retained history returns an error (see below).

Error Messages

Error	Meaning
”Transaction X not found or has been garbage collected”	Transaction ID has been GC’d or never existed
”No snapshot found for timestamp ’…’”	No snapshot exists at or before that time
”Invalid timestamp format:…”	Timestamp string couldn’t be parsed
”SCN X not found or has been garbage collected”	SCN has been GC’d or never existed

Performance Tips

Use TXN/SCN over TIMESTAMP: Faster resolution (no string parsing)
Tune retention: Balance history needs vs performance
Target overhead: <2x vs non-time-travel queries

Examples

Query Past State

-- Orders as they existed at transaction 50
SELECT * FROM orders AS OF TRANSACTION 50;

Audit Trail

-- Compare a row now with the same row at an earlier time
SELECT * FROM orders WHERE id = 42;
SELECT * FROM orders AS OF TIMESTAMP '2025-11-15 06:00:00' WHERE id = 42;

Point-in-Time Recovery

-- Data as it was just before an incident
SELECT * FROM orders AS OF TIMESTAMP '2025-11-15 05:59:00';

-- Or fork a branch at that instant and inspect it in isolation
CREATE BRANCH before_incident FROM main AS OF TIMESTAMP '2025-11-15 05:59:00';