I'm building an Internet of Things solution that has a number of real time feeds. I need to make a couple of design decisions with regards to the data store and reporting and looking for input.
I have two deployment models, local where a small Linux server captures multiple streams usually a new data point each second and record length is small, basically a fixed format message of about 20 bytes. The other model is cloud based where the transaction rate will be much higher, up to 1000 data points a second.
Concentrating on the local model first, my prototype uses SQLite under MONO and works fine in a traditional RDMS manner. However I'm aware that RDBMS isn't optimal for time series systems and possibly a NOSQL solution might be better.
I'm having a re-think on the SQL approach due to my real time analytics requirement. I want to be able to easily report on SUM, COUNT, AVERAGE, MEAN, SD, MIN, MAX on potentially each of the time series datasets, basically have the system recalculate these for each new data point. Although I haven't done any performance testing, I can see if I have a year or more data the dataset size is going to be pretty large (20 bytes / second over a year is 600MB and I could have up to 30 different time series datasets) and won't be great for real time calculation of averages etc. in normal SQL. I also want to keep the size / cost of the server low so won't be able to add gobs of CPU or memory but disk space should be OK.
The other option I have is that my system can store the delta (changes) to the real time feeds instead of the regular data point, which will save about 80% of the disk space. However I don't know of an easy way to do set calculations like running averages if I am storing deltas (as far as I know not possible with standard SQL statements but I could be wrong).
Given my use case above I have 3 questions:
1) Should I seriously consider a NOSQL data store? Do you have any suggestions on the advantages of a NOSQL store for this model?
2) How I can best do real time set calculations like average without a lot of complexity or hardware? I have platform options of either MONO or NODE on Linux or Windows.
3) How to do efficient real time set calculations (especially averages which will be the majority of calculations) on either SQLite or a NOSQL DB when I'm storing deltas?