-1

When we do the manual compression, instead of decreases the free space it was increased.

After compress, we have executed the vacuum command also.

enter image description here

Screenshot after executing the replied queries. screenshot-with-replied-queries

Sample data sample-data

Added the compression with below queries

ALTER TABLE data_table SET (timescaledb.compress,timescaledb.compress_segmentby = 'insert_time_stamp');

SELECT compress_chunk(i) FROM show_chunks('data_table', older_than => INTERVAL '10 days') i;

user3658578
  • 1
  • 1

2 Answers2

1

Have you checked how many chunks do you have compressed?

select total_chunks, number_compressed_chunks, pg_size_pretty(before_compression_total_bytes), pg_size_pretty(after_compression_total_bytes) from hypertable_compression_stats('pages');
┌──────────────┬──────────────────────────┬────────────────┬────────────────┐
│ total_chunks │ number_compressed_chunks │ pg_size_pretty │ pg_size_pretty │
├──────────────┼──────────────────────────┼────────────────┼────────────────┤
│          107 │                       19 │ 1883 MB        │ 543 MB         │
└──────────────┴──────────────────────────┴────────────────┴────────────────┘
(1 row)

You can also check individual chunks:

select  pg_size_pretty(sum(before_compression_total_bytes)),
   pg_size_pretty(sum(after_compression_total_bytes))
from chunk_compression_stats('pages') 
where compression_status = 'Compressed';
┌────────────────┬────────────────┐
│ pg_size_pretty │ pg_size_pretty │
├────────────────┼────────────────┤
│ 1883 MB        │ 543 MB         │
└────────────────┴────────────────┘
(1 row)
jonatasdp
  • 1,072
  • 6
  • 8
  • Thank you for your reply. I have checked, it's 2 chunks. But, the disk space not reduced. Added the screenshot in the question again. – user3658578 Sep 27 '21 at 15:25
  • Thanks for sharing the sample data and the screenshots. I think you may need to use some better `timescaledb.compress,timescaledb.compress_segmentby`. You can see you're using the timestamp column there and it's creating probably fragments that are not suitable for grouping. Try to use a column with a better cardinality. Like some category or something that is repeating over the rows and can be used further as a kind of index in your queries. If you have data changing every second and you segment by it, it will lead to more metadata from segments than data itself. – jonatasdp Oct 02 '21 at 19:21
0

i have the same question, and i have 12 chunks and set compression :

"ALTER TABLE diagnostics SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC,fuel_state',  timescaledb.compress_segmentby = 'tags_id');"
"ALTER TABLE readings SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC,latitude',  timescaledb.compress_segmentby = 'tags_id');"
"SELECT add_compression_policy('diagnostics', INTERVAL '12 hours');"              
"SELECT add_compression_policy('readings', INTERVAL '12 hours');"

when compressiong is completed , it didn't display disk usage; but i calcute the disk usage of directory, it increase from 4,841,552 KB to 16,612,380

    chunk_name     | hypertable_schema | hypertable_name |      range_start       |       range_end        | is_compressed | before_mbyte | after_mbyte 
-------------------+-------------------+-----------------+------------------------+------------------------+---------------+--------------+-------------
 _hyper_1_10_chunk | public            | diagnostics     | 2016-01-01 00:01:00+00 | 2016-01-01 00:01:15+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_12_chunk | public            | diagnostics     | 2016-01-01 00:01:15+00 | 2016-01-01 00:01:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_14_chunk | public            | diagnostics     | 2016-01-01 00:01:30+00 | 2016-01-01 00:01:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_16_chunk | public            | diagnostics     | 2016-01-01 00:01:45+00 | 2016-01-01 00:02:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_18_chunk | public            | diagnostics     | 2016-01-01 00:02:00+00 | 2016-01-01 00:02:15+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_20_chunk | public            | diagnostics     | 2016-01-01 00:02:15+00 | 2016-01-01 00:02:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_22_chunk | public            | diagnostics     | 2016-01-01 00:02:30+00 | 2016-01-01 00:02:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_24_chunk | public            | diagnostics     | 2016-01-01 00:02:45+00 | 2016-01-01 00:03:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_2_chunk  | public            | diagnostics     | 2016-01-01 00:00:00+00 | 2016-01-01 00:00:15+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_4_chunk  | public            | diagnostics     | 2016-01-01 00:00:15+00 | 2016-01-01 00:00:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_6_chunk  | public            | diagnostics     | 2016-01-01 00:00:30+00 | 2016-01-01 00:00:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_1_8_chunk  | public            | diagnostics     | 2016-01-01 00:00:45+00 | 2016-01-01 00:01:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_11_chunk | public            | readings        | 2016-01-01 00:01:15+00 | 2016-01-01 00:01:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_13_chunk | public            | readings        | 2016-01-01 00:01:30+00 | 2016-01-01 00:01:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_15_chunk | public            | readings        | 2016-01-01 00:01:45+00 | 2016-01-01 00:02:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_17_chunk | public            | readings        | 2016-01-01 00:02:00+00 | 2016-01-01 00:02:15+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_19_chunk | public            | readings        | 2016-01-01 00:02:15+00 | 2016-01-01 00:02:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_1_chunk  | public            | readings        | 2016-01-01 00:00:00+00 | 2016-01-01 00:00:15+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_21_chunk | public            | readings        | 2016-01-01 00:02:30+00 | 2016-01-01 00:02:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_23_chunk | public            | readings        | 2016-01-01 00:02:45+00 | 2016-01-01 00:03:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_3_chunk  | public            | readings        | 2016-01-01 00:00:15+00 | 2016-01-01 00:00:30+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_5_chunk  | public            | readings        | 2016-01-01 00:00:30+00 | 2016-01-01 00:00:45+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_7_chunk  | public            | readings        | 2016-01-01 00:00:45+00 | 2016-01-01 00:01:00+00 | t             | 0.00MB       | 0.00MB
 _hyper_2_9_chunk  | public            | readings        | 2016-01-01 00:01:00+00 | 2016-01-01 00:01:15+00 | t             | 0.00MB       | 0.00MB
(24 rows)

but when you decrease the scale of device from 100,000 to 100, its compression seems to meet expectations. So i think it's a bug for timescaledb' compression policy

    chunk_name     | hypertable_schema | hypertable_name |      range_start       |       range_end        | is_compressed | before_mbyte | after_mbyte 
-------------------+-------------------+-----------------+------------------------+------------------------+---------------+--------------+-------------
 _hyper_1_10_chunk | public            | diagnostics     | 2016-01-02 08:00:00+00 | 2016-01-02 16:00:00+00 | t             | 1282.84MB    | 65.55MB
 _hyper_1_12_chunk | public            | diagnostics     | 2016-01-02 16:00:00+00 | 2016-01-03 00:00:00+00 | t             | 1282.64MB    | 65.52MB
 _hyper_1_14_chunk | public            | diagnostics     | 2016-01-03 00:00:00+00 | 2016-01-03 08:00:00+00 | t             | 1282.21MB    | 65.69MB
 _hyper_1_15_chunk | public            | diagnostics     | 2016-01-03 08:00:00+00 | 2016-01-03 16:00:00+00 | t             | 1283.45MB    | 65.34MB
 _hyper_1_18_chunk | public            | diagnostics     | 2016-01-03 16:00:00+00 | 2016-01-04 00:00:00+00 | t             | 1282.09MB    | 65.48MB
 _hyper_1_19_chunk | public            | diagnostics     | 2016-01-04 00:00:00+00 | 2016-01-04 08:00:00+00 | t             | 1282.85MB    | 65.55MB
 _hyper_1_21_chunk | public            | diagnostics     | 2016-01-04 08:00:00+00 | 2016-01-04 16:00:00+00 | t             | 1283.11MB    | 65.26MB
 _hyper_1_24_chunk | public            | diagnostics     | 2016-01-04 16:00:00+00 | 2016-01-05 00:00:00+00 | t             | 1283.37MB    | 65.45MB
 _hyper_1_2_chunk  | public            | diagnostics     | 2016-01-01 00:00:00+00 | 2016-01-01 08:00:00+00 | t             | 1283.05MB    | 65.48MB
 _hyper_1_3_chunk  | public            | diagnostics     | 2016-01-01 08:00:00+00 | 2016-01-01 16:00:00+00 | t             | 1282.74MB    | 65.48MB
 _hyper_1_6_chunk  | public            | diagnostics     | 2016-01-01 16:00:00+00 | 2016-01-02 00:00:00+00 | t             | 1282.45MB    | 65.75MB
 _hyper_1_7_chunk  | public            | diagnostics     | 2016-01-02 00:00:00+00 | 2016-01-02 08:00:00+00 | t             | 1282.49MB    | 65.57MB
 _hyper_2_11_chunk | public            | readings        | 2016-01-02 16:00:00+00 | 2016-01-03 00:00:00+00 | t             | 1681.11MB    | 274.43MB
 _hyper_2_13_chunk | public            | readings        | 2016-01-03 00:00:00+00 | 2016-01-03 08:00:00+00 | t             | 1681.49MB    | 274.46MB
 _hyper_2_16_chunk | public            | readings        | 2016-01-03 08:00:00+00 | 2016-01-03 16:00:00+00 | t             | 1681.33MB    | 274.35MB
 _hyper_2_17_chunk | public            | readings        | 2016-01-03 16:00:00+00 | 2016-01-04 00:00:00+00 | t             | 1681.40MB    | 274.42MB
 _hyper_2_1_chunk  | public            | readings        | 2016-01-01 00:00:00+00 | 2016-01-01 08:00:00+00 | t             | 1680.11MB    | 274.87MB
 _hyper_2_20_chunk | public            | readings        | 2016-01-04 00:00:00+00 | 2016-01-04 08:00:00+00 | t             | 1681.08MB    | 274.38MB
 _hyper_2_22_chunk | public            | readings        | 2016-01-04 08:00:00+00 | 2016-01-04 16:00:00+00 | t             | 1681.87MB    | 274.44MB
 _hyper_2_23_chunk | public            | readings        | 2016-01-04 16:00:00+00 | 2016-01-05 00:00:00+00 | t             | 1681.26MB    | 274.38MB
 _hyper_2_4_chunk  | public            | readings        | 2016-01-01 08:00:00+00 | 2016-01-01 16:00:00+00 | t             | 1681.73MB    | 274.49MB
 _hyper_2_5_chunk  | public            | readings        | 2016-01-01 16:00:00+00 | 2016-01-02 00:00:00+00 | t             | 1681.15MB    | 274.42MB
 _hyper_2_8_chunk  | public            | readings        | 2016-01-02 00:00:00+00 | 2016-01-02 08:00:00+00 | t             | 1681.48MB    | 274.41MB
 _hyper_2_9_chunk  | public            | readings        | 2016-01-02 08:00:00+00 | 2016-01-02 16:00:00+00 | t             | 1680.20MB    | 274.45MB
(24 rows)
haoran920
  • 17
  • 2