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.

The timestamp can be extracted from the UUID (v1). Have you seen this? http://stackoverflow.com/questions/37713131/postgresql-sort-by-uuid-v1-timestamp — Andy Carlson, Apr 05 '17 at 19:05
I saw it, but ordering without the proposed function on this answer also seems to work (with plain ORDER BY column). Not sure if it's machine independent. — Thiago Sayão, Apr 05 '17 at 19:07
There is no universal ordering of UUIDs (or even byte layout). However, PostgreSQL has an *implementation specific* UUID ordering, as does SQL Server (and this is not guaranteed to be the same; .NET has a different ordering that SQL Server, even though both are Microsoft creations..). Thus the PostgreSQL ordering *will be consistent*. If `uuid_generate_v1` is PgSQL index-friendly and/or is well-ordered in PgSQL based on 'time' is a specific detail. — user2864740, Nov 01 '18 at 05:39
Regardless, I can't find any technical documentation but I'd "assume" that the order is 'guaranteed' *within* PostgreSQL. Changing the order would be a *large breaking change*, and may rely on the binary-storage encoding of the UUID. If there is any reason this data may be ordered *outside* of PgSQL then .. don't rely on it D: — user2864740, Nov 01 '18 at 05:48

score 3 · Answer 1 · answered Sep 23 '22 at 04:11

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.

user2864740 · Answer 2 · 2018-12-25T04:17:49.123

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.

For anyone came here wondered about the output from Postgresql of this script, with a little modification, the output is nicely ordered 0 1 2 3 ... F. (PostgreSQL 9.5.14 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit) — Codism, Dec 04 '18 at 21:48

how does ordering by UUID work in PostgreSQL?

2 Answers2