LoopTroops
All posts
2026-04-13·5 min read
Industrial

TimescaleDB Historian Module for Ignition

In industrial automation, time-series data is everywhere. Temperatures, pressures, flow rates, motor speeds — all sampled every second, stored for months or years, and queried for trending, reporting, and analysis. The platform that manages this data in many factories is Ignition by Inductive Automation.

Ignition's built-in SQL Historian works well at moderate scale. But when you push past 100 GB of historical data and start requesting aggregated views — hourly averages over a week, daily totals over a month — performance degrades in ways that are hard to work around. This module replaces the default historian with a direct connection to TimescaleDB, a PostgreSQL extension purpose-built for time-series data.

What Is Ignition?

Ignition is an industrial automation platform used for SCADA (Supervisory Control and Data Acquisition), HMI (Human-Machine Interface), and data integration. It runs on Java, connects to PLCs and sensors on the factory floor, and provides tools for building dashboards, storing historical data, and running automated processes.

At the center of Ignition is the tag system — a hierarchical namespace where every sensor reading, calculated value, and status flag lives as a "tag." Tags can be configured to log their values to a historian, which stores every change with a timestamp for later retrieval.

Ignition modules are Java plugins that extend the platform's functionality. This TimescaleDB module implements a custom historian that plugs into Ignition's historian framework, replacing the default storage and query engines.

The Problem with the Default Setup

When people try to use TimescaleDB with Ignition's standard SQL Historian, they hit several pain points:

No server-side aggregation. This is the biggest issue. When you request hourly averages over 7 days for 5 tags, Ignition's SQL Historian fetches all raw data points — potentially hundreds of thousands of rows — transfers them over JDBC to the Java gateway, and aggregates them in memory. TimescaleDB has time_bucket(), a native function that computes aggregations directly in the database. But the SQL Historian doesn't know about it.

Schema mismatch. Ignition's sqlth_* tables use BIGINT for timestamps and split values across intvalue/floatvalue columns. This doesn't align with TimescaleDB's expectations. Converting these tables to hypertables requires manual SQL, and the conversion can break if Ignition's schema changes.

Partition conflicts. Ignition manages its own partition system via sqlth_partitions. TimescaleDB has its own chunking. Running both creates conflicts that require careful workarounds.

Performance cliff at scale. Community workarounds (BRIN indexes, partition flags) help, but they're fragile and undocumented. Beyond ~100 GB, query performance becomes unpredictable.

What This Module Does Differently

The module bypasses Ignition's SQL Historian entirely. It implements Ignition's Historian Extension Point API (introduced in Ignition 8.3) with its own storage engine, query engine, and schema — all designed for TimescaleDB from the ground up.

Server-Side Aggregation

The single biggest performance improvement. When Ignition requests aggregated data, the module generates a SQL query using time_bucket() and the appropriate aggregate function (AVG, SUM, COUNT, MIN, MAX, etc.), and sends it directly to TimescaleDB.

The difference is dramatic. Requesting daily averages for 10 tags over 30 days:

ApproachRows transferred over JDBC
SQL Historian (fetches all raw data)~25,900,000
This module (server-side aggregation)~300

That's a 99.998% reduction in data transfer. Queries that take seconds with the default historian complete in milliseconds.

The module advertises support for 17 aggregation types: Average, Min, Max, Sum, Count, LastValue, Range, Variance, StdDev, and more. For each, it maps to the corresponding SQL function and lets TimescaleDB do the work.

Optimized Schema

Instead of Ignition's legacy sqlth_* tables, the module creates two tables:

tags — An enrollment table mapping tag paths to integer IDs. Stores metadata (data type, creation time, engineering units) so the tag path string isn't repeated on every value row.

tag_values — A TimescaleDB hypertable partitioned on the time column. Stores tag_id, value_double, value_string, quality, and a proper TIMESTAMPTZ timestamp.

The schema is created automatically on first startup. No manual SQL, no conversion steps.

Automatic Compression

After 7 days, chunks are automatically compressed with segmentby=tag_id. This means:

  • Data is stored in columnar format — queries reading only value_double skip all other columns
  • Only segments for requested tags are decompressed
  • COUNT, MIN, MAX can use pre-computed metadata without decompressing rows at all

Chunk Pruning

TimescaleDB automatically partitions data into time-based chunks (7 days by default). When a query asks for the last 24 hours, chunks from other weeks are never touched. Combined with the (tag_id, time DESC) index, this makes both raw and aggregated queries fast regardless of total data volume.

Architecture

The module consists of five main components:

Ignition Tag Changes
        |
TimescaleDbHistorianGatewayHook (module entry point)
        |
TimescaleDbHistoryProvider (owns both engines)
    |                    |
StorageEngine         QueryEngine
    |                    |
    +---- TimescaleDbClient (shared JDBC pool + tag cache) ----+
                         |
                    TimescaleDB

StorageEngine receives tag value changes from Ignition, resolves tag paths to integer IDs (auto-enrolling new tags), and writes data in configurable batches (default: 100 points or every 5 seconds).

QueryEngine handles system.tag.queryTagHistory() calls. For raw queries, it runs a simple SELECT with time range filtering. For aggregated queries, it generates time_bucket() SQL and pushes the computation to the database.

TimescaleDbClient manages a HikariCP connection pool (10 connections) and an in-memory cache mapping tag paths to integer IDs. The cache is loaded on startup and updated as new tags are enrolled, eliminating a database lookup on every write.

Configuration

Setting up a historian profile takes about a minute:

  1. Install the module in Ignition's gateway
  2. Go to Config > Tags > History > Historians
  3. Create a new TimescaleDB Historian profile
  4. Enter the JDBC URL, username, and password
  5. Configure tags to use this historian as their history provider

The module handles schema creation, index management, and compression policies automatically.

Advanced settings include batch size, batch interval, debug logging, and optional Store & Forward integration for write buffering during database outages.

Who Is This For?

This module is built for Ignition users who:

  • Store large volumes of historical data (100 GB+)
  • Need fast aggregated queries for dashboards and reports
  • Want TimescaleDB's compression and retention features without manual schema management
  • Run Ignition 8.3+ with Java 17

It's particularly relevant for manufacturing environments where trending dashboards show hourly or daily aggregates over weeks or months — exactly the queries where server-side aggregation makes the biggest difference.