Skip to Main Content
  • Questions

  • Using UUID as Primary Key in High-Volume Databases

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mahdi.

Asked: April 06, 2023 - 8:06 pm UTC

Last updated: April 12, 2023 - 11:57 pm UTC

Version: MySQL EE 8.0

Viewed 1000+ times

You Asked

Hello,

I would like to know if using UUID as the primary key in a database can cause any issues, assuming a fixed length. If you have experience with high-volume databases and different versions of databases, I would appreciate it if you could share it.

For my database, I am using Oracle Database Enterprise Edition 19c or 23c and MySQL Enterprise Edition 8.

and Connor said...

There is a lengthy but excellent discussion on this on an old forum post

https://forums.oracle.com/ords/apexds/post/using-sys-guid-as-primary-key-7247

If you read that, you can come to your own conclusions but for me:

- I'm not a fan of GUIDs because you can see from the post, that whilst *theoretically* its a great option, there can be many idiosyncrasies based on the platform/version you use etc.
- I generally prefer sequences with a high cache value (although see the blog post below) and global partitioned indexes to eliminate contention.

https://connor-mcdonald.com/2021/08/06/we-killed-sequence-contention/



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 11, 2023 - 12:05 pm UTC

My intention regarding is in terms of performance and indexing method in new versions. In old versions like 11, there were many problems with uuid indexing, which forced us to use partitioning. Are these problems still present in the new versions?
Connor McDonald
April 12, 2023 - 11:57 pm UTC

"In old versions like 11, there were many problems with uuid indexing"

In what sense?

A reader, April 11, 2023 - 12:17 pm UTC

My reasons for not using sequence in the application are due to security issues. If we use sequence in our application, we can easily fetch all the data from the web and database using a for loop(with attacking tools).
Connor McDonald
April 12, 2023 - 11:57 pm UTC

Add a random component and you're good to go.

AskTom question ID's are an example of this
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.