Optimizing PostgreSQL performance when using UUIDs as primary keys

Obvious_Grapefruit 0
Viewed 43

I understand that using UUIDs as primary keys can potentially have adverse performance implications when compared to sequential integer values.

I did some tests on my machine and observed that various operations (at considerable scale) were indeed quite a bit slower.

I had a table with sequential integer primary keys and inserted 20 million records - this completed in 1 minute and 55 seconds. I then dropped the table and created the same again, but this time with UUID primary keys. To insert 20 million records took 6 minutes and 44 seconds.

Currently, I am configuring the primary key column with a uuid data type and the default value is set to gen_random_uuid() - so the UUIDs are being generated at the database level, not the application level.

I was wondering if there were any suggestions to optimize the use of UUIDs as primary keys. For example, would it help if the PK was an integer, but another (indexed) field contained a UUID, specifically for public exposure?

I'm also open to other ideas for a non-sequential PK that may exist, while being more performant.

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

database-performance uuid postgresql
1 Answers

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.

Laurenz Albe0
Related