Tuesday, November 14, 2017

Using GuidCOMB in SQL Server and PostgreSQL

On a project I’m working on, we expect to have a really large amount of data. Therefore we decided to switch our ID strategy from Integers to GUIDs. Problem is that when you start using GUIDs as part of your database index, they become really fragmented resulting in longer write times.

To solve this, you can use the GuidCOMB technique where a part of the GUID is replaced by a sorted date/time value. This guarantees that values will be sequential and avoids index page splits.

NHibernate and Marten supports the GuidCOMB technique out-of-the-box but if you want to use it with other tools you can try RT.Comb,  a small .NET Core library that generated “COMB” GUID values in C#.

Here is a sample how to use it in combination with Entity Framework:

  • Let’s first create an Entity Framework Value Generator that uses the RT.Comb library:
  • To apply this generator when an object is added to a DbContext, you can specify it in the Fluent mapping configuration:

No comments: