TimeScale Notes: Difference between revisions
Ian.readman (talk | contribs) No edit summary |
Ian.readman (talk | contribs) No edit summary |
||
| Line 11: | Line 11: | ||
=== Compress Table === | === Compress Table === | ||
ALTER TABLE crypto_ticks | ALTER TABLE crypto_ticks </br> | ||
SET (timescaledb.compress, | SET (timescaledb.compress, </br> | ||
timescaledb.compress_segmentby='symbol', | timescaledb.compress_segmentby='symbol', </br> | ||
timescaledb.compress_orderby='time DESC'); | timescaledb.compress_orderby='time DESC'); </br> | ||
SELECT compress_chunk(c) from show_chunks('crypto_ticks') c; | SELECT compress_chunk(c) from show_chunks('crypto_ticks') c; </br> | ||
=== Check Compression === | === Check Compression === | ||
SELECT pg_size_pretty(before_compression_total_bytes) as before, | SELECT pg_size_pretty(before_compression_total_bytes) as before, </br> | ||
pg_size_pretty(after_compression_total_bytes) as after | pg_size_pretty(after_compression_total_bytes) as after </br> | ||
FROM hypertable_compression_stats('crypto_ticks'); | FROM hypertable_compression_stats('crypto_ticks'); </br> | ||
=== Compression Policy === | === Compression Policy === | ||
SELECT add_compression_policy('crypto_ticks', INTERVAL '8 days'); | SELECT add_compression_policy('crypto_ticks', INTERVAL '8 days'); </br> | ||
=== Links === | === Links === | ||
[https://docs.timescale.com/tutorials/latest/financial-tick-data/financial-tick-compress] | [[https://docs.timescale.com/tutorials/latest/financial-tick-data/financial-tick-compress]] | ||
* HyperCore: Since version 2.18.0 | * HyperCore: Since version 2.18.0 | ||
[https://docs.timescale.com/use-timescale/latest/compression] (Old) | [https://docs.timescale.com/use-timescale/latest/compression] (Old) | ||
[https://docs.timescale.com/use-timescale/latest/hypercore] (New) | [https://docs.timescale.com/use-timescale/latest/hypercore] (New) | ||
Revision as of 20:28, 7 April 2025
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)