Preliminary work
There are several database solutions existing like relational databases (e.g. PostgreSQL), non-relational databases (e.g. MongoDB) or familiar approaches (e.g. search engines).
After informing ourself about pros and cons of the different database solutions and also obtaining external opinions, we decided to stick with MongoDB as our preferred database solution. Based on the insights that were gained, a quick performance analysis was conducted to finally decide on one database solution for the project. For this purpose, special emphasis was laid on TimescaleDB as a timeseries-extension build on the relational solution of PostgreSQL and the native timeseries-capabilities (collections) of MongoDB. Both approaches were set up in a cloud-environment and not self-hosted. Therefore, MongoDB Atlas (MongoDB Atlas Database | Multi-Cloud Database Service ) and Timescale (PostgreSQL ++ for time series and events | Timescale ) were used in their free base-versions. As a sample data base, 250 sample measurements from the history of OpenSenseMap (/ ) were inserted.
The basic performance tests included the insertion, filtering based on the timestamp and also aggregation by using views. Basically, everything worked fine in both approaches. Regarding the query speed, TimescaleDB performed slightly faster, but both approaches were acting within a reasonable range. This also confirmed the experiences that Matthias Pfeil told us about. Basically, in the context of our project and possible subsequent projects in this domain, every database solution considered will work just fine. As the differences in query speed were only marginal, our team decided to stick with the cloud-hosted MongoDB approach, as we already made our experiences in this domain and also we had already established some backend-logic reliant on MongoDB as a database solution.
In order to test for any shortcoming or other concerns, little scripts were written to fetch sensor measurements and general data from the OpenSenseMap API (Loading...) and store it directly in our MongoDB database. These scripts can be found in the backend-branch “fetch-test” (GitHub - tinyaiot-ps/backend at feature/fetch-test) and parts of it might also be reused in the final backend-implementation.
As a result of this test it can be stated that there are no concerns when fetching data from the API and storing it in a MongoDB Collection. The only thing to note is that, when trying to insert data in a timeseries collection, this data must have a special data type.
© 2024 The TinyAIoT Project Seminar Team at the University of Münster.
This documentation is licensed under the Creative Commons Attribution 4.0 International license unless explicitly noted otherwise.
