TimeScale Training: Difference between revisions
Ian.readman (talk | contribs) No edit summary |
Ian.readman (talk | contribs) No edit summary |
||
Line 102: | Line 102: | ||
SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression'; | SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression'; | ||
</pre> | |||
== Tablespaces == | |||
https://docs.timescale.com/self-hosted/latest/manage-storage </br> | |||
<pre> | |||
CREATE TABLESPACE historic_data OWNER postgres LOCATION '/home/postgres/timescale'; | |||
SELECT show_chunks('metrics', older_than => INTERVAL '5 months'); | |||
SELECT move_chunk( | |||
chunk => '_timescaledb_internal._hyper_1_4_chunk', | |||
destination_tablespace => 'historic_data', | |||
index_destination_tablespace => 'historic_data', | |||
reorder_index => '_timescaledb_internal._hyper_1_4_chunk_netdata_time_idx', | |||
verbose => TRUE | |||
); | |||
</pre> | |||
== Limitations == | |||
Time dimensions (columns) used for partitioning cannot have NULL values. </br> | |||
Unique indexes must include all columns that are partitioning dimensions. </br> | |||
UPDATE statements that move values between partitions (chunks) are not supported. </br> | |||
Foreign key constraints from a hypertable referencing another hypertable are not supported. | |||
== Parameters == | |||
<pre> | |||
SELECT name, setting, short_desc FROM pg_settings WHERE name like 'timescaledb.%'; | |||
</pre> | </pre> |
Revision as of 16:57, 13 April 2025
Setup
SET search_path = "$user", public, "timescaledb_information"; SET timezone TO 'Europe/Berlin'; \dv timescaledb_information.*
DROP TABLE IF EXISTS metrics; CREATE TABLE metrics (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, device_time timestamptz NOT NULL, device_id integer, cpu_usage numeric);
Create Hypertable
SELECT create_hypertable('metrics', by_range('device_time'));
ERROR: cannot create a unique index without the column "device_time" (used in partitioning) HINT: If you're creating a hypertable on a table with a primary key, ensure the partitioning column is part of the primary or composite key.
ALTER TABLE metrics DROP CONSTRAINT metrics_pkey; ALTER TABLE metrics ADD PRIMARY KEY (id, device_time); SELECT create_hypertable('metrics', by_range('device_time'));
Chunk Interval
SELECT create_hypertable('metrics', by_range('time', INTERVAL '1 month')); SELECT set_chunk_time_interval('metrics', INTERVAL '14 days'); SELECT hypertable_name, column_name, time_interval FROM timescaledb_information.dimensions;
Generate Time Series Data
TRUNCATE TABLE metrics RESTART IDENTITY; INSERT INTO metrics (device_time, device_id, cpu_usage) ( SELECT time, device_id, random()*100 as cpu_usage FROM generate_series(date_trunc('hour',now()) - INTERVAL '6 months', now(), INTERVAL '5 minutes') as time, generate_series(1,1000) device_id );
Check Statistics
ANALYZE metrics; SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'metrics';
Examine Chunks
\d+ metrics SELECT MAX(device_time), MIN(device_time) FROM _timescaledb_internal.<chunk>;
Compression (Old)
ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby='device_id', timescaledb.compress_orderby='device_time DESC'); SELECT compress_chunk(c) from show_chunks('metrics') c; SELECT pg_size_pretty(before_compression_total_bytes) as before, pg_size_pretty(after_compression_total_bytes) as after FROM hypertable_compression_stats('metrics'); SELECT add_compression_policy('metrics', INTERVAL '1 days'); SELECT remove_compression_policy('metrics'); SELECT decompress_chunk(c, true) FROM show_chunks('metrics') c;
Compression (New)
ALTER TABLE metrics SET ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'device_id'); CALL add_columnstore_policy('metrics', after => INTERVAL '1d'); SELECT pg_size_pretty(before_compression_total_bytes) as before, pg_size_pretty(after_compression_total_bytes) as after FROM hypertable_compression_stats('metrics'); SELECT pg_size_pretty(before_compression_table_bytes) as before_table, pg_size_pretty(before_compression_index_bytes) as before_index, pg_size_pretty(before_compression_toast_bytes) as before_toast, pg_size_pretty(before_compression_total_bytes) as before_total, pg_size_pretty(after_compression_table_bytes) as after_table, pg_size_pretty(after_compression_index_bytes) as after_index, pg_size_pretty(after_compression_toast_bytes) as after_toast, pg_size_pretty(after_compression_total_bytes) as after_total FROM hypertable_columnstore_stats('metrics'); SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression';
Tablespaces
https://docs.timescale.com/self-hosted/latest/manage-storage
CREATE TABLESPACE historic_data OWNER postgres LOCATION '/home/postgres/timescale'; SELECT show_chunks('metrics', older_than => INTERVAL '5 months'); SELECT move_chunk( chunk => '_timescaledb_internal._hyper_1_4_chunk', destination_tablespace => 'historic_data', index_destination_tablespace => 'historic_data', reorder_index => '_timescaledb_internal._hyper_1_4_chunk_netdata_time_idx', verbose => TRUE );
Limitations
Time dimensions (columns) used for partitioning cannot have NULL values.
Unique indexes must include all columns that are partitioning dimensions.
UPDATE statements that move values between partitions (chunks) are not supported.
Foreign key constraints from a hypertable referencing another hypertable are not supported.
Parameters
SELECT name, setting, short_desc FROM pg_settings WHERE name like 'timescaledb.%';