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.
To follow this tutorial, you will need:
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
Next, refresh your APT cache to update your package lists:
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
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:
First, you will be asked to confirm the path to the PostgreSQL configuration file:
OutputUsing 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
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:
Outputshared_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.
Note: If a library specified by
shared_preload_libraries is not found, the database server will fail to start. Keep this in mind when debugging applications that make use of
shared_preload_libraries. For more information on this, see this PostgresqlCO.NF article on
Enable the TimescaleDB module by typing
y at this prompt and pressing
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:
OutputTune 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
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.
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:
OutputParallelism 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]:
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):
OutputWAL 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
Output... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated
You’ll now find some miscellaneous recommendations:
OutputMiscellaneous 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.
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
Note: If you are automating the installation, you could also run the initial command with the
--yes flags, which will automatically apply all the recommendations and will make changes to the
postgresql.conf configuration file:
- 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:
Now create a new database and connect to it. This tutorial will name the database
- 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:
OutputWARNING: 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
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:
Outputcreate_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
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:
OutputINSERT 0 1
You can also insert multiple rows of data at once. Try the following:
- INSERT INTO conditions
- (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:
OutputINSERT 0 3
You can also specify that the
INSERT command will return some or all of the inserted data using the
- INSERT INTO conditions
- VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;
You will see the following output:
Outputtime | 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
- 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.
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:
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:
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:
Outputtime | 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
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
- SELECT percentile_cont(0.5)
- WITHIN GROUP (ORDER BY temperature)
- FROM conditions
- WHERE device_id = 'weather-pro-000000';
You will see the following output:
Outputpercentile_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
- 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
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:
Outputhour | 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_segmentby = 'device_id'
You will receive the following data:
OutputNOTICE: 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
Note: You can also set up TimescaleDB to compress data over the specified time period. For example, you could run:
- SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');
In this example, the data will be automatically compressed after a week.
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:
Outputdrop_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:
Next, edit your
crontab with the following command, which should be run from the shell:
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.
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.