On PostgreSQL v15.13 using TimescaleDB v2.20.1 on Red Hat v9.6 self hosted on prem we have detailed data for year 2022, 2023, 2024 and 2025. We have compressed chunks for years between including 2022 and including 2024. Data for 2025 are still uncompressed, because of heavy updates.
Now we have performed some updates on 2022-2024 compressed chunks data.
Question 1: How to find out how much data are partially decompressed?
I have asked Devin AI to analyze TimescaleDB source code and to write an SQL to display partially decompressed files:
AI has written the following SQL:
SELECT
chunk_schema,
chunk_name,
pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)) as current_chunk_size,
compressed_total_size,
uncompressed_total_size,
pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)) - compressed_total_size as decompressed_data_size,
CASE
WHEN uncompressed_total_size > 0 THEN
((pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)) - compressed_total_size)::float / uncompressed_total_size::float) * 100
ELSE 0
END as percent_decompressed
FROM _timescaledb_internal.compressed_chunk_stats
WHERE compression_status = 'Compressed'
AND pg_total_relation_size(format('%I.%I', chunk_schema, chunk_name)) > compressed_total_size;
I have no idea if this is correctly written SQL. Does this SQL return info about decompressed?
Now I would like to manually recompress the chunks at the night time when system is not overwhelmed.
I have written SQL for particular chunk to compress:
CALL convert_to_columnstore('_timescaledb_internal._hyper_21_2820_chunk',true,true);"
Question 2: How to monitor the progress of above command compression process? For example I would like to know how much time will it take above command to finish or at least what is percentage of individually executed “convert_to_collumnstore” until chunk will be compressed? Or some other in progress info.