The benefits of a time series database (Historian) vs. a relational database
Relational database vs historian database. Which should you chose? Below we present the differences between the two different databases.
Manufacturing companies are increasingly collecting data and as it has become increasingly important to be able to collect, save and distribute all stored data for further use. Access to the information lays the foundation for faster decisions, increased productivity and reduced costs.
Many industrial companies use relational databases (RDBs) for this task. Relationship databases are built to handle and store contextual or genealogical information about the production process. The challenge is that they often do not have the right functions to handle large amounts of time series / process data.
Historian databases on the other hand are designed for the collection, storage and distribution of large amounts of high-resolution time series / process data. They retrieve time series and A&E data from the local system all the way up to the company level, giving you an overview of the entire production process.
In this article, we take a closer look at the differences between a time series database and a relational database, so you can choose the database that meets your needs.
Implementation
Relational databases do not offer built-in data collection capabilities. Therefore, custom code must be written for each data source. This type of specifically adapted systems can make further development and updates costly and time consuming. The lack of web-based interface means that import of tags and maintenance of the database must be done during planned downtime. In addition, you are limited to the experience and competence that exists within the company, with few opportunities to utilise technology from other industry players.
By contrast, historians come with built-in data collection, which can retrieve data from various sensors and systems. GE Digital’s Proficy Historian uses the industry standards OPC and OPC UA, which provides access to data across multiple devices. In addition, Proficy Historian comes with pre-built interfaces, without need for custom coding or scripting. Data is stored and processed seamlessly in one secure, central location. This provides better flexibility, saves time, and reduce costs.
Speed
Because the production operates in real time, it is important that you receive the data fast. Relational databases are well suited to answer queries such as trends in data flows, temperatures, and other analog values. The challenge arises when the queries concern large data sets or longer time periods. This means that the relational database delivers lower performance.
For faster read/write performance and continuous access to real-time data, you should choose a historian database. Historians can collect large amounts of process data in milliseconds. In comparison, Proficy Historian is 100-1000 times faster at reading and writing than a regular relational database.
Compression
Efficient data storage and compression enables high performance. For a relational database, compression is done manually, through administration of archives and disk space. This is fast becoming a time-consuming job.
Historians comes with powerful features for compression. With Proficy Historian, data is first compressed on prem on the collector, and again before storage. Archives can be automatically created, backed up and purged, enabling extended use without the need for a database administrator.
The graphic below shows a comparison between Proficy Historian and a relational database, where Proficy Historian offers much higher disk space efficiency than an RDB, even without compression.
* Data represents a specific test of 400,000 records logged to a standard relational database and Proficy Historian. The results will vary depending on the raw data set and which relational database is used.
Availability and redundancy
It’s important that production data always is available. Relational databases offer high availability for data stored through clustering, but they are vulnerable to network availability. Depending on where the collector is located, usually on a server, or on prem near the data source, data collection will stop if the connection is down.
Historian technology ensures good availability with redundancy at the collector. Proficy Historian comes with Store & Forward technology, which buffers the data at the collector if a disruption occurs. The buffers are eventually uploaded when the server comes back online with automatic reconnection – ensuring no data loss.
Data security
Networks and databases are under constant attacks from hackers and viruses, many of which are targeted at well-known relational databases. SQL Injection/Insertion attacks are common with RDBs.
Some historians are immune to such attacks as they do not allow insert, update, or deletion of data through standard interfaces. In addition, historians track all changes, including uses access, configurations, security violations, and system alerts.
Some historians are designed to help address strict regulatory requirements such as the FDA’s CFR Part 11.
A Historian improves production throughout the company
The historian database is designed to collect amounts of data from the entire plant for process visualisation. A plant wide historian connects both “islands” of automation data at the plant floor, and the plant operations up to business systems. You can compare previous production runs and analyse data before downtime, so you can easily identify trends, root causes, and implement improvements.
For collecting, storing, and distributing large amounts of high-resolution process data, a historian will give you the best insights. So relational database vs historian database? You would obviously choose a powerful historian database?
