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 10 or later.
The minor engine version of your RDS instance is 20230330 or later. If your RDS instance runs PostgreSQL 17, the minor engine of the instance must be 20241030 or later. For more information about how to view and update the minor engine version of your RDS instance, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
Important
The extension is supported in minor engine versions that are earlier than 20230330. However, the extensions that are supported for ApsaraDB RDS for PostgreSQL instances are changed. Starting April 17, 2023, some extensions can no longer be created for RDS instances that run minor engine versions earlier than 20230330. For more information, see [Notice] Starting April 17, 2023, some extensions can no longer be created for ApsaraDB RDS for PostgreSQL instances that run earlier minor engine versions.
If your RDS instance runs PostgreSQL 14 with a minor engine version of earlier than 20230330 and uses the TimescaleDB extension of version 2.5.0 or earlier, you must execute the ALTER EXTENSION timescaledb UPDATE;
statement to manually update the extension after you update the minor engine version to 20230330 or later. This way, the extension can run as expected. If you do not update the minor engine version of your RDS instance, the extension is not affected.
If your RDS instance runs PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, or PostgreSQL 15 with a minor engine version of earlier than 20230330 and uses the TimescaleDB extension, the extension is not affected.
If this is the first time you create the extension or you re-create the extension, you must update the minor engine version of your RDS instance to 20230330 or later.
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 by using the parameter modification feature of ApsaraDB RDS for PostgreSQL. For more information, see Modify 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
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 time series table
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 time series table. Example:
SELECT create_hypertable('conditions', 'time');
Insert data into a time series table
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);
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 Timescale License Agreement (TSL Agreement) 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.