The release of the new version of TimescaleDB 1.7 was announced, version in which added support for PostgreSQL 12 is highlighted, as well as in the modification of some functions. For those unaware of TimescaleDB, they should know that is a database designed to store and process data in the form of time series (segments of parameter values at given time intervals, the register forms the time and a set of values corresponding to this time).
This form of storage is optimal for applications such as monitoring systems, trading platforms, systems to collect metrics and sensor statuses.
About TimescaleDB
The TimescaleDB project is implemented as a PostgreSQL extension and is distributed under the Apache 2.0 license. Some of the code with advanced features is delivered under a proprietary separate Timescale license (TSL), which does not allow changes, prohibits the use of code in third-party products, and does not allow free use in cloud databases (database such as service).
The interesting part of TimescaleDB, is that allows you to use full SQL queries to analyze accumulated data, combining the ease of use inherent in relational DBMS with the scalability and capabilities inherent in specialized NoSQL systems.
The storage structure is optimized to provide a high data aggregation rate. Supports batch aggregation data sets, using indices stored in RAM, retroactively loading historical segments, applying transactions.
A key feature of TimescaleDB is the support for automatic partitiona (partition) of the data array. The incoming data stream is automatically distributed among the partitioned tables.
Sections are created based on time (each section stores data for a certain period of time) or relative to an arbitrary key (eg device identifier, location, etc.). Partitioned tables can be spread across different drives to optimize performance.
For queries, a partitioned database looks like a large table, called a hypertable. A hypertable is a virtual representation of many separate tables in which incoming data is accumulated.
What's new in TimescaleDB 1.7?
In this new version the support for the integration with PostgreSQL 12 DBMS, while support for PostgreSQL 9.6.x and 10.x has been deprecated, although for Timescale 2.0 only support for PostgreSQL 11+ will remain.
It is also highlighted that changed the behavior of queries with continuously executed aggregate functions (aggregation of continuously incoming data in real time).
Such queries now combine materialized views with newly arrived data that has not yet materialized (previously, aggregation only covered already materialized data). The new behavior is used for newly created continuous aggregations.
Moreover, some advanced data lifecycle management tools have been moved to the community version of the commercial edition, including the ability to regroup data and process policies to move stale data (allow you to store only current data and automatically delete, add, or archive stale records).
How to install TimescaleDB on Linux?
For those who are interested to be able to install TimescaleDB on your systemThey can do so by following the instructions we share below.
In the case of those that are Ubuntu users:
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add – sudo add-apt-repository ppa:timescale/timescaledb-ppa sudo apt-get update sudo apt install timescaledb-postgresql-11
In the case of debbian:
sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/debian/ `lsb_release -c -s` main' > /etc/apt/sources.list.d/timescaledb.list" wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add - sudo apt-get update sudo apt-get install timescaledb-postgresql-11
RHEL / CentOS:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL [timescale_timescaledb] name=timescale_timescaledb baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch repo_gpgcheck=1 gpgcheck=0 enabled=1 gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey sslverify=1 sslcacert=/etc/pki/tls/certs/ca-bundle.crt metadata_expire=300 EOL
sudo yum update -y sudo yum install -y timescaledb-postgresql-11
Now we are going to configure the database with:
sudo timescaledb-tune
Here various configurations can be made, of which you can consult In the following link.
At the end, just restart the service:
sudo service postgresql restart