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,


      pg_size_pretty(after_compression_total_bytes) as after 

 FROM hypertable_compression_stats('crypto_ticks'); 

Compression Policy

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


Links

[[1]]

• HyperCore: Since version 2.18.0

 [2] (Old)
 [3]   (New)