⏳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, a highly performant relational and time-series database that extends PostgreSQL. TimescaleDB serves as the core of Speculare's infrastructure, leveraging advanced PostgreSQL functionalities and additional plugins to ensure optimal performance and ease of development.

This guide provides detailed instructions on configuring your TimescaleDB instance to support Speculare effectively.

Note: In this documentation, "PostgreSQL" and "TimescaleDB" are often used interchangeably. While TimescaleDB functions as an add-on to PostgreSQL, they share the same foundational structure, with TimescaleDB enhancing PostgreSQL's capabilities.

Setup

Install TimescaleDB on Ubuntu

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

Install wal2json

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 that we're able to deserialize.

sudo apt install postgresql-X-wal2json

Enable wal2json

You now need to enable both plugins (wal2json & timescaledb), in your postgresql.conf update the following field:

# - 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 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.