Managing time travel space usage

Historical rows in temporal tables take up space on your NPSaaS disks over and above that which is necessary for current rows. The amount of this extra space, which might be significant, depends on the following factors:

• The number of days in the retention time interval.
• The rate of change (rows or bytes deleted and updated) of data in the table. The size of the current data in and of itself is not a factor if the rate of change is measured in absolute, not relative, terms. For example, 40% of a larger table means more rows or bytes than 40% of a smaller table.

A longer retention time interval might be suitable for a slowly changing dimension table, but not for a table where the number of rows or bytes that are deleted or updated per day is large.

For historical rows that are no longer visible to time travel queries, you can reclaim the space that is taken by these rows with the GROOM TABLE RECORDS ALL command. In some cases, not all rows can be reclaimed. For example, if the rows are also needed to support incremental backup. You can also schedule automatic grooming with AutoMaint in the web console.

If you altered the retention time interval for a temporal table and the table had historical rows that had been retained based on the earlier retention time interval (before you changed the retention time), you can groom the table to reclaim space.

Run the SHOW TEMPORAL HISTOGRAM command or by use the web console to view the space that is used by deleted rows per day or per range of days into the past to estimate the space that might be reclaimed.

SHOW TEMPORAL HISTOGRAM <TEMPORAL TABLE> [DAYSPERROW <number of days>]

show temporal histogram flight;
        BASE_TIMESTAMP      | STARTDAYSAGO | ENDDAYSAGO | DELETEDROWS | MBYTESUSED | USAGEPERCENT
----------------------------+--------------+------------+-------------+------------+--------------
 2022-11-08 15:35:59.442782 |            1 |          0 |         503 |    144.230 |        19.23
 2022-11-08 15:35:59.442782 |            2 |          1 |           0 |          0 |            0
 2022-11-08 15:35:59.442782 |            3 |          2 |         451 |    110.294 |        14.71
 2022-11-08 15:35:59.442782 |            4 |          3 |         913 |    218.529 |        29.14
 2022-11-08 15:35:59.442782 |            5 |          4 |           0 |          0 |            0
 2022-11-08 15:35:59.442782 |            6 |          5 |         389 |     88.235 |        11.76