Choosing a Historian for Production Data: Time-Series Databases

Historian selection: compression, retention, querying and backup in commercial historians versus InfluxDB and TimescaleDB, and common sizing mistakes.
Technical summary
Technology
Industrial historian, InfluxDB, TimescaleDB, PostgreSQL, deadband, swinging door compression, OPC UA
Topic
Choosing the historian or time-series database that will store production data
Level
Intermediate–advanced
Prerequisites
  • Basic knowledge of tag-based data collection (OPC UA, MQTT)
  • SQL and database backup concepts
In this article8 sections

Historian projects cause no problems in the first month. The price of the choice is paid when the disk starts to fill up, when raw data is deleted and the summary tables come back empty, or when an hour of data is written to the wrong time after an outage. A historian is the system that stores sensor, counter and status data with timestamps and lets you query it retrospectively; today, both commercial industrial historians and time-series databases (TSDBs) such as InfluxDB and TimescaleDB do this job. Below, we compare the two approaches in terms of compression, retention, querying, backup and OT integration, based on the mistakes we encounter in the field.

The difference between a historian and a general-purpose database

Production data is simple in structure: tag ID, timestamp, value and often quality information. The difficulty lies in volume and access pattern. Data is almost only appended, not updated. Almost all queries are of the form “the values of this tag in this range”. New data is read far more often than old data.

A relational database can carry this load. But the team has to solve the following by hand:

  • Partitioning by time and deleting old partitions with a single command,
  • Column-based compression and querying compressed data,
  • Fast generation of aggregates per time range such as average, minimum, maximum and last value,
  • Deriving an evenly spaced series by interpolation from unevenly spaced data,
  • Writing late data that arrives after a connection drop to the right place.

Industrial historians bring these inside the product, together with OT protocol drivers and tag management. TSDBs are strong on storage and querying; they need additional components for data collection, tag metadata and operator tools. The question of who will run these additional components is often more decisive than the licence price.

Compression, deadband and sampling strategies

The concept that gets most confused in selection meetings is “compression”. The same word describes two different things.

Value-based (lossy) compression

Many commercial historians use lossy algorithms that reduce the number of points stored. The first stage is the exception deadband: if the new value does not differ from the last value sent by a certain amount, it is not transmitted. The second stage is usually a swinging door-type slope algorithm: if consecutive points stay on a straight line within a defined tolerance, the intermediate points are discarded and only the points where the slope changes are written. When read back with linear interpolation, the result is a series that looks very close to the original but contains far fewer points.

If the tolerance is right, the method works well. If it is wrong, it deletes short peaks. A common mistake is assigning the same percentage tolerance to all tags in bulk; on the day of the fault analysis, the short spike in motor current is not in the trend. Keep the tolerance close to the instrument’s measurement uncertainty, and set it tighter for quality-critical tags.

Storage-based (lossless) compression

TSDBs generally store every point and compress losslessly: delta encoding for timestamps, XOR-based encoding for floating-point values, run-length encoding for repeated values. The ratio is high for constant or slowly changing signals and low for noisy analogues. Lossless compression does not change the data. The noise is stored too. That is why a small deadband at the source or in the collector is still useful.

Sampling

The choice between periodic reading (every second, every 5 seconds) and report by exception directly determines the number of points to be stored. Use report by exception for counters and status signals, and report by exception with a deadband for slow analogues such as temperature. Don’t force fast signals such as vibration into the historian; a separate high-frequency acquisition system often gives a better result.

Comparing commercial historians and open-source TSDBs

Criterion Commercial industrial historian Open-source TSDB
OT connectivity Protocol drivers and tag browser come with the product A separate collector layer is needed
Compression Lossy + lossless, configured per tag Mostly lossless; raw points are stored
Query Vendor-specific API; SQL layer varies by product SQL (TimescaleDB) or its own query language/SQL (depending on InfluxDB version)
Tag metadata Units, descriptions, alarm limits built in The team designs the schema
Licensing Mostly by tag count or by server Free core; enterprise features depend on edition
Skills required Product administration Database operation and schema design

The decision comes not from looking at the table but from honest answers to these questions: Is there database competence in the plant to bring this system back up when a fault comes in at night? Do operators and process engineers need a ready-made trend tool? Is sharing data with corporate BI tools via SQL a priority? How does the licence change when the tag count doubles?

The protocol choice for the collector layer is directly linked to this decision; for the behavioural differences between the two protocols, see the article OPC UA or MQTT.

Retention policy and downsampling

Keeping raw data forever is rarely necessary and is the habit that consumes the disk budget fastest. The sustainable model is tiered retention: raw data is kept for a short time, per-minute summary tables for a medium period, and hourly or per-shift summary tables for a long time. Don’t keep only the average in the summary table. Minimum, maximum and sample count should also be written; otherwise peak values and data gaps become invisible at the upper tier.

In TimescaleDB, this model is built with a hypertable, a compression policy, a continuous aggregate and a retention policy:

CREATE TABLE tag_data (
  time     timestamptz      NOT NULL,
  tag_id   integer          NOT NULL,
  value    double precision,
  quality  smallint
);
SELECT create_hypertable('tag_data', 'time',
       chunk_time_interval => INTERVAL '1 day');

ALTER TABLE tag_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'tag_id',
  timescaledb.compress_orderby   = 'time DESC'
);
SELECT add_compression_policy('tag_data', INTERVAL '7 days');

CREATE MATERIALIZED VIEW tag_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', time) AS bucket, tag_id,
       avg(value) AS avg_v, min(value) AS min_v,
       max(value) AS max_v, count(*) AS n
FROM tag_data
GROUP BY bucket, tag_id;

SELECT add_continuous_aggregate_policy('tag_1m',
       start_offset => INTERVAL '1 day',
       end_offset   => INTERVAL '1 minute',
       schedule_interval => INTERVAL '1 minute');

SELECT add_retention_policy('tag_data', INTERVAL '90 days');

The trap here is this: the raw data retention period must be longer than the aggregate’s refresh window (start_offset). Otherwise, the system tries to recalculate the aggregate from deleted raw data. Verify the current behaviour of the parameters for your own version in the TimescaleDB documentation. In InfluxDB, the same tiering is done with buckets of different retention periods and periodic downsampling tasks; because the details change from version to version, the InfluxData documentation should be the reference.

Example storage size calculation

The calculation below is an example; the real value varies with the compression ratio and signal behaviour.

  • Number of tags: 5,000
  • Average recording rate: 1 point per second per tag (worst case, no deadband)
  • Raw size per point: 8 bytes time + 4 bytes tag ID + 8 bytes value + 2 bytes quality ≈ 22 bytes; let’s assume about 40 bytes with row and index overhead

The number of points per day is 5,000 × 86,400 = 432 million; before compression, 432 million × 40 bytes ≈ 17 GB/day. 90 days of raw retention comes to around 1.5 TB uncompressed. If lossless compression shrinks the data, for example, tenfold, about 155 GB is enough for 90 days. If the deadband reduces the average recording rate to once every 10 seconds per tag, the figure is divided by 10 again.

What the calculation means in the field is clear: storage size is determined not by the disk but by the actual recording rate per tag. Before ordering a server, take a few days of sample data from the existing system, load it onto the target platform and measure the compression ratio. A historian sized on assumptions is either set up too expensively or raises a disk alarm in the sixth month.

Backup, high availability and DMZ replication

In most plants, the historian does not stop production; but you cannot bring back the data it loses. Ask two separate questions: Is data lost while the historian server is down? If the disk or database is corrupted, does the history come back?

The answer to the first question lies in the collector layer. When the edge gateway or collector agent cannot reach the historian, it must buffer data locally (store and forward) and, when the connection returns, write it retrospectively while preserving the source timestamp. Test how many hours the buffer lasts by pulling the network cable, not on paper. The second question requires regular database backups and restore tests. In PostgreSQL-based solutions, point-in-time recovery is possible with continuous WAL archiving; in commercial historians, the manufacturer’s archive file backup method is followed. A backup that has never been restored is not a backup.

Do not allow corporate users to connect directly to the historian on the OT network. The established structure is for the primary historian on the OT side to send its data to a replica in the DMZ, and for reporting tools to access only the replica. The network placement is described in the article OT DMZ design. The replication direction must be from OT to the DMZ. During commissioning, always test that the primary historian keeps running when the replica is cut off.

Selection criteria table

Requirements Direction to favour
Ready-made OT drivers and operator trends Commercial historian
Sharing with corporate BI via SQL SQL-based TSDB or a historian with an SQL layer
Many tags, limited licence budget Open-source TSDB + collector layer
No database operations competence Commercial historian or managed service
Validation and audit trail requirements A product with documented audit trail and electronic records features
Fast signals (kHz level) Outside the historian; a separate high-frequency system

The mistake we encounter most often after selection is commissioning the platform without a tag naming and metadata standard. A year later, the same sensor sits there under three different names with two different units. Whichever platform is chosen, tag names and units must be defined from the start, and data quality and timestamp rules must be written down; this topic is covered in detail in the article timestamps and data quality in production data. For the complete collection infrastructure, see our IIoT and production data collection service, and for screens to be built on the historian, our production monitoring and dashboards page.

Frequently asked questions

Can SQL Server be used directly instead of a historian in a small plant?

At low volumes, such as a few hundred tags and per-minute sampling, a properly indexed and partitioned relational table does the job. The problem starts as volume grows: compression, deleting old data piece by piece and time-range queries turn into work that has to be solved by hand. If growth is expected, choosing a platform with time-series capability from the start creates less work.

Should the deadband be set in the historian or in the gateway?

They serve different purposes. A deadband in the gateway or OPC UA subscription reduces network and server load; compression on the historian side reduces storage space. Use a small noise deadband at the source and leave the main compression to the historian. Using large deadbands on both sides compounds the error.

How long should raw data be kept?

There is no single right period; the decision depends on who will go back to the raw data and why. For fault analysis, weeks to a few months are usually enough; long-term trends and reporting are done from per-minute or hourly summary tables. If longer retention is required for customers, traceability or regulation, this requirement should be obtained in writing and the retention policy set up accordingly.

If we choose an open-source TSDB, what provides the OPC UA connection?

Time-series databases usually do not include an OPC UA client. A collector layer sits in between: an edge gateway, an agent such as Telegraf, a Node-RED flow or a service writing data via an MQTT broker. This layer’s ability to buffer, reconnect and carry quality information is as decisive as the choice of database.

Support

Contact us for support or a discovery session on this topic.

Contact form
[email protected]

Eretek Industrial

PLC, PROFINET/PROFIBUS and SCADA communication, IT/OT network segmentation and industrial field connectivity. We install and monitor without stopping the production line.

Knowledge Center

Other articles