[PHOENIX-7170] Conditional TTL

• Type: New Feature
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Tanuj Khurana
• Reporter: Kadir Ozdemir
• Created: 08/Jan/24 07:43
• Updated: 14/Jun/24 18:47

Description

Deleting rows using delete markers require running delete queries to insert them, one for each row to be deleted. Often applications need to run periodic jobs to issue delete queries to insert delete markers. Deleting rows using TTL is more performance optimized compared to adding delete markers in Phoenix since TTL works without inserting delete markers. Phoenix currently supports table and view (level) TTL. It is desirable to have a conditional TTL feature to extend the TTL future to expire a subset of rows of a table or updatable view using a different TTL value.

A condition TTL can be set using a CASE statement in CREATE and ALTER statements by adding TTL=<the case statement>. For example,
TTL = CASE WHEN ID IS BETWEEN 1 AND 100 THEN <10 days> WHEN ID IS BETWEEN 101 AND 200 <7 days> ELSE <5 days> END

The compaction scanner (CompactionScanner) in Phoenix can evaluate the case statement on a row and decide if the row should be removed. Similarly, on the read path TTLRegionScanner can mask the rows using the case statement. The TTL case statement can be stored in SYSCAT in header rows.

Issue Links

• Blocked: PHOENIX-7329 Change TTL column type to VARCHAR in syscat

Sub-Tasks

• Unify in-memory representation of Conditional TTL expressions and Literal TTL values: Open