This topic describes how to use the TimescaleDB plug-in on an ApsaraDB RDS for PostgreSQL instance. The TimescaleDB plug-in 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 plug-in and may not support specific advanced features due to license issues. For more information, see TimescaleDB.

Prerequisites

Note If the TimescaleDB plug-in 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 error, you must execute the following SQL statement on your RDS instance to update the TimescaleDB plug-in:

alter extension timescaledb update;

Create the TimescaleDB plug-in

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 plug-in:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Create a hypertable

  1. 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
    );
  2. Create a hypertable. Example:
    SELECT create_hypertable('conditions', 'time');
Note For more information, see Create a Hypertable.

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 most recent 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 analyze and query 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;