---
title: "HeliosDB Time-Travel Debugging User Guide"
---

# HeliosDB Time-Travel Debugging User Guide

---

## Table of Contents

1. [Overview](#overview)
2. [Getting Started](#getting-started)
3. [Core Concepts](#core-concepts)
4. [Basic Usage](#basic-usage)
5. [Advanced Queries](#advanced-queries)
6. [Flashback Queries (Oracle-Compatible)](#flashback-queries-oracle-compatible)
7. [Retention Policies](#retention-policies)
8. [Performance Tuning](#performance-tuning)
9. [Use Cases](#use-cases)
10. [Troubleshooting](#troubleshooting)
11. [Best Practices](#best-practices)
12. [API Reference](#api-reference)

---

## Overview

HeliosDB Time-Travel Debugging queries historical states of your data at any point in time. It provides:

- **Point-in-Time Queries**: See your data as it existed at any timestamp
- **Version History**: Track all changes to any row over time
- **Oracle Compatibility**: Full Flashback Query support (AS OF SCN, VERSIONS BETWEEN)
- **Efficient Storage**: Delta compression keeps overhead under 10%
- **Isolation**: ACID-compliant with snapshot isolation

### Key Benefits

| Benefit | Description |
|---------|-------------|
| **Debugging** | Investigate what data looked like when a bug occurred |
| **Audit Trail** | Complete history of all changes for compliance |
| **Data Recovery** | Retrieve accidentally deleted or modified data |
| **Analytics** | Analyze trends and changes over time |
| **Testing** | Compare data states before and after changes |

### Performance Characteristics

| Metric | Target | Typical |
|--------|--------|---------|
| Storage Overhead (7-day history) | <10% | 5-8% |
| Query Latency (vs current) | <2x | 1.3-1.8x |
| Write Throughput | 1M+ ops/sec | 1.2M ops/sec |
| Temporal Index Lookup | <100ms | 20-50ms |

---

## Getting Started

### Prerequisites

- HeliosDB Full
- Temporal extension enabled
- Sufficient storage for version history (estimate: 5-10% of data size per retained day)

### Quick Start (5 minutes)

#### 1. Enable Time-Travel for a Table

```sql
-- Create a temporal table (automatic version tracking)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(50),
    updated_at TIMESTAMP DEFAULT NOW()
) WITH (
    temporal_enabled = true,
    retention_days = 7
);

-- Or enable on existing table
ALTER TABLE orders SET (temporal_enabled = true);
```

#### 2. Make Some Changes

```sql
-- Insert initial data
INSERT INTO orders (customer_id, total, status)
VALUES (1, 100.00, 'pending');

-- Wait a moment, then update
UPDATE orders SET status = 'processing' WHERE id = 1;

-- Wait again, then update
UPDATE orders SET status = 'shipped', total = 105.00 WHERE id = 1;
```

#### 3. Query Historical States

```sql
-- See data as it was 1 minute ago
SELECT * FROM orders
AS OF TIMESTAMP (NOW() - INTERVAL '1 minute')
WHERE id = 1;

-- See all versions of a row
SELECT * FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '1 hour') AND NOW()
WHERE id = 1;
```

---

## Core Concepts

### Version Chain

Every change creates a new version in the history:

```
Time: T1          T2          T3          NOW
      |           |           |           |
      v           v           v           v
    [v1] -----> [v2] -----> [v3] -----> [v4]
    INSERT      UPDATE      UPDATE      CURRENT
    pending     processing  shipped     shipped
```

### Timestamps and SCNs

HeliosDB tracks changes using two systems:

- **Timestamp**: Human-readable time (e.g., `2026-01-04 10:30:00`)
- **SCN (System Change Number)**: Monotonic counter for precise ordering

Both can be used for temporal queries:

```sql
-- By timestamp
SELECT * FROM orders AS OF TIMESTAMP '2026-01-04 10:30:00';

-- By SCN
SELECT * FROM orders AS OF SCN 12345678;
```

### Storage Model

```
Current Table (Fast Lookups)
+----+-------------+-------+------------+
| id | customer_id | total | status     |
+----+-------------+-------+------------+
| 1  | 1           | 105   | shipped    |  <- Current version
+----+-------------+-------+------------+

History Table (Delta Compressed)
+----+--------+-----------------+-------------+-------------+
| id | seq    | columns_changed | valid_from  | valid_to    |
+----+--------+-----------------+-------------+-------------+
| 1  | 1      | {all}           | T1          | T2          |
| 1  | 2      | {status}        | T2          | T3          |
| 1  | 3      | {status,total}  | T3          | NULL        |
+----+--------+-----------------+-------------+-------------+
```

---

## Basic Usage

### AS OF TIMESTAMP Queries

Query data as it existed at a specific point in time:

```sql
-- Exact timestamp
SELECT * FROM orders
AS OF TIMESTAMP '2026-01-04 10:30:00';

-- Relative timestamp
SELECT * FROM orders
AS OF TIMESTAMP (NOW() - INTERVAL '1 hour');

-- With filters
SELECT order_id, total, status
FROM orders
AS OF TIMESTAMP '2026-01-04 10:00:00'
WHERE customer_id = 100
  AND total > 50.00;

-- Join with current data
SELECT
    h.id,
    h.status AS old_status,
    c.status AS current_status
FROM orders AS OF TIMESTAMP '2026-01-04 10:00:00' h
JOIN orders c ON h.id = c.id
WHERE h.status != c.status;
```

### VERSIONS BETWEEN Queries

Retrieve all versions of a row within a time range:

```sql
-- All versions in last hour
SELECT *
FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '1 hour') AND NOW()
WHERE id = 1
ORDER BY versions_starttime;

-- With version metadata
SELECT
    id,
    status,
    versions_starttime,  -- When this version became valid
    versions_endtime,    -- When this version was superseded
    versions_operation   -- INSERT, UPDATE, or DELETE
FROM orders
VERSIONS BETWEEN TIMESTAMP
    '2026-01-01' AND '2026-01-04'
WHERE customer_id = 100;
```

### FROM...TO Range Queries

Query data changes within a time range:

```sql
-- All changes from yesterday to now
SELECT *
FROM orders
FROM TIMESTAMP (NOW() - INTERVAL '1 day')
TO TIMESTAMP NOW()
ORDER BY versions_starttime;

-- From a specific point onwards
SELECT *
FROM orders
FROM TIMESTAMP '2026-01-01 00:00:00'
WHERE status = 'cancelled';
```

### Version History for Specific Keys

```sql
-- Complete history of a specific order
SELECT
    id,
    total,
    status,
    versions_starttime AS changed_at,
    versions_operation AS operation
FROM orders
VERSIONS ALL
WHERE id = 1
ORDER BY versions_starttime;

-- Count changes per order
SELECT
    id,
    COUNT(*) AS version_count,
    MIN(versions_starttime) AS first_version,
    MAX(versions_starttime) AS last_version
FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '7 days') AND NOW()
GROUP BY id
ORDER BY version_count DESC
LIMIT 10;
```

---

## Advanced Queries

### Comparing States at Different Times

```sql
-- Compare order status between two points in time
WITH before AS (
    SELECT id, status, total
    FROM orders
    AS OF TIMESTAMP '2026-01-03 00:00:00'
),
after AS (
    SELECT id, status, total
    FROM orders
    AS OF TIMESTAMP '2026-01-04 00:00:00'
)
SELECT
    COALESCE(b.id, a.id) AS order_id,
    b.status AS status_before,
    a.status AS status_after,
    a.total - COALESCE(b.total, 0) AS total_change
FROM before b
FULL OUTER JOIN after a ON b.id = a.id
WHERE b.status IS DISTINCT FROM a.status
   OR b.total IS DISTINCT FROM a.total;
```

### Finding When Data Changed

```sql
-- Find when a specific order status changed to 'shipped'
SELECT
    id,
    versions_starttime AS shipped_at,
    total
FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '30 days') AND NOW()
WHERE id = 12345
  AND status = 'shipped'
  AND versions_operation IN ('INSERT', 'UPDATE')
ORDER BY versions_starttime
LIMIT 1;
```

### Reconstructing Deleted Data

```sql
-- Find and recover deleted orders
SELECT *
FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '7 days') AND NOW()
WHERE versions_operation = 'DELETE';

-- Restore a deleted order
INSERT INTO orders (id, customer_id, total, status)
SELECT id, customer_id, total, status
FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '1 day') AND NOW()
WHERE id = 999
  AND versions_operation = 'DELETE'
ORDER BY versions_starttime DESC
LIMIT 1;
```

### Audit Queries

```sql
-- Who changed what and when (requires audit extension)
SELECT
    o.id,
    o.status,
    o.versions_starttime,
    o.versions_operation,
    a.user_name,
    a.client_ip
FROM orders VERSIONS ALL o
JOIN audit_log a
    ON a.table_name = 'orders'
    AND a.row_id = o.id
    AND a.timestamp = o.versions_starttime
WHERE o.id = 12345
ORDER BY o.versions_starttime;
```

---

## Flashback Queries (Oracle-Compatible)

HeliosDB provides full Oracle Flashback Query compatibility.

### AS OF SCN

```sql
-- Get current SCN
SELECT current_scn();  -- Returns: 12345678

-- Query at specific SCN
SELECT * FROM orders
AS OF SCN 12345670;

-- Use SCN for precise consistency across tables
SELECT o.*, c.name
FROM orders AS OF SCN 12345670 o
JOIN customers AS OF SCN 12345670 c
    ON o.customer_id = c.id;
```

### VERSIONS BETWEEN SCN

```sql
-- All versions between two SCNs
SELECT
    id,
    status,
    versions_startscn,
    versions_endscn,
    versions_operation
FROM orders
VERSIONS BETWEEN SCN 12345000 AND 12346000
WHERE customer_id = 100;
```

### Flashback Transaction

```sql
-- See all changes made by a specific transaction
SELECT *
FROM orders
FLASHBACK TRANSACTION transaction_id = 'txn_abc123';

-- Undo a transaction's changes
FLASHBACK TABLE orders
TO BEFORE TRANSACTION 'txn_abc123';
```

### SCN-Timestamp Mapping

```sql
-- Convert timestamp to SCN
SELECT timestamp_to_scn('2026-01-04 10:30:00');

-- Convert SCN to timestamp
SELECT scn_to_timestamp(12345678);

-- Find SCN range for a time period
SELECT
    timestamp_to_scn('2026-01-04 00:00:00') AS start_scn,
    timestamp_to_scn('2026-01-05 00:00:00') AS end_scn;
```

---

## Retention Policies

### Configuring Retention

```sql
-- Set retention at table creation
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
) WITH (
    temporal_enabled = true,
    retention_days = 30,          -- Keep 30 days of history
    retention_size_gb = 10,       -- Max 10GB of history
    retention_max_versions = 100  -- Max 100 versions per row
);

-- Modify retention on existing table
ALTER TABLE orders SET (
    retention_days = 14,
    retention_cleanup_strategy = 'time_based_oldest'
);
```

### Cleanup Strategies

| Strategy | Description | Use Case |
|----------|-------------|----------|
| `time_based_oldest` | Delete oldest versions first | Standard FIFO cleanup |
| `time_based_newest` | Keep oldest, delete newest | Preserve historical snapshots |
| `largest_first` | Delete largest versions first | Optimize storage quickly |
| `smallest_first` | Delete smallest versions first | Keep detailed history |

```sql
-- Configure cleanup strategy
ALTER TABLE orders SET (
    retention_cleanup_strategy = 'time_based_oldest'
);
```

### Manual Cleanup

```sql
-- Force immediate cleanup
CALL temporal_cleanup('orders');

-- Cleanup with specific cutoff
CALL temporal_cleanup('orders', TIMESTAMP '2026-01-01 00:00:00');

-- Check cleanup statistics
SELECT * FROM temporal_cleanup_stats
WHERE table_name = 'orders';
```

### Monitoring Retention

```sql
-- View temporal storage usage
SELECT
    table_name,
    current_row_count,
    history_row_count,
    history_size_bytes,
    oldest_version_time,
    newest_version_time,
    retention_days
FROM temporal_table_stats;

-- Check for tables exceeding retention limits
SELECT table_name, history_size_bytes
FROM temporal_table_stats
WHERE history_size_bytes > retention_size_limit_bytes;
```

---

## Performance Tuning

### Index Optimization

```sql
-- Create temporal index for faster lookups
CREATE INDEX idx_orders_temporal
ON orders (id, versions_starttime)
WITH (temporal = true);

-- Create index on frequently queried historical columns
CREATE INDEX idx_orders_status_temporal
ON orders (status, versions_starttime)
WITH (temporal = true);
```

### Query Optimization Tips

```sql
-- GOOD: Specific time range
SELECT * FROM orders
AS OF TIMESTAMP '2026-01-04 10:30:00'
WHERE id = 12345;  -- Uses index

-- LESS EFFICIENT: Wide time range scan
SELECT * FROM orders
VERSIONS BETWEEN TIMESTAMP
    '2020-01-01' AND NOW()  -- Scans entire history
WHERE status = 'pending';

-- BETTER: Narrow the range
SELECT * FROM orders
VERSIONS BETWEEN TIMESTAMP
    (NOW() - INTERVAL '7 days') AND NOW()
WHERE status = 'pending';
```

### Configuration Parameters

```sql
-- View temporal configuration
SHOW temporal_query_timeout;
SHOW temporal_cache_size;
SHOW temporal_parallel_workers;

-- Tune for better performance
SET temporal_cache_size = '1GB';        -- Increase cache
SET temporal_parallel_workers = 4;       -- Enable parallel scans
SET temporal_query_timeout = '30s';      -- Query timeout
```

### Monitoring Performance

```sql
-- Check temporal query statistics
SELECT
    query_type,
    avg_execution_time_ms,
    max_execution_time_ms,
    total_executions,
    cache_hit_rate
FROM temporal_query_stats
ORDER BY avg_execution_time_ms DESC;

-- Monitor version chain length (long chains = slower queries)
SELECT
    table_name,
    avg_versions_per_row,
    max_versions_per_row,
    rows_with_long_chains
FROM temporal_chain_stats
WHERE avg_versions_per_row > 50;
```

---

## Use Cases

### 1. Debugging Production Issues

```sql
-- What did the order look like when the error occurred?
SELECT * FROM orders
AS OF TIMESTAMP '2026-01-04 10:30:45'  -- Error timestamp from logs
WHERE id = 99999;

-- What changed just before the error?
SELECT *
FROM orders
VERSIONS BETWEEN TIMESTAMP
    '2026-01-04 10:25:00' AND '2026-01-04 10:31:00'
WHERE id = 99999
ORDER BY versions_starttime;
```

### 2. Compliance and Audit

```sql
-- Generate audit report for a customer
SELECT
    o.id AS order_id,
    o.total,
    o.status,
    o.versions_starttime AS changed_at,
    o.versions_operation AS action
FROM orders VERSIONS ALL o
WHERE o.customer_id = 12345
  AND o.versions_starttime BETWEEN '2026-01-01' AND '2026-02-01'
ORDER BY o.versions_starttime;

-- Export complete change history
COPY (
    SELECT * FROM orders
    VERSIONS BETWEEN TIMESTAMP '2026-01-01' AND NOW()
    ORDER BY id, versions_starttime
) TO '/tmp/orders_audit.csv' WITH CSV HEADER;
```

### 3. Data Recovery

```sql
-- Find accidentally updated rows
SELECT DISTINCT id
FROM orders
VERSIONS BETWEEN TIMESTAMP
    '2026-01-04 14:00:00' AND '2026-01-04 14:05:00'
WHERE versions_operation = 'UPDATE'
  AND status = 'cancelled';  -- Shouldn't have been cancelled

-- Restore to previous state
UPDATE orders o
SET
    status = h.status,
    total = h.total
FROM (
    SELECT DISTINCT ON (id) *
    FROM orders
    AS OF TIMESTAMP '2026-01-04 13:59:00'
    WHERE id IN (/* affected IDs */)
) h
WHERE o.id = h.id;
```

### 4. Trend Analysis

```sql
-- Daily order status distribution over past week
WITH daily_snapshot AS (
    SELECT
        date_trunc('day', d) AS snapshot_date,
        status,
        COUNT(*) AS count
    FROM generate_series(
        NOW() - INTERVAL '7 days',
        NOW(),
        INTERVAL '1 day'
    ) d
    CROSS JOIN LATERAL (
        SELECT status
        FROM orders
        AS OF TIMESTAMP d
    ) o
    GROUP BY 1, 2
)
SELECT * FROM daily_snapshot
ORDER BY snapshot_date, status;
```

---

## Troubleshooting

### Common Issues

#### Query Returns No Results

```sql
-- Check if temporal is enabled
SELECT temporal_enabled FROM pg_tables
WHERE tablename = 'orders';

-- Check if data exists in the time range
SELECT
    MIN(versions_starttime) AS earliest,
    MAX(versions_starttime) AS latest
FROM orders VERSIONS ALL;

-- Verify timestamp format
SELECT '2026-01-04 10:30:00'::timestamp;  -- Should not error
```

#### Slow Temporal Queries

```sql
-- Check version chain length
SELECT id, COUNT(*) AS version_count
FROM orders VERSIONS ALL
GROUP BY id
ORDER BY version_count DESC
LIMIT 10;

-- Check if temporal indexes exist
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
  AND indexdef LIKE '%temporal%';

-- Run EXPLAIN on slow queries
EXPLAIN ANALYZE
SELECT * FROM orders
AS OF TIMESTAMP '2026-01-04 10:30:00'
WHERE customer_id = 100;
```

#### Storage Growing Too Fast

```sql
-- Check history size
SELECT * FROM temporal_table_stats
WHERE table_name = 'orders';

-- Reduce retention if needed
ALTER TABLE orders SET (retention_days = 7);

-- Force cleanup
CALL temporal_cleanup('orders');
```

### Error Messages

| Error | Cause | Solution |
|-------|-------|----------|
| `temporal_not_enabled` | Temporal feature not enabled | `ALTER TABLE... SET (temporal_enabled = true)` |
| `timestamp_out_of_range` | Requested time before retention | Query within retention period |
| `scn_not_found` | Invalid or expired SCN | Use timestamp instead or recent SCN |
| `version_chain_too_long` | Too many versions | Reduce update frequency or enable cleanup |

---

## Best Practices

### 1. Choose Appropriate Retention

- **Development/Testing**: 1-7 days
- **Production (debugging)**: 7-30 days
- **Compliance (audit)**: 365+ days (consider tiered storage)

### 2. Index Strategy

```sql
-- Index primary key for temporal lookups
CREATE INDEX idx_orders_id_temporal
ON orders (id, versions_starttime)
WITH (temporal = true);

-- Index frequently filtered columns
CREATE INDEX idx_orders_customer_temporal
ON orders (customer_id, versions_starttime)
WITH (temporal = true);
```

### 3. Limit Query Scope

```sql
-- GOOD: Narrow time range + specific filter
SELECT * FROM orders
AS OF TIMESTAMP (NOW() - INTERVAL '1 hour')
WHERE id = 12345;

-- AVOID: Wide range without filter
SELECT * FROM orders
VERSIONS BETWEEN TIMESTAMP '2020-01-01' AND NOW();
```

### 4. Use SCNs for Consistency

```sql
-- Capture SCN at start of analysis
DO $$
DECLARE
    analysis_scn BIGINT := current_scn();
BEGIN
    -- All queries use same SCN for consistency
    SELECT * FROM orders AS OF SCN analysis_scn;
    SELECT * FROM customers AS OF SCN analysis_scn;
    SELECT * FROM payments AS OF SCN analysis_scn;
END $$;
```

### 5. Monitor and Maintain

```sql
-- Regular monitoring query
SELECT
    table_name,
    history_size_bytes / 1024 / 1024 AS history_mb,
    avg_versions_per_row,
    CASE
        WHEN history_size_bytes > retention_size_limit_bytes THEN 'OVER LIMIT'
        WHEN history_size_bytes > retention_size_limit_bytes * 0.8 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM temporal_table_stats
ORDER BY history_size_bytes DESC;
```

---

## API Reference

### SQL Syntax

#### AS OF TIMESTAMP
```sql
SELECT columns FROM table
AS OF TIMESTAMP timestamp_expression
[WHERE conditions];
```

#### AS OF SCN
```sql
SELECT columns FROM table
AS OF SCN scn_number
[WHERE conditions];
```

#### VERSIONS BETWEEN TIMESTAMP
```sql
SELECT columns FROM table
VERSIONS BETWEEN TIMESTAMP start_time AND end_time
[WHERE conditions];
```

#### VERSIONS BETWEEN SCN
```sql
SELECT columns FROM table
VERSIONS BETWEEN SCN start_scn AND end_scn
[WHERE conditions];
```

#### VERSIONS ALL
```sql
SELECT columns FROM table
VERSIONS ALL
[WHERE conditions];
```

### System Functions

| Function | Description | Example |
|----------|-------------|---------|
| `current_scn()` | Get current SCN | `SELECT current_scn()` |
| `timestamp_to_scn(ts)` | Convert timestamp to SCN | `SELECT timestamp_to_scn('2026-01-04')` |
| `scn_to_timestamp(scn)` | Convert SCN to timestamp | `SELECT scn_to_timestamp(12345678)` |
| `temporal_cleanup(table)` | Force cleanup | `CALL temporal_cleanup('orders')` |

### Version Metadata Columns

| Column | Description |
|--------|-------------|
| `versions_starttime` | When this version became valid |
| `versions_endtime` | When this version was superseded (NULL if current) |
| `versions_startscn` | SCN when version became valid |
| `versions_endscn` | SCN when version was superseded |
| `versions_operation` | INSERT, UPDATE, or DELETE |
| `versions_xid` | Transaction ID that created this version |

---

**Support**: For issues or questions, contact support@heliosdb.com.

**Related Documentation**:
- [MVCC Configuration Guide](https://www.heliosdb.com/docs/full/features/mvcc/mvcc_configuration_guide/)
- [Transactions Guide](https://www.heliosdb.com/docs/full/guides/user/transactions_user_guide/)
- [Backup and Recovery](https://www.heliosdb.com/docs/full/guides/user/backup_recovery_guide/)

**License**: SSPL-1.0 (HeliosDB Full). A commercial licence is available from [sales@heliosdb.com](mailto:sales@heliosdb.com).