Timescale

check retention policies

SELECT j.hypertable_name,
       j.job_id,
       config,
       schedule_interval,
       job_status,
       last_run_status,
       last_run_started_at,
       js.next_start,
       total_runs,
       total_successes,
       total_failures
  FROM timescaledb_information.jobs j
  JOIN timescaledb_information.job_stats js
    ON j.job_id = js.job_id
  WHERE j.proc_name = 'policy_retention';

short

SELECT hypertable_name,config FROM timescaledb_information.jobs;

show all hypertables

SELECT * FROM timescaledb_information.hypertables;

Create retention policy

SELECT add_retention_policy('tablename', INTERVAL '14 days');

hypertables sorted by size

SELECT hypertable_name, hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass) FROM timescaledb_information.hypertables order by hypertable_size DESC;

Check which extentions are installed/enabled

\dx

                                      List of installed extensions
    Name     | Version |   Schema   |                            Description
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.3.1   | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

pg_stat_statements extention

SELECT *
FROM pg_available_extensions
WHERE
    name = 'pg_stat_statements' and
    installed_version is not null;

CREATE EXTENSION pg_stat_statements;

in config

shared_preload_libraries = 'timescaledb, pg_stat_statements'

select distinct on (hypertable_schema, hypertable_name) hypertable_schema || '.' || hypertable_name from timescaledb_information.chunks where primary_dimension_type::text = 'timestamp with time zone' and hypertable_schema != '_timescaledb_internal';
WITH hyper_tables AS (select distinct on (hypertable_schema, hypertable_name) hypertable_schema || '.' || hypertable_name as name from timescaledb_information.chunks where primary_dimension_type::text = 'timestamp with time zone') SELECT add_retention_policy(ht.name, '7 days'::interval) FROM hyper_tables ht;

 WITH hyper_tables AS (select distinct on (hypertable_schema, hypertable_name) hypertable_schema || '.' || hypertable_name as name from timescaledb_information.chunks where primary_dimension_type::text = 'timestamp with time zone')
SELECT add_retention_policy(ht.name, '7 days'::interval, if_not_exists := True) FROM hyper_tables ht;