Timescale

Overview

Use our proprietary connector to replicate TimeBase streams to Timescale database.

Timescale is a relational database for time-series data, built as a PostgreSQL extension with full support of SQL features. Refer to Timescale Documentation to learn more.

TimeBase being a time-series database stores time-series events as Messages and each type of event has a personal message class assigned to it in TimeBase. Message class has a set of fields (attributes) that characterize, describe, identify each specific type of event. In object-oriented programing languages messages can be seen as classes, each with a specific set of fields. Messages are stored in Streams chronologically by their timestamps for each symbol. Refer to a Basic Concepts page to learn more about TimeBase main principles and data structure.

To replicate TimeBase stream data to Timescale, we take fields, objects and classes from a particular TimeBase stream and unfold them so each field corresponds to a particular Timescale table column. In case of an ARRAY of objects, data is inserted in a Timescale table as a JSON object, that contains all array elements and their fields. EventTime, Id and Symbol are auto generated and common for all Timescale tables where EventTime + Id = PrimaryKey. EventTime is mapped on a TimeBase message timestamp, Symbol on a TimeBase message symbol, id is an auto generated sequence by PostrgeSQL. Timescale tables are named after TimeBase stream names. Tables rows are created for each TimeBase message in a chronological order. Data is replicated in batches (TIMEBASE_BATCH_SIZE parameter if the application config to set the number of messages in one batch).

The column naming convention:

• column names for fixed-type objects' fields are named after the particular fields as is: for example Symbol.
• column names for nested objects' fields follow this pattern: nested-object_field-name, for example trade_size.

Data Type Mappings

• Refer to PostgreSQL Documentation to learn more about data types supported by Timescale.
• Refer to Data Types to learn more about data types supported by TimeBase.

TimeBase Type/Encoding	Timescale Type
INTEGER/SIGNED (8-32)	INTEGER
INTEGER/SIGNED (64)	BIGINT
ENUM	VARCHAR
VARCHAR	VARCHAR
CHAR	CHAR
BINARY	BYTEA
BOOLEAN	BOOLEAN
FLOAT/DECIMAL64	DECIMAL(36, 18)
FLOAT	DECIMAL
ARRAY	JSON
TIMESTAMP	TIMESTAMP
TIMEOFDAY	TIME

Docker compose sample:

# docker-compose.yml Enterprise Edition example
version: "3"
services:
  timebase:
    image: "registry.deltixhub.com/quantserver.docker/timebase/server:5.5.89"
    ulimits:
      nofile:
        soft: 65536
        hard: 65536
    environment:      
      - TIMEBASE_SERIAL=${TIMEBASE_SERIAL}
      - JAVA_OPTS=        
        -Xmx8g
        -XX:+HeapDumpOnOutOfMemoryError        
        -XX:HeapDumpPath=/timebase-home/timebase.hprof        
        -Xlog:gc=debug:file=/timebase-home/GClog-TimeBase.log:time,uptime,level,tags:filecount=5,filesize=100m                
    ports:
      - 8011:8011
    volumes:
      - "./timebase-home:/timebase-home"
    healthcheck:
      test: wget --no-verbose --tries=1 --spider http://localhost:8011 || exit 1

  timescale:
    image: timescale/timescaledb-ha:pg15.2-ts2.10.3-oss
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: timescaledb
      POSTGRES_PASSWORD: password
#    volumes:
#      - /timescaledb/data:/home/postgres/pgdata/data
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "timescaledb"]

  timescale-replicator:
    image: registry.deltixhub.com/deltix.docker/timebaseconnectors/timescale-connector:5.4.6
    environment:
      - TIMEBASE_STREAMS_FOR_REPLICATION=BINANCE,DATA*
      - TIMEBASE_BATCH_SIZE=10000
      - TIMEBASE_HOST=timebase
      - POSTGRES_HOST=timescale
      - POSTGRES_USERNAME=timescaledb
      - POSTGRES_PASSWORD=password      
    depends_on:
      timescale:
        condition: service_healthy
      timebase:
        condition: service_healthy

Timescale Configurations:

• POSTGRES_HOST - Timescale host name.
• POSTGRES_DATABASE - Timescale database.
• POSTGRES_MIN_IDLE - min number of connections that is not going to be terminated.
• POSTGRES_MAX_POOL_SIZE - max number of connections in a pool.

TimeBase Configurations:

• TIMEBASE_HOST - TimeBase host.
• TIMEBASE_PORT - TimeBase port.
• TIMEBASE_BATCH_SIZE - number of messages in one batch.
• TIMEBASE_STREAMS_FOR_REPLICATION - comma-separated list of stream names that will be replicated. Wildcards are supported.
• TIMEBASE_AUTO_DISCOVERY - flag that enables/disables the automated discovery of streams to be replicated.

Replicator Configurations:

• RETRY_ATTEMPT - number of replication retry attempts in case there has been any interruptions in the replication process or connection failures. With each retry the last ms data is deleted and the replication resumes from this timestamp.

Logging Configurations:

• ROOT_LOG_LEVEL - logs root level (TRACE, DEBUG, INFO, WARN, ERROR, FATAL).
• APP_LOG_LEVEL - logging level.

Known Limitations

• Timescale replicator does not currently support the replication of primitives' arrays.
• Timescale replicator does not currently support stream TRUNCATE, PURGE, DELETE commands.

Replication Tracker

Once you start the replication, we automatically create a system table called migrations_tracker with metadata about all the replicated streams, replication timestamps and the replication statuses. You can use this table to track your replication statistics.

                                            Table "public.migrations_tracker"
      Column       |            Type             | Collation | Nullable |                    Default                     
-------------------+-----------------------------+-----------+----------+------------------------------------------------
 id                | integer                     |           | not null | nextval('migrations_tracker_id_seq'::regclass)
 stream            | character varying           |           |          | 
 version           | bigint                      |           |          | 
 issuccess         | boolean                     |           |          | 
 migrationdatetime | timestamp without time zone |           |          | 
Indexes:
    "migrations_tracker_pkey" PRIMARY KEY, btree (id)