TimeScale Notes

Compression

Check Distinct Values

-- n_distinct (-1 = Unique, 1 = All The Same)
SELECT tablename, attname, most_common_vals, most_common_freqs, n_distinct 
FROM pg_stats 
-- WHERE tablename = '<TableName>'; 
WHERE schemaname = '<SchemaName>'; 

Compress Table

ALTER TABLE crypto_ticks
SET (timescaledb.compress,
     timescaledb.compress_segmentby='symbol',
     timescaledb.compress_orderby='time DESC');

SELECT compress_chunk(c) from show_chunks('crypto_ticks') c;

Check Compression

SELECT pg_size_pretty(before_compression_total_bytes) as before, </br>
       pg_size_pretty(after_compression_total_bytes) as after </br>
  FROM hypertable_compression_stats('crypto_ticks'); 

Compression Policy

SELECT add_compression_policy('crypto_ticks', INTERVAL '8 days'); 

Links

https://docs.timescale.com/tutorials/latest/financial-tick-data/financial-tick-compress

HyperCore: Since version 2.18.0

https://docs.timescale.com/use-timescale/latest/compression (Old) https://docs.timescale.com/use-timescale/latest/hypercore] (New)