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.
ERROR: could not access file "$libdir/timescaledb-0.8.0": No such file or directory.
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
- 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 );
- Create a time-series table, for example:
SELECT create_hypertable('conditions', 'time');
Insert data into 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);
-- 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;