SQL Server -is a GUID based PK the best practice to support tenant based horizontal partitioning

Question

I’m trying to figure out what the best approach is when designing a multi tenant database schema that will need to be horizontally partitioned in the future.

Some Rough Numbers on the database..

Total number of tenants will be approx 10,000. The amount of data stored per tenant varies between 500MB -> 3GB. The number of tenants will start off small and grow to 10,000 over a few years so initially we can start with a single multi tenant database but in the longer term this will need to scale horizontally for performance reasons.

Update - a complicating factor is that occasionally tenants (companies) can merge together and I need to support this as well...,

The multi tenancy will be implemented using a Shared Database, Shared Schema architecture as described in this paper http://msdn.microsoft.com/en-us/library/aa479086.aspx

Given that we’ll be faced with horizontally partitioning in the future and that it’s likely that we’ll be moving clients from one database to another several times before things settle down I think that it’s best to use GUID’s as the primary keys on every table along with a unique tenantID column.

I know that there is a performance overhead in using GUID’sas a primary keybut is this a trade off that I just need to accept? Is there another way to design for horizontal partitioning in the future ??

Heres an example - lets say I want to merge companies with tenants 100 and 200 in the future, if the PK is an integer there may be a collision when I copy the rows fromn database 2 to database 1, with {guids} i am guaranteed there wont be a collision...

database 1 database 2 tenantid, id, description tenantid, id, description 100 ,1 , 'foo' 200 ,1 , 'xxx' 100 ,2 , 'boo' 200 ,2 , 'yyy'

database 1 database 2 tenantid, id, description tenantid, id, description 100 ,{aaa} , 'foo' 200 ,{ccc} , 'xxx' 100 ,{bbb} , 'boo' 200 ,{ddd} , 'yyy'

Answer 1

GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

• Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
• 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

Marc

Answer 2

There are 2 things of note here:

1. identifying a tenant's set of rows within all rows
2. identifying a row within a tenant's rows within all rows

Point 2 is the primary key.

Using a GUID to identify a tenant is useful because you can't guess back to another tenant's ID (like if you use IDENTITY as per that whitepaper) column. But a GUID for the clustered key is a bad idea (as per marc_s' answer).

This leads to a composite PK of GUID and an IDENTITY column, probably

• the IDENTITY first as a unique clustered index,
• the GUID is an FK of a tenant table, non-clustered index
• and the PK on both columns but non-clustered

This should be a reasonable compromise to cover most query patterns and FKs of this table.

Of course, it depends on the final design: I've assumed here that this is some kind of "fact" or "parent of fact" table