how does ordering by UUID work in PostgreSQL?

Question

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

If yes,the 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

I tweaked the above query for PostgreSQL

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

In PostgreSQL, the sort order differs:

Position by highest-to-lowest value

id                                  uuid                             
 0  11000000-0000-0000-0000-000000000000
 1  00110000-0000-0000-0000-000000000000
 2  00001100-0000-0000-0000-000000000000
 3  00000011-0000-0000-0000-000000000000
 4  00000000-1100-0000-0000-000000000000
 5  00000000-0011-0000-0000-000000000000
 6  00000000-0000-1100-0000-000000000000
 7  00000000-0000-0011-0000-000000000000
 8  00000000-0000-0000-1100-000000000000
 9  00000000-0000-0000-0011-000000000000
 A  00000000-0000-0000-0000-110000000000
 B  00000000-0000-0000-0000-001100000000
 C  00000000-0000-0000-0000-000011000000
 D  00000000-0000-0000-0000-000000110000
 E  00000000-0000-0000-0000-000000001100
 F  00000000-0000-0000-0000-000000000011

This means that UUIDs are not sorted by their time component in PostgreSQL. Internally, PostgreSQL uses memcmp to sort UUIDs by their memory layout.