3 ms·

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 reaso

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.

perfmode 2y ago
would you still keep a uniqueness constraint on the uuid/ulid column?
- 9dev 2y ago
  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.
- dxdm 2y ago
  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.
- kreetx 2y ago
  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).
- vbezhenar 2y ago
  Of course. Index for this column is inevitable and with index, uniqueness basically free and protects from programmer errors.
cbsmith 2y ago
Or you can just store the UUIDs as bytes like the almighty intended.
- vbezhenar 2y ago
  UUID is 128 bits. Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.
  - cbsmith 2y ago
    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.
    - vbezhenar 2y ago
      May be I'm spoiled by postgres, but transactional ID allocator sounds like a problem solved in every database. Is there issues somewhere?
      - cbsmith 2y ago
        Hehe. It very much is a problem solved in every ACID database... as long as you want to serialize all your transactions.
itake 2y ago
Or just use ints and encrypt them when you want to return them to the user. https://sqids.org/ https://sqids.org/
- perfmode 2y ago
  NB: may be more accurate to call this hashing, versus encryption
  - jerbear4328 2y ago
    Actually, it's not hashing either, it's just encoding. Anyone who knows the alphabet can easily decode them.
    - cryptos 2y ago
      To make this secure, some secret salt value needs to be added before hashing.
    - perfmode 2y ago
      thank you for clarifying
  - itake 2y ago
    My link didn’t link to there library I thought it was. We encrypted ids at Yelp, not hashed them.
- carpenecopinum 2y ago
  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.
  - itake 2y ago
    Touché. The project used to be called hashids, but I guess they renamed it when they weren’t actually hashes

perfmode 2y ago

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

9dev 2y ago
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.
dxdm 2y ago
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.
kreetx 2y ago
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).
vbezhenar 2y ago
Of course. Index for this column is inevitable and with index, uniqueness basically free and protects from programmer errors.

cbsmith 2y ago

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

vbezhenar 2y ago
UUID is 128 bits. Even for huge tables 64 bits are enough. And for many tables 32 bits is more than enough.
- cbsmith 2y ago
  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.
  - vbezhenar 2y ago
    May be I'm spoiled by postgres, but transactional ID allocator sounds like a problem solved in every database. Is there issues somewhere?
    - cbsmith 2y ago
      Hehe. It very much is a problem solved in every ACID database... as long as you want to serialize all your transactions.

itake 2y ago

Or just use ints and encrypt them when you want to return them to the user. https://sqids.org/ https://sqids.org/

perfmode 2y ago
NB: may be more accurate to call this hashing, versus encryption
- jerbear4328 2y ago
  Actually, it's not hashing either, it's just encoding. Anyone who knows the alphabet can easily decode them.
  - cryptos 2y ago
    To make this secure, some secret salt value needs to be added before hashing.
  - perfmode 2y ago
    thank you for clarifying
- itake 2y ago
  My link didn’t link to there library I thought it was. We encrypted ids at Yelp, not hashed them.
carpenecopinum 2y ago
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.
- itake 2y ago
  Touché. The project used to be called hashids, but I guess they renamed it when they weren’t actually hashes