Enable data compression and retention in TimescaleDB

Over time, time-series data can consume a large amount of storage. To address this, there are three approaches:

1. Compression. This means reducing the required disk space by applying mathematical algorithms. This is lossless, so no data will be deleted. It will just take a bit more time to retrieve it. For more information on TimescaleDB compression, see About compression in their documentation.
2. Retention. Retention policies enable you to manage how long data is stored in your TimescaleDB database. This can be beneficial for managing the size of your database, as well as for adhering to data retention regulations. Data loss may occur, but it can be acceptable if data only needs to be kept for a short period of time.
3. Downsampling. Applying mathematical functions like an average to the data and calculating it for a different time interval is a way to process the data. Additionally, deleting old data points (see also retention) can lead to data loss. When exact data is not required, but only a higher-level view of it, downsampling can be used. For example, energy consumption is probably not useful to have 20 times per second, so it could be downsampled to 1-minute intervals instead.

This article will only cover the first two points. For more information on downsampling, please refer to the TimescaleDB documentation.

Instructions

To enable compression and retention, you will need to execute SQL commands. If you do not know how to do it, check out

Executing SQL commands in the United Manufacturing Hub?

Retention

To automatically remove data that is older than 7 days for all process values (retention), execute the following command:

SELECT add_retention_policy('processvaluetable', INTERVAL '7 days');

Compression

To automatically compress data that is older than 7 days for all process values (compression), execute the following command:

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