Data Compression

Uqbar supports automatic and manual data compression to reduce data storage costs and improve search efficiency.

Automatic Data Compression

The backend automatically performs compression of the time-series data according to the set compression delay time to save storage space. The time delay for compression can be set with the guc parameter uqbar.timeseries_compression_delay.

Syntax Format

Auto-compression is automatically triggered based on a set delay and does not require user execution.

Precautions

• Compression is performed at the chunkgroup granularity, i.e. all chunks within a chunkgroup are compressed together.
• After the set compression interval is reached, data compression also requires the following conditions to be met:
  • The end time of the chunkgroup is earlier than the current system time
  • No data is written in the chunkgroup for more than uqbar.timeseries_compression_delay interval
  • After successful compression, chunkgroup does not support re-writing data in the current version

Examples

If chunkGroupDuration is set to one week, and the current time is 2022-7-7 09:55:00.

1. Set the auto-compression delay to 1 min, i.e. uqbar.timeseries_compression_delay = 60;

2. Write data to ensure that all data within the slice is less than the current time;

   INSERT INTO weather SELECT '2022-07-01 00:00:00'::timestamp, 'beijing', 'park', generate_series(1, 2000); 

3. Check the chunk attribute and find the built chunks;

   SELECT * FROM timeseries_catalog.tschunk;

4. After two minutes, check the chunk attribute again and verify whether they are compressed.

Manual Data Compression

Uqbar supports manual user-triggered compression. Users can compress the time-series table or a chunkgroup within the time-series table by executing the compression command.

Syntax Format

COMPRESS TIMESERIES table_name [ PARTITION chunkgroupname ]

Precautions

• The current version does not support re-writing data to the compressed chunkgroup
• Not specifying the Partition parameter means that compression is performed on all chunkgroups of the time-series table; specifying Partition will perform compression on the specified chunkgroup

Examples

Uqbar=# COMPRESS TIMESERIES weather PARTITION chunkgroup_1;
COMPRESSED
Uqbar=# COMPRESS TIMESERIES weather;
COMPRESSED

Compression Ratio Views

The system has two built-in views, timeseries_views.compression_table and timeseries_views.compression_chunkgroup, which can display data such as data volume and compression rate before and after compression by table and chunkgroup dimensions. For more information about these two views, refer to timeseries_views.compression_table and timeseries_views.compression_chunkgroup.

Syntax Format

SELECT * FROM timeseries_views.compression_table;
SELECT * FROM timeseries_views.compression_chunkgroup;

Examples

--Query the compression ratio of the weather time-series table. 
Uqbar=# SELECT tablename, before_compression_size, after_compression_size, compression_rate FROM timeseries_views.compression_table WHERE tablename = 'weather';
 tablename | before_compression_size | after_compression_size | compression_ratio 
------------------------+-------------------------+--------------------+------------
 weather   |  1746534                |  1483457               |  1.17

(1 rows)

--Query the chunk group compression ratio of the weather time-series table. 
Uqbar=# SELECT tablename, chunkgroupname, before_compression_size, after_compression_size, compression_ratio FROM timeseries_views.compression_chunkgroup WHERE tablename = 'weather';
 tablename | chunkgroupname | before_compression_size | after_compression_size | compression_ratio 
--------------------+--------------------+---------------- --------+---------------------+------------
 weather   |  p_1_1         | 387564                  |  328576                |  1.17
 weather   |  p_1_2         | 417829                  |  358273                |  1.16

(2 rows)