TimeScale Training

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

https://docs.timescale.com/timescaledb/latest/overview/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.%';