时序数据库（TimescaleDB）

背景

随着物联网的发展，时序数据库的需求越来越多，比如水文监控、工厂的设备监控、国家安全相关的数据监控、通讯监控、金融行业指标数据、传感器数据等。

在互联网行业中，也有着非常多的时序数据，例如用户访问网站的行为轨迹，应用程序产生的日志数据等等。

时序数据有几个特点

1. 基本上都是插入，没有更新的需求。

2. 数据基本上都有时间属性，随着时间的推移不断产生新的数据，旧的数据不需要保存太久。

业务方对时序数据通常有几个查询需求

1. 获取最新状态，查询最近的数据（例如传感器最新的状态）

2. 展示区间统计，指定时间范围，查询统计信息，例如平均值，最大值，最小值，计数等。。。

3. 获取异常数据，根据指定条件，筛选异常数据

时序数据库应该具备的特点

1. 压缩能力

通常用得上时序数据库的业务，传感器产生的数据量都是非常庞大的，数据压缩可以降低存储成本。

2. 自动rotate

时序数据通常对历史数据的保留时间间隔是有规定的，例如一个线上时序数据业务，可能只需要保留最近1周的数据。

为了方便使用，时序数据库必须有数据自动rotate的能力。

3. 支持分片，水平扩展

因为涉及的传感器可能很多，单个节点可能比较容易成为瓶颈，所以时序数据库应该具备水平扩展的能力，例如分表应该支持水平分区。

4. 自动扩展分区，

业务对时序数据的查询，往往都会带上对时间区间进行过滤，因此时序数据通常在分区时，一定会有一个时间分区的概念。时序数据库务必能够支持自动扩展分区，减少用户的管理量，不需要人为的干预自动扩展分区。例如1月份月末，自动创建2月份的分区。

5. 插入性能

时序数据，插入是一个强需求。对于插入性能要求较高。

6. 分区可删除

分区可以被删除，例如保留1个月的数据，1个月以前的分区都可以删除掉。

7. 易用性(SQL接口)

SQL是目前最通用的数据库访问语言，如果时序数据库能支持SQL是最好的。

8. 类型丰富

物联网的终端各异，会有越来越多的非标准类型的支持需求。例如采集图像的传感器，数据库中至少要能够存取图像的特征值。而对于其他垂直行业也是如此，为了最大程度的诠释业务，必须要有精准的数据类型来支撑。

9. 索引接口

支持索引，毫无疑问是为了加速查询而引入的。

10. 高效分析能力

时序数据，除了单条的查询，更多的是报表分析或者其他的分析类需求。这对时序数据库的统计能力也是一个挑战。

11. 其他特色

11.1 支持丰富的数据类型，数组、范围类型、JSON类型、K-V类型、GIS类型、图类型等。满足更多的工业化需求，例如传感器的位置信息、传感器上传的数据值的范围，批量以数组或JSON的形式上传，传感器甚至可能上传图片特征值，便于图片的分析。（例如国家安全相关），轨迹数据的上层则带有GIS属性。

这个世界需要的是支持类型丰富的时序数据库，而不是仅仅支持简单类型的时序数据库。

11.2 支持丰富的索引接口，因为类型丰富了，普通的B-TREE索引可能无法满足快速的检索需求，需要更多的索引来支持 数组、JSON、GIS、图特征值、K-V、范围类型等。 (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引接口)

这两点可以继承PostgreSQL数据库的已有功能，已完全满足。

前提条件

• 实例版本为PostgreSQL 11、12、13。
   

  说明 如果实例版本为PostgreSQL 13，需要内核小版本大于等于20211130，如需升级内核小版本，请参见升级内核小版本。
• 使用该插件前，需要将timescaledb加入到shared_preload_libraries参数中。

  您可以使用RDS PostgreSQL参数设置功能，为shared_preload_libraries参数添加timescaledb。具体操作，请参见设置实例参数。

 

说明 部分存量用户可能已经创建过TimescaleDB插件，如果升级内核小版本后，出现如下类似提示：

 

ERROR:  could not access file "$libdir/timescaledb-1.3.0": No such file or directory

请在对应数据库执行如下SQL更新插件：

 

alter extension timescaledb update;

添加TimescaleDB插件

使用pgAdmin客户端连接实例，添加TimescaleDB，命令如下：

 

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

创建时序表

1. 创建标准表conditions，示例如下：
    

   CREATE TABLE conditions (
     time        TIMESTAMPTZ       NOT NULL,
     location    TEXT              NOT NULL,
     temperature DOUBLE PRECISION  NULL,
     humidity    DOUBLE PRECISION  NULL
   );

2. 创建时序表，示例如下：
    

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

 

说明 详细命令说明请参见Create a Hypertable。

高效写入

您可以使用标准SQL命令将数据插入超表（Hypertables），示例如下：

 

INSERT INTO conditions(time, location, temperature, humidity)
  VALUES (NOW(), 'office', 70.0, 50.0);

您还可以一次将多行数据插入到超表中，示例如下：

 

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

检索

您可以使用高级SQL查询检索数据，示例如下：

 

--过去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;

您也可以使用固有的函数进行分析查询，示例如下：

 

--均值查询（Median）
SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions;

 

--移动平均数（Moving Average）
SELECT time, AVG(temperature) OVER(ORDER BY time
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
    AS smooth_temp
  FROM conditions
  WHERE location = 'garage' and time > NOW() - interval '1 day'
  ORDER BY time DESC;