pg数据库插件timescale时序库使用记录

timescale学习记录

关于pgsql大量数据分表和数据保留策略

---

前言

基于postgresql的开源时序数据库，支持原生sql，对于数据量很大的表可以进行分表操作，在此基础上设置时间保留策略，能让过期数据自动删除。

一、pandas是什么？

TimescaleDB是一款针对快速获取和复杂查询而优化的开源时间序列数据库。

二、使用步骤

1.下载安装插件

下载安装操作

2.具体使用

启用插件：

CREATE EXTENSION IF NOT EXISTS timescaledb;

创建超级表：

create_hypertable()

SELECT create_hypertable('conditions', 'time');

设置块聚合时间：

chunk_time_interval（）

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');

//  修改分块的触发条件

SELECT set_chunk_time_interval('conditions', 1000);

压缩策略（暂时不需要使用）：

设置压缩分组

ALTER TABLE example SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

超过7天进行压缩处理

 SELECT add_compression_policy('example', INTERVAL '7 days');

数据保留策略：

保留24H数据

SELECT add_retention_policy('conditions', INTERVAL '24 hours');


//清除数据保留策略
SELECT remove_retention_policy('conditions');


// 查看计划作业
SELECT * FROM timescaledb_information.job_stats;

TimesacleDB设置

// 查看超表
select show_chunks('tablename');

SELECT drop_chunks('tablename', older_than =>id); 删除当前id之前的块

数据保留策略两种方法

方法一
步骤一：创建超表（分区字段time字段是时间格式 类型(TIMESTAMP, TIMESTAMPTZ，DATE)）
SELECT create_hypertable('tablename', 'time',chunk_time_interval => 5000000);
注 ：①  按时间分区，时间字段要设为主键（对于时间不重复的情况下）
    ②  chunk_time_interval => 5000000（分区时间段，单位为毫秒）
步骤二： 创建数据保留策略（默认一天执行一次该策略）
SELECT add_retention_policy('tablename', INTERVAL '1 minutes');
该语句表示只保留1分钟的数据
方法二：
步骤一：创建超表（分区字段id是Integer 类型(SMALLINT, INT, BIGINT)）
SELECT create_hypertable('tablename', 'id',chunk_time_interval => 5000000，if_not_exists => TRUE);
注 ：①  id类型分区，字段要设为主键
    ②  chunk_time_interval => 5000000（分区字段，表示按多少数量分区，表示500万数据一个分区）
步骤二： 创建数据保留策略，按时间保留（默认一天执行一次该策略）
SELECT add_retention_policy('tablename',BIGINT'60000');
必须运行如下语句：(记录当前插入数据的unix（秒）)
CREATE OR REPLACE FUNCTION unix_now() returns INTEGER LANGUAGE SQL STABLE as $$ SELECT extract(epoch from now())::INTEGER $$;


SELECT set_integer_now_func('tablename', 'unix_now');
其中 INERGER 要和id字段的类型相同

若要重建策略，先删除原来的策略
SELECT remove_retention_policy('cigarette_single');
查看策略任务详细信息
select * from timescaledb_information.jobs
查看策略任务执行情况
SELECT * FROM timescaledb_information.job_stats
修改策略执行时间
SELECT alter_job(1015, schedule_interval => INTERVAL '6 minutes');
注：1015是策略任务信息中的 job_id，schedule_interval 表示任务执行周期
其他：开启dbug调试，并查看任务运行情况
SET client_min_messages TO DEBUG1;
CALL run_job(1015);

删除块（整数型字段）

 SELECT drop_chunks('tablename',older_than =>4184002807); 删除id小于4184002807的块

---