Old API since TimescaleDB v2.18.0 Replaced by ALTER TABLE (Hypercore).

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

By itself, this ALTER statement alone does not compress a hypertable. To do so, either create a compression policy using the add_compression_policy function or manually compress a specific hypertable chunk using the compress_chunk function.

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 or 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.enable_segmentwise_recompressionTEXTSet to OFF to disable segmentwise recompression on compressed chunks. This can be beneficial for some user workloads where segmentwise recompression is slow, and full recompression is more performant. The default is ON.
timescaledb.compress_chunk_time_intervalTEXTEXPERIMENTAL: Set compressed chunk time interval used to roll chunks into. This parameter compresses every chunk, and then irreversibly merges it into a previous adjacent chunk if possible, to reduce the total number of chunks in the hypertable. Note that chunks will not be split up during decompression. It should be set to a multiple 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 or segment by
intervalTEXTTime interval used to roll compressed chunks into

Configure a hypertable that ingests device data to use compression. Here, if the hypertable is often queried about a specific device or set of devices, the compression should be segmented using the device_id for greater performance.

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 or Edit this page in GitHub.