Published at Aug 5, 2024

Moving Past Legacy Systems: Data Historian vs. Time-Series Database

Written by Anya Sage

Data historian or time-series database (TSDB)? It’s a pivotal choice facing every industrial IoT (IIoT) engineer or operator seeking to modernize their infrastructure and capitalize on digital transformation. That choice, which can be transformational for industrial organizations,  becomes easier to make once the nature, design, and possibilities of data historians are understood and compared to those of TSDBs.

Data Historian vs. TSDB: The Problem Set in Context

In the IIoT era, organizations are grappling with time-series data at unprecedented volumes and scale. The ability to efficiently collect, store, and analyze time-series data has become critical in maintaining competitive advantage and operational efficiency.

Traditionally, many industries have relied on data historians to manage time-series data. Data historians have served as the backbone for recording and retrieving historical data in industrial settings for decades. However, as data volumes explode and the need for real-time analytics becomes more pressing, the limitations of data historians have become increasingly apparent.

Data historians have a fundamentally different approach than modern IT systems. This approach makes them incompatible with cloud-based architectures. Compared to data historians, time-series databases offer superior scalability, performance, cost-effectiveness, and ease of use in handling time-stamped data—through flexible data models, analytics capabilities, storage compression, and seamless integration with modern technology stacks. These capabilities make TSDBs the optimal choice for organizations dealing with large volumes of time-series data.

Let's explore why organizations should consider migrating from data historians to time-series databases in depth. First, we define data historians and TSDBs and compare the two. Then, we highlight the key advantages of TSDBs over data historians. We then address the migration process, best practices, and tools. Finally, we show why Timescale is the right fit for developers handling IoT/IIoT data. 

Understanding Data Historians

Definition and purpose

Data historians, also known as process historians or operational historians, are specialized software systems designed to collect, store, and retrieve large volumes of time-series data from industrial processes. Historian software is often embedded in or used with standard Distributed Control Systems (DCS) and Programmable Logic Controller (PLC) systems to enable enhanced data capture, validation, compression, and aggregation.

Historical context

Data historians emerged in the 1980s, within the broader trend of increased computerization and digital control in industrial processes, to address the specific needs of process industries like oil & gas, manufacturing, and utilities. In these sectors, data historians have been commonly adopted due to features including: 

• Data compression: use techniques to compress data, reducing storage requirements

• Fast data retrieval: optimized for quick access to historical data

• Integration with industrial systems: have built-in connectors for Supervisory Control and Data Acquisition (SCADA), DCS, and other industrial control systems

• Data contextualization: allow adding metadata and annotations to time-series data

• Regulatory compliance: designed to meet industry-specific regulatory requirements

Data historian use cases

As shown below, data historians are used in industries and applications that require continuous monitoring, recording, and analysis of large volumes of time-series data. 

Data historian limitations 

While data historians have served industries well for decades, they have several limitations in the modern data landscape:

1. Scalability: Historians struggle with the volume and velocity of data generated by IoT devices and modern industrial systems.

2. Limited analytics: Many historians lack advanced analytics and machine learning integration.

3. High costs: Licensing, maintenance, and scaling historians can be expensive.

4. Proprietary systems: Historians are often closed, proprietary systems, making integration with modern data ecosystems challenging.

5. Inflexibility: Adapting historians to new types of data or changing business requirements can be difficult. 

These limitations have paved the way for the rise of time-series databases.

Introduction to Time-Series Databases

Definition and purpose

A time-series database is a database designed for handling time-series data. Its primary functions include efficient storage and retrieval of large volumes of time-stamped data collected at regular or irregular intervals from sensors, applications, or systems. TSDBs are optimized for fast, high-volume write operations to ingest time-stamped data streams, as well as for read operations that query and aggregate data over specified time ranges. 

TSDBs have functions that make it easier to work with historical and real-time data: 

• Improved data ingestion performance: internal optimizations like auto-partitioning and indexing that allow scaling up ingestion rate

• Simplified querying: specialized features to simplify and speed up the calculation of time-series queries

• Storing real-time and historical data in one place: the tools and scale needed to store both historical and real-time data in one data store, enabling seamless analysis

• Automated data management: automated time-series data management tasks such as downsampling, compression, and continuous aggregates

Modern applications of TSDBs

Time-series databases have become critical in modern data management. Factors driving TSDB adoption include: 

1. Proliferation of IoT devices and sensor data: IoT generates high-granularity and high-volume datasets that require efficient time-series data storage systems.

2. Need for real-time analytics: Organizations’ need to derive insights from their historical and real-time data is driving the demand for TSDBs.

3. The need for observability: The need to understand system health and enable 24/7 application monitoring has also contributed to TSDB popularity.

Time-series databases have found applications across a wide range of use cases. 

Key features of time-series databases

Time-series databases have key features that distinguish them from other types of databases:

1. Optimized data model: use data models designed for time series, allowing efficient storage and retrieval

2. High-speed ingestion: are built to handle high-velocity data streams, supporting millions of data points per second

3. Efficient compression: employ advanced compression algorithms tailored for time-series data, significantly reducing storage requirements

4. Flexible retention policies: include built-in features for data retention and downsampling, facilitating data lifecycle management

5. Time-based querying: optimized for time-range queries, allowing fast retrieval of data over specific time intervals

6. Scalability: designed to scale with increasing data volumes and query loads

7. Analytics and visualization: have built-in or easily integratable tools for data analysis and visualization

8. Support for irregular time-series data: handle data collected at regular and irregular time intervals

Comparing Data Historians and Time-Series Databases

Let’s compare data historians and TSDBs on four fronts, each represented in a table below.

Advantages of Time-Series Databases Over Data Historians

Let’s discuss TSDBs’ four key advantages over data historians. 

Scalability: Built for modern distributed architectures

TSDBs are built for multiple environments and designed to work in distributed environments and handle time-stamped data points from sources beyond industrial applications. This open architecture supports the data needs of growing businesses. 

As companies collect more time-series data, TSDBs can efficiently store and quickly retrieve this data. This allows scaling data operations without facing data management bottlenecks. By efficiently handling the challenges of time-stamped data at scale, TSDBs enable organizations to derive valuable insights, optimize operations, and drive innovation.

Performance: Real-time data processing and analytics

As for real-time data processing and analytics in industrial and IoT contexts, TSDBs also have the advantage. They can efficiently handle high-velocity, time-stamped data streams, allowing rapid ingestion, storage, and retrieval of massive volumes of time-series data. Their optimized data models and indexing strategies enable faster query performance, lower latency, and higher throughput, which are critical for applications requiring immediate insights from streaming data.

TSDBs’ built-in features for time-series analysis allow seamless real-time data processing, including on-the-fly aggregations, trend analysis, and anomaly detection. Unlike data historians, which may struggle with high cardinality data and complex queries, modern TSDBs can handle millions of unique series and offer flexible querying options. 

Cost-effectiveness: Storage compression and cloud pricing 

TSDBs offer significant cost benefits over traditional data historians, particularly in terms of infrastructure and operational expenses. TSDBs are designed to efficiently compress and store vast amounts of time-stamped data. Dramatic reduction in storage requirements translates directly to lower hardware costs and reduced cloud storage expenses. 

TSDBs typically require less maintenance and administrative overhead. Their built-in features reduce the need for custom ETL processes and data management scripts. Many TSDBs offer flexible deployment options, including cloud-native implementations with pay-as-you-go pricing. This flexibility can lead to substantial cost savings, especially for companies with variable workloads or seasonal demand patterns.

Ease of use: Designed for integration and interoperability

Time-series databases offer a more user-friendly experience, especially regarding access and analysis. Some TSDBs come with built-in visualization tools, while others seamlessly integrate with dashboarding platforms like Grafana. This enables users to quickly create insightful charts and graphs without the need for extensive programming knowledge often required with historians.

TSDBs’ IoT-friendly nature is evident in their design and integration capabilities. They typically support data ingestion protocols and formats used in IoT ecosystems, such as MQTT, HTTP APIs, and various line protocols. Many TSDBs offer client libraries for programming languages and platforms used in IoT development. This facilitates onboarding of new devices or sensor types without significant database restructuring, a task that can be cumbersome with traditional data historians.

How to Transition from a Data Historian to a Time-Series Database

Now, let’s examine the process of transitioning from data historians to TSDBs. It’s important to acknowledge that no two setups are the same, and each organization has distinct needs. Some choose to run a historian in parallel with a time-series database. Having said that, and for those who do want to transition, here’s a practical roadmap. 

Step-by-step migration guide

1. Assess your current setup: Begin by taking stock of your existing infrastructure.

   • Identify data sources, collection methods, and storage formats.

   • List all applications and systems that depend on the historian.

2. Define your requirements: Specify what you need from the time-series database.

   • Determine data retention needs (long-term and short-term).

   • Establish performance expectations (query speed, ingestion rate, etc.).

   • Identify required features (such as downsampling and aggregations).

   • Verify that the TSDB's APIs are sufficiently open and well-documented.

3. Design the new architecture: Plan out the structure of your chosen database.

   • Plan data collection and ingestion methods.

   • Set storage and retention policies.

   • Design data models and schemas.

4. Set up the environment: Prepare the infrastructure for your new database and install it on test servers. 

   • Configure storage, networking, and security settings.

   • Set up monitoring and backup solutions.

5. Develop data ingestion pipelines: Create the systems to feed data into your database.

   • Create connectors or adapters for existing data sources.

   • Implement data transformation logic if needed.

   • Set up data validation and error handling.

6. Migrate historical data: Transfer existing data from your historian to the new system.

   • Develop scripts to extract data from the historian.

   • Transform data to fit the time-series database schema.

   • Load historical data into the new database in batches.

7. Implement new data collection processes: Set up ongoing data ingestion for your time-series database.

   • Configure data buffering and batching as needed.

   • Implement data compression and encoding techniques.

8. Develop and test queries: Ensure efficient data retrieval from your new system.

   • Create new queries for common data retrieval tasks.

   • Optimize query performance using indexing and partitioning.

   • Implement aggregation and downsampling functions.

9. Perform thorough testing: Conduct performance tests comparing old and new systems.

   • Verify data integrity and consistency.

   • Test all dependent applications and integrations.

10. Plan the cutover strategy: Develop a plan for the final switch to the new system.

    • Decide on a phased or all-at-once migration approach.

    • Schedule the transition during a low-impact time window.

    • Prepare rollback procedures in case of issues.

11. Execute the migration: Carry out the actual transition to the new database.

    • Stop data ingestion to the old historian and perform final data synchronization.

    • Switch all systems to the time-series database and verify data flow and application functionality.

    • Once the transition is complete, safely retire the old system and ensure all stakeholders can use the new database by updating documentation and conducting training. 

Migration best practices 

The implementation of a TSDB represents a significant shift in how an organization manages time-series data. Here are some best practices and tips to ensure a smooth transition. 

• Plan for scalability from the beginning by considering data volume and query load.

• Conduct thorough planning and assessment, including a comprehensive audit and clear objectives, that will form the basis of your migration and architecture choices.

• Implement a phased approach, starting with a pilot project and using parallel systems during transition.

• Develop a robust data strategy focusing on data cleansing, retention policies, and disaster recovery.

• Optimize performance through testing, data model optimization, and appropriate indexing strategies.

• Implement strong security measures and access controls, and use encryption for data at rest and in transit. Regularly audit and update access controls to ensure least privilege principles are maintained.

• Ensure integration and interoperability through well-documented APIs and support for industrial protocols.

• Establish clear data governance practices, including ownership and quality standards.

• Ensure compliance with regulatory requirements and implement audit logging.

Migration tools and resources

What about recommended tools and resources to ensure a seamless transition and maximize data integrity? Here are some.

For assessment and planning, you can use tools like AWS Database Migration Assessment or Microsoft Data Migration Assistant to evaluate your current data historian setup. You can also leverage capacity planning tools to estimate resource requirements for your TSDB.

Data mapping and schema conversion tools help understand the structure and semantics of data in the legacy historian and facilitate mapping it to the schema of the target time-series database. Tools such as Apache NiFi, Talend, or custom scripts tailored to your specific data formats can automate much of this mapping process. 

Next, data transformation and cleansing tools ensure data quality during migration. Tools like Apache Spark, Pandas in Python, or even SQL-based Extract, Transform, Load (ETL) processes can be used to cleanse, validate, and transform data. For large-scale data migrations, bulk loading tools provided by the time-series database vendor can significantly accelerate data transfer. Some TSDBs offer multiple ingest methods. 

To check whether the new TSDB functions correctly under various scenarios, you can use testing and validation tools. Automated testing frameworks like JUnit or custom scripts can be used to validate data integrity, consistency, and performance post-migration.

Lastly, you can use Docker to set up isolated environments for testing different TSDBs. You can also leverage cloud provider sandboxes to experiment with managed time-series database services without significant investment. 

Why Timescale Is the Right Fit for Developers Handling IoT/IIoT Data

As TSDB popularity grows, so does the number of TSDB choices available. One time-series database—Timescale—excels at handling IoT/IIoT data. Here’s why.  

Introduction to Timescale 

Timescale is the industry-leading relational database for time series, built on the standards of PostgreSQL and SQL. More than 3.2 million Timescale databases power apps across IoT, sensors, AI, dev tools, crypto, and finance. Timescale is deployed for mission-critical applications, including industrial data analysis, complex monitoring systems, operational data warehousing, financial risk management, and geospatial asset tracking across industries. 

TimescaleDB (which powers Timescale Cloud) is a PostgreSQL extension that provides time-series functionality while maintaining SQL compatibility. By loading the TimescaleDB extension into a PostgreSQL database, you effectively “supercharge” PostgreSQL, empowering it to excel for both time-series workloads and classic transactional ones. 

TimescaleDB is the only open-source time-series database that natively supports full SQL, combining the power, reliability, and ease of use of a relational database with the scalability typically seen in NoSQL systems. As noted by Timescale CEO and co-founder Ajay Kulkarni, Timescale is built on the exception that PostgreSQL is in the database world:

"The answer to the Postgres paradox,” writes Ajay, “lies in its extension framework” which has made it “a platform: a steady, rock-solid base with fast-moving innovations on top.” 

PostgreSQL’s rich ecosystem with extensions for a variety of use cases 

In fact, PostgreSQL is consistently ranked by DB-Engines among the top five database management systems (DBMS) worldwide. 

DB-Engines Ranking: trend of PostgreSQL Popularity. (Source: DB-Engines)

The challenges of handling IoT/IIoT data

Developers building IIoT applications face the challenge of analyzing and storing the deluge of time series with other relational data without relying on multiple databases and complex data stacks. They need a solution to drive fast business decisions that also ensures SCADA systems, the foundation of industrial applications, keep running seamlessly. Here’s how their IIoT database journey usually progresses.

Traditionally, data historians have been used for long-term storage and analysis of data collected by SCADA systems. Yet IIoT application developers, familiar with the challenges SCADA systems pose, are driven to build an industrial sensor data solution on top of battle-tested, robust database technology, typically PostgreSQL.

The problem they then face is that IIoT applications need to process different data types: time-series data plus traditional relational data. As the IIoT application’s adoption grows and data accumulates, their rock-solid general-purpose database starts exhibiting query performance degrades and unmanageable storage footprint, resulting in growing costs. 

To solve the problem, teams at this point usually reach out for a time-series database separate from their relational database. This adds more complexity because they’ll have to maintain multiple databases (one for each data type), build pipelines to keep data in sync across databases and join data for querying if needed. 

This also means the team has to learn a new query language if they’re not using a database with full SQL support. Additionally, a new database comes with new data model limitations and also means additional cost, since you need a larger infrastructure to run two databases. 

Timescale solves this dilemma. Timescale engineers PostgreSQL for high performance in handling time-series workloads while retaining its native ability to handle relational data. With Timescale, you get the best of both worlds.

Timescale features for IoT/IIoT

Features that make Timescale ideal for IoT/IIoT include high ingest rates, compression, and scalability. TimescaleDB scales PostgreSQL, as shown in our benchmark, to ingest millions of rows per second, storing billions of rows, even on a single node with a modest amount of RAM. TimescaleDB consistently outperformed a vanilla PostgreSQL database, with 1,000x faster performance for time-series queries. 

TimescaleDB’s core concept is the “hypertable”: seamless partitioning of data while presenting the abstraction of a single, virtual table across all your data. This partitioning enables faster queries by quickly excluding irrelevant data, as well as enabling enhancements to the query planner and execution process. Once you've got data in a hypertable, you can compress it, efficiently materialize it, and even tier it to object storage to slash costs.

In fact, storage is the primary driver of cost for modern time-series applications. Timescale provides two methods to reduce the amount of data being stored: compression and downsampling using continuous aggregates. As shown in our benchmark, compression reduced disk consumption by over 90 percent compared to the same data in vanilla PostgreSQL. 

Given these capabilities, it’s not surprising that IIoT customers represent 66 percent of Timescale’s IoT client base. Many have, as Thred’s Keiran Stokes writes on LinkedIn, chosen Timescale for time-series data storage, hyperfunctions, and aggregates. 

To see TimescaleDB in action for IIoT, watch this video tutorial on the Timescale template for creating a sensor data pipeline:

Timescale success stories 

Timescale is trusted by companies like Lucid, Warner Music Group, Coinbase, Uber, and Hewlett Packard Enterprise. Companies use Timescale to build innovative, data-centric applications that wouldn’t have been possible without it. As reasons for adoption, clients commonly cite Timescale’s speed, scalability, cost savings, advanced time-series features, and active community. Across sectors, there are many Timescale success stories to explore.

Let’s highlight one of Timescale’s industrial clients: United Manufacturing Hub (UMH), an IT/OT integration platform that connects factory machines, sensors, and systems to a single point of truth—the Unified Namespace.

UMH founder and CTO Jeremy Theocharis writes that UMH chose Timescale over a data historian because it “fulfills the requirements of the OT engineer, but is still maintainable by IT.” He explains why UMH chose TimescaleDB for predictive maintenance: “The stability of TimescaleDB allows us to focus on developing our microservices instead of running around fixing breaking API changes.”

As for choosing TimescaleDB over InfluxDB, he writes about how “the introduction and implementation of an Industrial IoT strategy is already complicated and tedious” and that “there is no need to put unnecessary obstacles in the way through lack of stability, new programming languages, or more databases than necessary.” Jeremy cites reliability and scalability, query language, and proven native ability to handle relational data as the three reasons why UMH chose Timescale over InfluxDB: 

TimescaleDB is better suited for IIoT than InfluxDB because it is stable, mature, and failure-resistant. It uses the very common SQL query language, and you need a relational database for manufacturing anyway.

Conclusion

In this article, we’ve outlined the four key advantages time-series databases have over data historians (scalability, performance, cost-effectiveness, and ease of use) and provided a path forward by showing why Timescale is the right fit for developers, particularly those handling IoT/IIoT data. We’ve done that by:

• Defining data historians and time-series databases and their applications and use cases

• Comparing the two database types in terms of origin and purpose, performance and scalability, data ingestion and querying, and flexibility and adaptability 

• Providing an overview of TSDB advantages over data historians in terms of database design and capabilities

• Discussing migration process, best practices, and tools and resources

• Highlighting Timescale as the better choice over a data historian because it was designed to meet the needs of modern data environments, delivering high ingest rates, compression, and scalability.

Built for price-performance, TimescaleDB enables developers to build on top of PostgreSQL and “future-proof” their applications while keeping storage costs under control. TimescaleDB delivers powerful time-series functionality that fits right into your ecosystem and has none of the high maintainability costs or vendor lock-in issues of data historians.

Get Started With Timescale

Timescale provides several deployment options:

• TimescaleDB – an open-source database (packaged as a PostgreSQL extension)

• Timescale Cloud (powered by TimescaleDB) – a reliable and worry-free PostgreSQL cloud built for production and extended with cloud features like transparent data tiering to object storage

If you're running PostgreSQL on your hardware, you can simply add the TimescaleDB extension. If you prefer to try Timescale in AWS, sign up for a free 30-day trial and experience the supercharged, mature PostgreSQL cloud platform for time series, events, analytics, and demanding workloads—no credit card required.