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.
- 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 a later version. For more information about how to update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
- Add timescaledb 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 the ApsaraDB RDS API. For more information, see Manage the parameters of an ApsaraDB RDS for PostgreSQL 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
- 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');
Insert data into a hypertable
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);
--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;