⏳TimescaleDB

Find all the information about how to set up your TimescaleDB instance for Speculare. It will be the core of the whole infrastructure for Speculare.

Speculare is built on top of TimescaleDB which is itself built on top of PostgreSQL and is an extremely performant Relational / Timeseries Database.

As we need to use some advanced PostgreSQL functionality and to ease the development of our project, we rely on some features of PostgreSQL that need to be enabled as well as some plugins. We'll cover here the configuration needed to be able to run Speculare correctly.

In this document (and the others) I might refer to PostgreSQL for TimescaleDB as they are both pretty much the same thing but with the tweak that TimescaleDB is more like an add-on on top of PostgreSQL.

Setup

We strongly recommend you to use the v13+ even if in theory any version above v10 should work just fine.

Install TimescaleDB 13 on Ubuntu

You can refer to this link from Timescale for installation on your system: docs of Timescale. Their docs are really well written ! 👏

Upon successful installation of TimescaleDB, I suggest you to install timescaledb-tools as this will allow you to run:

timescaledb-tune

The above command will offer you to apply certain settings to your PostgreSQL instance (in order to "optimize" it).

You can then verify that PostgreSQL has been installed correctly by running:

systemctl status postgresql.service 

 ● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
    Active: active (exited) since Sun 2020-10-07 10:23:46 UTC; 6min ago
  Main PID: 8159 (code=exited, status=0/SUCCESS)
     Tasks: 0 (limit: 2362)
    CGroup: /system.slice/postgresql.service

Install wal2json (required)

Speculare rely on one more PostgreSQL plugin, which is wal2json. As a basic explanation of what it does: wal2json convert the events passing through the WAL of PostgreSQL into a JSON structure

You can install it easily by running (or by checking their docs):

sudo apt install postgresql-13-wal2json

Enable wal2json (required)

You now need to enable both plugins (wal2json & timescaledb) otherwise installing them would be useless. For that you have to edit /etc/postgresql/{version}/main/postgresql.conf to add wal2json & timescaledb to the shared_preload_libraries variable.

# - Shared Library Preloading -

shared_preload_libraries = 'wal2json, timescaledb' # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
#jit_provider = 'llvmjit'

Set up logical replication

As you probably know, PostgreSQL doesn't offer a real-time API to listen to change over a Web-socket or such. Because of that we had to develop a Change Data Capture service which listen to the WAL output.

This feature allow us to get a stream of changes made within PostgreSQL which is then filtered and broadcasted over Web-socket to subscribed clients. This is done though Speculare-PGCDC.

Logical replication can be enabled by changing a few line in /etc/postgresql/{version}/main/postgresql.conf:

# REPLICATION
wal_level = logical             # minimal, archive, hot_standby, or logical (change requires restart)
max_wal_senders = 5             # max number of walsender processes (change requires restart)
#wal_keep_segments = 4          # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s       # in milliseconds; 0 disables
max_replication_slots = 5       # max number of replication slots (change requires restart)
synchronous_commit = local      # synchronization level;

After all those changes, you will need to restart PostgreSQL for changes to take effect.

An important point to note is that those are "default" settings and may not be suitable for production use.

And you should now be ready to use PostgreSQL.

If something is missing in this guide, don't hesitate to open an issue on Github.