Skip to content
HeliosDB Full

HeliosDB Time-Travel Debugging User Guide

HeliosDB Time-Travel Debugging User Guide

Version: 1.0 Last Updated: January 4, 2026 Feature Status: Production Ready (100%)

Table of Contents

  1. Overview
  2. Getting Started
  3. Core Concepts
  4. Basic Usage
  5. Advanced Queries
  6. Flashback Queries (Oracle-Compatible)
  7. Retention Policies
  8. Performance Tuning
  9. Use Cases
  10. Troubleshooting
  11. Best Practices
  12. API Reference

Overview

HeliosDB Time-Travel Debugging enables you to query historical states of your data at any point in time. This powerful feature 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%
  • Production Ready: ACID-compliant with snapshot isolation

Key Benefits

BenefitDescription
DebuggingInvestigate what data looked like when a bug occurred
Audit TrailComplete history of all changes for compliance
Data RecoveryRetrieve accidentally deleted or modified data
AnalyticsAnalyze trends and changes over time
TestingCompare data states before and after changes

Performance Characteristics

MetricTargetTypical
Storage Overhead (7-day history)<10%5-8%
Query Latency (vs current)<2x1.3-1.8x
Write Throughput1M+ ops/sec1.2M ops/sec
Temporal Index Lookup<100ms20-50ms

Getting Started

Prerequisites

  • HeliosDB v7.0 or later
  • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

StrategyDescriptionUse Case
time_based_oldestDelete oldest versions firstStandard FIFO cleanup
time_based_newestKeep oldest, delete newestPreserve historical snapshots
largest_firstDelete largest versions firstOptimize storage quickly
smallest_firstDelete smallest versions firstKeep detailed history
-- Configure cleanup strategy
ALTER TABLE orders SET (
    retention_cleanup_strategy = 'time_based_oldest'
);

Manual Cleanup

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

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

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

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

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

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

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

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

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

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

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

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

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

ErrorCauseSolution
temporal_not_enabledTemporal feature not enabledALTER TABLE ... SET (temporal_enabled = true)
timestamp_out_of_rangeRequested time before retentionQuery within retention period
scn_not_foundInvalid or expired SCNUse timestamp instead or recent SCN
version_chain_too_longToo many versionsReduce 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

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

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

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

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

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

AS OF SCN

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

VERSIONS BETWEEN TIMESTAMP

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

VERSIONS BETWEEN SCN

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

VERSIONS ALL

SELECT columns FROM table
VERSIONS ALL
[WHERE conditions];

System Functions

FunctionDescriptionExample
current_scn()Get current SCNSELECT current_scn()
timestamp_to_scn(ts)Convert timestamp to SCNSELECT timestamp_to_scn('2026-01-04')
scn_to_timestamp(scn)Convert SCN to timestampSELECT scn_to_timestamp(12345678)
temporal_cleanup(table)Force cleanupCALL temporal_cleanup('orders')

Version Metadata Columns

ColumnDescription
versions_starttimeWhen this version became valid
versions_endtimeWhen this version was superseded (NULL if current)
versions_startscnSCN when version became valid
versions_endscnSCN when version was superseded
versions_operationINSERT, UPDATE, or DELETE
versions_xidTransaction ID that created this version

Support: For issues or questions, contact temporal-support@heliosdb.com or open an issue on GitHub.

Related Documentation:

License: Enterprise license required for production use.

Version: HeliosDB v7.0+ with Time-Travel extension