Ian Doob Technology - User contributions [en]

TimeScale Training

2025-04-14T09:53:27Z

2A02:8012:10C9:0:1454:8E3B:F3BA:4D18:

== Setup ==
<pre>
SET search_path = "$user", public, "timescaledb_information";
SET timezone TO 'Europe/Berlin';

\dv timescaledb_information.*
</pre>

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

== Create Hypertable ==
<pre>
SELECT create_hypertable('metrics', by_range('device_time'));
</pre>

ERROR: cannot create a unique index without the column "device_time" (used in partitioning) </br>
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.

<pre>
ALTER TABLE metrics DROP CONSTRAINT metrics_pkey;
ALTER TABLE metrics ADD PRIMARY KEY (id, device_time);
SELECT create_hypertable('metrics', by_range('device_time'));
</pre>

If the existing table already has data in it then you must use the "migrate_data" option. </br>
<pre>
SELECT create_hypertable('metrics', by_range('device_time'), migrate_data => true);
</pre>

== Chunk Interval ==
<pre>
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;
</pre>

== Generate Time Series Data ==
<pre>
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
);
</pre>

== Table Size ==
<pre>
SELECT PG_SIZE_PRETTY (pg_total_relation_size('metrics'));
SELECT PG_SIZE_PRETTY (hypertable_size('metrics'));
</pre>

== Check Statistics ==
<pre>
ANALYZE metrics;
SELECT tablename, attname, n_distinct FROM pg_stats WHERE tablename = 'metrics';
</pre>

== Examine Chunks ==
<pre>
\d+ metrics
SELECT MAX(device_time), MIN(device_time) FROM _timescaledb_internal.<chunk>;
</pre>

== Compression (Old) ==
<pre>
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;
</pre>

== Compression (New) ==
<pre>
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';
</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 ==
https://docs.timescale.com/timescaledb/latest/overview/limitations </br>

* 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 ==
<pre>
SELECT name, setting, short_desc FROM pg_settings WHERE name like 'timescaledb.%';
</pre>

TimeScale Training

2025-04-14T09:52:48Z

2A02:8012:10C9:0:1454:8E3B:F3BA:4D18: