0

I am creating an infrastructure to save measurements coming from a fleet of around 2000 cars. Each car contains about 60 sensors (depending from car) with a sum of about 800 values par second coming from all the sensors.

Each sensor is reading from 2 to 50 values of different type (boolean, integer and commasep).

I would like to save all this values in a database (in cloud) to allow us to read them in case of error and for future reports.

After a study of the possible database we have to chose between:

  • postgres with autopartitions
  • TimescaleDB
  • InfluxDB

Knowing the scenario my ingegneristic side thinks about InfluxDB since the use case better fit a schemaless option. However my conservative side is saying to use a 25-years story database, in this latter case, from your experience is it better to adopt an approach 1 or 2?

Approach 1 is where each row consists in lecture of a value from one sensor -> [timestamp, sensor_id, measure_title, measure_value] (so 800 * 2000 rows every second).

Approach 2 is where a row consists in a lecture of a sensor [timestamp, sensor_id, measure_value_1, …, measure_value_50] (so 60 * 2000 rows every second) where potentially 49 columns can be null and we have another table that contains anagraphic for each title of measure_value_n?

Otherwise do you know other approaches?

Edit 1.

Data must be maintained indefinitely. No way of delete/cancelling

Approach 1 will store around 138 billion of rows par day Approach 2 will store around 10 billion of rows par day

Improve this question
0

1 Answer 1

Reset to default
1

I'm not really sure what your question is but if the data has a schema and / or is relational, use a relational database management system.

You don't even necessarily need to use a timescale type of database system (as that's a micro-optimization of the existing classic RDBMS) but it's fine if you want to as well. Either way should suit your needs similarly.

It sounds like you're concerned about performance since you're looking into timescale databases and mentioned partitioning in PostgreSQL. Performance shouldn't really affect your decision of which database system to use, since all modern ones perform similarly. And partitioning is not a tool really meant for improving performance.

Improve this answer
3
  • Thanks J.D. , the issue is that I actually have 60 sensors, but in the next months customer can ask for other sensors with maybe 100 value to read. My concerns are about to use one row for each measure or one row for each sensor (but number of values are not known ahead). With approach 1 I will write 138 billion on rows for each day. Is it something feasible to manage? And data has to be maintained indefinitely
    – Jam. G.
    Nov 13, 2022 at 15:57
  • @Jam.G. Doesn't matter, all database systems are equal in that perspective. Performance will be a factor of how you architect your database, regardless of which tech you use. I've worked with tables with 10s of billions of rows on modest hardware and sub-second query times. That being said, I don't follow your math. With your original description of 800 values per second, that's only ~70 million rows per day. 100 more values per second is only around ~80 million rows per day.
    – J.D.
    Nov 13, 2022 at 17:02
  • @Jam.G. Also, keep in mind that regular B-Tree indexes are O(log(n)) search time, which means if your table had 1 quadrillion records in it, in the worst case, the index would need to seek through log(1 quadrillion) = 50 nodes. It takes milliseconds to search through 50 nodes of a B-Tree to find the subset of data that you're trying to read. Depending on the total size of the data you're trying to read at one time, your bottleneck will more so be hardware dependent (e.g. disk speed, network speed, etc) and render times. All things that'll be equal regardless of which database system used.
    – J.D.
    Nov 13, 2022 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.