Optimised and more flexible energy management with time series databases

Time series can be used to precisely track changes in a given variable. With the increasing use of smart metering systems and IoT sensor technology, more and more high-resolution data is being collected and analyzed as time series. The new Heating Costs Ordinance (HKVO) also calls for remote readings in the future, with data being transmitted continuously. This recorded data can then be used to calculate correlations and analyze trends, revealing potential areas for optimization.

To continue storing and processing the growing volume of time series data in energy management efficiently, we have planned the integration of a dedicated time series database for the next generation of our energy management software. One particular requirement is that, in addition to classic energy data, an increasing number of customer-specific and context-related time series must also be handled by the database.

In order to find a suitable time series database for our use case, our DEV team pre-selected suitable candidates. Many time series databases, such as OpenTSDB or Prometheus, were out of the question, as these are only intended for regular time series or not for long-term storage. The three most promising candidates InfluxDB, TimescaleDB and Elasticsearch were then analysed more closely. They were evaluated qualitatively and quantitatively in a total of five main categories (schema, storage, analysis, development and operation) using performance benchmarks*.

A comparison of the three candidates

• InfluxDB is a purpose-built time series database developed from the ground up. It’s easy to get started with and is currently the most popular time series database, according to DB-Engines.com.
• TimescaleDB is an extension for the relational database management system PostgreSQL that was first released in 2018 and benefits greatly from its maturity and ecosystem
• ‍Elasticsearch is a distributed search engine that we already use for full-text search and log monitoring

InfluxDB's storage engine impressed with very high write rates, followed by TimescaleDB and then Elasticsearch.

Since InfluxDB is still quite young, complex queries are usually difficult and sometimes unacceptably slow. Queries in TimescaleDB can be formulated in SQL and the response times there are consistently solid, while Elasticsearch was particularly convincing with complex aggregations.

In addition to the write rates, InfluxDB also impressed with its very high compression rates. TimescaleDB was also able to impress with good compression rates for column-orientated, compressed time series data storage (TS-c), although both this and row-orientated storage (TS) still have limitations, e.g. updates are not yet possible, only inserts and deletes. Elasticsearch requires significantly more storage space than the competition.

‍

InfluxDB also provides comparably poor support in the areas of index management, development and operation, while Elasticsearch's verbose, less intuitive JSON query language and high resource requirements were a negative factor.

'Fast time to market' was the key

The introduction of a time series database, which can also be used to store any customised time series, represents an important milestone in the further development of our products, as we attach great importance to being able to offer our customers new features as quickly as possible. InfluxDB was ruled out due to its weaknesses. TimescaleDB and Elasticsearch achieved similarly good results in the evaluation. In the end, we decided in favour of TimescaleDB because it allows us faster development cycles. We at GreenPocket have many years of experience with relational databases and SQL plus the PostgreSQL extension was consistently convincing in the benchmarks.

New features already available

Customers of GreenPocket's energy management software can already utilise the new features. Any time series can be imported into the portal and analysed in the flexible analysis together with the classic energy data. The new formula editor can be used to calculate efficiency indicators across time series, for example. More information on the new features can be found in our blog post from April. But that's not all: GreenPocket is already working on the development of further innovative features based on the TimescaleDB time series database.

‍

*Two test data sets were used for the benchmarks - one ETD (energy test data) with classic energy data and ITD (IoT test data) for Internet of Things use cases.