---
title: "Time Travel Queries Tutorial"
---

# 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

```sql
-- 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:

```sql
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?"

```sql
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"

```sql
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:

```sql
-- 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

```sql
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:

```sql
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](https://www.heliosdb.com/docs/lite/guides/tutorials/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:

```toml
# 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

- [DATABASE_BRANCHING_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/database_branching_tutorial/) — fork from history, write freely, merge back.
- [MATERIALIZED_VIEWS_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/materialized_views_tutorial/) — pin slow `AS OF` joins to incremental views.
- [Audit Logging](/docs/lite/features/audit_logging/) — built-in audit events are planned; combine MVCC history with trigger-based change records today.