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

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

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

前提條件

執行個體大版本為PostgreSQL 10或以上版本。
執行個體核心小版本至少升級到20230330。針對RDS PostgreSQL 17版本的執行個體，其核心小版本為20241030或以上。查看和升級核心小版本，請參見升級核心小版本。
重要
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外掛程式，如果升級核心小版本後，出現如下類似提示：

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

請在對應資料庫執行如下SQL更新外掛程式：

alter extension timescaledb update;

步驟一：添加TimescaleDB外掛程式

通過控制台安裝外掛程式

訪問RDS執行個體列表，在上方選擇地區，然後單擊目標執行個體ID。
在左側導覽列單擊外掛程式管理。
在外掛程式市場頁面中，請向下滾動以找到timescaledb外掛程式，並單擊安裝。
在彈出的視窗中選擇目標資料庫和高許可權帳號後，單擊安裝，將外掛程式安裝至目標資料庫。
當執行個體的狀態由維護執行個體中變為運行中時，表示外掛程式已成功安裝。

通過SQL命令安裝外掛程式

設定執行個體參數，在shared_preload_libraries的運行參數值中添加timescaledb。例如，將運行參數值改為'pg_stat_statements,auto_explain,timescaledb'。
串連目標資料庫，執行以下SQL安裝外掛程式。
```
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
```
說明
執行SELECT * FROM pg_extension;可以查看已安裝的外掛程式。

步驟二：建立時序表

串連目標資料庫，建立標準表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官方協議要求，不允許使用TSL（Timescale License Agreement）協議的功能提供database-as-a-service和software-as-a-service。更多資訊，請參見TimescaleDB官方協議的2.2章節。
Q：使用外掛程式時遇到hypertables do not support concurrent index creation.的錯誤，應該如何處理？
A：TimescaleDB官方目前不支援使用 CREATE INDEX CONCURRENTLY 方法建立索引。您可以使用CREATE INDEX ... WITH (timescaledb.transaction_per_chunk, ...)的方式來建立索引，從而盡量減少持鎖時間。更多資訊，請參見Timescale documentation。