We developed a system with very high volume of data but very simplified data structure. We had only cellX
, cellY
, timeStamp
and value
columns.
Operations are:
- Insert 455+ thousand rows per day
- Query using range filters on
cellX
,cellY
andtimeStamp
- 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
cellX
,cellY
andtimeStamp
. - 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?
Edit
The three dimension columns cellX
, cellY
and 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
).