TimeScale Training

Installation

https://docs.timescale.com/self-hosted/latest/install/

Debian

echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | /
  sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | /
  sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo apt update

# Get latest version
sudo apt install timescaledb-2-postgresql-17
# Get specific version
sudo apt-get install timescaledb-2-postgresql-15='2.14.2*' timescaledb-2-loader-postgresql-15='2.14.2*'

When running timescaledb-tune, accept the option to update shared_preload_libraries. Other settings are optional
https://docs.timescale.com/self-hosted/latest/configuration/timescaledb-tune

sudo timescaledb-tune
sudo systemctl restart postgresql

CREATE EXTENSION IF NOT EXISTS timescaledb;

Windows

https://docs.timescale.com/self-hosted/latest/install/installation-windows/#install-and-configure-timescaledb-on-postgresql

Setup

SET search_path = "$user", public, "timescaledb_information";
SET timezone TO 'Europe/London';

\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

The default chunk interval is 7 days.

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;

New (HyperCore)

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

Continuous Aggregates

Create

The materialised view will be created immediately unless you use the option WITH NO DATA

CREATE MATERIALIZED VIEW daily_cpu_stats
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', device_time) AS day,
       device_id,
       avg(cpu_usage) AS avg_cpu,
       max(cpu_usage) AS max_cpu
FROM metrics
GROUP BY day, device_id;

Refresh

Refresh all data between the dates specified.

CALL refresh_continuous_aggregate('daily_cpu_stats', '2023-01-01', '2025-04-01');
CALL refresh_continuous_aggregate('daily_cpu_stats', date_trunc('month', now())', date_trunc('day', now()));

Add Policy

Create a policy that refreshes data between 1 month old and 1 day. This occurs every hour.
This means data older than 1 month will never be removed from the CA, even if it is deleted from the underlying table.

SELECT add_continuous_aggregate_policy('daily_cpu_stats',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 h');