[jira] [Updated] (PHOENIX-7170) Phoenix Row TTL

["Kadir Ozdemir (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-7170?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kadir Ozdemir updated PHOENIX-7170:
-----------------------------------
    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 row level TTL feature to extend the TTL future to delete a subset of rows of a table or updatable view.

A row-level-TTL can be set using a CASE statement in CREATE and ALTER statements by adding TTL=<the case statement>. For example, ROW_TTL = . As for partial indexes, the where clause should be evaluable on a single row.

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

  was:
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 row level TTL feature to extend the TTL future to delete a subset of rows of a table or updatable view.

As in partial indexes, a row level TTL can be set using a where clause. This clause can be set using CREATE and ALTER statements by adding ROW_TTL=(<where clause>). For example, ROW_TTL = (WHERE CURRENT_TIME() - LAST_UPDATE > 8640000 AND ID IS BETWEEN 1 AND 100) where LAST_UPDATE and ID are the columns of the table (or updatable view). As for partial indexes, the where clause should be evaluable on a single row.

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


> Phoenix Row TTL
> ---------------
>
>                 Key: PHOENIX-7170
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-7170
>             Project: Phoenix
>          Issue Type: New Feature
>            Reporter: Kadir Ozdemir
>            Priority: Major
>
> 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 row level TTL feature to extend the TTL future to delete a subset of rows of a table or updatable view.
> A row-level-TTL can be set using a CASE statement in CREATE and ALTER statements by adding TTL=<the case statement>. For example, ROW_TTL = . As for partial indexes, the where clause should be evaluable on a single row.
> The compaction scanner (CompactionScanner) in Phoenix can evaluate a row-TTL-where clause on a row and decide if the row should be deleted. Similarly, on the read path TTLRegionScanner can mask the deleted rows using row-TTL-where clauses. The row-TTL-where clauses can be stored in SYSCAT in header rows.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)