Best way to store Time series data with heavy writing and high aggregation. (~1 billion points)
Check out some time series databases:
Installed Software
- OpenTSDB: http://opentsdb.net/
- RRDTool: http://oss.oetiker.ch/rrdtool/
- Graphite: http://graphite.wikidot.com/
Services
- TempoDB (disclosure: cofounder): https://tempo-db.com/
Others that might be worth a look:
- Kairosdb rewrite of OpenTSDB for Cassandra
- Blueflood (also using Cassandra)
- Axibase Time-Series Database runs on top of HBase
- Time Series Database wikipedia page.
For simplicity, I would just make a table of timestamps with a column for each measurement point, and an integer primary key would be technically redundant since the timestamp uniquely identifies a measurement point, however it's easier to refer to a particular row by number than by timestamp. You will have nulls for any measured parameter that was not taken during that timestamp, which will take up a few extra bits per row (log base 2 of number of columns, rounded up), but you also won't have to do any joins. It is true if you decide you want to add columns later, but that's really not too difficult, and you could just make another separate table that keys on this one.
Please see here for an example with your data: http://www.sqlfiddle.com/#!2/e967c/4
I would recommend making some dummy databases of large size to make sure whatever structure you use still performs adequately.
The (time,key,value) suggestion smells like EAV, which I would avoid if you're planning on scaling.