Editing TimeScale Training

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

# 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*'
</pre>

When running timescaledb-tune, accept the option to update shared_preload_libraries. Other settings are optional </br>
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/London';

\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 ===
The default chunk interval is 7 days.
<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>

=== New (HyperCore) ===
<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>

== Continuous Aggregates ==
=== Create ===
The materialised view will be created immediately unless you use the option <b>WITH NO DATA</b>

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

=== Refresh ===
Refresh all data between the dates specified.
<pre>
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()));
</pre>

=== Add Policy ===
Create a policy that refreshes data between 1 month old and 1 day. This occurs every hour. </br>
This means data older than 1 month will never be removed from the CA, even if it is deleted from the underlying table.
<pre>
SELECT add_continuous_aggregate_policy('daily_cpu_stats',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 h');
</pre>