Get information about compression-related settings for hypertables. Each row of the view provides information about individual orderby and segmentby columns used by compression.

How you use segmentby is the single most important thing for compression. It affects compresion rates, query performance, and what is compressed or decompressed by mutable compression.

NameTypeDescription
hypertable_schemaTEXTSchema name of the hypertable
hypertable_nameTEXTTable name of the hypertable
attnameTEXTName of the column used in the compression settings
segmentby_column_indexSMALLINTPosition of attname in the compress_segmentby list
orderby_column_indexSMALLINTPosition of attname in the compress_orderby list
orderby_ascBOOLEANTrue if this is used for order by ASC, False for order by DESC
orderby_nullsfirstBOOLEANTrue if nulls are ordered first for this column, False if nulls are ordered last
CREATE TABLE hypertab (a_col integer, b_col integer, c_col integer, d_col integer, e_col integer);
SELECT table_name FROM create_hypertable('hypertab', by_range('a_col', 864000000));
ALTER TABLE hypertab SET (timescaledb.compress, timescaledb.compress_segmentby = 'a_col,b_col',
timescaledb.compress_orderby = 'c_col desc, d_col asc nulls last');
SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = 'hypertab';
-[ RECORD 1 ]----------+---------
hypertable_schema | public
hypertable_name | hypertab
attname | a_col
segmentby_column_index | 1
orderby_column_index |
orderby_asc |
orderby_nullsfirst |
-[ RECORD 2 ]----------+---------
hypertable_schema | public
hypertable_name | hypertab
attname | b_col
segmentby_column_index | 2
orderby_column_index |
orderby_asc |
orderby_nullsfirst |
-[ RECORD 3 ]----------+---------
hypertable_schema | public
hypertable_name | hypertab
attname | c_col
segmentby_column_index |
orderby_column_index | 1
orderby_asc | f
orderby_nullsfirst | t
-[ RECORD 4 ]----------+---------
hypertable_schema | public
hypertable_name | hypertab
attname | d_col
segmentby_column_index |
orderby_column_index | 2
orderby_asc | t
orderby_nullsfirst | f
Note

The by_range dimension builder is an addition to TimescaleDB 2.13.

Keywords

Found an issue on this page?

Report an issue!