Reduce database size

Over time, time-series data can consume a large amount of disk space. To reduce the amount of disk space used by time-series data, there are three options:

• Enable data compression. This reduces the required disk space by applying mathematical compression to the data. This compression is lossless, so the data is not changed in any way. However, it will take more time to compress and decompress the data. For more information, see how TimescaleDB compression works.
• Enable data retention. This deletes old data that is no longer needed, by setting policies that automatically delete data older than a specified time. This can be beneficial for managing the size of the database, as well as adhering to data retention regulations. However, by definition, data loss will occur. For more information, see how TimescaleDB data retention works.
• Downsampling. This is a method of reducing the amount of data stored by aggregating data points over a period of time. For example, you can aggregate data points over a 30-minute period, instead of storing each data point. If exact data is not required, downsampling can be useful to reduce database size. However, data may be less accurate.

Before you begin

You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.

Open the database shell

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

4. Connect to the database:

   \c factoryinsight
   

Enable data compression

To enable data compression, you need to execute the following SQL command from the database shell:

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

This command will set a retention policy on the processvaluetable table, which will delete data older than 7 days.

Enable data retention

To enable data retention, you need to execute the following SQL command from the database shell:

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

This command will set a compression policy on the processvaluetable table, which will compress data older than 7 days.

What’s next