I'm developing a software solution that requires storing raw data points originating from many PLCs (programmable logic controller, used to control industrial machines such as conveyor belts and robots) into a SQL Server database. My main concern is how to properly store large amounts of raw numeric data.
Each data point has the following properties:
- Datetime stamped
- May be a: boolean, integer, floating point, or a string
- A data point may be stored for each second all year (~31,536,000 data points per year)
Each PLC I'm collecting data from may have as many as 500 data points, and I want to plan on supporting thousands of PLCs. Meaning, I'll have at least 15,768,000,000,000 data points per year (31,536,000 points per year * 500 points * 1000 plcs). I currently have a table for each data type (bool, int, float, string). In an effort to reduce the number of records and storage, I am storing 4 data points per record [ID, DateTime, DataGroupID, Value1, Value2, Value3, Value4].
I have this implemented, and it's working well (fast queries with my largest table at ~72 million records) though it is isn't very flexible (Changing data types requires moving data from table to table, regrouping data requires table/column changes, etc.)
So, my thought to improve flexibility is to keep the data type specific tables, but to simply store each data point as its own record. Some brief math of the max size of a bigint (the data type of record ID), showed that I have plenty of IDs available. Though the storage requirements, and query performance is still a concern. Should I be concerned about anything using this method? Is there a better option?
The root of my concern is that I simply have not experienced gigantic databases/tables, so while I know that a few million records is small, I still don't know what an over-the-top table would look like. In addition, the storage of the data is the foundation for my software, so updating requires a lot of work to make it happen.