TimescaleDB

刷新层级结构 SQL

select ('call refresh_continuous_aggregate(''' || user_view_name || ''', floor(extract(epoch from ''2023-01-01 00:00:00.000''::timestamp) * 1000)::int8, floor(extract(epoch from ''2023-09-08 23:59:59.999''::timestamp) * 1000)::int8);')::varchar
from _timescaledb_catalog.continuous_agg
order by split_part(user_view_name, '_', 2), bucket_width;

删除层级结构 SQL

select ('drop materialized view ' || user_view_name || ';')::varchar
from _timescaledb_catalog.continuous_agg
order by split_part(user_view_name, '_', 2), bucket_width desc;

查询最早和最晚数据时间

select to_timestamp(min(range_start_integer) / 1000) as min, to_timestamp(max(range_end_integer) / 1000) as max
from timescaledb_information.chunks;

修改层级结构查询策略

select ('alter materialized view ' || user_view_name || ' set (timescaledb.materialized_only = false);')::varchar
from _timescaledb_catalog.continuous_agg
order by split_part(user_view_name, '_', 2), bucket_width desc;

总行数

select approximate_row_count('data_history');

数据库大小

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc;

TimescaleDB 版本

select default_version, installed_version
from pg_available_extensions
where name = 'timescaledb';

历史数据大小

select case when ca.view_name is null then s.hypertable_name else ca.view_name end, pg_size_pretty(s.total_size)
from timescaledb_information.continuous_aggregates ca
         right join (select hypertable_name, sum(total_bytes) as total_size from _timescaledb_internal.hypertable_chunk_local_size group by hypertable_name) s on s.hypertable_name = ca.materialization_hypertable_name
order by s.total_size desc;

后台任务情况

select bj.id as job_id,
       bj.proc_name,
       bj.scheduled,
       bjs.total_runs,
       bjs.total_successes,
       bjs.total_failures,
       bjs.last_run_success,
       bjs.last_finish,
       bjs.next_start,
       bjs.last_start,
       bjs.total_duration,
       bj.schedule_interval,
       ca.view_name
from _timescaledb_internal.bgw_job_stat bjs
         left join _timescaledb_config.bgw_job bj on bj.id = bjs.job_id
         left join timescaledb_information.jobs tj on tj.job_id = bj.id
         left join timescaledb_information.continuous_aggregates ca on ca.materialization_hypertable_name = tj.hypertable_name;

查询某时间段历史数据大小

select to_timestamp(min(c.range_start_integer) / 1000) as start, to_timestamp(max(c.range_end_integer) / 1000) as "end", pg_size_pretty(sum(hcls.total_bytes)) total_size
from _timescaledb_internal.hypertable_chunk_local_size hcls
         left join timescaledb_information.chunks c on c.chunk_name = hcls.chunk_name and c.chunk_schema = hcls.chunk_schema
where hcls.hypertable_name = 'data_history'
  and floor(extract(epoch from '2023-01-01 00:00:00.000 +08:00'::timestamptz) * 1000) <= c.range_start_integer
  and c.range_end_integer <= floor(extract(epoch from '2023-01-01 00:00:00.000 +08:00'::timestamptz) * 1000);

删除指定时间之前的历史数据

select ('select drop_chunks(''' || user_view_name || ''', newer_than => floor(extract(epoch from ''2024-04-11 00:00:00.000 +08:00''::timestamptz) * 1000)::bigint);')::varchar
from (select 'data_history' as user_view_name, 0 as bucket_width
      union
      (select user_view_name, bucket_width
       from _timescaledb_catalog.continuous_agg
       order by split_part(user_view_name, '_', 2), bucket_width desc)) t
order by bucket_width desc;

历史数据分块大小

select hcs.chunk_name, pg_size_pretty(hcs.total_bytes), to_timestamp(c.range_start_integer/1000) 
from _timescaledb_internal.hypertable_chunk_local_size hcs
left join timescaledb_information.chunks c on c.hypertable_schema  = hcs.hypertable_schema and c.chunk_name = hcs.chunk_name 
where hcs.hypertable_name = 'data_history'
order by hcs.total_bytes desc, c.range_start_integer desc;

‍