Time Travel

ParticleDB supports time travel queries that read historical snapshots of your data. Every DML operation (INSERT, UPDATE, DELETE) records a versioned snapshot, allowing you to query the state of any table at a previous point in time.

AS OF Syntax

Three forms are supported:

Absolute Timestamp

Query data as it existed at a specific wall-clock time:

SELECT * FROM orders AS OF TIMESTAMP '2025-01-01 12:00:00';

Relative Interval

Query data from a duration in the past relative to the current time:

SELECT * FROM orders AS OF INTERVAL '1 hour ago';
SELECT * FROM orders AS OF INTERVAL '7 days ago';

System Time (Epoch Microseconds)

Query data at a specific MVCC timestamp (microseconds since UNIX epoch):

SELECT * FROM orders AS OF SYSTEM TIME 1234567890;

Comparing Current vs Historical Data

You can use standard set operations to see what changed between two points in time:

-- Rows that exist now but did not exist at the given timestamp
SELECT * FROM orders
EXCEPT
SELECT * FROM orders AS OF TIMESTAMP '2025-01-01';

-- Rows that were deleted since the given timestamp
SELECT * FROM orders AS OF TIMESTAMP '2025-01-01'
EXCEPT
SELECT * FROM orders;

Retention and Garbage Collection

Configurable Retention Window

By default, snapshots are retained for 7 days. You can configure the retention window to match your auditing or compliance requirements:

Setting	Default	Description
retention_window	7 days	How far back snapshots are kept
gc_interval	1 hour	How often the background GC task runs

Background Garbage Collection

A background task runs periodically (default: every hour) and removes snapshots older than the retention window. This keeps memory and storage usage bounded without manual intervention.

Snapshots outside the retention window are no longer queryable. Attempting to query a timestamp older than the retention window returns an error.

Limitations

• Snapshots consume memory proportional to table size at the time of each mutation. High-frequency DML on large tables may require tuning the retention window.
• Time travel queries are read-only. You cannot modify historical data.
• The retention window applies globally. Per-table retention is not currently supported.