Unexpected downsides of UUID keys in PostgreSQL

Re:Unexpected downsides of UUID keys in PostgreSQL by xahrepap on Thursday June 22, 2023 @11:41AM (#36433888)

ULIDs have made our MySql caches breathe a sigh of relief.

One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity).

So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by ilyt on Thursday June 22, 2023 @12:02PM (#36434249)

I wonder if just encrypting them for public usage would be enough here. Then application can convert between public and private representation at will.

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by selcuka on Thursday June 22, 2023 @11:18PM (#36442388)

Encryption is good until someone leaks the private key. Depending on the application, it may be very difficult to reassign all primary keys and foreign keys when that happens, especially if they have already been used as canonical identifiers.

You don't have such issues with randomly generated ids, unless someone obtains a full dump of your database (in which case you will have to think about bigger problems anyway).

[ Parent | on HN ]

Re:Unexpected downsides of UUID keys in PostgreSQL by smaddox on Thursday June 22, 2023 @03:00PM (#36437088)

KSUID's are have temporal-lexicographical order plus 128 bits of entropy, which is more than UUIDv4.

https://github.com/segmentio/ksuid

[ Parent | on HN ]