2025-04-17T11:58:15Z

Ian.readman:

== Installation ==
https://docs.timescale.com/self-hosted/latest/install/
=== Debian ===
<pre>
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
sudo apt install timescaledb-2-postgresql-17 postgresql-client-17
sudo apt-get install timescaledb-2-postgresql-14='2.6.0*' timescaledb-2-loader-postgresql-14='2.6.0*'
</pre>

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

<pre>
sudo timescaledb-tune
sudo systemctl restart postgresql
</pre>

<pre>
CREATE EXTENSION IF NOT EXISTS timescaledb;
</pre>

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

== 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) 
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. 
<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 
<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 

* 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>