The ApsaraDB RDS for PostgreSQL Basic Edition and ApsaraDB RDS for PostgreSQL Cluster Edition instances introduce the TimescaleDB 1.3.0 plug-in. The plug-in supports automatic sharding, efficient writing, retrieval, and near real-time aggregation of time-series data.

Currently, the TimescaleDB plug-in supported by ApsaraDB RDS for PostgreSQL 10 is an open-source edition and may not support some advanced features. For more information, see TimescaleDB.

Prerequisites

You can directly use the TimescaleDB 1.3.0 plug-in in instances that are created after May 20, 2019. To use the TimescaleDB 1.3.0 plug-in in the instances that are created before May 20, 2019, you can restart these instances.
Note If you receive the following error message after restarting an instance:
ERROR: could not access file "$libdir/timescaledb-0.8.0": No such file or directory.
Run the following SQL statement in the corresponding database to update the plug-in:
alter extension timescaledb update;

Add the TimescaleDB plug-in

Use the pgAdmin client to connect to an instance. Run the following command to add the TimescaleDB plug-in:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Create a time-series table

  1. Create a standard table conditions, for example:
    CREATE TABLE conditions (
      time        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
    );
  2. Create a time-series table, for example:
    SELECT create_hypertable('conditions', 'time');
Note For more information about the statement, see Create a hypertable.

Insert data into hypertables

You can run standard SQL commands to insert data into hypertables, for 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, for 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 use advanced SQL queries to retrieve data, for example:
-- Data is collected every 15 minutes in the past three hours and sorted 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 for analysis and query, for example:

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