PostgreSQL插件TimeScaleDB基本使用

时序数据是按照时间序列进行组织的数据，主要用于描述事物、现象随时间的变化趋势；每个数据点通常由时间戳和一个或多个与之相关的值组成；在金融，工业物联网，IT行业系统数据等多个领域有着较为广泛的应用。
时序数据面临的问题： 由于时序数据是采集频率较高，数据随时间增长速度较快，因此面临着存储效率，查询性能，实时性需求等问题，需要进行专门的处理以满足实际应用需要。
TimeScaleDB是专门为处理时序数据而推出的PostgreSQL中一款扩展插件，用于更好的辅助数据库对时序数据进行管理分析。

基本使用

安装扩展

在根据官网指导安装TimeScaleDB在对应操作系统安装完插件后在需要添加时序表的数据库添加TimeScaleDB扩展。

安装扩展

CREATE EXTENSION IF NOT EXISTS timescaledb;

查看扩展

--命令
\dx
--sql语句
SELECT extname AS name, extversion AS version, n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON e.extnamespace = n.oid;

创建超表

创建超表

-- 创建超表
CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL );
-- 指定时间间隔对超级表进行分区
SELECT create_hypertable( 'conditions', by_range('time', INTERVAL '1 day') );
-- 默认时间间隔分区
SELECT create_hypertable( 'conditions', by_range('time'))

将已有数据的普通表转为超表

SELECT create_hypertable('your_table', 'time',migrate_data=>true);

这里需要注意的一个问题就是表中对应的所有唯一索引都必须加上时间列，不然TimeSacleDB无法进行分区。

更改超表的分区Chunk时间间隔

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

基础使用

TimeScaleDB支持Pgsql中的常规sql语句,可以直接像普通表一样使用超表。此外TimeScaleDB还提供一些额外的函数扩展对时序数据的管理分析功能。

写数据

新增

INSERT INTO conditions VALUES (NOW(), 'office', 70.0, 50.0), (NOW(), 'basement', 66.5, 60.0), (NOW(), 'garage', 77.0, 65.2);

删除

DELETE FROM conditions WHERE temperature < 35 OR humidity < 60;

更新

  
UPDATE conditions

  SET temperature = temperature + 0.1

  WHERE time >= '2017-07-28 11:40'

    AND time < '2017-07-28 11:50';

Upsert
Upsert操作如果尚未存在匹配行，则插入新行；如果已经存在匹配的记录，要么更新现有记录，要么什么也不做。

-- 插入，如果存在则更新
INSERT INTO conditions

  VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.2, 50.1)

  ON CONFLICT (time, location) DO UPDATE

    SET temperature = excluded.temperature,
        humidity = excluded.humidity;
-- 插入，如果存在什么也不做
INSERT INTO conditions

  VALUES ('2017-07-28 11:42:42.846621+00', 'office', 70.1, 50.0)

  ON CONFLICT DO NOTHING;

读取数据

基础查询

-- 100条最新数据
SELECT * FROM conditions ORDER BY time DESC LIMIT 100;
-- 查询至此刻12小时内数据总数
SELECT COUNT(*) FROM conditions

  WHERE time > NOW() - INTERVAL '12 hours';
-- 获取24小时内有上报过数据的位置总数
SELECT COUNT(DISTINCT location) FROM conditions JOIN locations ON conditions.location = locations.location WHERE locations.air_conditioning = True AND time > NOW() - INTERVAL '1 day';

time_buket()函数
time_buket函数可以根据一个时间间隔对数据进行聚合统计，比如统计 3小时内，每15分钟内的温度和湿度的最大值。

SELECT time_bucket('15 minutes', time) AS fifteen_min,

    location,

    COUNT(*),

    MAX(temperature) AS max_temp,

    MAX(humidity) AS max_hum

  FROM conditions

  WHERE time > NOW() - INTERVAL '3 hours'

  GROUP BY fifteen_min, location

  ORDER BY fifteen_min DESC, max_temp DESC;

使用SkipScan加速Distinct查询
SkipScan 可缩短 DISTINCT 查询的查询时间。适用于 PostgreSQL 表、Timescale hypertables 和 Timescale 分布式 hypertables。SkipScan 包含在 TimescaleDB 2.2.1 及更高版本中，需要注意SkipScan目前不适用于压缩块。

查询中可以使用Distinct查询获取数据表某个字段的最新值，比如获取每个测点的最新的温度值，周期性查询每个设备或监控项的最新值。但当数据量不断增大，distinct查询会变慢，原因PostgreSQL没有从有序索引中提取唯一值列表的良好机制。即使你有一个与这类查询的顺序和列完全匹配的索引，PostgreSQL 也会扫描整个索引来查找所有唯一值。随着表的增长，这种操作会越来越慢.
SkipScan 允许查询从一个有序值增量跳转到下一个有序值，而无需读取中间的所有行。如果不支持这一功能，数据库引擎就必须扫描整个有序索引，然后在最后删除重复数据，这个过程要慢得多。

SkipScan 是对 SELECT DISTINCT ON column_name 形式查询的优化。从概念上讲，SkipScan 就是普通的 IndexScan，它会跳过索引，寻找下一个大于当前值的值。

当你发出使用 SkipScan 的查询时，EXPLAIN 输出会包含一个新的操作符或节点，它可以从一个正确排序的索引中快速返回不同的项。使用仅索引扫描时，PostgreSQL 必须扫描整个索引，但 SkipScan 会递增搜索有序索引中的每个连续项。当找到一个项目时，SkipScan 节点会迅速重新开始搜索下一个项目。这是在有序索引中查找不同项的一种更有效的方法。
使用SkipScnn时，建立的索引必须满足以下条件：

包含作为第一列的 DISTINCT 列。
是 BTREE 索引。
与查询中使用的 ORDER BY 相匹配
查询语句必须在单列上使用 DISTINCT 关键字。如果 DISTINCT 列不是索引的第一列，则需要确保在查询中使用任何前导列作为约束。这意味着，如果提出的问题是 “按顺序检索唯一 ID 列表 ”和 “检索每个 ID 的最后读数”，需要建立如下索引：

CREATE INDEX "cpu_customer_tags_id_time_idx" \

ON readings (customer_id, tags_id, time DESC)

设置正确的索引后，查询执行的分析应如下：

-> Unique 
	-> Merge Append 
	Sort Key: _hyper_8_79_chunk.tags_id, _hyper_8_79_chunk."time" DESC 
	-> Custom Scan (SkipScan) on _hyper_8_79_chunk 
	-> Index Only Scan using _hyper_8_79_chunk_cpu_tags_id_time_idx on _hyper_8_79_chunk Index Cond: (tags_id > NULL::integer) 
	-> Custom Scan (SkipScan) on _hyper_8_80_chunk 
	-> Index Only Scan using _hyper_8_80_chunk_cpu_tags_id_time_idx on _hyper_8_80_chunk Index Cond: (tags_id > NULL::integer)

参考链接

TimeScaleDB官网
 基于PostgreSQL的时序数据库TimescaleDB的基本用法和概念
 ChatGPT
TimescaleDB：快速入门教程

posted @ 2024-11-22 17:50 岛dao 阅读(5) 评论(0) 编辑收藏举报

刷新页面返回顶部