Help Center/ GaussDB(DWS)/ Best Practices/ Data Development/ Best Practice of Converting a Time Series Table to an HStore Table
Updated on 2025-07-30 GMT+08:00
View PDF
Share

Best Practice of Converting a Time Series Table to an HStore Table

The new HStore table outperforms the time series table in terms of import speed, compression ratio, and query performance. It is recommended to replace the time series table with the HStore table.

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.

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='1 hour', 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='1 hour', 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')
    PARTITION p2 VALUES LESS THAN('2023-02-14')
	...
);
--Create one or more B-tree indexes on other TSTag columns based on query performance.
Parent Topic: Data Development