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)