We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to store the data.
Basically we have dataloggers which send data back to the server every 5 minutes. With current system we only have few sensors as we've just been starting so we only have approx 250 loggers out there and they are generating data at a pace of 2.5gb per year or about 21M rows per year. The data is mainly just numbers like light levels, temperature, humidity etc. We expect these logger numbers to grow quite fast and we should have thousands of loggers by end of the next year.
However with this next generation platform we'll have multiple sensors that can be used and multiple similar sensors can be connected to one system. System will have about 8 ports for sensor boards but each sensor board can have multiple sensors. As an rough example one system can have anywhere from 5 to 100 different kind of sensor inputs. ( 2-10 times as much as we currently have)
Data is only written once and then only read. 99% of the reads are to data which is maximum of two months old.
So far I've come up with few different options and I'd love to hear if someone has good insights which one might be the most scalable.
- Have a main Ping table which contains some basic data for each report and then have separate tables for each sensor board types. If all of the data needs to be shown the select would be at most 8 table join.
- Have one very wide table with all the ping data
- Fully or partly use NoSQL ideas and insert most of the ping data in JSON or similar format.
Secondly it would be interesting to hear as to what database setup would be the most scalable:
Have a production server which has maximum of 2 months data so it would be easier to keep fully in memory. Offload all historical data to secondary server.
Have everything in one server.