Optimizing PostgreSQL performance when using UUIDs as primary keys

Question

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.)

Adding another column with another unique index would certainly make things slower, not faster. Btw: there is no such thing as AUTOINCREMENT in Postgres — user330315
– user330315, Commented Sep 23, 2022 at 19:45
To expound on what @AsadAwadia said, using a ulid is better since they are sortable. Spec here: github.com/ulid/spec random UUID's destroy performance because btree indices work best when the data can be sorted. ULID's are unfortunately not native, but you can find peoples' functions around. — Optimum
– Optimum, Commented Sep 23, 2022 at 20:18

Laurenz Albe · Accepted Answer · 2022-09-24 15:06:30Z

8

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.

answered Sep 24, 2022 at 15:06

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matthew Setter · Accepted Answer · 2023-02-21 08:30:50Z

2

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.

answered Feb 21, 2023 at 8:30

Matthew Setter

2,4631 gold badge21 silver badges19 bronze badges

Collectives™ on Stack Overflow

Optimizing PostgreSQL performance when using UUIDs as primary keys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related