I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start testing, though 5 seconds maximum.
If I do a single entry for each sensor at each second, I can get away with just 5 columns,
sensorValue. However, that equates to
(86,400 * 1000) * 31 or 2,678,400,000 rows in a month. That is a whole lot of rows.
If I had 1004 columns, I could do
86,400 * 31 or 2,678,400. That is a ton of columns.
Most of what I've read online tells me it is always undesirable to use columns in this way for RDS. However, if I were to do an entry per sensor, do I dump in a single fact table? Of course I wouldn't make 1000 tables, but should sensors be grouped together in fact tables, maybe by unit (flow, amperage, etc) to make the data set smaller for queries?
Or maybe RDS is not the proper choice for this application, and I should consider NoSQL? I have never worked with it.
I'm using a 16GB, Intel Core i7-4650u machine with a 1TB SSD for the development environment.
EDIT: I should note that my data pipeline is summarizing this data and dumping it in other tables. The issue is that this has to be an on-prem solution, and I want to be able to bring back big batches of raw data for analysis. Hence the long period and small grain.