TimescaleDB时间序列数据库

标签：聚合 chunk -- 压缩 TimescaleDB time 序列数据库 SELECT

TimescaleDB:

这是一款支持完整sql开源的时间序列数据库。

用处

1、数据量庞大

2、只做时间索引类的插入

3、很少更新数据

TimescaleDB的好处：

基于时序优化
自动分片（自动按时间、空间分片(chunk)）
全 SQL 接口
支持垂直于横向扩展
支持时间维度、空间维度自动分区。空间维度指属性字段（例如传感器 ID，用户 ID 等）
支持多个 SERVER，多个 CHUNK 的并行查询。分区在 TimescaleDB 中被称为 chunk。
自动调整 CHUNK 的大小
内部写优化（批量提交、内存索引、事务支持、数据倒灌）。
内存索引，因为 chunk size 比较适中，所以索引基本上都不会被交换出去，写性能比较好。
数据倒灌，因为有些传感器的数据可能写入延迟，导致需要写以前的 chunk，timescaleDB 允许这样的事情发生(可配置)。
复杂查询优化（根据查询条件自动选择 chunk，最近值获取优化(最小化的扫描,类似递归收敛)，limit 子句 pushdown 到不同的 server,chunks，并行的聚合操作）
利用已有的 PostgreSQL 特性（支持 GIS，JOIN 等），方便的管理（流复制、PITR）
支持自动的按时间保留策略（自动删除过旧数据）

超表的创建

--将普通的表转换为超标 将时间字段作为分片字段
SELECT create_hypertable('表名', '字段');

超表的操作跟普通表的操作一样，只不过是插件自动帮我们分片

参数详解：

chunk_time_interval：显式配置时间间隔，就是根据时间来自行分区，默认是7天,选择时间间隔的关键属性是属于最近间隔的块（包括索引）（或块，如果使用空间分区）适合内存。因此，我们通常建议设置间隔，使这些块不超过主内存的 25%。

--创建超表的时候设置时间间隔 例：设置一天
SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

针对已创建的超标设置

SELECT set_chunk_time_interval('表名', interval '24 hours');
SELECT set_chunk_time_interval('表名', 86400000000);
按空间分区，创建4个分区
SELECT create_hypertable('表名', 'time', 'location', 4);

获取超表空间大小

SELECT hypertable_size('devices') ;

数据保留策略：

相当于任务，定时清除历史数据

--清除6个月以前的历史数据
SELECT add_retention_policy('表名', INTERVAL '6 months');

--删除现有表的策略
SELECT remove_retention_policy('表名');

手动删除旧的数据块

--要删除超过三周的所有数
SELECT drop_chunks('stocks_real_time', INTERVAL '3 weeks');

--删除超过两周但新于三周的所有数据：
SELECT drop_chunks(
  'stocks_real_time',
  older_than => INTERVAL '2 weeks',
  newer_than => INTERVAL '3 weeks'
)

创建连续聚合:

聚合查询：

--当前语句查询当前数据集 每天的 最高值，最低值，第一个值和最后的一个值
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

从聚合查询变为连续聚合：

可以通过CREATE MATERIALIZED VIEW命令触发数据库创建具有给定名称的物化视图，（相当于里面是有数据存储的）WITH (timescaledb.continuous)指示 TimescaleDB 创建一个连续的聚合，而不仅仅是一个通用的物化视图。最后，在AS关键字之后添加之前的查询。

CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;

查询数据的话只需要查询当前视图数据即可

SELECT * FROM stock_candlestick_daily
  ORDER BY day DESC, symbol;

实时连续聚合

默认情况下，所有连续聚合都创建为实时聚合。这意味着 TimescaleDB 会将UNION尚未通过刷新策略具体化的最新数据附加（或）到连续聚合的输出中

使用和设置连续聚合策略：

1、自动连续聚合刷新策略

--此策略每天运行一次，由 设置schedule_interval。当它运行时，
--它会具体化 3 天前到 1 小时前的数据，由 start_offset和设置end_offset。
--偏移时间是相对于查询执行时间计算的。
--执行的查询是在连续聚合中定义的查询stock_candlestick_daily。
SELECT add_continuous_aggregate_policy('stock_candlestick_daily',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 days');

2、手动刷新

这在插入或修改超出刷新策略start_offset和end_offset间隔的数据时最有用。这在边缘物联网系统中很常见，其中设备长时间失去互联网连接，并在重新连接后最终发送历史读数。

--此手动刷新仅更新您的连续聚合一次。它不会自动使聚合保持最新。
--要设置自动刷新策略，请参阅前面关于连续聚合刷新策略的部分。
--当前语句是刷新1周前到当前的数据到连续聚合中
CALL refresh_continuous_aggregate(
  'stock_candlestick_daily',
  now() - INTERVAL '1 week',
  now()
);

数据压缩

要启用压缩，您需要ALTER超stocks_real_time表。启用压缩时可以指定三个参数：
timescaledb.compress（必需）：在超表上启用 TimescaleDB 压缩
timescaledb.compress_orderby（可选）：用于对压缩数据进行排序的列
timescaledb.compress_segmentby（可选）：用于对压缩数据进行分组的列
如果不指定compress_orderby或compress_segmentby列，则压缩数据按超表时间列自动排序

在超表上启用压缩

ALTER TABLE stocks_real_time SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'symbol'
);

自动压缩：

压缩策略：

add_compression_policy：许您设置一个策略，系统会根据该策略在块达到给定年龄后在后台自动压缩块。

添加策略以压缩“cpu”超表上超过 60 天的块。

SELECT add_compression_policy('cpu', INTERVAL '60d');

压缩超表上超过两周的数据

SELECT add_compression_policy('stocks_real_time', INTERVAL '2 weeks');

与连续聚合策略和保留策略类似，当您运行此 SQL 时，包含至少两周前的数据的所有块都将被压缩stocks_real_time，并创建循环压缩策略。

不要试图压缩所有数据，这一点很重要。尽管您可以将新数据插入到压缩块中，但无法更新或删除压缩行。因此，最好只在数据老化后才压缩数据，一旦数据不太可能需要更新。

手动压缩：

compress_chunk 函数用于压缩特定的块。当用户想要更多地控制压缩的调度时，这最常用来代替 add_compression_policy函数。
使用此查询手动压缩包含超过 2 周的数据的块。如果您手动压缩超表块，请考虑添加 if_not_compressed=>true（设置为true跳过已压缩的块）到compress_chunk()函数中。否则，TimescaleDB 在尝试压缩已压缩的块时会显示错误

SELECT compress_chunk(i, if_not_compressed=>true)
  FROM show_chunks('stocks_real_time', older_than => INTERVAL ' 2 weeks') i;

验证压缩：

可以使用此查询检查超表的整体压缩率，以查看应用压缩前后压缩块的大小

SELECT pg_size_pretty(before_compression_total_bytes) as "before compression",
  pg_size_pretty(after_compression_total_bytes) as "after compression"
  FROM hypertable_compression_stats('stocks_real_time');

标签：聚合,chunk,--,压缩,TimescaleDB,time,序列,数据库,SELECT
From： https://blog.csdn.net/wxj_wxj_wxj/article/details/140440175