[Feature] Time Travel: SELECT ... FOR TIME AS OF '<timestamp>' in cloud/decoupled mode

[nsivarajan]

Search before asking

• I had searched in the issues and found no similar issues.

Description

Modern analytical workloads require querying data as it existed at a specific point in time — for audit trails, data debugging, incremental pipeline recovery, and regulatory compliance.

Apache Doris cloud/decoupled mode currently has no mechanism to read historical versions of a table. Once data is compacted or overwritten, earlier states are unrecoverable from SQL.

Requested syntax:

SELECT * FROM orders FOR TIME AS OF '2024-01-15 10:00:00';

CREATE TABLE snapshot AS
    SELECT * FROM orders FOR TIME AS OF '2024-01-15 10:00:00';

Supported on all DML table models: Duplicate Key, Unique Key (MoW), and Aggregate Key.

Solution

Key design points

Per-table opt-in — zero overhead for non-TT tables:

 CREATE TABLE orders (...)
 PROPERTIES (
     "enable_time_travel"         = "true",
     "time_travel_retention_days" = "7"
 );

Two-level FDB index (cloud mode):

• Level 1: sparse version index — one FDB key per commit per partition (T → V mapping)
• Level 2: compaction checkpoint — pre-compaction rowset manifests written before each cumulative compaction, so historical reads survive compaction

One batch RPC resolves all partitions and fetches all manifests in a single FDB transaction — consistent snapshot across all partitions, one network round trip.

Post-compaction correctness — the critical test: a query at timestamp T returns identical results before and after compaction of the rowsets that covered T.

Scope

• Cloud/decoupled mode only (this PR)
• Coupled/local storage mode: same architecture, separate follow-up (~40% effort)
• SHOW TIME TRAVEL ON : listed as future work

  Test coverage

  • Unit tests: Nereids optimizer timestamp propagation, property parsing
  • Regression tests: DUP table, MoW table, post-compaction correctness

  PR
  Single PR with 4 logical commits for reviewability — metadata layer, meta service,recycler, FE+BE query path.

  Use case

  Apache Doris cloud/decoupled mode has no built-in mechanism to query a table as it existed at a specific point in the past. Once data is modified, compacted, or overwritten, earlier states are unrecoverable via SQL.

  This blocks several real-world use cases that rely on historical data access:

  Audit and compliance — regulations (GDPR, SOX, HIPAA) require proving what data looked like at a specific date. Today this requires maintaining separate snapshot tables manually, duplicating storage and ETL complexity.

  Data debugging — when a pipeline produces unexpected results at 2 AM, engineers need to inspect what the source table contained at that exact moment. Without time travel, they must restore from backup or replay the entire pipeline from scratch.

  Incremental pipeline recovery — streaming ingestion pipelines that fail mid-batch need to re-read source data from the point of failure. Without historical access, the only option is full re-scan.

  Accidental data correction — DELETE FROM orders WHERE id IN (...) with a wrong predicate has no undo. Users must restore from external backup, which is slow and loses data written after the backup.

  What other systems provide

  Snowflake, Databricks Delta Lake, Apache Iceberg, and Databend all support

  time travel syntax:

  -- Snowflake / Delta Lake
  SELECT * FROM orders AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP);
  
  -- Iceberg
  SELECT * FROM orders FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-15 10:00:00';
  
  -- Databend
  SELECT * FROM orders AT (SNAPSHOT => '<snapshot_id>');

  Apache Doris has no equivalent. Users of cloud/decoupled Doris who migrate from any of the above systems lose this capability.

  Related issues

  TimeTravel for Cloud:Decoupled Mode.pdf

  Are you willing to submit PR?

  • Yes I am willing to submit a PR!

  Code of Conduct

  • I agree to follow this project's Code of Conduct

[nsivarajan]

[doris@ip-240-79-98-14 ~]$ mysql -uroot -P9030 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.99 doris version doris-0.0.0-8dd0879f83 (Cloud Mode)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| __internal_schema  |
| information_schema |
| mysql              |
| tt_test_db         |
+--------------------+
4 rows in set (0.001 sec)

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]> use tt_test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [tt_test_db]>
MySQL [tt_test_db]> show tables;
+----------------------+
| Tables_in_tt_test_db |
+----------------------+
| tt_orders_dup        |
| tt_orders_mow        |
| tt_travel_v2         |
| tt_travel_v3         |
| tt_travel_v4         |
| tt_travel_v5         |
+----------------------+
6 rows in set (0.001 sec)

MySQL [tt_test_db]>
MySQL [tt_test_db]> show create table tt_travel_v5;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt_travel_v5 | CREATE TABLE `tt_travel_v5` (
  `id` int NULL,
  `status` varchar(20) NULL,
  `amount` decimal(10,2) NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 3
PROPERTIES (
"file_cache_ttl_seconds" = "0",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V3",
"light_schema_change" = "true",
"storage_vault_id" = "1",
"storage_vault_name" = "dr_s3_vault",
"disable_auto_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_time_travel" = "true",
"time_travel_retention_days" = "7"
); |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)

MySQL [tt_test_db]>
MySQL [tt_test_db]> select * from tt_travel_v5;
+------+-----------+--------+
| id   | status    | amount |
+------+-----------+--------+
|    4 | shipped   | 400.00 |
|    5 | delivered | 500.00 |
|    6 | delivered | 600.00 |
|    2 | pending   | 200.00 |
|    1 | pending   | 100.00 |
|    3 | shipped   | 300.00 |
|    7 | delivered | 700.00 |
|    8 | delivered | 800.00 |
+------+-----------+--------+
8 rows in set (0.055 sec)

MySQL [tt_test_db]> SELECT * FROM tt_travel_v5 FOR TIME AS OF '2026-07-05 17:07:35' ORDER BY id;
+------+---------+--------+
| id   | status  | amount |
+------+---------+--------+
|    1 | pending | 100.00 |
|    2 | pending | 200.00 |
+------+---------+--------+
2 rows in set (0.037 sec)

MySQL [tt_test_db]>