Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Unexpected downsides of UUID keys in PostgreSQL

cybertec-postgresql.com

Post reply on HN
Print view View original

51–60 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#51

Post by gradys »

jongjong wrote: post #27
jongjong wrote: post #20

I 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…

Recently, I wrote a client-side wallet app for cryptocurrency use case and it required transactions to be created and signed entirely on the client-side and so the transaction UUID had to be created on the client side (before signing) and I was surprised at how elegant and simple the application's logic turned out to be (both on the front end and back end). One of the best things about UUIDs is that if your front end…

You can achieve this without UUID keys if you attach UUIDs to the requests. This has the advantage of working for all kinds of mutating requests, not just creates, but requires that you store these request UUIDs for some period of time as well.

Re: Unexpected downsides of UUID keys in PostgreSQL

#52

Post by londons_explore »

There are downsides to anything ascending/descending in a database too...

They mess with table statistics. If you do a query like "SELECT * from users where creation_date > NOW-1h", the query analyzer doesn't know that there might be thousands of users created in the last hour. It is probably working from day-old statistics that say all users have a creation_date between 2008 and 2023-06-21.

That makes it sometimes pick an exceptionally poor query plan. Ie. instead of your query taking 50 milliseconds, it might take 50 hours and involve an n^2 scan of all data in your database.

Re: Unexpected downsides of UUID keys in PostgreSQL

#53

Post by willvarfar »

> if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option. Hopefully it’s coming to PostgreSQL 17

Yeap, UUIDv7 directly addresses the issues in the article. Warmly recommended.

(There is the issue that exposing ULIDs to users may 'leak' information about when things were created etc, but that is usually not a problem.)

Re: Unexpected downsides of UUID keys in PostgreSQL

#54

Post by pgaddict »

jongjong wrote: post #20

I 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, because of FPW and stuff like that. With serial IDs we'd write a fraction of that. It doesn't take much to hit max_wal_size and trigger a checkpoint, starting a new cycle with FPWs. Got a replica? Well, now you need to send the WAL over network. Is the bandwidth limited (another DC?), sorry to hear that. Is the replica sync and you have to wait. Well, that's unfortunate.

In other words, the lack of locality seems like a detail but at scale it's actually a damn huge deal.

Re: Unexpected downsides of UUID keys in PostgreSQL

#55

Post by phito »

maxgee wrote: post #35
dewey wrote: post #32

Earlier 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?

Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.

Nothing? Really? Have you ever thought about that?

Re: Unexpected downsides of UUID keys in PostgreSQL

#56

Post by heipei »

rjh29 wrote: post #33
zero_iq wrote: post #23

Article should be called "Totally Expected Downsides..." If you want temporal locality, use ULIDs instead.

I was going to use UUID with the time portion at the start (also known as UUIDv7) but this looks better.

I've been thinking about converting a legacy application which uses UUIDv4 to use ULIDs instead going forward, but then to represent these ULIDs in a format that is compliant with a UUIDv4. I have not thought through the possible downsides, but I think it should be a pretty straightforward change. Of course old records will remain true random UUIDv4s, but at least new records will be time-order and as such will create less stress on the B-Tree index when writing them.

Re: Unexpected downsides of UUID keys in PostgreSQL

#57

Post by bavell »

babbledabbler wrote: post #41
gregwebs wrote: post #4

UUID 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…

As someone who has been on the fence about this for awhile now, thank you for your valuable anecdote!

Re: Unexpected downsides of UUID keys in PostgreSQL

#58

Post by [deleted] »

whack wrote: post #48
jongjong wrote: post #20

I 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…

[deleted]

Re: Unexpected downsides of UUID keys in PostgreSQL

#59

Post by willvarfar »

VWWHFSfQ wrote: post #25
WeAddValue wrote: post #13

Earlier quoted context omitted.

Or use hashids.org. Use it to obfuscate your integer auto increment ids anywhere a user might see it.

It's a good option purely for cosmetics but don't rely on it for any kind of serious obscurity since it's trivially reversed. I've used it to great effect in the past to encode multiple integer values like start/end ID, sort order (0/1), etc. for cursor-based pagination. But that's only because there's nothing secret in those numbers. Just purely for convenience.

I once heard of some fancy data science that inferred that the company was the supplier's major customer from the gaps in the sequential ids they saw when using that supplier's API. That was useful in negotiations.

And so the company that did that data science realised they too were susceptible to exactly the same 'attack'. So they created a system to obscure the ids they were themselves exposing to their customers, using some cheap cut-down tea64 encryption iirc. My memory is it never went live, though.

Re: Unexpected downsides of UUID keys in PostgreSQL

#60

Post by FroshKiller »

kogus wrote: post #38
dewey wrote: post #32

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

Crucially, "5" might also be the key to a thousand different records in dozens of other development & QA databases for applications with integrations configured incorrectly. Integers can match coincidentally. UUIDs won't.
Post reply on HN