Projects:Generation Of Indexable UUIDs

Contents 1 Introduction 2 How it currently works 2.1 PostgreSQL 2.2 Oracle 3 How it would work better 3.1 PostgreSQL 3.2 Oracle 4 Performance Test

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.

Performance Test

https://docs.google.com/spreadsheets/d/1SEJ1RTFjaf6Vh4jRVZ2A-zV7Sg4eS9_bUdFwCNL9gN8/edit#gid=966670869