Planning for data storage

Time series data is stored in containers within dbspaces. You can use the default containers that are created in the same dbspace as the table into which you are loading data or you can create containers in separate dbspaces. You can estimate how much storage space you need. Rolling window containers have specific storage requirements.

If you are loading high volumes of data, you can improve the performance of loading the data if you use multiple dbspaces. Similarly, if you have multiple TimeSeries columns in the same table, consider creating multiple containers that store data in different dbspaces.

Estimate the amount of storage space you need by using the following formulas:

Regular and irregular time series:

space in bytes = [primary_key + index_entry 
+ (timestamp + ts_columns * elements)
 + 4 * elements] * (table_rows) + B-tree_size

Hertz time series:

space in bytes = [primary_key + index_entry + (timestamp + ts_columns * records)
 + (4 + 11) * elements] * (table_rows) + B-tree_size

Compressed time series:

space in bytes = [primary_key + index_entry 
+ (timestamp + ts_columns * records)
 + (4 + 11) * elements] * (table_rows) + B-tree_size

B-tree_size: The size of the B-tree index, not including the index entries. Typically, the B-tree index is approximately 2% of the size of the data for a regular time series and is approximately 4% of the size of the data for an irregular time series.
elements: The number of elements of time series data in each row. For example, the ts_data table in stores_demo database has 8640 elements for each of the 28 rows.; For hertz time series, each element represents one second of data.; For compressed time series, each element represents approximately 4 KB of data.
index_entry: The size of an index entry, which is approximately the size of the primary key columns plus 4 bytes.
primary_key: The size of the data types of the primary key columns and other non-time series columns in the time series table.
records: For hertz and compressed time series, the number of records.
table_rows: The number of rows in the time series table.
ts_columns: The size of the data types of the columns in the TimeSeries data type, except the timestamp column. The CHAR data type requires an additional 4 bytes when it is included in a TimeSeries data type.
timestamp: The size of the timestamp per element:; Regular time series = 0; Irregular time series = 11 bytes; The size of the timestamp per record:; Hertz time series = 1 byte; Compressed time series = 2 bytes

The 4 bytes per element is a slot entry.

For hertz and compressed time series, each element has an 11-byte timestamp in addition to the 1-byte or 2-byte timestamp, respectively, for each record.

The equation is a guideline. The amount of required space can be affected by other factors, such as the small amount of space that is needed for the slot table and the null bitmap for each element. The equation might underestimate the amount of required space if the row size of your time series data size is small. The maximum number of elements that are allowed on a data page is 254. If the row size of your time series data is small, the page might contain the maximum number of elements but have unused space, especially if you are not using a 2 KB page size.

Rolling window container storage requirements

Rolling window containers allow you to limit the amount of current data to a specific time range.

Rolling window containers have two different types of partitions with different storage requirements: the container partition and the window partitions. The container partition contains information about the rolling window intervals and partitions. The window partitions store time series elements. The container partition typically requires much less space than the window partitions. To avoid allocating unnecessary space for the container partition, store the container partition and the window partitions in different dbspaces that have different extent sizes.

A rolling window container has one container partition. Use the following formula to estimate the size of the container partition:

Space = (container_name_length + dbspace_name_length + 48) * (active_windowsize + dormant_windowsize) * 2

active_windowsize: The maximum number of partitions in the active window. If you do not intend to set a limit to the number of partitions, estimate the maximum number of partitions you expect.
container_name_length: The length of the container name, in bytes.
dbspace_name_length: The length of the dbspace name for the container partition, in bytes.
dormant_windowsize: The maximum number of partitions in the dormant window. If you do not intend to set a limit to the number of partitions, estimate the maximum number of partitions you expect.

A rolling window container has multiple window partitions. You can allocate multiple dbspaces for window partitions. Use the following formula to estimate the number of partitions in each dbspace:

Approximate number of partitions in each dbspace = CEIL((active_windowsize + dormant_windowsize) / number_dbspaces) + 1

active_windowsize: The maximum number of partitions in the active window. If you do not intend to set a limit to the number of partitions, estimate the maximum number of partitions you expect.
dormant_windowsize: The maximum number of partitions in the dormant window. If you do not intend to set a limit to the number of partitions, estimate the maximum number of partitions you expect.
number_dbspaces: The number of dbspaces that are allocated for the window partitions.