The current situation
We're currently looking into a new product that will send device data back to us to interpret.
These are the numbers we're looking at:
- Devices will most likely send data every 5 minutes
- 26.000 devices by the end of next year
- 26.000 INSERTS every 5 minutes. We will most likely have little control over the interval, so chances are that these 26.000 INSERTS are not evenly spread across these 5 minutes.
- ~ 2.733.120.000 data entries per year
- Each packet will be JSON-formatted, with a size between 300 - 500 bytes.
- We'er expecting about 8.000 new devices every year.
We currently manage several databases for our internal systems, but have little experience with volumes like this. We use AWS Aurora right now, which, in theory, should support 100.000 INSERTS p/s.
How will this data be used?
The data will primarily be used to create reports in our customer portals:
- Real time reports of device metrics
- Historical reports, IE.:
- How did device stats look on Feb 2th, 2019?
- What did week 12 look like?
- Give me a summary of January's metrics
- Show a graph of a specific column sum, grouped by month
To be quite honest, I find it pretty hard to make a solid choice, considering I don't have any hands-on experience with data-volumes like this.
Our current stack
We use a combination of AWS EC2 machines and an AWS Aurora cluster to manage our data. The ideal solution would be AWS-oriented.
Infrastructure I am considering:
Option #1: To keep things simple, storing everything into Aurora directly could be a good solution.
Option #2: But, to create a separation of our "real time" data and interpreted data, perhaps, something like this is better.
The actual questions
- Is a MySQL-compatible database management system, like Aurora, suitable for something like this?
- The incoming data will be used to generate "realtime" daily, weekly, monthly and yearly reports, aggregated per device. Would it be advisable to create separate tables for these different "perspectives" to make querying the data easier, or am I overcomplicating things and should I just store the measurements into one table?
- Should we look into table partitioning?
- Is there anything else that I did not mention, but we should look into?
If all of this is too vague, please let me know so that I can clarify the issue.
Would love to hear your thoughts.