Time Series Data storing: RDBMS vs NoSQL

Question

I am facing, in these days, the problem of storing some Time Series Data.

This data is taken from an industrial machine: for each job (about 3 per hour, 24/24h), a software records:

• oil pressure;
• oil temperature;
• some vibrational data.

Vibrational data is taken at very high frequency (> 10 kHz), and leads to very massive memory requirements. This issue made my company evaluate some possibilities to efficiently store this data.

Inserts will be not very frequent (maybe 1 or 2 times per day, when the machine is not operative). Reads will be potentially very frequent (another software will retrieve data for plotting and analyzing purposes).

For now, a single node will be used for storing data, so I don't want (for now) to take into account partitions and parallelization matters.

What solution should I prefer? A relational DBMS (such as MySQL or PostgreSQL), or a common-purpose NoSQL DB (e.g. a column-oriented one - consider that all Time Series will be univariate -, like Cassandra, or a document-oriented one, like MongoDB)?

Beyond my particular use case, when generally to prefer RDMBS over NoSQL for Time Series storing? When to prefer NoSQL over RDBMS?

Answer 1

tl;dr:

Typically for time series, I would use a time-series database like InfluxDb.

Use NoSQL for unstructured high volumes like: logging results, website search data etc. Ideal to optimize for a particular query.

Also use NoSQL (document-store) when you have simple entities which can contain basically everything there is to say about the entity. Comes in handy with micro-services due to the often small data models.

Use Relational database: when you have a hierarchical structure like: the in and outs of the workflow of a sales process. Relational works better if data-structure integrity must be kept over a lot of data.

Here's a nice summary on how to deal with various relations relational vs document store: https://completedeveloperpodcast.com/document-vs-relational-databases/

---

Well, in general, there is a lot around on the net on this subject. In general, in a relational database, the schematics are known "upfront" - although it can change over time, it's pretty static.

The big "benefit" of most Not-only-SQL is that they:

• do not require a fixed schematic and fixed relations to maintain data consistency. This means - e.g. a graph database - you can relate to other objects easier and more flexible or you have must a couple of independent tables.
• by design are capable of (better) horizontal scaling, which is - in bigger systems - is a big benefit in solving performance related issues. (Consider of to be a couple of independent tables to see why)
• data does not need to be (very) structured. This again, is a benefit if you need to include external data sources or typical unstructured data in your database.
• works well in small entities, query optimized storage.

note: there are multiple NoSQL database types, all with a different approach and their own pro's and con's.

---

So:

Beyond my particular use case, when generally to prefer RDMBS over NoSQL for Time Series storing?

When using RDMBS you need to - at least - know your schematics upfront, and they are not expected to change very often.

You prefer RDMBS if:

• this kind of structured data and consistency checks are an intrinsic property of the data you are storing. For example: to maintain a warehouse inventory listing, keep track of work-hours, etc.
• your data store can be seen as an isolated authority. For example: a file system indexer or product test result storage.

When to prefer NoSQL over RDBMS?

You prefer NoSQL if:

• You cannot determine all relationships upfront and expect to add data, sources and relations on a frequent basis. Typical use cases are big-data stores, relationship stores; more concrete: social networking, advanced statistical correlations or frequently changing external data providers.
• You need high-scalability, which is more natural in most NoSQL systems.
• You just want to dump some data somewhere in a cloud in a more or less structured way. Create, for example, a simple table to hold settings records.
• have simple entities and queries which doesn't have the need for complicated joins and hierarchical data

---

As for your use case:

It seems that your data structure is well known and fixed. This pleads for a relational database.

The amount of data and it's simplicity is an argument to go for the NoSQL.

As for the high load: the data structure is known upfront as well. Nevertheless, there are some catches involved to deal with the high load. A relational database can be configured to coupe with this amount and perform very well, but NoSQL is usually better optimized for this.

---

I feel this balances out a bit, and whilst in the past going for relational; I would go for a document store in this case now.

---

It does, however, poses another question: Since you are monitoring 24/7; how often do you need data of last year, or the year before? Last month or week?

I am just asking because there are more options to coupe with these amounts of data. Historical data is often treated as a log and requested only "now and then". In that case, you could store data chucks on different servers, or even in different forms. For example, the 10kHz vibration data could also be stored on a dedicated server, in the form of a blob, or stored data stream.