TimeScale Notes: Difference between revisions
Ian.readman (talk | contribs) No edit summary |
Ian.readman (talk | contribs) No edit summary |
||
| Line 1: | Line 1: | ||
== Compression == | |||
== Check Distinct Values == | === Check Distinct Values === | ||
-- n_distinct (-1 = Unique, 1 = All The Same) | -- n_distinct (-1 = Unique, 1 = All The Same) | ||
SELECT tablename, attname, most_common_vals, most_common_freqs, n_distinct | SELECT tablename, attname, most_common_vals, most_common_freqs, n_distinct | ||
| Line 8: | Line 8: | ||
WHERE schemaname = '<SchemaName>'; | WHERE schemaname = '<SchemaName>'; | ||
== Compress Table == | === Compress Table === | ||
ALTER TABLE crypto_ticks | ALTER TABLE crypto_ticks | ||
SET (timescaledb.compress, | SET (timescaledb.compress, | ||
| Line 16: | Line 16: | ||
SELECT compress_chunk(c) from show_chunks('crypto_ticks') c; | SELECT compress_chunk(c) from show_chunks('crypto_ticks') c; | ||
Check Compression | === Check Compression === | ||
SELECT pg_size_pretty(before_compression_total_bytes) as before, | SELECT pg_size_pretty(before_compression_total_bytes) as before, | ||
pg_size_pretty(after_compression_total_bytes) as after | pg_size_pretty(after_compression_total_bytes) as after | ||
FROM hypertable_compression_stats('crypto_ticks'); | FROM hypertable_compression_stats('crypto_ticks'); | ||
Compression Policy | === Compression Policy === | ||
SELECT add_compression_policy('crypto_ticks', INTERVAL '8 days'); | SELECT add_compression_policy('crypto_ticks', INTERVAL '8 days'); | ||
https://docs.timescale.com/tutorials/latest/financial-tick-data/financial-tick-compress | === Links === | ||
[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/hypercore] (New) | |||
Revision as of 20:23, 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
- HyperCore: Since version 2.18.0
[2] (Old) [3] (New)