[Feature] Row-level TTL Design

[AntiTopQuark]

Search before asking

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

Description

1. Supported Scope and Mutual Exclusions

1.1 Supported Scope

Capability	Status	Notes
DUPLICATE KEY	Supported	The TTL hidden column uses aggregation type NONE
UNIQUE KEY MOW	Supported	The TTL hidden column uses aggregation type REPLACE
UNIQUE KEY MOR	Supported	Queries must filter Post-Merge; GC requires a compaction that covers all history
AGGREGATE KEY	Unsupported	Rejected by both FE DDL and BE tablet creation
Full INSERT/load	Supported	FE copies the source time into the hidden column
MOW fixed-column partial update	Supported	Updating the source updates TTL; omitting it preserves TTL
MOW Flexible Partial Update	Supported	The Skip Bitmap keeps the source and hidden column in sync
Synchronous Rollup	Supported	The TTL hidden column is appended automatically
Horizontal/vertical Compaction	Supported	Expired rows are reclaimed when model-specific safety conditions hold
Local storage/compute-storage separation	Supported	TTL metadata is persisted in the corresponding Tablet Schema

1.2 Unsupported or Mutually Exclusive Scope

• The TTL source column cannot be a Key column.
• The source type must be DATE, DATETIME, DATEV2, DATETIMEV2, or TIMESTAMPTZ.
• Row TTL is mutually exclusive with these Sequence features:
  • function_column.sequence_col
  • function_column.sequence_type
  • sequence_mapping.*
  • ENABLE FEATURE "SEQUENCE_LOAD" on a Row TTL table
• ALTER TABLE ... ENABLE FEATURE "ROW_TTL" is unsupported for existing tables.
• TTL properties cannot be modified or disabled online.
• FOR VERSION AS OF, FOR TIME AS OF, and table@incr() are unsupported.
• A Row TTL table cannot be the Base Table of CREATE STREAM.
• Direct-expiration mode has no user-facing SQL/Redis EXPIRE command interface.

2. Table Creation and Parameters

2.1 Source-Time Mode

The recommended mode is a source-time column plus a fixed duration:

CREATE TABLE event_detail (
    id BIGINT,
    event_time DATETIMEV2(6) NULL,
    payload STRING
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "function_column.enable_row_ttl" = "true",
    "function_column.ttl_col" = "event_time",
    "function_column.ttl" = "7 day"
);

Property	Required	Meaning
function_column.enable_row_ttl	Yes	Accepts only true or false; enabling requires explicit true
function_column.ttl_col	Source-time mode only	A non-Key date/time source column
function_column.ttl	Source-time mode only	A non-negative integer fixed duration

Valid combinations:

• No properties: Row TTL is disabled.
• Only function_column.enable_row_ttl=false: Row TTL is disabled.
• The switch is true and both ttl_col and ttl are present: source-time mode.
• Only the switch is true: direct-expiration mode.

The following configurations are rejected:

• ttl_col or ttl is set without setting the switch to true.
• Only one of ttl_col and ttl is set.
• The obsolete top-level property name enable_row_ttl is used.
• The source column is missing, is a Key column, or has an unsupported type.

2.2 TTL Duration Syntax

function_column.ttl accepts only a non-negative decimal integer. A value without a unit is seconds. Units are case-insensitive, and whitespace between the integer and unit is optional.

Unit	Accepted forms	Fixed duration
Second	no unit, s, second, seconds	1 second
Minute	m, minute, minutes	60 seconds
Hour	h, hour, hours	3,600 seconds
Day	d, day, days	86,400 seconds
Week	week, weeks	604,800 seconds

Examples:

"30"        -> 30 seconds
"2 minutes" -> 120 seconds
"12h"       -> 43,200 seconds
"7 day"     -> 604,800 seconds
"2 weeks"   -> 1,209,600 seconds

Constraints:

• 0 is valid and expires a row as soon as its source time is reached.
• Negative numbers, decimals, empty strings, unknown units, and multiplication overflow are rejected.
• month, year, ms, us, and the abbreviation w are unsupported.
• All units are fixed durations; a day is always 86,400 seconds, not calendar-day addition.
• After CREATE, FE normalizes the duration to an integer seconds string; for example, 7 day may be displayed as 604800.
• BE stores the duration in microseconds, but DDL duration granularity is one second.

2.3 Direct-Expiration Mode

CREATE TABLE direct_expiration_table (
    id BIGINT,
    payload STRING
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "function_column.enable_row_ttl" = "true"
);

This mode creates a nullable BIGINT hidden column:

• NULL: never expires.
• Non-NULL: the final Unix Epoch expiration time in microseconds.

Ordinary INSERT/load has no public expiration parameter, so the hidden column defaults to NULL. BE provides primitive functions for relative/absolute time conversion and physical-row updates, but the PR does not implement command parsing, Key lookup, version resolution, or transaction commit. Ordinary Doris users should therefore prefer source-time mode.

3. Usage Examples

3.1 Seven-Day Retention with Permanent Rows

Using the event_detail table from Section 2.1:

INSERT INTO event_detail VALUES
    (1, NOW(6) - INTERVAL 8 DAY, 'expired'),
    (2, NULL, 'persistent'),
    (3, NOW(6), 'live');

SELECT id, payload
FROM event_detail
ORDER BY id;

• id=1 has expired and is invisible to ordinary queries.
• id=2 has a NULL TTL and remains visible permanently.
• id=3 remains visible until event_time + 7 day.

3.2 ttl=0 with an Absolute Expiration Column

When a business column already contains the final expiration time:

CREATE TABLE scheduled_item (
    id BIGINT,
    expire_at TIMESTAMPTZ(6) NULL,
    payload STRING
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 8
PROPERTIES (
    "replication_num" = "3",
    "function_column.enable_row_ttl" = "true",
    "function_column.ttl_col" = "expire_at",
    "function_column.ttl" = "0"
);

The row is invisible when expire_at <= query_now; expire_at=NULL means it never expires.

3.3 MOW Partial Updates

SET enable_unique_key_partial_update = true;

-- event_time is omitted: preserve the existing expiration time
INSERT INTO mow_event(id, payload) VALUES (1, 'new payload');

-- event_time is explicit: refresh this Key's expiration time
INSERT INTO mow_event(id, event_time) VALUES (1, NOW(6));

For an existing Key, omitting the source-time column preserves the previous hidden value; explicitly updating the source also updates the hidden column. For a new Key that omits the source, the source-column default is used, or NULL is written if the column is nullable and has no default.

3.4 Migrating an Existing Table

Row TTL cannot be enabled in place on an existing non-TTL table. Migrate it as follows:

1. Create a new table with the final TTL properties.
2. Validate the source type, time zone, default value, and NULL semantics.
3. Backfill historical data and catch up incremental writes through dual writes, a synchronization tool, or a controlled write pause.
4. Validate expiration boundaries and query results, then switch the application.

4. Core Data Semantics

4.1 TTL Hidden Column

FE automatically adds the following column to the Base Index and every later Rollup:

__DORIS_TTL_COL__

It is a non-Key, nullable column with default NULL. It is absent from ordinary SELECT * and cannot be dropped, renamed, or modified. It may be inspected temporarily with show_hidden_columns=true for diagnosis, but is not a stable application interface.

Mode	Hidden-column type	Stored value	Tablet duration
Source-time mode	Exactly the source type	A copy of the source time	Non-negative microseconds
Direct-expiration mode	BIGINT	Final Unix Epoch microseconds	-1, unused

The hidden column uses REPLACE for UNIQUE tables so the final winner carries its own TTL, and NONE for DUP tables.

4.2 Expiration Formula and Boundary

Source-time mode:

expiration_us = to_epoch_us(__DORIS_TTL_COL__) + row_ttl_duration_us

Direct-expiration mode:

expiration_us = __DORIS_TTL_COL__

The common visibility rule is:

TTL is NULL                  -> visible; never expires
expiration_us > now_us       -> visible
expiration_us <= now_us      -> invisible

A row is already expired when its expiration equals the current time. If time conversion or addition overflows int64, the query or Compaction returns an error instead of silently retaining or deleting the row.

4.3 Time Points and Time Zones

• Every Fragment/Scanner in a query uses the same query-level time point from TQueryGlobals; the clock is not reread for each Block.
• A Compaction obtains one row_ttl_gc_now_us when it starts and reuses it throughout that rewrite.
• DATE, DATETIME, DATEV2, and DATETIMEV2 are converted using the BE local time zone, cctz::local_time_zone(), not the query session time zone.
• TIMESTAMPTZ is treated as an absolute time with UTC semantics.
• Direct-expiration values are already Epoch microseconds and are time-zone independent.

When using a type without a time zone, every BE must use the same, stable system time zone. Prefer TIMESTAMPTZ when absolute-time semantics are required.
FE, BE, and external writers must also maintain reliable clock synchronization; clock errors cause premature or delayed expiration.

5. Core Design and Flows

5.1 Creation and Metadata Propagation

flowchart LR
    A["CREATE TABLE properties"] --> B["FE validates and normalizes duration"]
    B --> C["Append __DORIS_TTL_COL__"]
    C --> D["Persist table properties and Index Schema"]
    D --> E["CreateReplicaTask: ttl_col_idx + duration_us"]
    E --> F["Local/Cloud TabletSchemaPB"]
    D --> G["OlapTableSink: source-column metadata"]

Loading

FE validates the table model, Sequence conflicts, source column, and duration; creates the hidden column; and propagates TTL metadata through creation, Schema Change, Rollup, Backup/Restore, and replica-repair tasks. BE persists ttl_col_idx and row_ttl_duration_us in the local or Cloud Tablet Schema so rebuilding a tablet does not lose TTL semantics.

5.2 Writes and Partial Updates

A full write in source-time mode is projected as:

id, event_time, payload
        |
        +--> id, event_time, payload, event_time AS __DORIS_TTL_COL__

The implementation does not compute event_time + ttl while writing; the final expiration is calculated in queries and safe Compactions.

Partial updates follow these rules:

• Explicitly update the source: update the hidden column with it.
• Omit the source for an existing Key: restore the old row and preserve the hidden value.
• Omit the source for a new Key: use the source default or NULL.
• Flexible Partial Update: the Skip Bitmap keeps update/preserve state identical for the source and hidden column.
• In direct mode, ordinary updates preserve an existing hidden value and new Keys default to NULL.

5.3 Rollup

When a synchronous Rollup is created, FE appends __DORIS_TTL_COL__ even if the user column list does not include the source column. The write Schema Param carries the complete source-column description, so a Rollup without the source column can still process defaults for new Keys.

5.4 Query Post-Merge Filtering

FE injects the following expression above LogicalOlapScan:

row_ttl_is_visible(__DORIS_TTL_COL__, <duration_us>)

flowchart LR
    A["Read candidate rows"] --> B["UNIQUE winner / merge / delete sign"]
    B --> C["Evaluate TTL at query-global now"]
    C --> D["Project / Aggregate / Sort / Result"]

Loading

The TTL Slot cannot be an early Segment, index, or MOR value predicate. UNIQUE MOR also disables Pre-Aggregation so TTL executes after winner selection, version merging, and delete-sign handling; otherwise an expired new version could make an older version visible again.

Ordinary predicates unrelated to TTL may still be pushed down under existing safety rules. Ordinary point lookups remain correct, but Short-Circuit Point Query is disabled.

Row TTL tables also disable or bypass:

• Storage TopN and TopN Filter pushdown.
• Score TopN and vector ANN TopN pushdown.
• FE SQL Cache.
• BE Query Cache/Query Cache Normalization.

5.5 Compaction GC

Row TTL uses normal Compaction scheduling:

• It does not record the earliest/latest Rowset TTL.
• Expiration does not increase the Compaction Score.
• A fully expired Rowset is not dropped directly.
• A single expired Rowset does not automatically trigger a Full Rewrite.

Query correctness does not depend on physical GC; delayed Compaction affects only disk usage.
A TTL conversion failure or overflow fails Compaction while preserving its input Rowsets for retry through the existing mechanism.

Table model	Cumulative	Base	Full	Segment	Binlog/Cold Data
DUPLICATE KEY	GC	GC	GC	GC	No GC
UNIQUE MOW	GC	GC	GC	GC	No GC
UNIQUE MOR	No GC	GC only when input versions start at 0	GC	No GC	No GC

UNIQUE MOR deletes an expired winner only when all historical versions are covered, preventing an uncovered older version from becoming visible. READER_ALTER_TABLE does not perform TTL GC.

Horizontal Compaction builds a unified Visibility Mask after merging and delete-sign processing. Vertical Compaction places TTL in the first column group and uses RowSourcesBuffer so later Value groups skip the same rows. Deleted-row counts reuse rows_del_filtered; there is no separate rows_del_by_ttl.

6. DDL and Usage Restrictions

6.1 CREATE-only

The following operations are rejected:

ALTER TABLE event_detail ENABLE FEATURE "ROW_TTL" ...;

ALTER TABLE event_detail SET (
    "function_column.ttl" = "14 day"
);

ALTER TABLE event_detail SET (
    "function_column.enable_row_ttl" = "false"
);

A Row TTL table does not permit the source column or __DORIS_TTL_COL__ to be dropped, renamed, or modified, and it does not permit adding a Sequence Column, Sequence Type, or Sequence Mapping. Schema Change on other ordinary columns is allowed and preserves TTL metadata.

6.2 Query and Optimization Restrictions

• Historical snapshot reads and table@incr() incremental reads are rejected.
• A Row TTL table cannot be the Base Table of a Table Stream.
• TTL must be evaluated Post-Merge and cannot be filtered early through a Segment predicate or index.
• Point lookup, TopN, ANN, and cache paths may fall back to more expensive but semantically correct execution.
• Disk reclamation depends on normal Compaction and has no reclamation SLA.

6.3 Version Requirements

Row TTL depends on a new hidden column, Scalar Function, Thrift fields, Tablet Schema fields, and Compaction behavior. Do not create or use Row TTL tables until every FE and BE has been upgraded to a supporting version.

The TTL hidden column in Index Schema is the authoritative marker for identifying a TTL table. FE can use it to restore the switch from an older Catalog; BE can convert an older branch's seconds-duration field to microseconds, while new code writes only the microseconds field, and retired field numbers must not be reused. These compatibility reads do not permit arbitrary mixed-version operation. Before downgrade, verify that the target version can recognize Row TTL tables.

Use case

No response

Related issues

#61056
#65858

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