TimescaleDB is an open-source database built on Postgres that’s optimized for time-series data. You can use Sequin to stream data from TimescaleDB. You can setup HTTP endpoints that let consumers subscribe to changes in your TimescaleDB tables. Or you can receive webhooks when rows are inserted.

The two together are a formidable, Postgres-based alternative to Kafka.

In this guide, you’ll learn how to set up Sequin with TimescaleDB to stream your time-series data.

Setting up TimescaleDB

First, set up a local TimescaleDB instance:

1

Start TimescaleDB container

Run the following command to start a TimescaleDB container:

docker run -d --name timescaledb -p 5432:5432 \
-e POSTGRES_PASSWORD=postgres \
timescale/timescaledb-ha:pg16
2

Configure WAL level

Sequin requires logical replication to be enabled. To do this, you need to set wal_level to logical:

# Connect to the container
docker exec -it timescaledb bash

# Edit postgresql.conf
echo "wal_level = logical" >> /home/postgres/pgdata/data/postgresql.conf

# Exit the container
exit

# Restart the container for changes to take effect
docker restart timescaledb

Connecting Sequin to TimescaleDB

Now that TimescaleDB is set up, connect it to Sequin:

1

Connect Sequin to your database

  1. In the Sequin console, click Databases > Connect Database.
  2. Enter the connection details for your TimescaleDB instance:
    • Host: localhost (or your Docker machine IP)
    • Port: 5432
    • Database: postgres
    • Username: postgres
    • Password: postgres
2

Create publication and replication slot

Run the following SQL commands in your TimescaleDB instance:

create publication sequin_pub for all tables;
select pg_create_logical_replication_slot('sequin_slot', 'pgoutput');

These commands create a publication for all tables and a replication slot for Sequin to use.

3

Complete the connection

Name your database connection in Sequin and click the Connect Database button.

Creating a Stream for a TimescaleDB Hypertable

TimescaleDB uses hypertables to optimize time-series data storage. Hypertables work like any other table in Sequin. Let’s create a Stream for a hypertable:

1

Create a hypertable

First, create a hypertable in TimescaleDB:

-- Create a regular table
create table sensor_data (
  time timestamptz not null,
  sensor_id integer not null,
  temperature double precision null,
  humidity double precision null
);

-- Convert it to a hypertable
select create_hypertable('sensor_data', 'time');
2

Create a Stream in Sequin

  1. In the Sequin console, go to the Streams tab and click Create Stream.
  2. Select the sensor_data hypertable.
  3. Choose time as the sort column.
  4. Click Create Stream.

Consuming TimescaleDB Data with Sequin

Now that you have a Stream set up, you can consume data using Sequin’s Consumer Group API, webhooks, or Sync API (coming soon).

Here’s an example of how to consume data using the Consumer Group API:

curl -X GET 'https://api.sequinstream.com/api/http_pull_consumers/sensor_data_consumer/receive?batch_size=10' \
-H 'Authorization: Bearer YOUR_API_TOKEN'

This will return up a batch of up to 10 messages from your sensor_data hypertable. The response will look something like this:

{
  "data": [
    {
      "ack_id": "MTYyeJ7abUjl1pO",
      "data": {
        "record": {
          "time": "2023-06-01T12:00:00Z",
          "sensor_id": 1,
          "temperature": 25.5,
          "humidity": 60.2
        }
      }
    },
    // More messages...
  ]
}

After processing these messages, you can acknowledge them using the /ack endpoint:

curl -X POST 'https://api.sequinstream.com/api/http_pull_consumers/sensor_data_consumer/ack' \
-H 'Authorization: Bearer YOUR_API_TOKEN' \
-H 'Content-Type: application/json' \
-d '{
  "ack_ids": ["MTYyeJ7abUjl1pO"]
}'

With Sequin’s Consumer Group API or Webhook Subscriptions, you can process your TimescaleDB data in real-time, with exactly-once processing guarantees.

Next steps

Explore Sequin further by checking out our documentation. Join our Discord community for support.