Activity on compressed chunk

1

PostgreSQL Version: PostgreSQL 15.3 on x86_64-pc-linux-musl,
Timescale version: 2.11.0

We use timescale with a chunk_time_interval of one week.
There are currently 671 chunks available.
The oldest chunk start date is 01.01.2013 (_hyper_4_1794_chunk)
We have a CompressPolicy of 6 months.

Currently we have the problem, that in the oldest compressed chunks from 2013 (for example _hyper_4_1794_chunk) many rows are inserted.

Using the values n_tup_ins, n_ins_since_vacuum, idx_scan from the view pg_stat_user_tables, we can see that there is a lot of activity on the chunk.
The real number of rows does not increase in the chunk.
However, this activity does not occur from our applications :frowning:

As a result, these chunks become very bloated. The table size is reduced again by the autovacuum, but the index size remains very high.
With a VACUUM FULL the chunks can be brought back to their original size.

I have already written a trigger specifically for the chunk that logs all INSERT/DELETE/UPDATE.
Unfortunately, the trigger did not fire and did not log any activity.

Is it possible to see who or what is causing the activity on the chunk/s ?

2

Hi @WausD, my guess is that your activities are becoming from background workers. Compression is done in background.

It proves that there’s no extra data coming to the database.

Can you try to check the job_stats and maybe even pause some of the jobs related to the hypertable to see if that can be causing the issue.

Can you check this blog post: Allowing DML Operations in Highly Compressed Data in Postgres

It may help you to understand how compression works and you may see some potential of your application to bring side effects to the database.