On Data Compression in TimescaleDB

Environment

PostgreSQL 13 TimescaleDB 2.4.2

Enabling compression on the target table

First, we enable compression on the target table that's been converted into a hypertable. As a quick note, "hypertable-ized" means converting a PostgreSQL table into the hypertable format so it can be treated as a time-series table. Compression is enabled with ALTER TABLE.

ALTER TABLE iot_data SET (timescaledb.compress,timescaledb.compress_segmentby = 'sensor_id');
> Query 1 OK: ALTER TABLE

Manual compression

Narrowing down which chunks to compress

Next, we use show_chunks() to narrow down the chunks to compress. I won't go into detail about chunks, but think of them as boxes that hold time-series data, with multiple chunks existing per hypertable. Running the query below returns the chunk names, so make a note of them.

-- get the names of chunks containing data older than 14 days
SELECT show_chunks('iot_data', older_than => INTERVAL '14 days');
> _timescaledb_internal._hyper_19_210_chunk
> _timescaledb_internal._hyper_19_211_chunk
> ...
> ...

Running the compression query

Using the chunk names noted in the previous step, running the query below performs the compression. If you're compressing a chunk that already has a fair amount of data stored in it, this can take a while, so be patient. You can check whether compression has completed with chunk_compression_stats().

-- query that performs the compression
SELECT compress_chunk('_timescaledb_internal._hyper_19_210_chunk');

-- query to check whether a chunk has been compressed
SELECT * FROM chunk_compression_stats('iot_data')

Automating compression with a policy

There's also a feature that lets you define a policy to automatically compress data once a certain amount of time has passed.

-- register a policy to automatically compress data older than 14 days
SELECT add_compression_policy('iot_data', INTERVAL '14 days');

-- remove a configured compression policy
SELECT remove_compression_policy('iot_data');

What changes with compression

Benefits

• Saves disk space You can check table-level compression info with hypertable_compression_stats(). In our own data, we confirmed a disk usage change from 73.1GB → 1.93GB.
• Faster searches? According to the documentation, some queries can see improved speed, which we were hoping for, but in the queries we tested locally, we didn't see any benefit — no improvement, but no degradation either. We'd like to know what kind of query actually benefits from this.

Drawbacks

• Updates and deletes are difficult Once compressed, you can't run updates or deletes; to update, you'd need to decompress at the chunk level, which is quite costly. When we tested an update query, it failed with cannot update/delete rows from chunk "_hyper_19_210_chunk" as it is compressed.

  Also, the documentation says inserts are only partially supported, and when we tested it, the query failed with insert into a compressed chunk that has primary or unique constraint is not supported. In this version, it seems you can't even insert into a table that has a primary key or unique key constraint.

  We're hoping this gets supported in a future version.

References

Compression https://docs.timescale.com/api/latest/compression/ Hypertable & Chunks https://docs.timescale.com/api/latest/hypertable/