---
title: "Time-Travel Queries in Production"
description: "Real-world patterns for using AS OF queries in production: audit compliance, debugging, data recovery, and A/B testing analysis."
date: 2025-09-25
updated: 2026-10-15
tag: "Tutorial"
canonical: https://www.heliosdb.com/blog/time-travel-queries-in-production.html
---

# Time-Travel Queries in Production

**Editor's note (2026-10-15):** Time-travel queries run in HeliosDB Nano and Lite; for HeliosDB Full they are planned (8.3.0 accepts AS OF but does not read historical snapshots). The samples below show the 2025 syntax and configuration. In Nano, `AS OF TIMESTAMP`, `AS OF NOW`, `AS OF TRANSACTION` and `AS OF SCN` are the documented forms, history is kept by default, and a `version_retention` window lets older versions be reclaimed (see [time travel](https://www.heliosdb.com/docs/nano/features/time-travel/)). On HeliosDB Cloud the time-travel lookback depends on the plan (see [pricing](https://www.heliosdb.com/pricing.html)).

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 HeliosDB Nano and Lite; for HeliosDB Full they are planned (8.3.0 accepts AS OF but does not read historical snapshots). On HeliosDB Cloud, the time-travel lookback depends on the plan.