Time Travel

Discover Tacnode's Time Travel feature to easily query historical data and enhance your analysis with our comprehensive guide.

Time Travel allows you to query your database as it existed at any point in the past. This powerful feature enables data recovery, historical analysis, and debugging by accessing previous states of your data.

How Time Travel Works

Time Travel uses the FOR SYSTEM_TIME AS OF syntax to specify a point in time for your query. Instead of seeing current data, you’ll see exactly what the database looked like at that moment.

Key Capabilities

• Data Recovery - Restore accidentally deleted or modified data
• Historical Analysis - Track changes and trends over time
• Debugging - Identify when and how data issues occurred
• Conflict Resolution - Avoid transaction conflicts in high-concurrency scenarios

Time Range Limitations

Common Use Cases

1. Recover Accidentally Deleted Data

Quickly restore data that was mistakenly deleted:

-- Check what data existed before deletion
SELECT * FROM users FOR SYSTEM_TIME AS OF '-2h' 
WHERE department = 'Engineering';

-- Restore the deleted records
INSERT INTO users 
SELECT * FROM users FOR SYSTEM_TIME AS OF '-2h'
WHERE id IN (101, 102, 103);

2. Debug Data Issues

Track down when and how data problems occurred:

-- Compare current state with past state
SELECT 
    current.id,
    current.status AS current_status,
    past.status AS past_status,
    current.updated_at
FROM orders current
JOIN orders FOR SYSTEM_TIME AS OF '-1h' past 
    ON current.id = past.id
WHERE current.status != past.status;

3. Historical Analysis

Analyze trends and changes over time:

-- Compare user counts at different time points
SELECT 
    'current' AS timepoint,
    COUNT(*) AS user_count
FROM users
UNION ALL
SELECT 
    '1 hour ago',
    COUNT(*)
FROM users FOR SYSTEM_TIME AS OF '-1h'
UNION ALL  
SELECT 
    '1 day ago',
    COUNT(*)
FROM users FOR SYSTEM_TIME AS OF '-24h';

4. Avoid Transaction Conflicts

Read consistent data during high-concurrency operations:

-- Start transaction at a specific point in time
BEGIN FOR SYSTEM_TIME AS OF '-30s';
    -- All queries in this transaction see data from 30 seconds ago
    SELECT COUNT(*) FROM orders WHERE status = 'pending';
    SELECT SUM(amount) FROM payments WHERE created_at > '2024-01-01';
COMMIT;

Time Specification Formats

Tacnode supports multiple ways to specify time points:

1. Relative Time (Recommended)

Use relative time strings for convenience:

-- Simple relative times
SELECT COUNT(*) FROM products FOR SYSTEM_TIME AS OF '-10s';   -- 10 seconds ago
SELECT * FROM users FOR SYSTEM_TIME AS OF '-5m';             -- 5 minutes ago  
SELECT * FROM orders FOR SYSTEM_TIME AS OF '-2h';            -- 2 hours ago
SELECT * FROM logs FOR SYSTEM_TIME AS OF '-1d';              -- 1 day ago

2. Exact Timestamps

Use specific timestamps for precise control:

-- Get nanosecond timestamp for specific datetime
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-09-30 15:15:00') * 1000000000;
-- Result: 1727680500000000000

-- Query using exact timestamp
SELECT * FROM transactions 
FOR SYSTEM_TIME AS OF '1727680500000000000';

3. Mixed Time Points in Joins

Set different time points for each table:

-- Compare data from different time points
SELECT 
    u.username,
    o1.order_count AS current_orders,
    o2.order_count AS yesterday_orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY user_id
) o1 ON u.id = o1.user_id
JOIN (
    SELECT user_id, COUNT(*) AS order_count 
    FROM orders FOR SYSTEM_TIME AS OF '-24h'
    GROUP BY user_id  
) o2 ON u.id = o2.user_id;

Advanced Examples

Transaction-Level Time Travel

-- Set time for entire transaction
BEGIN FOR SYSTEM_TIME AS OF '-1h';
    -- All queries use data from 1 hour ago
    UPDATE inventory SET quantity = quantity + 100 
    WHERE product_id = 'PROD001';
    
    INSERT INTO audit_log (action, timestamp) 
    VALUES ('inventory_adjustment', NOW());
COMMIT;

Materialized View Time Travel

-- Create materialized view
CREATE MATERIALIZED VIEW daily_sales AS 
SELECT DATE(created_at) AS sale_date, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE(created_at);

-- Query historical state of materialized view
SELECT * FROM daily_sales FOR SYSTEM_TIME AS OF '-6h'
WHERE sale_date = '2024-09-30';

Complex Historical Analysis

-- Find all changes to user permissions in the last hour
WITH current_perms AS (
    SELECT user_id, permissions 
    FROM user_permissions
),
past_perms AS (
    SELECT user_id, permissions 
    FROM user_permissions FOR SYSTEM_TIME AS OF '-1h'
)
SELECT 
    c.user_id,
    p.permissions AS old_permissions,
    c.permissions AS new_permissions
FROM current_perms c
FULL OUTER JOIN past_perms p ON c.user_id = p.user_id
WHERE c.permissions != p.permissions 
   OR c.user_id IS NULL 
   OR p.user_id IS NULL;

Configuration and Optimization

Adjusting Retention Period

Control how long historical data is kept with the gc_ttl_seconds parameter:

-- Set during table creation (6000 seconds = 100 minutes)
CREATE TABLE user_activity (
    user_id INT,
    action TEXT,
    timestamp TIMESTAMPTZ DEFAULT NOW()
) WITH (gc_ttl_seconds = 6000);

-- Modify existing table (23 hours retention)
ALTER TABLE user_activity SET (gc_ttl_seconds = 82800);

-- Check current setting
SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'user_activity';

Configuration Guidelines:

• Minimum: 1800 seconds (30 minutes)
• Default: 82800 seconds (23 hours)
• Maximum Recommended: 604800 seconds (1 week)
• Performance Impact: Longer retention reduces query performance

Performance Considerations

1. Shorter retention = Better performance - Balance historical access needs with query speed
2. Index optimization - Ensure proper indexes exist for time-travel queries
3. Storage overhead - Historical versions consume additional storage space
4. Cleanup frequency - More frequent cleanup improves performance but uses more CPU

Best Practices

• Use relative time formats when possible ('-1h' vs exact timestamps)
• Set appropriate gc_ttl_seconds based on your recovery needs
• Monitor storage usage with longer retention periods
• Test time-travel queries on non-production data first
• Document your data recovery procedures using time travel