postgresql

TIMESCALEDB

  • Reliability
  • Robustness
  • Security
  • Ecosystem
  • Specifically for time-series data
9 min read
TIMESCALEDB

WHAT IS TIMESCALE DB?

TimescaleDB is a relational database for time-series data that is implemented as an extension to PostgreSQL, which means it executes within a PostgreSQL server as part of the same process, with code that offers new features for time-series data management.

ADVANTAGES OF TIMESCALE DB

  • Reliability 
  • Robustness
  • Security
  • Ecosystem
  • Specifically for time-series data

WHAT IS TIME SERIES DATA?

Time series data is frequently changing, meaning the data change with time. We can use this for various places like healthcare, DevOps monitoring, IoT, etc.

The two main concepts of TimescaleDB:

  • Hypertables 
  • Chunks

Hypertables contain singular tables; also, the TimescaleDB can be deployed as either a single node, with physical replicas, or as a multi-node cluster to enable distributed hyper tables.

Chunks have a lot of individual tables which store the data.

The hyper table’s data can be either one or two dimensions. It can be built up by a time interval and an  (optional) “partition key” value. Approximately all user interactions with TimescaleDB are with hyper tables. Creating tables, indexes, altering tables, selecting data, and inserting data must be executed on the hyper table.**

HOW TO INSTALL AND SET UP THE TIMESCALE DB EXTENSION IN POSTGRESQL?

Timescaledb, similarly to PostgreSQL, supports many different ways of installation, including installation on Ubuntu, Debian, RHEL/Centos, Windows, or cloud platforms.

Below command will pull a Docker image from Docker Hub** if it still needs to be installed and then run it.

.fusion-syntax-highlighter-52 > .CodeMirror, .fusion-syntax-highlighter-52 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-52 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-52 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# docker pull timescale/timescaledb-ha:pg14-latest.fusion-syntax-highlighter-53 > .CodeMirror, .fusion-syntax-highlighter-53 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-53 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-53 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# docker run -d --name timescale db -p 5432:5432 -e POSTGRES_PASSWORD=********* timescale/timescaledb## How to install Debian/Ubuntu

  • **** Add the TimescaleDB third-party repository:* PostgreSQL third-party repository to get the latest PostgreSQL packages:

.fusion-syntax-highlighter-54 > .CodeMirror, .fusion-syntax-highlighter-54 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-54 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-54 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt install gnupg postgresql-common apt-transport-https lsb-release wget          *  Run the PostgreSQL repository setup script:

.fusion-syntax-highlighter-55 > .CodeMirror, .fusion-syntax-highlighter-55 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-55 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-55 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh## In Debian: .fusion-syntax-highlighter-56 > .CodeMirror, .fusion-syntax-highlighter-56 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-56 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-56 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

  • Install Timescale GPG key

.fusion-syntax-highlighter-57 > .CodeMirror, .fusion-syntax-highlighter-57 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-57 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-57 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

  • Update your local repository list:

.fusion-syntax-highlighter-58 > .CodeMirror, .fusion-syntax-highlighter-58 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-58 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-58 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt update

  • Install TimescaleDB:

.fusion-syntax-highlighter-59 > .CodeMirror, .fusion-syntax-highlighter-59 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-59 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-59 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt install timescaledb-2-postgresql-14

  • Ensure that your apt repository is up to date. Then Install the PostgreSQL-client package:

.fusion-syntax-highlighter-60 > .CodeMirror, .fusion-syntax-highlighter-60 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-60 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-60 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt-get install postgresql-client## In Ubuntu: .fusion-syntax-highlighter-61 > .CodeMirror, .fusion-syntax-highlighter-61 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-61 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-61 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

  • Install Timescale GPG key

.fusion-syntax-highlighter-62 > .CodeMirror, .fusion-syntax-highlighter-62 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-62 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-62 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

  • Update your local repository list:

.fusion-syntax-highlighter-63 > .CodeMirror, .fusion-syntax-highlighter-63 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-63 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-63 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # apt update

  • Install TimescaleDB:

.fusion-syntax-highlighter-64 > .CodeMirror, .fusion-syntax-highlighter-64 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-64 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-64 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt install timescaledb-2-postgresql-14

  • Make sure your apt repository is up to date. Then Install the PostgreSQL-client package:

.fusion-syntax-highlighter-65 > .CodeMirror, .fusion-syntax-highlighter-65 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-65 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-65 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # apt-get install postgresql-client

  • Edit the config file in this path ** (vi /etc/postgresql/14/main/postgresql.conf)

** Add this line**  shared_preload_libraries = ‘timescaledb’**

  • Restart PostgreSQL and create the TimescaleDB extension:**

**

Restart the service after enabling TimescaleDB with timescale db-tune:

.fusion-syntax-highlighter-66 > .CodeMirror, .fusion-syntax-highlighter-66 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-66 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-66 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# systemctl restart postgresql

  • On your local system,open the psql command-line for  Postgres superuser:

.fusion-syntax-highlighter-67 > .CodeMirror, .fusion-syntax-highlighter-67 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-67 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-67 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# sudo -u postgres psql

  • Set the password for the Postgres user:

.fusion-syntax-highlighter-68 > .CodeMirror, .fusion-syntax-highlighter-68 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-68 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-68 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # \password postgresExit from the PostgreSQL:

.fusion-syntax-highlighter-69 > .CodeMirror, .fusion-syntax-highlighter-69 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-69 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-69 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# \q

  • Use psql client to connect to PostgreSQL:

.fusion-syntax-highlighter-70 > .CodeMirror, .fusion-syntax-highlighter-70 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-70 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-70 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# psql -U postgres -h localhost

  • At the psql prompt, create an empty database like  tsdb:

.fusion-syntax-highlighter-71 > .CodeMirror, .fusion-syntax-highlighter-71 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-71 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-71 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# CREATE database tsdb;

  • Then Connect to the database you created:

.fusion-syntax-highlighter-72 > .CodeMirror, .fusion-syntax-highlighter-72 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-72 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-72 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# \c tsdb

  • **Add the TimescaleDB extension:

CREATE EXTENSION IF NOT EXISTS timescaledb;**

  • **Check the TimescaleDB extension using the \dx command at the psql prompt. **

.fusion-syntax-highlighter-73 > .CodeMirror, .fusion-syntax-highlighter-73 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-73 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-73 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# \dx After you have established the extension and the database, you can connect to your database directly using this command:

.fusion-syntax-highlighter-74 > .CodeMirror, .fusion-syntax-highlighter-74 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-74 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-74 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# psql -U postgres -h localhost -d tsdb ## In RHEL/Centos : .fusion-syntax-highlighter-75 > .CodeMirror, .fusion-syntax-highlighter-75 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-75 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-75 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  • Create the Timescale repository

.fusion-syntax-highlighter-76 > .CodeMirror, .fusion-syntax-highlighter-76 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-76 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-76 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # tee /etc/yum.repos.d/timescale_timescaledb.repo

  • Install TimescaleDB:

.fusion-syntax-highlighter-77 > .CodeMirror, .fusion-syntax-highlighter-77 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-77 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-77 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# apt install timescaledb-2-postgresql-14

  • Make sure your  yum repository is up to date:

.fusion-syntax-highlighter-78 > .CodeMirror, .fusion-syntax-highlighter-78 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-78 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-78 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # yum update

  • Install the PostgreSQL-client package:

.fusion-syntax-highlighter-79 > .CodeMirror, .fusion-syntax-highlighter-79 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-79 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-79 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# dnf install postgresql14

  • Install the PostgreSQL-client package:

.fusion-syntax-highlighter-80 > .CodeMirror, .fusion-syntax-highlighter-80 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-80 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-80 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# yum install postgresql-client

  • Edit the config file  (vi /var/lib/pgsql/14/data/postgresql.conf)

Add this line**  shared_preload_libraries = ‘timescaledb’****

  • Restart PostgreSQL and create the TimescaleDB extension:Restart the service after enabling TimescaleDB with timescale db-tune: **

.fusion-syntax-highlighter-81 > .CodeMirror, .fusion-syntax-highlighter-81 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-81 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-81 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# systemctl restart postgresql

  • On your local system,open the psql command-line for  Postgres superuser:

.fusion-syntax-highlighter-82 > .CodeMirror, .fusion-syntax-highlighter-82 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-82 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-82 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # sudo -u postgres psql

  • Set the password for the Postgres user:

.fusion-syntax-highlighter-83 > .CodeMirror, .fusion-syntax-highlighter-83 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-83 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-83 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# \password postgres

  • Exit from the PostgreSQL:

.fusion-syntax-highlighter-84 > .CodeMirror, .fusion-syntax-highlighter-84 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-84 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-84 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # \q

  • Use psql client to connect to PostgreSQL:

.fusion-syntax-highlighter-85 > .CodeMirror, .fusion-syntax-highlighter-85 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-85 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-85 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # psql -U postgres -h localhost

  • At the psql prompt, create an empty database like  tsdb:

.fusion-syntax-highlighter-86 > .CodeMirror, .fusion-syntax-highlighter-86 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-86 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-86 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # CREATE database tsdb;

  • Then Connect to the database you created:

.fusion-syntax-highlighter-87 > .CodeMirror, .fusion-syntax-highlighter-87 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-87 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-87 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # \c tsdb

  • **Add the TimescaleDB extension:

**CREATE EXTENSION IF NOT EXISTS timescaledb;

  • **Check the TimescaleDB extension using the \dx command at the psql prompt. **

.fusion-syntax-highlighter-88 > .CodeMirror, .fusion-syntax-highlighter-88 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-88 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-88 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter # \dx

  • After you have established the extension and the database, you can connect to your database directly using this command:

.fusion-syntax-highlighter-89 > .CodeMirror, .fusion-syntax-highlighter-89 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-89 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-89 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter# psql -U postgres -h localhost -d tsdb ### About the Author### Aryamol RamananCloud Dev-Ops Engineer | Cloud Control

Cloud DevOps Engineer with more than 1 years of experience in supporting, automating, and optimizing deployments to hybrid cloud platforms using DevOps processes, tools, CI/CD, containers, and Kubernetes in both Production and Development environments.

TIMESCALEDB