时序数据库(TimescaleDB)

更新时间:2025-01-07 06:42:44

RDS PostgreSQL实例新增TimescaleDB插件版本,支持时序数据的自动分片、高效写入、检索、准实时聚合等。

目前RDS PostgreSQL对外开放的是Open Source版本的TimescaleDB,由于License等问题,可能暂不支持一些高级特性,详情参见TimescaleDB

您可以加入RDS PostgreSQL插件交流钉钉群(103525002795),进行咨询、交流和反馈,获取更多关于插件的信息。

前提条件

  • 实例大版本为PostgreSQL 10或以上版本。

  • 实例内核小版本至少升级到20230330。针对RDS PostgreSQL 17版本的实例,其内核小版本为20241030或以上。查看和升级内核小版本,请参见升级内核小版本

    重要

    20230330内核小版本之前已支持此插件,但RDS PostgreSQL支持插件策略已于2023417日调整,部分插件在20230330或以上内核小版本才能创建,更多信息,请参见【通知】2023417日起RDS PostgreSQL部分插件在低内核版本限制创建

    • 如果您的实例大版本为PostgreSQL 14,在20230330内核小版本前已使用了TimescaleDB插件(插件版本小于等于2.5.0),在不升级内核小版本时,不影响使用,在升级内核小版本到20230330或以上版本后,必须手动执行ALTER EXTENSION timescaledb UPDATE;命令进行插件的手动升级后,才能继续使用。

    • 如果您的实例大版本为PostgreSQL 15、13、12、1110,在20230330内核小版本前已使用了TimescaleDB插件,不影响使用。

    • 如果您首次创建或重新创建此插件,请升级内核小版本到20230330或以上版本。

  • 使用该插件前,需要将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插件

连接PostgreSQL实例,添加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;

    返回结果:

          fifteen_min       | location | count | max_temp | max_hum 
    ------------------------+----------+-------+----------+---------
     2024-12-20 16:00:00+08 | garage   |     1 |       77 |    65.2
     2024-12-20 16:00:00+08 | office   |     2 |       70 |      50
     2024-12-20 16:00:00+08 | basement |     1 |     66.5 |      60

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

    • 均值查询

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

      返回结果:

       percentile_cont 
      -----------------
                    70
    • 移动平均数

      --移动平均数(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;

      返回结果:

                   time              | smooth_temp 
      -------------------------------+-------------
       2024-12-20 16:11:14.691822+08 |          77

常见问题

Q:使用插件时遇到如下报错如何处理?

ERROR:  functionality not supported under the current license "ApacheOnly", license
HINT:  Upgrade to a Timescale-licensed binary to access this free community feature

A:TimescaleDB官方协议要求,不允许使用TSL(Timescale License Agreement)协议的功能提供database-as-a-servicesoftware-as-a-service。更多信息,请参见TimescaleDB官方协议2.2章节。

  • 本页导读 (1)
  • 前提条件
  • 添加TimescaleDB插件
  • 创建时序表
  • 高效写入
  • 检索
  • 常见问题