Create a compression policy

Compress your data automatically, based on the conditions you set

Old API since 2.18.0. Superseded by Optimize your data for real-time analytics. However, compression APIs are still supported, you do not need to migrate to the hypercore APIs.

You can enable compression on individual s, by declaring which column you want to segment by.

Enable a compression policy

This page uses an example table, called example, and segments it by the device_id column. Every chunk that is more than seven days old is then marked to be automatically compressed. The source data is organized like this:

time	device_id	cpu	disk_io	energy_consumption
8/22/2019 0:00	1	88.2	20	0.8
8/22/2019 0:05	2	300.5	30	0.9

1. Alter the table

   At the psql prompt, alter the table:

   ALTER TABLE example SET (
     timescaledb.compress,
     timescaledb.compress_segmentby = 'device_id'
   );
   

2. Add a compression policy

   Add a compression policy to compress chunks that are older than seven days:

   SELECT add_compression_policy('example', INTERVAL '7 days');
   

For more information, see the API reference for ALTER TABLE (compression) and add_compression_policy.

View current compression policy

To view the compression policy that you’ve set:

SELECT * FROM timescaledb_information.jobs
  WHERE proc_name='policy_compression';

For more information, see the API reference for timescaledb_information.jobs.

Pause compression policy

To disable a compression policy temporarily, find the corresponding job ID and then call alter_job to pause it:

SELECT * FROM timescaledb_information.jobs where proc_name = 'policy_compression' AND relname = 'example'

SELECT alter_job(<job_id>, scheduled => false);

To enable it again:

SELECT alter_job(<job_id>, scheduled => true);

Remove compression policy

To remove a compression policy, use remove_compression_policy:

SELECT remove_compression_policy('example');

For more information, see the API reference for remove_compression_policy.

Disable compression

You can disable compression entirely on individual s. This command works only if you don’t currently have any compressed chunks:

ALTER TABLE <EXAMPLE> SET (timescaledb.compress=false);

If your contains compressed chunks, you need to decompress each chunk individually before you can turn off compression.