Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I want to write a web service that stores home automation device readings such as thermostats, humidifiers, etc. The data would take the form of:

user_id | device_id | tag | value | timestamp

A user could have 5, 10, or 20 devices connected based on account type and readings could be taken at 1, 5, or 10 minute intervals based on account type. The service could record 10, 50, or 100 tags for each connected device.

This means I would theoretically need to support a batch insert of 2000 rows per user per minute.

Let's say I want to scale to 10,000 users. That means an upper limit of 20,000,000 rows inserted per user per minute. So 28 billion rows per day.

What kind of databases should I be considering to handle this kind of load and how would it scale? I'd especially be worried about storage space, I think it would need to make use of cloud storage.

share|improve this question
1  
What kind of queries are you going to be asking it? Analytics, primary key lookups, etc –  rfusca Mar 12 at 17:03
    
Analytics. Date range graphs of selected tag values. –  Chris G. Mar 12 at 21:25
    
I could probably limit the dataset to have 1,5, and 10 minute intervals for a 30 day period per customer. For data older than that, perhaps I can just save one row per tag per day with max/min/avg values. –  Chris G. Mar 12 at 21:27

2 Answers 2

I've heard of a company which is doing this. They're using a Cassandra cluster with several nodes (in two data centers).

share|improve this answer

I would ask you first how much money are you willing to spent for this ?

I terms of number of rows stored in the database it all depends on your data type,length.. so you can figure out how much space you will need.
Now to answer your question and give you my database engine recommendation i would go for HP Vertica - by far the best choice for your project.

Why is HP Vertica the best choice :

  • 1- is much cheaper then Oracle or teradata or and DW - and does not require self appliance.- you can insstall it on any linux box.
  • 2- it works in cluster so MMP(massive parallel processin) will be a great add in.
  • 3- since you say you have alot of data - Vertica comes with build in capabilities to store encoded/compressed data and as well query encoded data.
    Depending on the data type you might get up to 90% in storage gains.
  • 4- i see you have a time window between data load:
  • 5- Vertica scales out linearly and no need of down time, the nodes are shared nothing arch so is like plug and play basically- add the node re-balance the data and that's it the node will participate

    With Vertica you can load those 20.000.000 rows in no time using its load capabilities.

  • Because of it internal structure Verica will handle big loads with ease.

  • Working with Vertica is so easy it comes with SQL ANSI syntax , has some great build in functions that can help you deal with lots of big data problems.


More on how you can build a Vertica Cluster in like 10 min see this link and as well this one link2 - both of this link will get to my website where i have wrote some articles on how to work with Vertica database.

Fell free to contact me if you need any guided help - i would gladly do, no charge what so ever , i just love this Vertica database .
One more thing i don't represent in any way any HP or any HP re-seller. enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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