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'));

If the existing table already has data in it then you must use the "migrate_data" option.

SELECT create_hypertable('metrics', by_range('device_time'), migrate_data => true);

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
);

Table Size

SELECT PG_SIZE_PRETTY (pg_total_relation_size('metrics'));
SELECT PG_SIZE_PRETTY (hypertable_size('metrics'));

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.%';