Skip to main content

TSL Module Configuration

The TSL (Time-Series Library) module is a PointValueDao implementation for Mango 4.3 and later that supports reading and writing point values to external time-series databases through an abstraction layer developed by Radix IoT. This module is an alternative to the built-in NoSQL time-series store for deployments that require the scalability, replication, or management features of dedicated time-series database engines.

Supported Databases​

The TSL module currently supports two time-series databases:

  • ClickHouse -- A column-oriented OLAP database optimized for analytical queries over large datasets.
  • TimescaleDB -- A PostgreSQL extension that adds time-series capabilities to standard PostgreSQL.

Aggregation Support​

Starting from Mango 4.4, the TSL module supports pre-aggregating point values and storing them in a separate aggregates table. Down-sampling point values in this manner can dramatically reduce disk usage. For example, 5-second period data aggregated into 15-minute windows reduces the number of stored samples by a factor of 180.

The TSL module supports aggregation for NUMERIC data type points only. It stores count, sum, min, and max values and calculates average as an arithmetic mean.

Aggregation Rollup Types​

Three new rollup types were introduced to distinguish between raw and aggregated queries:

Original RollupNew Rollup (Aggregates)Difference
MINIMUMMINIMUM_IN_PERIODExcludes the start value of each period
MAXIMUMMAXIMUM_IN_PERIODExcludes the start value of each period
AVERAGEARITHMETIC_MEANTime-weighted average vs. arithmetic mean

How the Aggregation Boundary Works​

The TSL module stores raw data for a configurable "boundary" period. After this period, raw data is aggregated and the aggregates are stored in a separate table. When querying point values, the module seamlessly combines:

  • Raw data from within the boundary period (aggregated on the fly if a rollup is requested)
  • Pre-aggregated data from outside the boundary period

Both raw and pre-aggregated data can be resampled to any requested rollup period. For example, if you have 15-minute aggregates, you can still view hourly rollups -- the backend combines four aggregate values into a single hourly result.

Retention Policies​

Data retention for TSL data is configured at the database level, not through Mango's Purge System Settings. Mango's built-in purge settings have no effect on data stored by the TSL module.

DataRetentionNotes
Raw data3 weeks minimumBuffer for aggregation failures
Aggregation period15 minutesGood balance between resolution and storage
Aggregates10 yearsLong-term trend analysis
DataRetentionNotes
Raw data10 yearsNo aggregation available

Use "log on change" logging to reduce storage for non-numeric points that do not change frequently.

TimescaleDB Configuration​

Add the following to your mango.properties file:

# Enable TimescaleDB as the TSL backend
db.tsl.timescale.enabled=true
db.tsl.timescale.order=0

# Database connection
db.tsl.timescale.host=localhost
db.tsl.timescale.port=5432
db.tsl.timescale.db=mango_tsl
db.tsl.timescale.username=mango
db.tsl.timescale.password=password

# Batch insert size
db.tsl.timescale.chunkSize=16384

# Duplicate handling: UPDATE, IGNORE, or ERROR
db.tsl.timescale.conflictMode=UPDATE

# Aggregation settings
db.tsl.timescale.aggregation.zone=UTC
db.tsl.timescale.aggregation.period=15 MINUTES
db.tsl.timescale.aggregation.boundary=2 WEEKS
db.tsl.timescale.aggregation.overlap=1 DAYS

ClickHouse Configuration​

Add the following to your mango.properties file:

# Enable ClickHouse as the TSL backend
db.tsl.clickhouse.enabled=true
db.tsl.clickhouse.order=0

# Database connection
db.tsl.clickhouse.host=localhost
db.tsl.clickhouse.port=8123
db.tsl.clickhouse.db=default
db.tsl.clickhouse.username=default
db.tsl.clickhouse.password=

# Batch insert size
db.tsl.clickhouse.chunkSize=16384

# Duplicate handling: UPDATE or IGNORE
db.tsl.clickhouse.conflictMode=UPDATE

# Aggregation settings
db.tsl.clickhouse.aggregation.zone=UTC
db.tsl.clickhouse.aggregation.period=15 MINUTES
db.tsl.clickhouse.aggregation.boundary=2 WEEKS
db.tsl.clickhouse.aggregation.overlap=1 DAYS

Setting ClickHouse Retention Policy​

Configure retention using ClickHouse's TTL (Time To Live) feature. The following example keeps raw numeric data for 21 days and all other raw data for 10 years:

ALTER TABLE data MODIFY TTL
  toDateTime(time) + INTERVAL 21 DAY DELETE WHERE numeric_value IS NOT NULL,
  toDateTime(time) + INTERVAL 10 YEAR DELETE;

For aggregate data retention:

ALTER TABLE aggregates MODIFY TTL
  toDateTime(time) + INTERVAL 10 YEAR DELETE;

See the ClickHouse MergeTree documentation for more details on TTL configuration.

Configuration Properties Reference​

PropertyDescription
db.tsl.*.enabledEnable this TSL backend
db.tsl.*.orderPriority order (lower = higher priority)
db.tsl.*.hostDatabase server hostname
db.tsl.*.portDatabase server port
db.tsl.*.dbDatabase name
db.tsl.*.usernameDatabase username
db.tsl.*.passwordDatabase password
db.tsl.*.chunkSizeMaximum records per batch insert
db.tsl.*.conflictModeHow to handle duplicate timestamps (UPDATE, IGNORE, ERROR)
db.tsl.*.aggregation.zoneTimezone for date truncation in aggregation
db.tsl.*.aggregation.periodAggregation period (e.g., "15 MINUTES"). If not set, aggregation is disabled
db.tsl.*.aggregation.boundaryTime boundary between raw and aggregate queries
db.tsl.*.aggregation.overlapOverlap period where both raw and aggregate data exist

Local Development Setup (ClickHouse)​

For local development and testing with ClickHouse:

  1. Ensure you have a local Mango instance with paths.data set appropriately.
  2. Install ClickHouse locally following the ClickHouse installation guide.
  3. Configure the TSL module properties in mango.properties as shown above.
  4. Start Mango -- the TSL module will automatically create the required tables in ClickHouse.