0

I'm thinking to use UUID as PK for all tables into my new PostgreSQL database, for such a goal I will be using uuid_generate_v5 and uuid_ns_dns or uuid_ns_url functions, but I have some doubts:

  • is there any problems using UUIDs only in a database (i.e. performance)?
  • is there any pros and cons respect to use uuid_generate_v4?
  • When to use uuid_ns_dns or uuid_ns_url? (I haven't found info about it)
  • can name parameter for uuid_generate_v5 function be any string (even an empty string) or it depends on namespace parameter? Examples, please.
1
  • 1
    I don't know enough specific knowledge to answer your other questions so I'll refrain from posting and answer, but regarding your first question, UUID makes for a somewhat poorer data type to index on (particularly B-Tree indexes) as opposed to an INT or BIGINT because of their larger size and their potential for higher fragmentation. So yes UUID will be less performant than an INT or BIGINT data type for example. – J.D. Feb 20 at 17:34
0

Answers to your questions:

  • There are no problems, but the performance will be somewhat worse than using a sequence generated bigint primary key, because adding one to an integer number has to perform better than anything more complicated. Also, a uuid takes twice the storage space (16 bytes).

    You should benchmark if that makes a noticeable difference for you.

  • Apart from the performance impact, it makes no difference.

  • It does not matter, they just return different constants.

  • The name parameter can be any string. That function is IMMUTABLE, so you have to make sure that you use different names if you want different UUIDs.

Use UUIDs if you need to generate artificial primary keys in a decentralized fashion, for example in the application code or in different database if you use sharding. If the primary keys are generated in a single database, use a sequence.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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