# TimescaleDB

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.

{% hint style="info" %}
**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.
{% endhint %}

### Setup

#### Install TimescaleDB on Ubuntu

You can refer to this link from Timescale for installation on your system: [docs of Timescale](https://docs.timescale.com/self-hosted/latest/install/installation-linux/#install-and-configure-timescaledb-on-postgresql). Their docs are really well written ! :clap:

#### Install wal2json

Speculare rely on one more PostgreSQL plugin, which is [wal2json](https://github.com/eulerto/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.

```bash
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:

```yaml
# - 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](https://martichou-andre.gitbook.io/speculare/speculare-pgcdc/getting-started).&#x20;

Logical replication can be enabled by changing a few line in `postgresql.conf`:

```yaml
# 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;
```

{% hint style="info" %}
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.
{% endhint %}
