0

I'm thinking to use (on PostgreSQL v13) an UUID type (with uuid_generate_v4()) for my tables with high traffic, and the others with a Text type (as primary key) by using an auto-generated similar to crypt(now()::text, gen_salt('des')) (short UUID?).

What concerns are there respect to use this short-UUID-like type? Can it be considered a good practice? If not, what suggestions do you propose?

Let's suppose to have a table with various foreign keys (FK), every FK is an UUID type, then when we open that table we'll see a long sheet stretching horizontally, or when we make a API call returning a list of objects with their IDs, well it is not cool.

I was checking some API calls available on Net, and they use some short-UUID-like type with a fixed length of 10 chars, so I was thinking, how do they get that result without getting any conflicts? What about its performance? etc.

3
  • 1
    Why not use uuid for all of them? Btw: Postgres 13 has a built-in gen_random_uuid() function, no need to install an extension to get uuid_generate_v4() any more. I would never choose a data type over another, just because one has a more visually appealing representation.
    – user1822
    Commented Oct 15, 2021 at 5:13
  • 1
    Do you have proof that your (expensive!) "short UUID" is collision resistant? Do you really want to waste all that performance? For what gain? I recommend that you use a sequence-generated numerical key, or if that is not feasible for your use case, go with a regular UUID. Commented Oct 15, 2021 at 8:28
  • @LaurenzAlbe I'm not saying my solution (?) is the best, it is a proposal only where my lacking knowledge is evident, hence my current question. Commented Oct 15, 2021 at 13:45

1 Answer 1

4

Whatever you do, don't use a text type to store fixed-length binary data. For example, UUIDs stored as text take 33 bytes, while the UUID type takes only 16 bytes. Your "short-UUID-like type" is 13 characters, taking 14 bytes when stored as text. For a saving of only 2 byes and a risk of collisions, I wouldn't use it.

1
  • I will take it into consideration, thanks. Secondly I've noted many API calls on net use keys like this ZRgPP9dBMm, and I'm sure they are aware of collisions, then how do they manage it? what's your opinion? Commented Oct 15, 2021 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.