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

The problem

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.

Diagram of our theoretical infrastructure

Option #2: But, to create a separation of our "real time" data and interpreted data, perhaps, something like this is better.

An alternative diagram of our theoretical infrastructure

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.

  • I can imagine the question I am asking is a bit broad to give a "definite" answer. At this point, I am mostly wondering about how to tackle this challenge and to learn from the experience of others so that I can hopefully prevent rookie mistakes. – derp Mar 22 '19 at 7:45
  • I added a diagram to visualize the infrastructure we're currently considering. I think this should be a really solid approach, but I am still unsure about the way we should structure our actual data set. – derp Mar 22 '19 at 14:31

I work for an IoT company and have recently implemented something similar (device sensor data readings) using a Kinesis stream, Spot Instances and DynamoDB.

The Spot Instances were a cost saving step to replace the Lambdas, which were processing the stream data and bulk inserting to DynamoDB, they were costing too much. We changed the Lambdas to EC2s and then to Spots to save cash.

I would suggest trying the Lambdas to get data from SQS and put it in a DB, but for scale and cheapness please look at DynamoDB for storage. The downside of DynamoDB is that you have to know your query paths before you build the table but as you are using an API you will probably know what those are.

| improve this answer | |

Part answers originally left as comments:

mustaccio: Whatever choice you make today, be prepared that you'll have to rethink it sooner or later. In any case don't let your devices or the API they call insert into the database directly; put a message queue in between. This way you can better manage the flow and swap out parts independently.

mark-s: I'd suggest taking a look at non-RDBMS solution too. We're using Druid for similar projects. You can use S3 as the storage back end, and users can access data with SQL.

raphael75: I worked on a site for many years that added several MB of data per day, and while it was much smaller than what you're dealing with, the queuing we added was essential in making it run smoothly. We also used AWS with Aurora. Since you don't need up-to-the-second reporting (according to your examples), I think what you have should work fine for now. This video goes into good detail about partitioning. It may be a good idea for your project.

| improve this answer | |

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