support history tables like DB2

[zz-jason]

Feature Request

Is your feature request related to a problem? Please describe:

Background: in some application scenarios, there is a need to keep the history data (for example, within a month) of a table.

In TiDB, we can keep the history data by setting the value of tikv_gc_life_time in mysql.tidb table. For example, the following statement makes the MVCC GC keep history data for the most recent 30 days:

update mysql.tidb set VARIABLE_VALUE="30d" where VARIABLE_NAME="tikv_gc_life_time";

But keeping lots of MVCC keys can enlarge the query time because there are lots of old MVCC keys that need to be scanned and skipped.

Describe the feature you'd like:

1. Maybe a table-level MVCC GC strategy is needed, see https://github.com/pingcap/tidb/issues/18117 for details.
2. Changing the MVCC GC behavior to this: insert these old MVCC keys to a related history table, erase the old MVCC keys from the original table. So that we can gain both history view of that table and remain low query latency on the original table after MVCC GC.

References:

• DB2: Histogry Tables

Describe alternatives you've considered:

N/A, suggestions are welcome.

Teachability, Documentation, Adoption, Migration Strategy:

N/A

[scsldb]

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

[dveeden]

This is a bit similar to the TTL feature, but is used to set a minimum time and not a maximum time, if I understand this issue correctly?