Hacker News Reader

Comment by vbezhenar | Hacker News Reader

At this point I think that ideal setup is to use numerical id for primary and foreign keys and maintaining a separate uuid field for everything else.

The reason being that index size matters a lot (for caches and other things) and index size depends on underlying field size, obviously.

Whether to use UUID or ULID is depends on tooling. While it's not hard to write ascending UUID generator and I did it myself few times, using standard APIs might be better approach despite the shortcomings.

View on HN

Or just use ints and encrypt them when you want to return them to the user.

https://sqids.org/

NB: may be more accurate to call this hashing, versus encryption

Actually, it's not hashing either, it's just encoding. Anyone who knows the alphabet can easily decode them.

To make this secure, some secret salt value needs to be added before hashing.

thank you for clarifying

My link didn’t link to there library I thought it was. We encrypted ids at Yelp, not hashed them.

I like the general idea, but the page you linked explicitly states that it isn't meant to be used to hide (user) IDs, or actually encrypt data.

Touché. The project used to be called hashids, but I guess they renamed it when they weren’t actually hashes

Or you can just store the UUIDs as bytes like the almighty intended.

UUID is 128 bits.

Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.

Oh, 32-bits is enough for a lot of tables assuming you have a transactional ID allocator. The whole reason to have a UUID is so you don't have to serialize all your ID allocation. Agreed though that for some lower volume systems, 64-bits is likely enough to make collisions statistically unlikely.

May be I'm spoiled by postgres, but transactional ID allocator sounds like a problem solved in every database. Is there issues somewhere?

Hehe. It very much is a problem solved in every ACID database... as long as you want to serialize all your transactions.

would you still keep a uniqueness constraint on the uuid/ulid column?

I wouldn’t, it’s redundant for all intents and purposes unless you’re Google, which you are not. UUIDs/ULIDs provide strong uniqueness guarantees on their own.

Yes, I'd keep a uniqueness constraint, because it prevents bugs from inserting the same value more than once. From concurrency issues to broken id generation, I've seen it happen before. So unless there are other considerations, having the database enforce uniqueness is a good thing in my book.

Depends on how many new U(U/L)IDs you insert, but when it's part of an index anyway then I personally would, as the performance penalty would be small (much smaller than anything else you do).

Of course. Index for this column is inevitable and with index, uniqueness basically free and protects from programmer errors.

Reply on news.ycombinator.com