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

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:

Oracle

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

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

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Generation_Of_Indexable_UUIDs"

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.