Skip to content
HeliosDB HeliosDB Lite

Time Travel Queries Tutorial

Build: default — no feature flag required SQL: SELECT … AS OF TIMESTAMP, AS OF TRANSACTION, AS OF SCN, VERSIONS BETWEEN

UVP

Auditors ask “what did this row say last Tuesday?” — and most databases need a sidecar audit table to answer. Lite stores MVCC versions natively and exposes them through three SQL clauses: AS OF TIMESTAMP, AS OF TRANSACTION, and AS OF SCN. Snapshot retention is configurable; queries against historical state run at less than 2× the overhead of a current-state read. Pair with CREATE BRANCH … AS OF to fork the past, or with materialized views for moving baselines. Audit, debug, recover, and compare — all without leaving SQL.

Prerequisites

  • HeliosDB Lite
  • A REPL or psql client
  • 10 minutes

1. The Three AS OF Variants

-- 1. Wall-clock time (timestamp literal).
SELECT * FROM orders
  AS OF TIMESTAMP '2025-01-15 10:30:00'
WHERE customer_id = 42;


-- 2. Transaction id (monotonic; visible via system views).
SELECT * FROM inventory
  AS OF TRANSACTION 987654
WHERE product_id = 'SKU-99';


-- 3. System change number (LSN-style global counter).
SELECT * FROM audit_log
  AS OF SCN 123456789;

Use TIMESTAMP for human-driven audits (“the state on closing day”), TRANSACTION when you have an explicit txn id (e.g. captured in an outbox), and SCN for cross-table consistency at a single global commit point.

2. Comparing Past and Present

The most common use is a self-join between current and historical state:

SELECT
    cur.id,
    cur.price            AS current_price,
    old.price            AS price_2025_01_01,
    cur.price - old.price AS delta
FROM products cur
JOIN products AS OF TIMESTAMP '2025-01-01 00:00:00' old
  ON cur.id = old.id
WHERE cur.price <> old.price
ORDER BY delta DESC
LIMIT 20;

Every existing optimisation — indexes, materialized views, RLS — applies to historical scans. The only difference is the snapshot the planner reads from.

3. Use Cases

Audit query — “Who changed this row, and when?”

WITH revisions AS (
    SELECT account_id, balance, '2025-01-01' AS snap FROM accounts AS OF TIMESTAMP '2025-01-01'
    UNION ALL
    SELECT account_id, balance, '2025-02-01' AS snap FROM accounts AS OF TIMESTAMP '2025-02-01'
    UNION ALL
    SELECT account_id, balance, 'now' AS snap FROM accounts
)
SELECT * FROM revisions WHERE account_id = 'ACC-123' ORDER BY snap;

Debugging — “Reproduce the report from yesterday”

SELECT
    region,
    sum(total)
FROM orders
  AS OF TIMESTAMP '2026-04-25 23:59:59'
GROUP BY region;

You don’t need to roll back the database; the live state continues serving traffic.

Soft-recovery without a restore

If a runaway UPDATE zeroed a price column at 09:42:

-- Inspect.
SELECT id, price FROM products
  AS OF TIMESTAMP '2026-04-26 09:41:00'
WHERE price = 0;


-- Repair from the historical snapshot.
UPDATE products cur
SET price = old.price
FROM products AS OF TIMESTAMP '2026-04-26 09:41:00' AS old
WHERE cur.id = old.id
  AND cur.price = 0;

Moving baselines for analytics

CREATE MATERIALIZED VIEW yoy_revenue AS
SELECT
    date_trunc('month', cur.order_date) AS month,
    sum(cur.amount)                     AS this_year,
    sum(prev.amount)                    AS last_year
FROM orders cur
LEFT JOIN orders AS OF TIMESTAMP (CURRENT_TIMESTAMP - INTERVAL '1 year') prev
  ON cur.id = prev.id
GROUP BY 1;

To serve this comparison repeatedly, store it in a materialized view and refresh it on a schedule with REFRESH MATERIALIZED VIEW.

4. Branch + Time Travel

CREATE BRANCH … AS OF forks at a historical instant. Once on the branch you can mutate to your heart’s content without disturbing live traffic:

CREATE BRANCH year_end FROM main AS OF TIMESTAMP '2025-12-31 23:59:59';
USE BRANCH year_end;


-- Free to write here; main is unaffected.
INSERT INTO trial_balance SELECT * FROM accounts;

Drop the branch when you’re done. See DATABASE_BRANCHING_TUTORIAL for the full branching story.

5. Retention and Garbage Collection

Historical versions don’t live forever. A background garbage collector removes versions that fall outside the retained history, and an AS OF query older than that returns an error such as “Transaction X not found or has been garbage collected”. VACUUM reclaims space on demand.

Versioning is on by default. Turn it off for databases that never need history:

heliosdb.toml
[storage]
time_travel_enabled = false

The system view pg_database_branches() reports per-branch sizing.

6. Performance Notes

The MVCC engine keeps versioned tuples inline with current data; an AS OF read is a regular scan with a different visibility filter. Indexed historical lookups take under 100 ms on typical workloads, and end-to-end overhead stays under 2× compared to a current-state query (internal tests, HeliosDB Lite, representative fixtures).

For analytical workloads that hammer historical state, consider creating a materialized view on the AS OF query and refreshing it on a schedule (automatic background refresh is planned).

Where Next