Use the TimescaleDB extension - RDS PostgreSQL Database| Alibaba Cloud Documentation Center

This topic describes how to use the TimescaleDB extension on an ApsaraDB RDS for PostgreSQL instance. The TimescaleDB extension supports the automatic sharding, efficient writes, retrieval, and near real-time aggregation of time series data.

ApsaraDB RDS for PostgreSQL supports only the open source TimescaleDB extension and may not support specific advanced features due to license issues. For more information, see TimescaleDB.

Prerequisites

Your RDS instance runs PostgreSQL 11, PostgreSQL 12, or PostgreSQL 13.

Note If your RDS instance runs PostgreSQL 13, the minor engine version of your RDS instance must be 20211130 or later. For more information about how to update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
timescaledb is added to the value of the shared_preload_libraries parameter.
You can add timescaledb to the value of the shared_preload_libraries parameter in the ApsaraDB RDS console or by using an ApsaraDB RDS API operation. For more information, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance.

Note If the TimescaleDB extension has been created, an error message similar to the following message may appear after you update the minor engine version of your RDS instance:

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

To resolve the issue, you must execute the following SQL statement on your RDS instance to update the TimescaleDB extension:

alter extension timescaledb update;

Create the TimescaleDB extension

Use pgAdmin to connect to your RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance. Then, execute the following statement to create the TimescaleDB extension:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Create a hypertable

Create a standard table named conditions. Example:

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

Create a hypertable. Example:

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

Note For more information, see Create hypertables.

Insert data into a hypertable

You can execute standard SQL statements to insert data into a hypertable. Example:

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

You can also insert multiple rows of data into a hypertable at a time. Example:

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

Retrieve data

You can run advanced SQL queries to retrieve data. Example:

--Collect data from the last 3 hours at a 15-minute interval, and sort the data by time and temperature. 
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;

You can also use built-in functions to query and analyze data. Examples:

--Query the median.
SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions;

--Query the 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;

FAQ

What do I do if I encounter the following error when I use the TimescaleDB extension?

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

The TimescaleDB license agreement states that you are prohibited from using any TSL Licensed Software to provide database-as-a-service services or any form of software-as-a-service. For more information, see Section 2.2 of the TimescaleDB License Agreement.