Sample Header Ad - 728x90

PostgreSQL: How is the uuid datatype sorted?

-1 votes
1 answer
2489 views
I have a system which uses the proposed UUID7 format for its uuid values. UUID7 has timestamp encoded into into its value. Does PostgreSQL document how ORDER BY clauses work with the UUID data type? https://www.postgresql.org/docs/current/datatype-uuid.html There are no notes on sorting here.
lab7=# SELECT resource_id, uuid, created_timestamp from resource where "name" in ('ABC000004',
 'ABC000003') order by uuid, "name" desc;
 resource_id |                 uuid                 |     created_timestamp
      
-------------+--------------------------------------+----------------------
------
    17704701 | 018ec350-6976-79d5-b0f5-41af99b98fa4 | 2024-04-09 14:43:30.6
09909
    17704718 | 018ec353-ee50-7765-9e8b-3ebf22654662 | 2024-04-09 14:47:21.3
78086
Comparing the leftmost characters (assuming this was a string), I would expect 018ec353 to be in the result set before 018ec350. As this is not stored as a string, that assumption may be naive. Performance wise, sorting by resource_id here or create_timestamp is more performant, but I would have expected the sorting on the uniquely incrementing resource_id, the globally unique uuid, and the created_timestamp to all be alignment. (Newest created entry sorts first when order by desc) Are there any details on how this sorting is performed and why my assumptions are wrong?
Asked by Justin Lowen (68 rep)
Apr 9, 2024, 04:09 PM
Last activity: Apr 9, 2024, 05:56 PM