For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future proofing for a problem that never arose, and the senior dev who made us switch left the company a few years ago. I just don't like the dev experience of UUIDs in a world where you do have to get hands (and eyes) on the data all the time.
Unexpected downsides of UUID keys in PostgreSQL
71–80 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#72My go-to pattern for many years now is to use a plain bigint autoincrement column for internal database relations and then a uuid for application-level identifiers and natural keys. Basically never use the uuid as the actual primary key because they're enormous and now the DBMS has to copy that gigantic number to every side of the relation. Don't do it
This is a good answer. You want your primary/cluster key to usually be something that makes sense in your data. If you use something like UUID the placement can be basically random. That is usually in SQL is not a desirable trait. If you use it for a primary key you are basically saying you are fine with a memory cmp vs a register compare for you finding the right data. That can be undesirable too. UUIDs do have very desirable traits such as uniqueness. But that does come at a cost and usually you can fix that by deciding what process decides the source of truth in a different way.
Re: Unexpected downsides of UUID keys in PostgreSQL
#73For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…
Something I saw a longtime ago was hashing UUIDs into a RGB value and then colouring the background of the cell (at least when viewing in a DB IDE). That way you can quickly at a glance tell if it's worth going over character by character.
Re: Unexpected downsides of UUID keys in PostgreSQL
#74UUID 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…
I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed…
That sounds like a very simplistic framework and I'm sure you could do some metaprogramming to abstract boilerplate. Like you couldn't do database multitenancy with those constraints.
I've used the int keys and UUID public keys on multiple projects - it wasn't an issue for EF core or RoR
Re: Unexpected downsides of UUID keys in PostgreSQL
#75I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
Then just make a generated column that's a 32bit integer hash of the uuid for this particular case and create an index on that? Use that during expensive queries that blow up your cache locality if it matters.
Re: Unexpected downsides of UUID keys in PostgreSQL
#76I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
Re: Unexpected downsides of UUID keys in PostgreSQL
#77I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
> The moral of the story is that data locality matters, and it can pop up in the most surprising of places. Using random is typically the worst thing you can do for locality, so if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a ve…
I agree that UUIDv7 will usually be the best choice. The one downside I can think of is that it may sometimes be necessary to let people know the identity of a thing without also telling them exactly when that thing was created.
Re: Unexpected downsides of UUID keys in PostgreSQL
#78For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…
Interesting take. I do agree there's some visual noise there, but not how you described it. UUID just takes so much screen space compared to its integer counterpart. If you don't have a good UI sorting things out it's quite annoying. Otherwise it's pretty great.
Re: Unexpected downsides of UUID keys in PostgreSQL
#79Earlier quoted context omitted.
> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?
“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.
[dead]
Re: Unexpected downsides of UUID keys in PostgreSQL
#80Earlier quoted context omitted.
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
Then just make a generated column that's a 32bit integer hash of the uuid for this particular case and create an index on that? Use that during expensive queries that blow up your cache locality if it matters.
A 32bit integer hash won't have locality either.