Projects:Generation Of Indexable UUIDs
Contents |
Introduction
The way UUIDs are currently generated in Openbravo makes the primary key columns poorly indexable, as the UUIDs are too scattered. This results in very slow index updates.
Tracking issue: https://issues.openbravo.com/view.php?id=30659
How it currently works
PostgreSQL
In PostgreSQL the uuid_generate_v4 function is used to generate UUIDs. It produces random numbers. The results of executing it twice on two different computers are:
- Computer A: 4207A21E83414D08A3151694A62E7186, 68019C31E8884058AD582D82F93DE078
- Computer B: E5673209633B4867B6AF823BB6720BF8, 02928E0F086742A8AC930944548AF588
Oracle
In Oracle environments Openbravo uses the sys_guid function to generate UUIDs. The results of executing it twice on two different computers are:
- Computer A: 1E0E64AD14731A1AE050007F01002CAE, 1E0E64AD14681A1AE050007F01002CAE
- Computer B: 1E0EF5BEFF26A274E050007F01004914, 1E0EF5BEFF27A274E050007F01004914
How it would work better
PostgreSQL
We could use the uuid_generate_v1 function. The UUID generated by this function has two parts: the MAC address of the computer where it is executed plus a date-time that indicates the time passed since the adoption of the Gregorian calendar in the West. If the time-date part were put in the first part of the UUID, it could be more optimally indexed, as the UUIDs would be less scattered, regardless of whether they are generated in different computers.
This is one possible way to implement this approach in PostgreSQL:
var VARCHAR:=substr(uuid_generate_v1()::character varying, 10); prefix VARCHAR; BEGIN WHILE var=substr(uuid_generate_v1()::character varying, 10) LOOP END LOOP; var = uuid_generate_v1()::character varying; prefix = substr(var, 0, 9); RETURN REPLACE(upper(substr(var, 10) || prefix),'-','');
This implementation needs careful testing, as the uuid_generate_v1 function is known to have returned duplicate UUIDs in the past if it was execute several times consecutively. It is likely that this was fixed in PostgreSQL 9.2, but still has to be checked.
If the problem remained, the uuid_generate_v4 function could be used to replace some characters at the end of the UUID.
Oracle
It seems that UUIDs generated in Oracle are already pretty much grouped. Unless further research finds that they are actually more scattered than what it seems, they would probably be left as they are.