CDB: Temporal Tables or 'Time Travel'

I’m interested in temporal tables which are SQL:11 standard, and exist in MS and Oracle.

I’ve seen both:


https://www.cockroachlabs.com/docs/stable/as-of-system-time.html

I haven’t found other resources and in particular I’m curious about the impact of setting the GC configurable date for a versioned table to a long period of time. What’s the impact on performance/space/other?

Does anyone have additional reading for the above topic and/or are there other bits that need to be considered in the CDB world of temporal tables vs something like SQL server? (https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017)

Increasing the GC time-to-live is not recommended at this time. There is a per-row size limit of 64MiB, and all historical versions of a row count towards this limit. Therefore it is only safe to increase the amount of history that is retained if your tables change so infrequently that you can be confident that the size will stay under 64MiB.

Additionally, this historical data is not currently preserved when restoring from a backup, so if your application requires the ability to see historical changes, you need to model this explicitly in your schema. (Both of these limitations may be lifted in the future)

By making the timestamp data explicit in the schema, you can also use our partitioning (enterprise-edition) feature to move historical data to separate storage, mitigating the cost of retaining it.

CockroachDB’s time-travel queries currently implement a subset of the SQL Server temporal tables feature (for example, SQL Server can query for records that changed any time within a range of timestamps; we only support point lookups with AS OF SYSTEM TIME).