We developed a system with very high volume of data but very simplified data structure. We had only
- Insert 455+ thousand rows per day
- Query using range filters on
- Query is not required to return instantly, we can notify the user when requested data is ready.
Because the data was too large and we required index for query, we used this scheme:
- Use SQL Server.
- Clustered index on
- Separate tables for every year, therefore total number of rows in a table remains within limit (~166 million).
- Use a custom format for
timeStamp. Skip year, keep only month, date and hour. We were able to keep it within 16 bit int.
- Use partition on each years table.
- Insert one day data at a time. Use partition switching to keep the database live while inserting data.
This has been working good so far. Although we notify the user after data is ready, the delay is never more than a few seconds as long as the query is reasonable.
But recently we got the opportunity to get more precised data and the data volume has been increased by 68 times!. Therefore, now we have:
- insert 30+ million rows per day.
- store 11 billion rows in a table for a year. This can be reduced by making quarterly (2.7 billion) or monthly (1 billion) tables.
This is possible that we might be able to receive more precised data in a year or two. Therefore it is possible that data volume will increase again by a significant factor.
The question is, will this scheme we are using sustain? Or we should migrate to another scheme, may be another database system leaving SQL Server?
The three dimension columns
timeStamp are very regular in nature. You can define all of them by
f(x) = mx + c, for some integer
x ranging (
0, 1, 2, ..., X).