Unexpected downsides of UUID keys in PostgreSQL

Re:Unexpected downsides of UUID keys in PostgreSQL by fauigerzigerk on Thursday June 22, 2023 @09:21AM (#36431748)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by pkilgore on Thursday June 22, 2023 @09:56AM (#36432241)

COMB UUIDs can help here.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by fauigerzigerk on Thursday June 22, 2023 @12:37PM (#36434786)

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

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by pkilgore on Friday June 23, 2023 @05:38PM (#36452489)

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.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by remram on Thursday June 22, 2023 @10:47AM (#36432960)

Encrypt it. Block ciphers are bijective so they will turn sequential IDs into unique random IDs in a way you can easily reverse.

You can also do that to get opaque identifiers from an auto-increment primary key.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by fauigerzigerk on Thursday June 22, 2023 @12:04PM (#36434283)

Seems awfully complicated to me.

[ Parent | on HN ]