Skip to content
HeliosDB HeliosDB Lite

Time Travel Queries Tutorial

Time Travel Queries Tutorial

Available since: v3.0.0 Build: default — no feature flag required Module: heliosdb_lite::sql::extensions::time_travel, heliosdb_lite::storage::time_travel 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 v3.0+
  • 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. The Rust API

Two convenience methods on EmbeddedDatabase:

use heliosdb_lite::EmbeddedDatabase;


let db = EmbeddedDatabase::new("./mydb.helio")?;


let historical = db.query_as_of_timestamp(
    "SELECT * FROM products WHERE category = $1",
    &["Electronics"],
    "2025-01-01 12:00:00",
)?;


let at_txn = db.query_as_of_transaction(
    "SELECT count(*) FROM orders",
    &[],
    42,
)?;

For raw control, just feed the AS OF clause through the standard query API — the parser preprocesses the syntax (sql::parser::Parser::preprocess_time_travel_sql) and the executor’s scan node honours it.

4. 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;

Combined with the auto-refresh scheduler, the comparison stays current without manual recomputation.

5. 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.

6. Retention and Garbage Collection

Historical versions don’t live forever. The retention horizon is configurable:

heliosdb.toml
[phase3]
time_travel_retention_days = 30

Or per-tenant via the multi-tenant quotas (time_travel_days field on TenantQuotas). Once a version falls outside the window it becomes a candidate for vacuum.

The system view pg_database_branches() reports per-branch sizing; pair with the storage vacuum_backup module if you need finer-grained reclamation control.

7. 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. The README quotes <100ms for indexed historical lookups against typical workloads, and end-to-end overhead under 2× compared to a current-state query.

For analytical workloads that hammer historical state, consider creating a materialized view on the AS OF query — the auto-refresh scheduler handles maintenance.

Where Next