Home / Blog / Time-Travel Queries in Production
Tutorial 9 min read

Time-Travel Queries in Production

Every database supports backups. But what if you could query your data as it existed at any point in the past, without restoring from backup? That's what time-travel queries enable, and they're one of the most surprisingly useful features in HeliosDB. Here are real-world patterns we've seen from production users.

How Time-Travel Works

HeliosDB maintains multiple versions of every row using MVCC (Multi-Version Concurrency Control). When a row is updated or deleted, the old version is preserved with its timestamp. Time-travel queries read from these historical versions:

-- Query data as it existed at a specific timestamp
SELECT * FROM orders AS OF TIMESTAMP '2025-09-01 12:00:00';

-- Query data from 24 hours ago
SELECT * FROM users AS OF INTERVAL '24 hours ago';

-- Compare current data with historical data
SELECT
    current.balance AS current_balance,
    historical.balance AS yesterday_balance,
    current.balance - historical.balance AS change
FROM accounts current
JOIN accounts AS OF INTERVAL '24 hours ago' historical
    ON current.id = historical.id
WHERE ABS(current.balance - historical.balance) > 1000;

Pattern 1: Audit Compliance

Financial regulations (SOX, PCI-DSS) require proving what data looked like at specific points in time. Time-travel queries make this trivial:

-- SOX audit: What did the financial statements look like on Dec 31?
SELECT * FROM general_ledger
  AS OF TIMESTAMP '2025-12-31 23:59:59'
  WHERE account_type = 'revenue';

-- PCI-DSS: Was this customer's card data accessed before the breach?
SELECT * FROM access_logs
  AS OF TIMESTAMP '2025-09-15 14:30:00'
  WHERE resource = 'payment_cards'
  AND user_id = 42;

Pattern 2: Debugging Production Issues

When users report "my data was different yesterday," you can actually verify what they saw:

-- What did this user's profile look like before the bug?
SELECT * FROM user_profiles
  AS OF TIMESTAMP '2025-09-24 09:00:00'
  WHERE user_id = 12345;

-- Find exactly when a value changed
SELECT snapshot_timestamp, status, total
FROM order_history(
    table_name => 'orders',
    row_id => 98765,
    from_ts => '2025-09-24 00:00:00',
    to_ts => '2025-09-25 00:00:00'
)
ORDER BY snapshot_timestamp;

Pattern 3: Data Recovery

Accidentally deleted or corrupted data? Recover it without restoring from backup:

-- Oops, someone deleted important records
DELETE FROM products WHERE category = 'electronics';

-- Recover them from 5 minutes ago
INSERT INTO products
SELECT * FROM products AS OF INTERVAL '5 minutes ago'
WHERE category = 'electronics'
AND id NOT IN (SELECT id FROM products);

-- Or recover an entire table state
CREATE TABLE products_recovered AS
SELECT * FROM products AS OF TIMESTAMP '2025-09-25 14:00:00';

Pattern 4: A/B Testing Analysis

Compare metrics before and after a feature launch to measure impact:

-- Compare conversion rates before and after feature launch
WITH before AS (
    SELECT COUNT(*) FILTER (WHERE converted) * 100.0 / COUNT(*) AS rate
    FROM user_sessions AS OF TIMESTAMP '2025-09-20 00:00:00'
    WHERE created_at > '2025-09-15'
),
after AS (
    SELECT COUNT(*) FILTER (WHERE converted) * 100.0 / COUNT(*) AS rate
    FROM user_sessions
    WHERE created_at > '2025-09-20'
)
SELECT
    before.rate AS before_launch,
    after.rate AS after_launch,
    after.rate - before.rate AS improvement
FROM before, after;

Branching + Time-Travel

HeliosDB's database branching combined with time-travel creates powerful workflows. Create a branch from a historical point, run "what if" analyses, and discard:

-- Create a branch from last week's data
CREATE BRANCH analysis FROM main AS OF TIMESTAMP '2025-09-18 00:00:00';
USE BRANCH analysis;

-- Run destructive analysis safely
DELETE FROM orders WHERE total < 10;
SELECT AVG(total) FROM orders;  -- Average without small orders

-- Discard when done
DROP BRANCH analysis;

Configuration

# heliosdb.toml
[time_travel]
retention_period = "90 days"    # How far back you can query
auto_cleanup = true             # Automatically purge old versions
cleanup_interval = "1 hour"     # Cleanup frequency

Time-travel queries are available in all HeliosDB tiers. Community gets 7-day retention, Professional gets 1 year, and Enterprise gets unlimited retention.

← Back to Blog