CQRS and primary key: guid or not?

Question

For my project, which is a potentially big web site, I have chosen to separate the command interface from the query interface. As a result, submitting commands are one-way operations that don't return a result. This means that the client has to provide the key, for example:

service.SubmitCommand(new AddUserCommand() { UserId = key, ... });

Obviously I can't use an int for the primary key, so a Guid is a logical choice - except that I read everywhere about the performance impact it has, which scares me :)

But then I also read about COMB Guids, and how they provide the advantages of Guid's while still having a good performance. I also found an implementation here: Sequential GUID in Linq-to-Sql?.

So before I take this important decision: does someone have experience with this matter, of advice?

Thanks a lot!

Lud

Answer 1

First of all, I use sequential GUIDs as a primary key and I don't have any problems with performance.

Most of tests Sequential GUID vs INT as primary key operates with batch insert and selects data from idle database. But in a real life selects and updates happen in SAME time.

As you are applying CQRS, you will not have batch inserts and burden for opening and closing transactions will take much more time than 1 write query. As you have separated read storage, your select operations on a table with GUID PK will be much faster than they would be on a table with INT PK in a unified storage.

Besides, asynchrony, that gives you messaging, allows your applications scale much better than systems with blocking RPC calls can do.

In consideration of aforesaid, choosing GUIDs vs INTs seems to me as be penny-wise and pound-foolish.

Answer 2

You didn't specify which database engine you are using, but since you mentioned LINQ to SQL, I guess it's MS SQL Server.
If yes, then Kimberly Tripp has some advice about that:

• Disk space is cheap...
• GUIDs as PRIMARY KEYs and/or the clustering key

To summarize the two links in a few words:

• sequential GUIDs perform better than random GUIDs, but still worse than numeric autoincrement keys
• it's very important to choose the right clustered index for your table, especially when your primary key is a GUID

Answer 3

Instead of supplying a Guid to a command (which is probably meaningless to the domain), you probably already have a natural key like username which serves to uniquely identify the user. This natural key make a lot more sense for the user commands:

1. When you create a user, you know the username because you submitted it as part of the command.
2. When you're logging in, you know the username because the user submitted it as part of the login command.

If you index the username column properly, you may not need the GUID. The best way to verify this is to run a test - insert a million user records and see how CreateUser and Login perform. If you really to see a serious performance hit that you have verified adversely affects the business and can't be solved by caching, then add a Guid.

If you're doing DDD, you'll want to focus hard on keeping the domain clean so the code is easy to understand and reflects the actual business processes. Introducing an artificial key is contrary to that goal, but if you're sure that it provides actual value to the business, then go ahead.