Unexpected downsides of UUID keys in PostgreSQL
cybertec-postgresql.com
Unexpected downsides of UUID keys in PostgreSQL
1–10 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#2How can this be unexpected?
Isn't this what Percona discussed for MySQL already several years ago?
Or am I missing something?
Re: Unexpected downsides of UUID keys in PostgreSQL
#3How can this be unexpected? Isn't this what Percona discussed for MySQL already several years ago? Or am I missing something?
They suggest to use UUID v7 at the end for more sequential keys. Maybe that's the new part since v7 is relatively recent.
Re: Unexpected downsides of UUID keys in PostgreSQL
#4UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users.
If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB.
If it is a concern, one approach is to use random UUIDs to give to end users but then internally to have something like auto increment ids.
Re: Unexpected downsides of UUID keys in PostgreSQL
#5Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?
Re: Unexpected downsides of UUID keys in PostgreSQL
#6That's... not unexpected. That's been the known technical compromise of UUID for years, and why ULID and UUID 7 are born.
And usually it is still worth it.
Re: Unexpected downsides of UUID keys in PostgreSQL
#7Is this a downside of UUID keys in general, or of using them as indexes? Would it be possible to create a primary index on a combination of inserted date and uuid and get opaque UUIDs and good indexing?
Re: Unexpected downsides of UUID keys in PostgreSQL
#8I don't find myself dealing with databases in any depth very often, so this was news to me, and I thought it was well written, for what it's worth. Thanks for posting.
Re: Unexpected downsides of UUID keys in PostgreSQL
#9Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?
They can be generated anywhere, otherwise they wouldn't be universally unique.
Re: Unexpected downsides of UUID keys in PostgreSQL
#10Nothing unexpected about it at all. PostgreSQL's default index in this case is a B-tree, and they don't index disorderly data very well which is the nature of all UUID versions.
On the topic of how important it is for B-trees to be "orderly": https://news.ycombinator.com/item?id=34404641
PostgreSQL can do (among other types) 32-bit hash indices which work out better for certain use cases. Personally I would avoid B-trees for any UUID unless I really had to do partial or ranged scans on them.