How To Install and Use TimescaleDB on Ubuntu 18.04

The author selected the Computer History Museum to receive a donation as part of the Write for DOnations program.

Introduction

Many applications, such as monitoring systems and data collection systems, accumulate data for further analysis. These analyses often look at the way a piece of data or a system changes over time. In these instances, data is represented as a time series, with every data point accompanied by a timestamp. An example would look like this:

2019-11-01 09:00:00    server.cpu.1    0.9
2019-11-01 09:00:00    server.cpu.15   0.8
2019-11-01 09:01:00    server.cpu.1    0.9
2019-11-01 09:01:00    server.cpu.15   0.8
...

Managing time series data has become an essential skill with the rise of the Internet of Things (IoT) and Industrial Internet of Things. There are more and more devices that collect various time series information: fitness trackers, smart watches, home weather stations, and various sensors, to name a few. These devices collect a lot of information, and all this data must be stored somewhere.

Classic relational databases are most often used to store data, but they don’t always fit when it comes to the huge data volumes of time series. When you need to process a large amount of time series data, relational databases can be too slow. Because of this, specially optimized databases, called NoSQL databases, have been created to avoid the problems of relational databases.

TimescaleDB is an open-source database optimized for storing time series data. It is implemented as an extension of PostgreSQL and combines the ease-of-use of relational databases and the speed of NoSQL databases. As a result, it allows you to use PostgreSQL for both storing business data and time series data in one place.

By following this tutorial, you’ll set up TimescaleDB on Ubuntu 18.04, configure it, and learn how to work with it. You’ll create time series databases and make simple queries. Finally, you’ll see how to get rid of unnecessary data.

Prerequisites

To follow this tutorial, you will need:

• One Ubuntu 18.04 server set up by following our Initial Server Setup Guide for Ubuntu 18.04, including a non-root user with sudo privileges and a firewall.
• PostgreSQL installed on your server. Follow Step 1 of How To Install and Use PostgreSQL on Ubuntu 18.04 to install it.

Step 1 — Installing TimescaleDB

TimescaleDB is not available in Ubuntu’s default package repositories, so in this step you will install it from the TimescaleDB Personal Packages Archive (PPA).

First, add Timescale’s APT repository:

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

Confirm this action by hitting the ENTER key.

Next, refresh your APT cache to update your package lists:

sudo apt update

You can now proceed with the installation. This tutorial uses PostgreSQL version 10; if you are using a different version of PostgreSQL (11 or 9.6, for example), replace the value in the following command and run it:

sudo apt install timescaledb-postgresql-10 

TimescaleDB is now installed and ready to be used. Next, you will turn it on and adjust some of the settings associated with it in the PostgreSQL configuration file to optimize the database.

Step 2 — Configuring TimescaleDB

The TimescaleDB module works fine with the default PostgreSQL configuration settings, but to improve performance and make better use of processor, memory, and disk resources, the developers of TimescaleDB suggest configuring some individual parameters. This can be done automatically with the timescaledb-tune tool or by manually editing your server’s postgresql.conf file.

In this tutorial, you will use the timescaledb-tune tool, which will read the postgresql.conf file and interactively suggest making changes.

Run the following command to start the configuration wizard:

sudo timescaledb-tune

First, you will be asked to confirm the path to the PostgreSQL configuration file:

Output
Using postgresql.conf at this path:
/etc/postgresql/10/main/postgresql.conf

Is this correct? [(y)es/(n)o]:

The utility automatically detects the path to the configuration file, so confirm this by entering y:

Output
...
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201911181111

Next, you will be prompted to change the shared_preload_libraries variable to preload the TimescaleDB module upon starting the PostgreSQL server:

Output
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]:

shared_preload_libraries accepts a comma separated list of modules as a value, designating which modules PostgreSQL should load before starting the database server. Making this change will add the timescaledb module to that list.

Enable the TimescaleDB module by typing y at this prompt and pressing ENTER:

Output
...
Is this okay? [(y)es/(n)o]:  y
success: shared_preload_libraries will be updated

Based on the characteristics of your server and the PostgreSQL version, the script will then offer to tune your settings. Press y to start the tuning process:

Output
Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]:  y
Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1994MB
effective_cache_size = 5982MB
maintenance_work_mem = 1021001kB
work_mem = 5105kB
Is this okay? [(y)es/(s)kip/(q)uit]:

timescaledb-tune will automatically detect the servers’s available memory and calculate recommended values for a number of settings. shared_buffers, for example, determines the amount of memory allocated for caching data. By default this setting is relatively low to account for a wider range of platforms, so timescaledb-tune has suggested increasing the value from 128MB to 1994MB, taking better advantage of resources by making more room to store cached information like repeated queries. The work_mem variable has been increased as well to allow for more complicated sorts.

If you would like to learn more about the process of tuning memory settings for PostgreSQL, see the Tuning Your PostgreSQL Server article on the PostgreSQL wiki.

Enter y to accept the values:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]:  y
success: memory settings will be updated

At this point, if your server has multiple CPUs, you will find the recommendations for parallelism settings. These settings determine how multiple CPUs can make simultaneous queries in parallel to scan databases and return the requested data quicker.

Those with multiple CPUs will encounter recommendations like this:

Output
Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 13
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
Is this okay? [(y)es/(s)kip/(q)uit]:

These settings regulate the number of workers, which process requests and background tasks. You can learn more about these settings from the TimescaleDB and PostgreSQL documentation.

Type y then ENTER to accept these settings:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated

Next, you will find recommendations for Write Ahead Log (WAL):

Output
WAL settings recommendations
Current:
#wal_buffers = -1
#min_wal_size = 80MB
#max_wal_size = 1GB
Recommended:
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
Is this okay? [(y)es/(s)kip/(q)uit]:

WAL is a logging method in which PostgreSQL logs changes to data files before the changes are made to the database. By prioritizing an up-to-date record of data changes, WAL ensures that you can reconstruct your database in the event of a crash. In this way, it preserves data integrity. However, the default settings can cause inefficient input/output (I/O) operations that slow down write performance. To fix this, type and enter y:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated

You’ll now find some miscellaneous recommendations:

Output
Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.5
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200
Is this okay? [(y)es/(s)kip/(q)uit]:

All of these different parameters are aimed at increasing performance. For example, SSDs can process many concurrent requests, so the best value for the effective_io_concurrency might be in the hundreds. You can find more info about these options in the PostgreSQL documentation.

Press y then ENTER to continue.

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /etc/postgresql/10/main/postgresql.conf

As a result, you will get a ready-made configuration file at /etc/postgresql/10/main/postgresql.conf.

sudo timescaledb-tune --quiet --yes

In order for the configuration changes to take effect, you must restart the PostgreSQL service:

sudo systemctl restart postgresql.service

Now the database is running with optimal parameters and is ready to work with the time series data. In the next steps, you’ll try out working with this data: creating new databases and hypertables and performing operations.

Step 3 — Creating a New Database and Hypertable

With your TimescaleDB setup optimized, you are ready to work with time series data. TimescaleDB is implemented as an extension of PostgreSQL, so operations with time series data are not much different from relational data operations. At the same time, the database allows you to freely combine data from time series and relational tables in the future.

To demonstrate this, you will use PostgreSQL commands to create a database, then enable the TimescaleDB extension to create a hypertable, which is a higher-level abstraction of many individual tables. Hypertables are the main structures you will work with in TimescaleDB.

Log into your PostgreSQL database:

sudo -u postgres psql

Now create a new database and connect to it. This tutorial will name the database timeseries:

CREATE DATABASE timeseries;
\c timeseries

You can find additional information about working with the PostgreSQL database in our How To Create, Remove & Manage Tables in PostgreSQL on a Cloud Server tutorial.

Finally, enable the TimescaleDB extension:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

You will see the following output:

Output
WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.5.1
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION

As mentioned earlier, the primary points of interaction with your time series data are hypertables, which consist of many individual tables holding data, called chunks.

To create a hypertable, start with a regular SQL table and then convert it into a hypertable via the function create_hypertable.

Make a table that will store data for tracking temperature and humidity across a collection of devices over time:

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

This command creates a table called conditions with four columns. The first column will store the timestamp, which includes the time zone and cannot be empty. Next, you will use the time column to transform your table into a hypertable that is partitioned by time:

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

This command calls the create_hypertable() function, which creates a TimescaleDB hypertable from a PostgreSQL table, replacing the latter.

You will receive the following output:

Output
    create_hypertable
-------------------------
 (1,public,conditions,t)
(1 row)

In this step, you created a new hypertable to store time series data. Now you can populate it with data by writing to the hypertable, then run through the process of deleting it.

Step 4 — Writing and Deleting Data

In this step, you will insert data using standard SQL commands and import large sets of data from external sources. This will show you the relational database aspects of TimescaleDB.

First, try out the basic commands. You can insert data into the hypertable using the standard INSERT SQL command. Insert some sample temperature and humidity data for the theoretical device weather-pro-000000 using the following command:

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

You’ll see the following output:

Output
INSERT 0 1

You can also insert multiple rows of data at once. Try the following:

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 will receive the following:

Output
INSERT 0 3

You can also specify that the INSERT command will return some or all of the inserted data using the RETURNING statement:

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

You will see the following output:

Output
             time              |     device_id      | temperature | humidity 
-------------------------------+--------------------+-------------+----------
 2019-09-15 14:14:01.576651+00 | weather-pro-000002 |        70.1 |     50.1
(1 row)

If you want to delete data from the hypertable, use the standard DELETE SQL command. Run the following to delete whatever data has a temperature higher than 80 or a humidity higher than 50:

DELETE FROM conditions WHERE temperature > 80;
DELETE FROM conditions WHERE humidity > 50;

After the delete operation, use the VACUUM command to reclaim space still used by data that has been deleted.

VACUUM conditions;

You can find more info about VACUUM command in the PostgreSQL documentation.

These commands are fine for small-scale data entry, but since time series data often generates huge datasets from multiple devices simultaneously, it’s essential also to know how to insert hundreds or thousands of rows at a time. If you have prepared data from external sources in a structured form, for example in csv format, this task can be accomplished quickly.

To test this out, you will use a sample dataset that represents temperature and humidity data from a variety of locations. This is an official TimescaleDB dataset, made to test out their database. You can check out more info about sample datasets in the TimescaleDB documentation.

Let’s see how you can import data from the weather_small sample dataset into your database. First, quit Postgresql:

\q

Then download the dataset and extract it:

wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
tar -xvzf weather_small.tar.gz

Next, import the temperature and humidity data into your database:

sudo -u postgres psql -d timeseries -c "\COPY conditions FROM weather_small_conditions.csv CSV"

This connects to the timeseries database and executes the \COPY command that copies the data from the chosen file into the conditions hypertable. It will run for a few seconds.

When the data has been entered into your table, you will receive the following output:

Output
COPY 1000000

In this step, you added data to the hypertable manually and in batches. Next, continue on to performing queries.

Step 5 — Querying Data

Now that your table contains data, you can perform various queries to analyze it.

To get started, log in to the database:

sudo -u postgres psql -d timeseries

As mentioned before, to work with hypertables you can use standard SQL commands. For example, to show the last 10 entries from the conditions hypertable, run the following command:

SELECT * FROM conditions LIMIT 10;

You will see the following output:

Output
          time          |     device_id      |    temperature     | humidity
------------------------+--------------------+--------------------+----------
 2016-11-15 12:00:00+00 | weather-pro-000000 |               39.9 |     49.9
 2016-11-15 12:00:00+00 | weather-pro-000001 |               32.4 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 |     50.2
 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000004 |               71.8 |     50.1
 2016-11-15 12:00:00+00 | weather-pro-000005 |               71.8 |     49.9
 2016-11-15 12:00:00+00 | weather-pro-000006 |                 37 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000007 |                 72 |       50
 2016-11-15 12:00:00+00 | weather-pro-000008 |               31.3 |       50
 2016-11-15 12:00:00+00 | weather-pro-000009 |               84.4 |     87.8
(10 rows)

This command lets you see what data is in the database. Since the database contains a million records, you used LIMIT 10 to limit the output to 10 entries.

To see the most recent entries, sort the data array by time in descending order:

SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

This will output the top 20 most recent entries.

You can also add a filter. For example, to see entries from the weather-pro-000000 device, run the following:

SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

In this case, you will see the 10 most recent temperature and humidity datapoints recorded by the weather-pro-000000 device.

In addition to standard SQL commands, TimescaleDB also provides a number of special functions that are useful for time series data analysis. For example, to find the median of the temperature values, you can use the following query with the percentile_cont function:

SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions
  WHERE device_id = 'weather-pro-000000';

You will see the following output:

Output
 percentile_cont 
-----------------
            40.5
(1 row)

In this way, you’ll see the median temperature for the entire observation period where the weather-pro-00000 sensor is located.

To show the latest values from each of the sensors, you can use the last function:

select device_id, last(temperature, time)
  FROM conditions
  GROUP BY device_id;

In the output you will see a list of all the sensors and relevant latest values.

To get the first values use the first function.

The following example is more complex. It will show the hourly average, minimum, and maximum temperatures for the chosen sensor within the last 24 hours:

SELECT time_bucket('1 hour', time) "hour",
trunc(avg(temperature), 2) avg_temp,
trunc(min(temperature), 2) min_temp,
trunc(max(temperature), 2) max_temp
FROM conditions
WHERE device_id = 'weather-pro-000000'
GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

Here you used the time_bucket function, which acts as a more powerful version of the PostgreSQL date_trunc function. As a result, you will see which periods of the day the temperature rises or decreases:

Output
          hour          | avg_temp | min_temp | max_temp
------------------------+----------+----------+----------
 2016-11-16 21:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-16 20:00:00+00 |    41.92 |    41.69 |    42.00
 2016-11-16 19:00:00+00 |    41.07 |    40.59 |    41.59
 2016-11-16 18:00:00+00 |    40.11 |    39.79 |    40.59
 2016-11-16 17:00:00+00 |    39.46 |    38.99 |    39.79
 2016-11-16 16:00:00+00 |    38.54 |    38.19 |    38.99
 2016-11-16 15:00:00+00 |    37.56 |    37.09 |    38.09
 2016-11-16 14:00:00+00 |    36.62 |    36.39 |    37.09
 2016-11-16 13:00:00+00 |    35.59 |    34.79 |    36.29
 2016-11-16 12:00:00+00 |    34.59 |    34.19 |    34.79
 2016-11-16 11:00:00+00 |    33.94 |    33.49 |    34.19
 2016-11-16 10:00:00+00 |    33.27 |    32.79 |    33.39
 2016-11-16 09:00:00+00 |    33.37 |    32.69 |    34.09
 2016-11-16 08:00:00+00 |    34.94 |    34.19 |    35.49
 2016-11-16 07:00:00+00 |    36.12 |    35.49 |    36.69
 2016-11-16 06:00:00+00 |    37.02 |    36.69 |    37.49
 2016-11-16 05:00:00+00 |    38.05 |    37.49 |    38.39
 2016-11-16 04:00:00+00 |    38.71 |    38.39 |    39.19
 2016-11-16 03:00:00+00 |    39.72 |    39.19 |    40.19
 2016-11-16 02:00:00+00 |    40.67 |    40.29 |    40.99
 2016-11-16 01:00:00+00 |    41.63 |    40.99 |    42.00
 2016-11-16 00:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-15 23:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-15 22:00:00+00 |    42.00 |    42.00 |    42.00
(24 rows)

You can find more useful functions in the TimescaleDB documentation.

Now you know how to handle your data. Next, you will go through how to delete unnecessary data and how to compress data.

Step 6 — Configuring Data Compression and Deletion

As data accumulates, it will take up more and more space on your hard drive. To save space, the latest version of TimescaleDB provides a data compression feature. This feature doesn’t require tweaking any file system settings, and can be used to quickly make your database more efficient. For more information on how this compression works, take a look at this Compression article from TimescaleDB.

First, enable the compression of your hypertable:

ALTER TABLE conditions SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

You will receive the following data:

Output
NOTICE:  adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num)
ALTER TABLE

SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

You can see the statistics on the compressed data with the command:

SELECT * 
FROM timescaledb_information.compressed_chunk_stats;

You will then see a list of chunks with their statuses: compression status and how much space is taken up by uncompressed and compressed data in bytes.

If you don’t have the need to store data for a long period of time, you can delete out-of-date data to free up even more space. There is a special drop_chunks function for this. It allows you to delete chunks with data older than the specified time:

SELECT drop_chunks(interval '24 hours', 'conditions');

This query will drop all chunks from the hypertable conditions that only include data older than a day ago.

You will receive the following output:

Output
              drop_chunks
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk
(1 row)

To automatically delete old data, you can configure a cron task. See our tutorial to learn more about how to use cron to automate various system tasks.

Exit from the database:

\q

Next, edit your crontab with the following command, which should be run from the shell:

crontab -e

Now add the following line to the end of the file:

...

0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1

This job will delete obsolete data that is older than one day at 1:00 AM every day.

Conclusion

You’ve now set up TimescaleDB on your Ubuntu 18.04 server. You also tried out creating hypertables, inserting data into it, querying the data, compressing, and deleting unnecessary records. With these examples, you’ll be able to take advantage of TimescaleDB’s key benefits over traditional relational database management systems for storing time-series data, including:

• Higher data ingest rates
• Quicker query performance
• Time-oriented features

Now that you know how to store time series data, you could use the data to create graphs. TimescaleDB is compatible with visualization tools that work with PostgreSQL, like Grafana. You can use our How To Install and Secure Grafana on Ubuntu 18.04 tutorial to learn more about this popular visualization tool.