CODEWITHSUNDEEP
postgresqlcompressiontimescaledb
Sassa
Sassa

Reputation: 2013

TimescaleDB compress chunks during after first insertion of older data

I have a TimescaleDB hypertable that breaks in chunks by every 1-month interval based in my timestamp column.

Does TimescaleDB use the same timestamp column to define when a chunk has to be compressed?

If so, how is the best method to handle the following issue:

I want to populate this table starting from an older time, let's say, 2017.

When I start my software to do these insertions, after it inserts the first row (with a timestamp from 2017) it will compress the table and then fail to do the next insertion in the same chunk.

Is there a way to avoid that? can I momentarily disable compression for all chunks and then reenable it after I'm done?

Or can I someway force the insertion making TimescaleDB do the decompression, insertion, and recompression after that transparently?

Also, can I change the behavior of the compression interval to be based when the line was inserted and not by my timestamp (that way even if I add data from 2017 now, it will only compress it after one month from today)?

Upvotes: 3

Views: 3029

Answers (1)

TmTron
TmTron

Reputation: 19411

Does TimescaleDB use the same timestamp column to define when a chunk has to be compressed?

It will always use the PrimaryKey time-column. You can specify the interval.
When you have additional columns for the primary key (other than time, e.g. device_id in the example below), you can use the segmentedby option: see this example from the docs

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

SELECT add_compress_chunks_policy('measurements', INTERVAL '7 days');

If so, how is the best method to handle the following issue: I want to populate this table starting from an older time, let's say, 2017. When I start my software to do these insertions, after it inserts the first row (with a timestamp from 2017) it will compress the table and then fail to do the next insertion in the same chunk. Is there a way to avoid that? can I momentarily disable compression for all chunks and then reenable it after I'm done?

You can create the hypertable first (without compression), then insert your historical data and finally enable compression (see example code above).
You can also use Manual Compression: then you have full control and can decide which chunks to compress and when to compress them

Or can I someway force the insertion making TimescaleDB do the decompression, insertion, and recompression after that transparently?

Modifying compressed data is planned in future versions

Also note, that timescale will disallow schema changes for a compressed hypertable (see warning in the docs)

The current release of TimescaleDB supports the ability to query data in compressed chunks. However, it does not support inserts or updates into compressed chunks. We also block modifying the schema of hypertables with compressed chunks.

Upvotes: 3

Related Questions