View source | View content page | Page history | Printable version   

Projects:Generation Of Indexable UUIDs



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:

How it currently works


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:


In Oracle environments Openbravo uses the sys_guid function to generate UUIDs. The results of executing it twice on two different computers are:

How it would work better


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;
 WHILE var=substr(uuid_generate_v1()::character varying, 10) 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.


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.

Performance Test

Retrieved from ""

This page has been accessed 1,048 times. This page was last modified on 25 August 2015, at 13:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.