how does ordering by UUID work in PostgreSQL?

Question

Is there any ordering guarantee on using uuid_generate_v1() on postgresql?

If yes,tThe guarantees are per machine or it does not matter on which machine the UUID was generated? Since V1 UUID is by timestamp + MAC address, does Postgres internally order by the timestamp part and then by MAC?

Can I "order by" an UUID type column and expect it to always work (seems to work)?

I want to generate UUIDs on multiple machines (with postgresql uuid_generate_v1()), copy them to one Postgres instance and then order by the UUID column. It must guarantee order by machine, not the order of the UUIDs of all machines.

Answer 1

While in no means a definitive answer - ie. "is the behavior defined across all PostgreSQL installations?," this SQL (for SQL Server) to check the ordering of each byte within a GUID. Might need some tweaks for PostgreSQL.

Generating such a mapping should allow one to see if a particular UUID structure (one of the well-defined types or otherwise) "orders in a particular way" in PostgreSQL.

With UIDs As (--                           0 1 2 3  4 5  6 7  8 9  A B C D E F
            Select ID = 'F', UID = cast ('00000000-0000-0000-0000-000000000011' as uniqueidentifier)
    Union   Select ID = 'E', UID = cast ('00000000-0000-0000-0000-000000001100' as uniqueidentifier)
    Union   Select ID = 'D', UID = cast ('00000000-0000-0000-0000-000000110000' as uniqueidentifier)
    Union   Select ID = 'C', UID = cast ('00000000-0000-0000-0000-000011000000' as uniqueidentifier)
    Union   Select ID = 'B', UID = cast ('00000000-0000-0000-0000-001100000000' as uniqueidentifier)
    Union   Select ID = 'A', UID = cast ('00000000-0000-0000-0000-110000000000' as uniqueidentifier)
    Union   Select ID = '9', UID = cast ('00000000-0000-0000-0011-000000000000' as uniqueidentifier)
    Union   Select ID = '8', UID = cast ('00000000-0000-0000-1100-000000000000' as uniqueidentifier)
    Union   Select ID = '7', UID = cast ('00000000-0000-0011-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '6', UID = cast ('00000000-0000-1100-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '5', UID = cast ('00000000-0011-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '4', UID = cast ('00000000-1100-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '3', UID = cast ('00000011-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '2', UID = cast ('00001100-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '1', UID = cast ('00110000-0000-0000-0000-000000000000' as uniqueidentifier)
    Union   Select ID = '0', UID = cast ('11000000-0000-0000-0000-000000000000' as uniqueidentifier)
)
Select * From UIDs Order By UID desc

In SQL Server (2014, and matches that in SQL Server 2005) the descending ordering is:

Position by highest-to-lowest value:

A B C D E F | 8 9 | 7 6 | 5 4 | 3 2 1 0

Since SQL Server's newsequentialid utilizes this ordering for index-friendly GUID generation, the behavior will probably never change. SQL Server also has to maintain this behavior across all systems to support replication. Thus, if the question was about SQL Server I would definitely say "there is a consistent ordering of GUIDs in SQL Server" that absolutely can be relied upon in SQL Server.

However, this ordering is different than .NET's GUID ordering and I would not be surprised if the ordering differed in PostgreSQL. The 'flipping' differences in SQL Server are because it is following COM GUIDs's 'Variant 2' (aka little-endian) ordering; this is done even for 'Variant 1' UUIDs. (However, it seems more arbitrary as why the groups themselves are ordered right-to-left: more Microsoft history?)

The interesting question still remains: where/how is this specified to be ordered in PostgreSQL? If it is not well-specified, can the implementation still be treated as a behavior axiom?

Also see this question for more details about SQL Server's UUIDs; and tasty details for 'why' these differences exist.