2

I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day).

Requirements:

  • Spatial queries must be fast
  • Temporal queries (on a timestamp column) must be fast
  • The table must be partitioned over the timestamp column

The schema might look something like this:

Table schema

With this schema, I can not use a clustered columnstore index due to the Position column which is a spatial type. My gut reaction to this was to split out the Position column to a new table with a 1-to-1 relationship.

Table schema revised

However, this also introduces some problems. The spatial index will not be partitioned, unless I partition the second table. To do that, I will have to duplicate the timestamp column.

Is there a better way to achieve a partitioned clustered columnstore index for this dataset?

  • 2
    The partitioning column must be part of the primary key (and all partitioned unique indexes) so the BroadcastInformation primary key will need to be implemented as a non-clustered rowstore index with both BroadcastID and Timestamp. Those columns will also need to be the BroadcastPosition primary key anyway for the one-to-one relationship. I suggest you add representative queries to your question and specify if forgoing constraints in the interest of performance is an option. – Dan Guzman Aug 20 '17 at 11:37
  • Just a quick comment on this question in case someone stops by in the future; A very good stack to solve this is PostGreSQL + PostGis + Timescale. This stack is capable of outstanding performance on large amount of spatial time series data, and it's open source all the way. – andrerav Jul 30 '18 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.