Skip to content
HeliosDB HeliosDB Nano

Time-Travel Queries

Time-Travel Queries

Query your data as it existed at any point in the past using HeliosDB Nano’s time-travel feature.

Overview

Time-travel queries allow you to:

  • Audit changes: See what data looked like before modifications
  • Debug issues: Understand how data changed over time
  • Recover data: Find and restore accidentally deleted records
  • Compare states: Diff current vs historical data

Syntax

Query by Timestamp

SELECT * FROM orders
AS OF TIMESTAMP '2025-01-15 10:30:00';

Query by Transaction ID

SELECT * FROM orders
AS OF TRANSACTION 12345;

Query by System Change Number (SCN)

SELECT * FROM orders
AS OF SCN 50000;

Examples

View Historical Data

-- See orders from yesterday
SELECT * FROM orders
AS OF TIMESTAMP '2025-01-14 00:00:00'
WHERE status = 'completed';

Compare Current vs Historical

-- Find price changes in the last week
SELECT
    c.id,
    c.price as current_price,
    h.price as week_ago_price,
    c.price - h.price as change
FROM products c
JOIN products AS OF TIMESTAMP '2025-01-08 00:00:00' h
    ON c.id = h.id
WHERE c.price != h.price;

Find Deleted Records

-- Find records that existed yesterday but don't exist now
SELECT h.*
FROM orders AS OF TIMESTAMP '2025-01-14 00:00:00' h
LEFT JOIN orders c ON h.id = c.id
WHERE c.id IS NULL;

Audit Trail

-- See the state of a specific order at different times
SELECT 'Current' as version, * FROM orders WHERE id = 123
UNION ALL
SELECT '1 hour ago', * FROM orders AS OF TIMESTAMP '2025-01-15 09:00:00' WHERE id = 123
UNION ALL
SELECT 'Yesterday', * FROM orders AS OF TIMESTAMP '2025-01-14 10:00:00' WHERE id = 123;

REPL Commands

Terminal window
# Show current LSN (Log Sequence Number)
\show lsn


# Show available snapshots
\snapshots

Performance

Time-travel queries have approximately <2x overhead compared to current-data queries due to:

  1. Snapshot lookup
  2. Version filtering
  3. Page reconstruction (if needed)

Optimization Tips

  • Use specific timestamps rather than ranges
  • Add indexes on commonly queried historical columns
  • Configure snapshot retention for your use case

Snapshot Management

View Snapshots

SELECT * FROM pg_snapshots();

Retention

Snapshots are retained based on configuration:

[storage]
snapshot_retention_days = 30
snapshot_gc_interval = 3600  # seconds

Older snapshots are automatically garbage collected.

Limitations

  • Cannot query data older than the oldest retained snapshot
  • JOINs between current and historical data may be slower
  • Some DDL changes may affect historical query availability

Use Cases

Compliance & Auditing

-- Prove what data existed at report time
SELECT * FROM financial_records
AS OF TIMESTAMP '2024-12-31 23:59:59'
WHERE account_id = 'ACCT001';

Data Recovery

-- Recover accidentally deleted customer
INSERT INTO customers
SELECT * FROM customers
AS OF TIMESTAMP '2025-01-15 09:00:00'
WHERE id = 456;

A/B Testing Analysis

-- Compare metrics before and after feature launch
WITH before AS (
    SELECT COUNT(*) as orders_before
    FROM orders AS OF TIMESTAMP '2025-01-01 00:00:00'
),
after AS (
    SELECT COUNT(*) as orders_after FROM orders
)
SELECT * FROM before, after;