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

21–30 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#21

Post by CGamesPlay »

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

Re: Unexpected downsides of UUID keys in PostgreSQL

#22

Post by iwwr »

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

Sequential serial numbers reveal information. See: https://en.m.wikipedia.org/wiki/German_tank_problem

Re: Unexpected downsides of UUID keys in PostgreSQL

#24

Post by exyi »

robertlagrant wrote: post #7

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

Yes, it's a downside of using them in b-tree indexes. However, the purpose of having an ID is usually to use it for lookup (from foreign keys, query string arguments, ...). If you create an index on two columns, you won't be able to lookup using only the second value, at least not in Postgres.

Re: Unexpected downsides of UUID keys in PostgreSQL

#25

Post by VWWHFSfQ »

WeAddValue wrote: post #13
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…

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.

Re: Unexpected downsides of UUID keys in PostgreSQL

#26

Post by VWWHFSfQ »

My 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

Re: Unexpected downsides of UUID keys in PostgreSQL

#27

Post by jongjong »

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 tries to create a resource and fails due to a bad connection, you can simply resend that exact same resource for creation again (with the same UUID) and you don't need to worry about duplicate records being inserted into the database since you would get an ID collision; it results in much simpler/cleaner code which is idempotent and deterministic by default.

With auto-incrementing IDs, if there is a connection issue or other failure, it's not possible to cleanly figure out if a resource was already created or not since only the database knows what the ID of that resource was until that ID is sent back and reaches the client across the network.

Just because the client did not receive a successful response from the server, it does not mean that the resource was not created; and if it was, you cannot know since the client has no way of referencing that resource in a reliable way. It just seems completely wrong that the client (which created the resource) cannot reference it until the database has inserted it.

It's a hack to pretend that resource creation starts in the database, when in fact, it starts on the front end.

Re: Unexpected downsides of UUID keys in PostgreSQL

#28

Post by westurner »

stevesimmons wrote: post #15
jamil7 wrote: post #5

Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?

Yes, UUID v7 can be generated independently. The UUID has three parts, which maintains strict chronological ordering for UUIDs generated in one process, and to within clock skew when UUIDs are generated on different systems. The three parts are: - time-based leading bits. - sequential counter, so that multiple UUID 7s generated very rapidly within the same process will be monotonic even if the time counter does not i…

> There are new UUID formats that are timestamp-sortable; for when blockchain cryptographic hashes aren't enough entropy.

Note that multiple rounds of cryptographic hashing is not considered sufficient anymore; PBKDF2 and Argon2 are Key Derivation Functions, and those are used instead of hash functions.

"New UUID Formats – IETF Draft" https://news.ycombinator.com/item?id=28088213

draft-peabody-dispatch-new-uuid-format-04 Internet-Draft "New UUID Formats" https://datatracker.ietf.org/doc/html/draft-peabody-dispatch... ; UUID6, UUID7, UUID8

Re: Unexpected downsides of UUID keys in PostgreSQL

#29

Post by roenxi »

jamil7 wrote: post #16
jayknight wrote: post #9

Earlier quoted context omitted.

They can be generated anywhere, otherwise they wouldn't be universally unique.

I'm not asking about the uniqueness but about ordering across different clients.

There is probably an impossibility theory somewhere for two clients generating ordered ID without communicating with each other. What if one client was travelling near the speed of light for example? It isn't obvious what order would be correct since the two clients would have wildly different perceptions of time.

Re: Unexpected downsides of UUID keys in PostgreSQL

#30

Post by sibit »

jamil7 wrote: post #16
jayknight wrote: post #9

Earlier quoted context omitted.

They can be generated anywhere, otherwise they wouldn't be universally unique.

I'm not asking about the uniqueness but about ordering across different clients.

If they're generated on the client you would need to keep clock drift[0] in mind since the leading bits are time based.

[0]: https://youtu.be/mAyW-4LeXZo (Clock Synchronization in Distributed Systems by Martin Kleppmann)

Post reply on HN