0

I am a student and am currently programming an API in Python. Among other things, it is possible to register, log in, create a user profile with data, etc.

I would like to be able to store and retrieve sensor data as time-efficiently as possible. For example, the data from a light sensor. It should also be possible to assign this data to a user.

My question is, does it make sense or is it allowed to split the data into SQL and NoSql? I would store data that needs to be organized like user data in a SQL database and the sensor data in a NoSQL. Theoretically you can assign the same "id" to the measured values as to the user in the SQL database and so you could assign the sensor data in the NoSQL to a user.

Many thanks for the answers, tips and constructive criticism

1
  • 3
    "does it make sense" Nobody can answer this given the very vague level of detail i your question "is it allowed" Yes, there are no laws about using SQL and NoSQL together in any jurisdiction I am aware of. Feb 1 at 9:32

3 Answers 3

3

The first question you should ask before deciding how to store any Data is:

How are you going to retrieve this Data?

If you need to be able to query your data quickly by specific elements or combinations of elements, then the capabilities of a relational database might be better suited to your needs.

Relational database are generally:

  • Very good at finding lots of small bits of Data and putting them together.
  • Pretty rubbish at finding big blocks of Data and pulling them apart.

Relational Database SQL syntax doesn't have dozens of String-handling functions because it's just not what they do.

Second question:

What are you going to do with this Data after retrieving it?

Again, pulling a big, single unit of Data and passing it back to an application might lean towards a NoSql solution. One particular case is where you're retrieving files for use elsewhere, where there's even an argument to say that you shouldn't put them into a database at all.
As soon as you need to start "delving into" those big blocks and breaking them up [a bit], then a relational database starts to look a lot more attractive.

1
  • Hey, thank you very much for your reply. Unfortunately my answer is 1400 characters too long, which is why I had to post it as a "Add Another Answer".
    – flo
    Feb 1 at 14:50
3

I would like to be able to store and retrieve sensor data as time-efficiently as possible.

Are you sure? "As efficiently as possible" could lead to all sorts of overengineered schemes. Or do you just want to be reasonably efficient within your available time and cost constraints? NoSQL systems became popular for very specific use cases with very large numbers of users, no transactions, and limited kinds of queries. Whereas databases on even fairly ordinary computers in 2024 can achieve tens of thousands of transactions per second in Postgres.

Theoretically you can assign the same "id" to the measured values as to the user in the SQL database and so you could assign the sensor data in the NoSQL to a user.

That sounds like a query to me. Specifically, that sounds like trying to do a join between two different databases, which is always going to have poor performance.

Just build it in SQL. Once you get to the hundreds of thousands of users and start to outgrow your DB cluster, you can get your team to rethink this question.

1
  • It has helped me a lot. Yes, you are right, I had tried or thought about creating a "connection" between Sql and NoSql so that I can save the sensor data in the NoSql database. Thank you very much, I will continue with sql and test it.
    – flo
    Feb 1 at 14:49
0

First of all, thank you very much for the detailed answer!

Based on your answer (Phill) and if I understood it correctly, NoSql would make sense in my this case.

My thought process was as follows: the API or database should be used to store user profile data, settings and many other data for differnet "functions". I also want to be able to save sensor data at the shortest possible intervals and retrieve it as required. No old data should be deleted, only new data should be added and, depending on the function, certain data (e.g. from the last 24 hours) should be retrieved and processed externally. Theoretically, it should be possible for a sensor to provide real-time data, store it in the database and retrieve this data as quickly as possible via API on one or more devices.

If I only do this for one user, there should be no problem with a relational database, I think. But if, for example, 20 people use the API simultaneously (with more than one sensor) to store and retrieve the sensor data, there will (I think) be delays, right? I have read on the Internet that a relational database can only be scaled up to a limited extent with more hardware. I have also read that once the tables have reached a certain size, performance is (logically) impaired. The database would also grow relatively quickly due to the large amount of sensor data (e.g. sensor data).

Due to the fact that the query or the database should save a lot of sensor data quickly, there are also longer delays with other queries such as logging in or other functions of the API. Is this correct?

This is how my question arose and I asked myself whether it is smart to manage the sensor data via NoSQL, so that the database for the user data and so on is relieved and theoretically more people can use the API at the same time, many sensors can store data and without there being long delays.

I would be very pleased if you could share a few more words or your opinion with me in this regard.

Thank you for your patience :))

Have a nice day

PS: Unfortunately my answer1449 character is too long for a comment and I have to post this as a reply... I hope this is not a problem

7
  • "No old data should be deleted, only new data should be added..." - an absurd principle on which to design any computer system.
    – Steve
    Feb 1 at 14:16
  • People write many things; they won’t always be applicable to your situation. So write a PoC already and bench it, measure it. It’s not that hard. Configure N fake sensors to produce M observations per second. Crank up the rate till Postgres starts to fall behind. Now you have a design constraint. // Hint: to append more rows per second to the log, send more than one INSERT per COMMIT. It can also be helpful to append such records to local text file, to avoid losing observations due to power fail or reboot. See also “fsync”.
    – J_H
    Feb 1 at 19:06
  • @flo, not sure whether that's sarcasm haha, but on a serious note, you should never design any system on the assumption of infinite accumulation of data. A system that is otherwise quite functional and performant over a very reasonable period of time, may well eventually grind to a halt if you don't think up-front about how old data will be archived or purged. I've worked in a few businesses which have suffered pain like this.
    – Steve
    Feb 1 at 19:19
  • 1
    @J_H thank you very much! That actually helps me a lot. As I said, I'm a student and still have a lot to learn. Getting experience, help and approaches from an experienced person is worth its weight in gold. Thank you! I'll get right on it and give it a try. Thanks for the tip with the local file!
    – flo
    Feb 1 at 23:31
  • Thank you too @Steve for the answer. Please don't take offense at my sarcasm ;) But I have to agree with you and I have to think about this point. I don't know how off the top of my head, because theoretically I would also like to access "very" old sensor data so that I can display it as a graph. But if there are too many, you will probably have to cut back and limit this to 1 year or so and delete old ones. Thank you for taking the time to share your experience with me.
    – flo
    Feb 1 at 23:42

Your Answer

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

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