Question 114486 in PostgreSQL Question posted in
The official documentation can be found here.

Optimizing PostgreSQL performance when using UUIDs as primary keys

I’m new to databases and have been considering using a UUID as the primary key in my project, but I read some things which made me interested to learn more about the performance.

Currently, I am using the uuid data type and the default value is set to gen_random_uuid().

First of all, I was wondering: would UUIDs be less performant as a primary key?

If so…

  • Are there any tips to optimize it, e.g. would it help if the PK was sequential, but another field contained a UUID, specifically for public exposure?
  • Are there any similar alternatives which may be more performant?

(I’m not working with data of any considerable scale just yet; it’s more of a theoretical question.)

Tags:

2

Answers


  1. 0

    UUIDs are slower than keys generated by a sequence. You’ll just have to accept that, there is no way around it. For that reason you use UUIDs only if you have a compelling reason, like the keys are generated outside the database or need to be unique across several database.

    There is some deeper discussion of this in my article.

    Login or Signup to reply.
  2. 0

    While this post focuses on MySQL, the solution offered could just as easily apply to any other database. It shows a significant performance boost by rearranging a UUID to have the time-related component be the first part of the value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search