Auto increment keys vs. UUID

Tldr: Programmers should love values and UUID is a value.

Today I would like to write about not so obvious point of view on classic database design question: “Auto increment ids vs. UUID, what is better”.

But first we need some basic information. Primary key can be divided into two main parts:

Nature key
Nature keys are keys that are for free. We have a table where we want to store an object which has an attribute which is unique in the whole set of similar objects which can be stored in this table.
Surrogate key
In this case objects, we want to store in a table don’t have a unique attribute — or this unique attribute is too long.

In this blog spot, I write about a surrogate key. I believe that in bigger system nature key isn’t very convenient way to ensure uniqueness of records in a table.

What is what?

Auto increment primary keys.

The auto-increment key allows a unique number to be generated when a new record is inserted into a table. So every time we want to create a new record the database engine creates primary key automatically, and its responsibility is that this key is unique to the table.

UUID

UUID is unique too. But it is unique in “the whole universe”. It is not, of course, true. When we talk about the uniqueness of UUID we talk about “practically unique” rather than “guaranteed unique”. But for our daily purposes, this kind of uniqueness is all right.

In the following section, I described well-know disadvantages of these keys.

Obvious disadvantages of UUID.

• The main disadvantage of UUID is a performance in relation databases. In the database like MSSQL, MySQL or Oracle primary keys can have some performance issues. The main problem is inserting to database. If we have clustered primary key in our database, the SQL engine has to do some reordering rows while inserting new rows. So this can be an issue if the table contains a large number of rows. This issue can be lowered by sequential guid(for example in MSSQL )
• UUID can make a debugging more cumbersome — for instance, work with object with id 10 is more comfortable than with object with id 1FA169A1–1…. But for me, this is a minor disadvantage.
• Storage. UUID is a four times larger than the traditional 4-byte index value. So it can have some performance and storage issues. So when you concern about storage and performance you should thoroughly decide if using UUID can harm your system.
• Lack of easy ordering. The auto-increment key can be used ordering. We can easily see in what order the records were inserted.

Obvious disadvantages of auto increment keys

• These types of keys aren’t suitable for distributed system. When we have a table which generated primary keys we don’t have an easy way to have this table in distributed environment.

Not so obvious advantage of UUID over auto-increment keys.

Now I would like to talk about others advantages UUID over auto increment ids. My view is an only philosophical view, but it is also vital. So hold on.

Value, first of all, we need to step aside and clarify what value is. Value has some significant traits. There is some of them:

Transparency

Let’s have a value “1”. It’s number without any other meaning, and it is not unique.

Let’s look at UUID. We have UUID like this:
“3FDBC820–1F8D-4855–9A69–9FDCFA31B7DF”.

It’s string representation of UUID. The primary benefit is that it is unique. But it isn’t the only benefit.

Creating a value
Let’s talk about creating value. How can you generate value “1”?.
In C# it is very simple:

var val = 1;

How we can create an uuid in C#.

var val = Guid.NewGuid(); // Guid is Microsoft implementation of             UUID.

Ok, it is the same.

And now how we can create the primary key for a row in sql table.

In C#:

var obj = GetNewObject(); //create some object and obj.Id == 0
ORMService.SaveObject(obj);
obj.Id // obj.Id == some new generated id.

And now how we can generate primary key UUID.

var primaryKey = Guid.NewGuid();

Wow. It is utterly simple and transparent. The same code can create unique value and value for primary key in C#. We don’t have to connect to the database to create a primary key. It’s BIG UUID’s advantage over auto-increment id.

Environment independent

Another section is about environment dependency. We don’t have to rely on the database which can generate the primary key. UUID is environment independent.

The value of number “1” is a numeric type, and it is a real number. It’s uniqueness is bound to environment(in our case it is a database table)

What if we want to change database which doesn’t have a feature of generating auto-increment primary keys. We can’t. UUID approach is much more universal.

UUID has another good “traits”. It’s uniqueness have provided another value. It can support distribution. We can have one table split and placed on two physical sql servers. If we have a autoincrement key, we are doomed.

UUID is a real value not a pseudo value like a number in an SQL table.

The auto-increment key is so 90s. It was created in some time and in these times served very well. But these times are gone. I often hear and read that auto increment key is great, and it should be the first option when we need to make a design decision about our database design. But I hope that after reading this post you try to challenge this programmer’s and database designer’s “muscle memory”.

So, this is an excellent time to abandon this dogma. UUID is new black in the word of database design.