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

201–210 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#201

Post by ants_a »

daneel_w wrote: post #10

Nothing 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-t…

B-trees index random UUIDs perfectly fine, it's just that range scans over the resulting indexes cause random access patterns that interact really poorly with caches when data size exceeds cache size. The blog was about an interesting case when dataset fits into buffer cache, but the random access pattern still hurts because of other caches.

Re: Unexpected downsides of UUID keys in PostgreSQL

#202

Post by ants_a »

zero_iq wrote: post #23

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

The unexpected part for me was not the lack of temporal locality, but which cache it thrashed. The whole dataset fits into buffer cache so one might think that the lack of locality is not that important...

Re: Unexpected downsides of UUID keys in PostgreSQL

#203

Post by pksunkara »

samokhvalov wrote: post #164

We discussed some of these problems here during a Postgres.tv session, and developed a patch to support ULID / UUIDv7: https://www.youtube.com/watch?v=YPq_hiOE-N8 And then Andrey proposed a patch in the pgsql-hackers mailing list: https://www.postgresql.org/message-id/flat/CAAhFRxitJv%3DyoG... , https://commitfest.postgresql.org/43/4388/ Everyone who can help (test, discuss, etc.) – please participate in that discuss…

Implementing support for UUIDv7 does not equal implementing support for ULID. ULID needs to be treated as a first class citizen (type, read format, storage format, converters) instead of being an afterthought result from UUIDv7.

I have built a postgres extension for adding ULID support which does what I described. https://github.com/pksunkara/pgx_ulid

Re: Unexpected downsides of UUID keys in PostgreSQL

#204

Post by cryptos »

damethos wrote: post #12

Why not just use auto-increment integers and expose them with something like this: https://www.npmjs.com/package/hashids (The linked library is just an example). Yes, you will need to transform the IDs before returning them to an external caller which is a little bit annoying but it's probably not that a big of a deal. Also another approach I've seen, which personally I find it a bit complicated, is to use auto-incre…

Incrementing integers doesn't work well in a distributed system, where the IDs might be generated outside of the database (to have it before the transaction ends, for example).

Re: Unexpected downsides of UUID keys in PostgreSQL

#205

Post by cryptos »

While UUIDs are pretty common, they are by far not the only or superior solution. To be able to easily generate IDs on different nodes in a distributed system something host-specific, pure random, or a mixture of both is needed. To be avoid problems with random numbers on DB indexes some time-based part would be useful. There are other approaches like Ulid or TSID which are similar to UUID v7, but are represented in are more compact way.

Example Ulid vs UUID:

    000360TJXZDDMSKJSQGBQHA5YA
    fb87f306-b613-4948-be24-00609cf9ccc8
https://github.com/ulid/spec https://tsid.com/de

Re: Unexpected downsides of UUID keys in PostgreSQL

#206

Post by pkilgore »

fauigerzigerk wrote: post #155
pkilgore wrote: post #100

Earlier quoted context omitted.

COMB UUIDs can help here.

Why do COMB UUIDs not have this problem? Do they not contain a timestamp?

It just orders ids based on a monotonic clock that wraps after a certain time -- it's enough to get a lot of of the performance benefit of ordered keys without directly exposing the unix wall clock time they were created (absent a dedicated attacker with the ability to create ids without you picking up on it for a timing attack, I suppose).

Whether that tradeoff makes sense probably depends.

Re: Unexpected downsides of UUID keys in PostgreSQL

#207

Post by rjh29 »

gmhafiz wrote: post #117
rjh29 wrote: post #33

Earlier quoted context omitted.

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

One potential downside is ULID does not have an RFC, unlike UUID V7

Good point. It has libraries in most languages and that's good enough for me, but might be a problem if you're interfacing with other projects.

Re: Unexpected downsides of UUID keys in PostgreSQL

#208

Post by Fire-Dragon-DoL »

jandrewrogers wrote: post #194
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…

Organizations that care about leaking information encrypt their identifiers as UUIDs. UUIDs conveniently have the same size as a single AES block, for which there is dedicated silicon on all modern CPUs. There is almost no overhead. Every other issue with random UUIDs etc, which are ignored here, are solved by encrypting your identifier. Random UUIDs (i.e. UUIDv4) are banned in many places for good reasons.

Won't encrypting defeat the ordering though, reintroducing the performance penalties?

Re: Unexpected downsides of UUID keys in PostgreSQL

#209

Post by Fire-Dragon-DoL »

insanitybit wrote: post #186
insanitybit wrote: post #87

Another (potentially!) significant problem with uuids is that they're inefficient to store. Compared to an 8 byte value obviously they double the storage size. But it's actually worse - if you have loosely ordered integers, even with gaps, you can compress those down to even less, like practically 1 byte on average. That makes uuids ~16x worse for disk storage, ~2x worse for memory storage (cache). That + Losing loca…

Oh yeah, this also makes pagination trivial. You can basically just give your clients a literal number indicating where they are. So good, you basically get paging APIs for free.

Well, records can be deleted

Re: Unexpected downsides of UUID keys in PostgreSQL

#210

Post by insanitybit »

Fire-Dragon-DoL wrote: post #209
insanitybit wrote: post #186

Earlier quoted context omitted.

Oh yeah, this also makes pagination trivial. You can basically just give your clients a literal number indicating where they are. So good, you basically get paging APIs for free.

Well, records can be deleted

That doesn't matter unless you reuse ids, which if you have a 64bit counter there is no need.
Post reply on HN