*: Support 'TTL'(like the one in HBase) in TiDB

[tianyu4552]

我们现在遇到一个问题，我们打算建一个数据基础维表，数据会被周更。
但是周更的过程不能保证100%全量覆盖，但不希望旧数据被命中出来，这样上层使用方需要做额外处理。
能否像HBase的TTL机制那样，有过期淘汰策略。
可以接受以rowId为主键，对整条数据过过期处理

Now we have a problem. We plan to build a basic dimension table of data, and the data will be updated weekly.

However, the weekly shift process can not guarantee 100% full coverage, but the old data is not expected to be hit, so the upper users need to do additional processing.

Can there be a strategy of expiration and elimination like the TTL mechanism of HBase.

It is acceptable to use rowId as the primary key to process the whole data after expiration

[bb7133]

Hi @tianyu4552 , thanks for reporting this request!

Would you mind modifying the title to English, please? Generally, it would be better if everyone is able to understand it.

[bb7133]

Hi @tianyu4552 I've translated the title in English, hope that it can be accepted, thank you!

[ghost]

MongoDB also has something called capped collections.

I think an sql-like way to do this would be with partitioning + truncate partition. We could look at the syntax of interval partitioning (oracle feature) and add syntax to retain only the last n partitions.

[zz-jason]

read from the old partition, update it, write it to the new partition, truncate the old partition periodically. @nullnotnil Am I correct?

[ghost]

@zz-jason To be TTL based there would need to be a partition on modified_date. An update to an old partition would auto-move it to the newest partition. It would need some design work, but I like the idea of ttl based tables in principal. It solves a pain point of users that fail trying to do this themselves (with delete statements).

[zz-jason]

here is the reference from HBase Time To Live (TTL):

ColumnFamilies can set a TTL length in seconds, and HBase will automatically delete rows once the expiration time is reached. This applies to all versions of a row - even the current one. The TTL time encoded in the HBase for the row is specified in UTC.

[ghost]

See also: https://www.percona.com/blog/2020/08/04/the-road-story-of-a-myrocks-mariadb-migration/

This describes the use of a TTL in the SHOW CREATE TABLE statement.

[tianyu4552]

I feel that we can declare the TTL mechanism which is work on row or partition by CREATE TABLE statement, and it should be automated.

[scsldb]

We need to investigate the user scenario in depth, and then make a decision after the research is completed.

[ghost]

See https://brandur.org/fragments/ttl-indexes and https://docs.mongodb.com/manual/core/index-ttl/ for another implementation type. This makes it an index property rather than a table property.

[SunRunAway]

TTL feature is released with TiDB v6.5: https://docs.pingcap.com/tidb/stable/time-to-live#periodically-delete-expired-data-using-ttl-time-to-live

tracking issue: #39262