TimescaleDB is a free, open-source, powerful database system powered by PostgreSQL. It is an extension of PostgreSQL and is specially designed to analyze time-series data with PostgreSQL. TimescaleDB is very similar to PostgreSQL, but it is optimized for speed and scale. Generally, relational databases are mainly used to store data, and they cannot handle the large volumes of time series data. This where TimescaleDB excels; it combines the speed of NoSQL databases and the ease-of-use of relational databases.

In this tutorial, we will show you how to install and use TimescaleDB on Ubuntu 20.04.

Step 1 – Install PostgreSQL Database

First, you will need to install a PostgreSQL server in your server. By default, it is available in the Ubuntu standard repository. You can install it easily using the following command:

apt-get install postgresql postgresql-contrib -y

Once the installation is completed, log in to PostgreSQL and set the postgres password:

su - postgres
psql -c "alter user postgres with password 'password'"

Next, exit from the PostgreSQL shell with the following command:

exit

Step 2 – Install TimescaleDB

By default, TimescaleDB is not available in the Ubuntu 20.04 default repository, so you will need to add the repository for it.

First, install all the required dependencies with the following command:

apt-get install gnupg2 software-properties-common curl git unzip -y

Once all the dependencies are installed, add the TimescaleDB repository with the following command:

add-apt-repository ppa:timescale/timescaledb-ppa -y

Next, install TimescaleDB by running the following command:

apt-get install timescaledb-postgresql-12 -y

Once the TimescaleDB is installed, it is recommended to tune some desired configurations. You can do it with the following command:

timescaledb-tune --quiet --yes

You should get the following output:

Next, restart the PostgreSQL service to apply the configuration changes:

systemctl restart postgresql

Step 3 – Create a New Database and Enable TimescaleDB

In this section, we will create a new database, enable TimescaleDB, and connect the database to it.

First, log in to the PostgreSQL shell and create a new database named testdb with the following command:

su - postgres
psql
CREATE DATABASE testdb;

Next, change the database to testdb and connect it to the TimescaleDB with the following command:

\c testdb
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

You should get the following output:

The time-series data are hypertables which consist of many individual tables. We will create a regular SQL table and then convert it into a hypertable via the function create_hypertable.

First, create a table to store temperature and humidity information.

CREATE TABLE conditions (
time        TIMESTAMP WITH TIME ZONE NOT NULL,
device_id   TEXT,
temperature  NUMERIC,
humidity     NUMERIC
);

Next, transform your table into a hypertable with the following command:

SELECT create_hypertable('conditions', 'time');

Next, insert some data into the hypertable with the following command:

INSERT INTO conditions(time, device_id, temperature, humidity)
VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

Next, insert multiple rows of data with the following command:

INSERT INTO conditions
VALUES
(NOW(), 'weather-pro-000002', 71.0, 51.0),
(NOW(), 'weather-pro-000003', 70.5, 50.5),
(NOW(), 'weather-pro-000004', 70.0, 50.2);

You can now verify your inserted data with the following command:

INSERT INTO conditions
VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

You should get the following output:

time              |     device_id      | temperature | humidity
-------------------------------+--------------------+-------------+----------
2020-12-03 15:48:24.329461+00 | weather-pro-000002 |        70.1 |     50.1
(1 row)

INSERT 0 1

You can also delete data from the hypertable using the following command:

DELETE FROM conditions WHERE temperature > 80;

Once the data is deleted, you can run the VACUUM command to reclaim space still used by data that has been deleted.

VACUUM conditions;

Conclusion

Congratulations! You have successfully installed and configured TimescaleDB on Ubuntu 20.04. You can now use TimescaleDB for storing time-series data and use those data to create graphs. Get started with TimescaleDB on VPS hosting from Atlantic.Net!