Time Travel

Introduction to Time Travel

Time travel allows users to access the database as it appeared at a specific time. This feature uses the syntax FOR SYSTEM_TIME AS OF.

The FOR SYSTEM_TIME AS OF syntax lets you specify a particular moment in time to query the state of a table or materialized view. This functionality is useful in various scenarios, such as resolving transaction conflicts, recovering deleted data, and identifying issues with historical data.

Scenarios

Recover accidentally deleted data

If you mistakenly delete data from a table, you can restore it by querying with a timestamp from before the deletion occurred.

Historical data problem location

To diagnose and troubleshoot issues with historical data, specify various time points to examine changes and more effectively identify and resolve problems.

Resolving transaction conflicts

Transaction conflicts often arise in high-concurrency situations. By querying a previous time point, your current query can avoid interference from subsequent read and write operations. This method can effectively manage transaction conflicts if you can tolerate some data delay.

Examples

We provide specific examples to demonstrate the application of the FOR SYSTEM_TIME AS OF syntax.

1. Query for a single table

SELECT COUNT(1) FROM t1 FOR SYSTEM_TIME AS OF '-10s' WHERE id = 5;

This query returns the count of records in the table t1 that had a value of 5 exactly 10 seconds ago for the specified id.

2. Set the time for each table during the join

SELECT t1.col1, t2.col2
FROM t1 FOR SYSTEM_TIME AS OF '-30m', t2 FOR SYSTEM_TIME AS OF '-1h'
WHERE t1.id = t2.id;

In this query, the t1 table contains status information from 30 minutes ago, while the t2 table shows the status from 1 hour ago. You can adjust the time points for each table as required.

3. Specified time on the transaction

BEGIN FOR SYSTEM_TIME AS OF '-60s';
-- All queries in this transaction will read data from 60 seconds ago
SELECT * FROM t1;
COMMIT;

When a specific time is set at the start of a transaction, every query within that transaction will access the data as it was at that moment.

4. Materialized View defines the time

CREATE MATERIALIZED VIEW mv AS SELECT id FROM t1;
SELECT * FROM mv FOR SYSTEM_TIME AS OF '-1s';

This query retrieves the materialized view mv data from one second ago.

5. Query by specifying a timestamp

# date -d '2024-09-30 15:15:00' +%s%N
1727680500000000000
 
SELECT * FROM t1 FOR SYSTEM_TIME AS OF '1727680500000000000';

In this example, we first use the date command to get the nanosecond timestamp '1727680500000000000' for the time point 2024-09-30 15:15:00. Next, we employ the FOR SYSTEM_TIME AS OF '1727680500000000000' syntax in the query to check the data status of the table t1 at that specific moment.

6. Adjusting the gc_ttl_seconds Property

gc_ttl_seconds defines the retention period for MVCC multi-version garbage collection. Default: 82800 seconds (23 hours). Minimum: 1800 seconds (30 minutes). Maximum recommended: 604800 seconds (1 week). Longer retention periods reduce query efficiency.

-- Set during table creation
CREATE TABLE tbl
(
    a int,
    b int,
    PRIMARY KEY (a)
) WITH (gc_ttl_seconds = 6000);
 
-- Alter table property
ALTER TABLE tbl SET (gc_ttl_seconds = 82800);