'ALTER TABLE' statement is used to turn on compression and set compression options.

The syntax is:

ALTER TABLE <table_name> SET (timescaledb.compress, timescaledb.compress_orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
timescaledb.compress_segmentby = '<column_name> [, ...]', timescaledb.compress_chunk_time_interval='interval'
);
NameTypeDescription
timescaledb.compressBOOLEANEnable/Disable compression
NameTypeDescription
timescaledb.compress_orderbyTEXTOrder used by compression, specified in the same way as the ORDER BY clause in a SELECT query. The default is the descending order of the hypertable's time column.
timescaledb.compress_segmentbyTEXTColumn list on which to key the compressed segments. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate. The default is no segment by columns.
timescaledb.compress_chunk_time_intervalTEXTEXPERIMENTAL: Set compressed chunk time interval used to roll compressed chunks into. This parameter compresses every chunk, and then merges it into a previous adjacent chunk if possible, to reduce the total number of chunks in the hypertable. It should be set to a factor of the current chunk interval. This option can be changed independently of other compression settings and does not require the timescaledb.compress argument.
NameTypeDescription
table_nameTEXTHypertable that supports compression
column_nameTEXTColumn used to order by and/or segment by
intervalTEXTTime interval used to roll compressed chunks into

Configure a hypertable that ingests device data to use compression.

ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC', timescaledb.compress_segmentby = 'device_id');

You can also specify compressed chunk interval without changing other compression settings:

ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '24 hours');

To disable the previously set option, set the interval to 0:

ALTER TABLE metrics SET (timescaledb.compress_chunk_time_interval = '0');

Keywords

Found an issue on this page?

Report an issue!