Hologres:Query historical data with time travel

Limitations

• Only Hologres internal tables and Dynamic Tables support Time Travel. For Time Travel on foreign tables (Paimon data), see Access Paimon data using a DLF Catalog.

• Time Travel parameters can only be set at table creation time. You cannot enable Time Travel on an existing table.

• Once enabled, Time Travel cannot be disabled.

• Storing multiple data versions increases storage consumption and may introduce some query performance overhead.

Enable Time Travel on a table

Set enable_mvcc and mvcc_gc_ttl_seconds in the WITH clause when creating a table:

CREATE TABLE <table_name> (...)
WITH (
    enable_mvcc = 'true',
    mvcc_gc_ttl_seconds = '<seconds>'
);

When you query a timestamp outside the retention window, the system returns an error similar to: Tablexxx only supports time traveling between xxx and xxx;

Query historical data

Use the FOR TIMESTAMP AS OF clause to query a table at a specific point in time within the retention window:

-- Relative: 1 hour ago from now
SELECT * FROM <table_name> FOR TIMESTAMP AS OF (now() - INTERVAL '1 hour');

-- Absolute: a specific timestamp with timezone offset
SELECT * FROM <table_name> FOR TIMESTAMP AS OF '2025-08-05 11:18:50+08';

-- Unix timestamp (epoch seconds)
SELECT * FROM <table_name> FOR TIMESTAMP AS OF 1754374966;

Modify the retention window

Change the retention window on an existing Time Travel-enabled table with ALTER TABLE:

ALTER TABLE <table_name> SET (mvcc_gc_ttl_seconds = <seconds>);

Decreasing the TTL — the system garbage-collects expired versions in the background based on the new, shorter window. Queries for timestamps outside the new window will fail with an error.

Increasing the TTL — versions already garbage-collected under the previous shorter window cannot be recovered. Queries for timestamps that fall in the gap between the old and new window will return inaccurate results. Avoid increasing the TTL in production.

Example

The following example creates a Time Travel-enabled table, inserts a row, queries it at the current time, applies an update, and then queries the historical state from before the update.

Step 1: Create a table with Time Travel enabled and insert data.

BEGIN;
CREATE TABLE test1 (
    a INT NOT NULL PRIMARY KEY,
    b INT,
    c INT
)
WITH (
    enable_mvcc = TRUE,
    mvcc_gc_ttl_seconds = 864000  -- 10-day retention window
);
COMMIT;

INSERT INTO test1 VALUES (1, 2, 3);

Step 2: Query the current state.

SELECT * FROM test1 FOR TIMESTAMP AS OF now();

Result:

a  b  c
-------
1  2  3

Step 3: Update a row, then query the state from 1 minute ago.

-- Update column b using upsert
INSERT INTO test1 (a, b, c) VALUES (1, 4, 5)
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.b;

-- Query the state from before the update
SELECT * FROM test1 FOR TIMESTAMP AS OF (now() - INTERVAL '1 minute');

Result:

a  b  c
-------
1  4  5

What's next

• If your instance runs a version earlier than Hologres V4.0, see Instance Upgrade before enabling Time Travel.

• For Time Travel on Paimon data via foreign tables, see Access Paimon data using a DLF Catalog.