Converting a Time Series Table to an HStore Table

The column-store HStore tables outperform time series tables in terms of data import performance, compression ratio, and query performance. This section describes how to convert existing time series tables to HStore tables.

For details about how to use the HStore tables, see Using Hybrid Data Warehouses and HStore Tables.

A time series table consists of three columns: TSTime, TSTag, and TSField.

• TSTime represents the time sequence, with automatic partitioning based on this sequence.
• TSTag serves as the dimension column.
• TSField represents a fact column where dimension and fact data coexist.

For example, create a time series table CPU.

1
2
3
4
5
6
7
8

CREATE TABLE CPU(
scope_name text TSTag,
server_ip text TSTag,
group_path text TSTag,
time timestamptz TSTime,
idle numeric TSField,
users numeric TSField) 
with (TTL='30 days', PERIOD = '1 hour', orientation=TIMESERIES);

To convert a time series table to an HStore table, create the HStore table, establish level-2 partitions, and implement bitmap indexes for optimal HStore table performance.

• Use the TSTime column as the partition key.
• Use the first TSTag column as the secondary partition column.
• Use all TSTag columns as bitmap index columns.

The table creation statements post-reconstruction are as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE CPU(
scope_name text,
server_ip text,
group_path text,
time timestamptz,
idle numeric,
users numeric) 
WITH(TTL='30 days', PERIOD = '1 hour', 
ORIENTATION=column, ENABLE_HSTORE_OPT=true, --Use the column-store hstore_opt type.
SECONDARY_PART_COLUMN='scope_name', --Use the first TSTag column as the secondary partition column.
BITMAP_COLUMNS='scope_name,server_ip,group_path' --Use all TSTag columns as bitmap index columns.
)
PARTITION BY RANGE(time) --time is the TSTime column in TIMESERIES.
(
    PARTITION p1 VALUES LESS THAN('2023-02-13 12:00:00'),
    PARTITION p2 VALUES LESS THAN('2023-02-13 13:00:00')
	...
);
--Create one or more B-tree indexes on other TSTag columns based on query performance.