[Bug]: Large hypertable has only 1 chunk, and chunk continually uncompresses

[zadeviggers]

What type of bug is this?

Other

What subsystems and features are affected?

Compression

What happened?

In my largest hypertable all the chunks seem to have rolled themselves into one huge chunk (~1.5tb) and uncompressed themselves (they were around ~150gb compressed the other day). This is the second time this has happened. I tried running the compression manually, and that brought it back down to ~150gb, but the next day it was uncompressed again.

sensor_data=# SELECT show_chunks('sensor_data_4');
                show_chunks
-------------------------------------------
 _timescaledb_internal._hyper_29_338_chunk
(1 row)

sensor_data=# SELECT * FROM timescaledb_information.jobs
sensor_data-# WHERE proc_name = 'policy_compression' AND hypertable_name='sensor_data_4';
 job_id |     application_name      | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema       |     proc_name      |  owner   | scheduled | fixed_schedule |                       config                        | next_start | initial_start | hypertable_schema | hypertable_name |      check_schema      |        check_name
--------+---------------------------+-------------------+-------------+-------------+--------------+------------------------+--------------------+----------+-----------+----------------+-----------------------------------------------------+------------+---------------+-------------------+-----------------+------------------------+--------------------------
   1006 | Compression Policy [1006] | 02:00:00          | 00:00:00    |          -1 | 01:00:00     | _timescaledb_functions | policy_compression | postgres | t         | f              | {"hypertable_id": 29, "compress_after": "02:00:00"} | -infinity  |               | public            | sensor_data_4   | _timescaledb_functions | policy_compression_check
(1 row)

Here's a chart of pg_database_size('sensor_data') over the past couple days - we've had no significant increase in our data ingest volume:

We were running Timescale 2.11, and I just upgraded to 2.13.1, hoping it might help with the issue.

I already mentioned this in #6233 with @mkindahl, but the problem seemed to be partially resolved after I increased the disk space. However, it has now come back.

TimescaleDB version affected

2.13.1

PostgreSQL version used

14.10

What operating system did you use?

Ubuntu 20.04.6 LTS (GNU/Linux 5.4.0-170-generic x86_64)

What installation method did you use?

Not applicable

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

Not sure how to reproduce sorry!

[fabriziomello]

@zadeviggers Thanks for reaching out. Can u please detail your hypertable structure including the compression settings? Also would be good to show your current compression policies including stats.

[zadeviggers]

@fabriziomello thanks for getting back to me! Here's the SQL we run to set up the table. It should have what you asked for:

CREATE TABLE IF NOT EXISTS sensor_data_4 (
	sensor_id int NOT NULL,
	data_timestamp timestamptz NOT NULL,
	data_channel char(3) NOT NULL,
	data_values FLOAT[] NOT NULL,
	CONSTRAINT fk_sensor_id FOREIGN KEY(sensor_id) REFERENCES sensors(id)
);
SELECT create_hypertable('sensor_data_4','data_timestamp', if_not_exists => TRUE);
ALTER TABLE sensor_data_4 SET (timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id');
-- Note this was originally two days, and then 12 hours
SELECT add_compression_policy('sensor_data_4', INTERVAL '2 hours', if_not_exists => TRUE);

I'm not super experienced with TimeScale, so if you could give me SELECT queries for any other stats you need to see that would be great thanks!

[akuzm]

In my largest hypertable all the chunks seem to have rolled themselves into one huge chunk

Let's figure this out first. This is not something that should happen by itself. The data is supposed to be split into the chunks based on time, so if the time of the given row doesn't change, it stays in the same chunk. Let's check what time intervals you have there:

 select format('select ''%s'', min(data_timestamp), max(data_timestamp) from %s', x, x) from show_chunks('sensor_data_4') x \gexec

[zadeviggers]

@akuzm yes, my guess is that TimeScale is failing to compress because the chunk is too large & the CPU in my server isn't up to the task of compressing it all within 24 hours (the server automatically restarts at midnight for various other reasons).

Here's what that query spat out:

                 ?column?                  |            min             |            max
-------------------------------------------+----------------------------+----------------------------
 _timescaledb_internal._hyper_29_338_chunk | 1970-01-20 19:56:39.491+12 | 1970-01-21 06:23:32.394+12
(1 row)

For reference, there are just over 2 billion rows in this table at the moment.

[akuzm]

                 ?column?                  |            min             |            max
-------------------------------------------+----------------------------+----------------------------
 _timescaledb_internal._hyper_29_338_chunk | 1970-01-20 19:56:39.491+12 | 1970-01-21 06:23:32.394+12
(1 row)

So this means that the timestamps of the records in your table go from 1970-01-20 19:56:39.491+12 to 1970-01-21 06:23:32.394+12, one day in 1970, is this correct? If so, you might want to use a custom chunk_time_interval to split your table into more chunks. The default one is 1 day.

https://docs.timescale.com/api/latest/hypertable/set_chunk_time_interval/