Time-series data can be handled by RDBMS.

This article is for the simplification of your data stack.

There are many NoSQL databases available now that can handle time-series data. Indeed, they answer many use cases and enable out-of-the-box connections with visualization tools. Before adding a new component to the data architecture (assuming it is a new addition to handle time-series data), let’s take a step back and consider whether this is truly necessary. Each new component poses its operational issues, as it will in the long run. 

This post will help you simplify your data stack. Let’s say your use case involves relational databases, and you now need to handle time-series data so that near-real-time queries may be run on it. Would you like to add a new component to the mix? 

The answer depends on various factors, so don’t dismiss the relational database just yet. Please continue reading to learn how to manage time-series data in a relational database and make an informed decision. 

To begin, let’s define what time-series data is. 

Data(Facts) recorded across a time dimension is called time-series data, and one of the axes for this data is time. This data is typically (but not always) recorded in near-real-time. It enables us to answer queries such as: 

​​

Time Series Data

Data(Facts) recorded across a time dimension is called time-series data, and one of the axes for this data is time. This data is typically (but not always) recorded in near-real-time. It enables us to answer queries such as: ​

• How many users visit the app every minute?
• What is the average number of products sold each hour? 
• What are the sales projections for the coming month? 

Now, this time-series data must be absorbed, stored, and converted in a way that yields meaningful results. When working with time-series data, there are a few considerations to bear in mind:

• It is consumed in a near-real-time manner. 
• It is a real-time reporting system. Near-real-time aggregates are typically calculated over this data. Because aggregation searches are temporal, any aggregations established on the data will alter over time as new information is added. 
• Although the data is append-only, it might have high ingestion rates, necessitating support for quick writes. 
• It is possible to estimate future metrics by looking at historical trends. Time series forecasting is done using machine learning techniques like ARIMA. 

Now the challenge is, how do we engineer this data in a relational database management system (RDBMS)? 

Relational databases cannot immediately address the data mentioned above considerations due to high ingestion rates and near-real-time aggregates. However, adding some padding to relational databases can make them far more capable of handling time-series data. The relational database we’ll be discussing now is PostgreSQL, and the padding we’re talking about is PostgreSQL extensions. 

Below, we’ll look at two examples of such extensions.

DB TimeScale

TimeScale DB is an open-source PostgreSQL extension. It can support PostgreSQL, including relational queries, full SQL support (not SQL-like), and real-time queries because it is included as an extension to PostgreSQL.

Timescaledb’s approach to time series data

Hyper tables, which are simple bits of data organized by time and saved in ordinary tables, are a notion. This nervous tablet serves as abstraction layers for users, allowing them to query, join, and perform other operations in the same way they would on a regular table. 

Consumption of these hyper-tables is rapid. According to the documentation, each server may consume 1.5 million metrics every second. 

Materialized views can be generated over these hyper tables for continuous aggregations, allowing for near-real-time collections. Internally, timeframe DB contains background processes that update the display, and the refresh interval and parameters can be customized. It also integrates with Kafka, Kinesis, and other systems for data intake. 

The query results are stored in a materialized view, which is a database object.

What is the best way to make real-time visualizations of this time series data?

Materialized views, which aid in creating real-time aggregates, can also be utilized to build real-time visualization. Because the extension is pre-installed with PostgreSQL, it may be used with any visualization tool, such as Graphene, to update continuous aggregates. 

So, now that you have this simple architecture, you can ingest time series data and even develop visualizations over it to display it on large monitors in the workplace (well, if we ever go to the office, fingers crossed). Isn’t it straightforward? Is it possible to use only a relational layer?

Additionally, it includes methods for forecasting time-series data! Timescaledb’s documentation is extensive and can be used to prove the concept.

​​

DB for pipelines

Pipeline DB is another PostgreSQL extension that may be used for real-time reporting and provides real-time aggregations in materialized views over time series data, similar to time scaled. 

The efficiency of pipeline DB is because it does not keep raw data; thus, if raw data must be saved, pipeline DB is not for you (IMO, it should always store it). However, it seems promising and serves the purpose, so it’s worth discussing here. 

That’s all there is to it. I decided to write this essay because I appreciate the idea of combining relational and time-series databases for stack simplification and complete SQL support. The problem with SQL-like queries is that learning them takes time, whereas full SQL allows for quick learning of the stack. 

And now you know how to give your PostgreSQL a cape and turn it into a super database. Well, joking aside, it’s critical to comprehend all principles before making any design decisions, and I hope this essay has helped you do so.

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.