Design decision - to use UUIDs as primary key or not?

[vlad1]

Hi,

SQL Anywhere database, to be used by a Powerbuilder 11.5 application.

We're at the stage of designing a set of 6 tables, all of which cascade down to the next one, like

Country->Region->District->Organisation->Group->Member

The dilemma that we are facing now is that the last three tables will have about 15.000, 30.000 and around 200.000 rows, for which we need to generate long random IDs. We have never used UUIDs as primary keys so far and we are considering using them as primary keys for these last three tables which will have thousands of rows.

However, having no experience with UUIDs, we are reading up on them now, and there are several articles suggesting that UUIDs are not very efficient, especially if we link up these last three tables with foreign keys as described above: Organisation->Group->Member.

So our question is: with these table sizes, are we likely to run into noticeable efficiency problems with using UUIDs as primary/foreign keys? And if so, what would be the more recommended approach?

Cheers

[former_member188493 · Accepted solution]

If your application is going to have a future where administration, maintenance, enhancement and debugging tasks will be ongoing, then do NOT use UUIDs as primary keys... they are impossible for human beings to deal with in a production volume setting. The argument that artificial identifiers (like UUIDs) can be hidden from the end user is a theoretical argument with no absolutely no validity in the real world... administrators and maintenance developers are also end users when it comes to the data, and primary keys cannot be hidden from them when dealing with problems.

Over the years I have held many strong opinions for a while and then given them up as experience grows; for example, "always reject invalid user input" (it's usually more important to save user input now and fix it later), "wizards are good" (nobody likes using wizards), "small is always better" (simple is always better, not necessarily small), "never use SELECT *" (it is only bad in one single context out of many) and so on.

However, "if you care about people, never use UUIDs" is an opinion that has stood the test of time.

Autoincrement keys aren't perfect but they are better than UUIDs. Global Autoincrement (partitioned) keys are often used for synchronization purposes... those values are harder to deal with but STILL way better than UUIDs.

[vlad1 · Accepted solution]

Thanks very much Breck, helpful as always. After investigating Global autoincrements, we played around with them and decided that these indeed suit our needs much better.

Cheers!

[JasonHinsperger]

If you are looking at UUIDs vs. autoincrement or some other sort of integer based key, the main differences from the database perspective are:

1) UUIDs require more storage space than auto-increment and indexes on these columns will be less efficient.  However, your tables are small enough that you are not likely to see a significant difference between using UUIDs as opposed to auto-increment keys.

2) UUIDs have no order, so you can't use them as an indicator of when a row was inserted compared to another row.

HTH,

--Jason

[vlad1]

No, autoincrement is not important, and we won't use it as any indicator of the sequential order. Right now we are overhauling an old system where each of these records had for a primary key a completely random long 16-digits integer to ensure uniqueness, as it is highly likely that there will have to be lots of databases needed to be synchronized.

Thanks!