為RDS PostgreSQL執行個體添加TimescaleDB外掛程式 - ApsaraDB RDS

RDS PostgreSQL執行個體新增TimescaleDB外掛程式版本，支援時序資料的自動分區、高效寫入、檢索、准即時彙總等。

目前RDS PostgreSQL對外開放的是Open Source版本的TimescaleDB，由於License等問題，可能暫不支援一些進階特性，詳情參見TimescaleDB。

前提條件

執行個體大版本為PostgreSQL 10或以上版本。
執行個體核心小版本至少升級到20230330。查看和升級核心小版本，請參見升級核心小版本。
重要
20230330核心小版本之前已支援此外掛程式，但RDS PostgreSQL支援外掛程式策略已於2023年4月17日調整，部分外掛程式在20230330或以上核心小版本才能建立，更多資訊，請參見【通知】2023年4月17日起RDS PostgreSQL部分外掛程式在低核心版本限制建立。
- 如果您的執行個體大版本為PostgreSQL 14，在20230330核心小版本前已使用了TimescaleDB外掛程式（外掛程式版本小於等於2.5.0），在不升級核心小版本時，不影響使用，在升級核心小版本到20230330或以上版本後，必須手動執行ALTER EXTENSION timescaledb UPDATE;命令進行手動升級外掛程式後，才能繼續使用。
- 如果您的執行個體大版本為PostgreSQL 15、13、12、11或10，在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;

建立時序表

建立標準表conditions，樣本如下：

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

建立時序表，樣本如下：

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官方協議要求，不允許使用TLS協議的功能提供database-as-a-service和software-as-a-service。更多資訊，請參見TimescaleDB官方協議的2.2章節。