Avoid UUID Version 4 Primary Keys in Postgres
andyatkinson.com
Avoid UUID Version 4 Primary Keys in Postgres
1–10 of 463 posts
Re: Avoid UUID Version 4 Primary Keys in Postgres
#2[dead]
Re: Avoid UUID Version 4 Primary Keys in Postgres
#3"if you use PostgreSQL"
(in the scientific reporting world this would be the perennial "in mice")
Re: Avoid UUID Version 4 Primary Keys in Postgres
#4The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#5To summarise the article: in PG, prefer using UUIDv7 over UUIDv4 as they have slightly better performance.
If you're using latest version of PG, there is a plugin for it.
That's it.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#6From the fine article:
> Random values don’t have natural sorting like integers or lexicographic (dictionary) sorting like character strings. UUID v4s do have "byte ordering," but this has no useful meaning for how they’re accessed.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#7What kills me is I can’t double click the thing to select it.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#8Postgresql 18 released in September and has uuidv7
Re: Avoid UUID Version 4 Primary Keys in Postgres
#9"if you use PostgreSQL" (in the scientific reporting world this would be the perennial "in mice")
The thing is, none of us are mice, but many of us use Postgres.
It would be the equivalent of "if you're a middle-aged man" or "you're an American".
P.S. I think some of the considerations may be true for any system that uses B-Tree indexes, but several will be Postgres specific.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#10"if you use PostgreSQL" (in the scientific reporting world this would be the perennial "in mice")
It's not just Postgres or even OLTP. For example, if you have an Iceberg table with SCD2 records, you need to regularly locate and update existing records. The more recent a record is, the more likely it is to be updated.
If you use UUIDv7, you can partition your table by the key prefix. Then the bulk of your data can be efficiently skipped when applying updates.