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

121–130 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#121

Post by patrec »

ok123456 wrote: post #89
insanitybit wrote: post #80

Earlier quoted context omitted.

A 32bit integer hash won't have locality either.

Ok. Pick a resolution where you won't get collisions and that is a native datatype.

I feel that you don't understand what locality is about: the property of IDs that were generated closely together in time to be close together numerically. You don't get that by "hashing" the UUID (which makes no sense anyway, since you might as well just take some truncation of the UUID). In fact the whole idea behind a hash is to destroy this property of the input data. The reason the numerical locality matters is that it is much more efficient to in-sequence-insert several numbers that are close together into an index than to in-sequence-insert the identical amount of randomly distributed numbers.

The GP was talking about the first graph here: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...

Re: Unexpected downsides of UUID keys in PostgreSQL

#122

Post by tgv »

CGamesPlay wrote: post #21
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…

How could temporal information be used to infer the size of the dataset for all users? Specifically, more accurately than “I now know that this database was created in 2022”.

I use uuids in links. Using sequential ids gives people access to information they shouldn't see. Unfortunately, the link has to be publicly accessible. Then UUIDs become a very practical key for your records.

That said, the number of times those records are accessed is low, so there are no performance considerations.

Re: Unexpected downsides of UUID keys in PostgreSQL

#123

Post by unshavedyak »

pgaddict wrote: post #54
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, b…

Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing.

Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)?

[1]: Again, totally out of my wheel house.

edit: Apparently UUIDv7 exists, which is similar to ULID, so my question pertains to UUIDv5 and below, i think.

edit2: I think Nanoids do suffer the same problem. They're just small... i think.

Re: Unexpected downsides of UUID keys in PostgreSQL

#124

Post by ok123456 »

patrec wrote: post #121
ok123456 wrote: post #89

Earlier quoted context omitted.

Ok. Pick a resolution where you won't get collisions and that is a native datatype.

I feel that you don't understand what locality is about: the property of IDs that were generated closely together in time to be close together numerically. You don't get that by "hashing" the UUID (which makes no sense anyway, since you might as well just take some truncation of the UUID). In fact the whole idea behind a hash is to destroy this property of the input data. The reason the numerical locality matters is…

I thought you were talking about cache locality within the CPU. Real cache locality.

If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.

Re: Unexpected downsides of UUID keys in PostgreSQL

#125

Post by NicoJuicy »

Tbh. I use UUID's a lot, perhaps usefull to know that Deterministic UUID exist,

eg. DeterministicGuid.Create(tenantNamespace, "my-tenant-name");

Note: https://github.com/Informatievlaanderen/deterministic-guid-g... for dotnet.

Additionally, know that there are different versions of UUID's. If you want to create chronologic UUID's so that queries are more easily ordered, use the correct type.

Re: Unexpected downsides of UUID keys in PostgreSQL

#126

Post by qaq »

pgaddict wrote: post #54
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, b…

this sounds horrible until you realise that a modern SSD will write that in about 1.5 sec.

Re: Unexpected downsides of UUID keys in PostgreSQL

#127

Post by ibash »

dpcx wrote: post #50
totalhack wrote: post #46

Earlier quoted context omitted.

You can incorrectly join on any columns you want. That's not really the fault of the column types IMO, that's a problem in the layers above.

True, but you can't incorrectly join on UUID due to its anti-collision nature.

Yes you can, you’ll not get the correct result set.

Re: Unexpected downsides of UUID keys in PostgreSQL

#128

Post by hn_throwaway_99 »

The new/upcoming UUID v7 really solves pretty much all of these problems:

1. They're ordered by a timestamp, so they preserve DB locality

2. They include a total of 74 bits of random data. This isn't enough to be used as unguessable keys, but it does offer some good protection if you have other bugs that could otherwise lead to IDOR vulnerabilities.

3. They're still 16 bytes, but IMO any minor hit to storage/time is completely worth it for the benefits they provide, especially since the other option is usually to have an integer primary key AND another "public ID" column that stores a UUID.

Re: Unexpected downsides of UUID keys in PostgreSQL

#129

Post by letitbeirie »

unshavedyak wrote: post #123
pgaddict wrote: post #54

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

Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing. Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)? [1]: Again, totally out of my…

Postgres supports UUIDs natively
Post reply on HN