#White Papers

The benefits of a relational database versus a historian database

Manufacturing companies are increasingly collecting data and it has become correspondingly more important to collect, save and distribute all stored data for further use. Access to this information lays the foundation for faster decisions, increased productivity and reduced costs.

In this article, George Walker, managing director of Novotek UK & Ireland, takes a closer look at the difference between a time series or historian database and a relational database, to help you choose the one that best suits your needs.

Many industrial companies use relational databases (RDBs) for this task of increasing productivity and reducing costs. 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 data from the local system all the way up to the company level, giving you an overview of the entire production process. Here is a closer look at some of the key differences and the relative benefits of each database.

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 system 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, for example, 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 reduces costs.

Speed

Because 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. Compared to regular relational databases, Proficy Historian is 100-1000 times faster at reading and writing.

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.

Availability and redundancy

It’s important that production data is always 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.

The verdict

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?