15.10 - Capacity Planning for Temporal Tables - Teradata Database

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

Temporal tables typically contain more rows than otherwise equivalent nontemporal tables. This is due to the way rows are automatically added to temporal tables as a result of most kinds of modifications. Furthermore, rows are never physically deleted from system-time or bitemporal tables, but persist in the tables indefinitely.

For these reasons, temporal tables grow faster than nontemporal tables, at a rate that depends on how frequently they are modified, and on the nature of those modifications.

The following tables show how temporal tables that have a system-time dimension can grow depending on the nature and frequency of table modifications. Valid-time tables are likely to experience less growth, because rows in valid-time tables can be physically deleted from the tables.

Use these examples to estimate annual growth of temporal tables for capacity planning.

Table and Row Size Calculation forms are available in Database Design for nontemporal tables.

Note:  

  • Sequenced modifications are typically historical in nature.
  • To reflect conservative estimates, the table size increase calculation is based on the maximum number of rows that can be produced by each type of modification.
  •  

     

    System-time Table

    Bitemporal Table

    Table Size Before Modifications (rows)

    100

    100

    Modification Type

    Current (open rows)

    FOR PORTION OF

    Modifications per Year (percent of rows)

    10% (0.19% weekly)

    10% (0.19% weekly)

    Number of Additional Rows Produced per Modification

    1

    1, 2, or 3

    Largest Table Size After Modifications (rows)

    110

    130

    Annual Increase in Table Size

    10%

    30%

     

     

    System-time Table

    Bitemporal Table

    Table Size Before Modifications (rows)

    100

    100

    Modification Type

    Current

    FOR PORTION OF

    Modifications per Year (percent of rows)

    30% (0.58% weekly)

    30% (0.58% weekly)

    Number of Additional Rows Produced per Modification

    1

    1, 2, or 3

    Largest Table Size After Modifications (rows)

    130

    190

    Annual Increase in Table Size

    30%

    90%

     

     

    System-time Table

    Bitemporal Table

    Table Size Before Modifications (rows)

    100

    100

    Modification Type

    Current

    FOR PORTION OF

    Modifications per Year (percent of rows)

    50% (0.96% weekly)

    50% (0.96% weekly)

    Number of Additional Rows Produced per Modification

    1

    1, 2, or 3

    Largest Table Size After Modifications (rows)

    150

    250

    Annual Increase in Table Size

    50%

    150%