8

Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not sequential in nature, so updating an index can be inefficient.

Alternatively, MS SQL Server offers the NEWSEQUENTIALID command. To quote their documentation:

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Is there a way to get the more efficiently-indexed UUID in Postgres?

  • 2
    @a_horse_with_no_name index fragmentation still happens in Postgres, so it would still help out there. – Adam B Jun 2 '17 at 21:41
  • @AdamB - I suggest that it is not the 'fragmentation', but the randomness of the 'next' GUID that causes the performance problem. – Rick James Jul 13 '18 at 22:01
  • @RickJames that is exactly what I meant by 'fragmentation'. Since the UUID is random, it gets inserted in a random page in the index causing random IO, as opposed to an always increasing integer number which will always be placed at the end of an index. – Adam B Jul 20 '18 at 18:41
  • @AdamB - OK, there are multiple meanings of "fragmentation". The issue is the increased likelihood that the next block needed is not in cache. That leads to a significant performance problem; 10-fold in some measurements I have seen. Hence, I advise against UUIDs/GUIDs for very large tables. NEXSEQUENTIALID seems to be a workaround for such. (And there are other techniques.) – Rick James Jul 20 '18 at 18:55
3

uuid-ossp module

PostgreSQL uses the standardized UUID generation algorithms provided by ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122. From the docs on uuid-ossp,

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants.

uuid_generate_v1() This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.

So long as the MAC address does not change, you'll be golden.

That all said, I agree with @a_horse_with_no_name,

From my understanding this is only necessary in SQL Server because tables are stored in a clustered index which makes random insertions slower then with a heap table. Postgres has no such concept, so I don't think that would make a difference in Postgres

In fact, given the chance of fewer collisions and more security, I would take it. And to that I would use uuid_generate_v4()

uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.

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.