If index lookup speed or packing truly was the primary concern, people
would use a suitably sized SEQUENCE. They would not use UUID.

I believe the last time I calculated this, the result was that you
could fit 50% more entries in the index if you use a 32-bit sequence
number instead of a 128-bit UUID.

I've been mixed on my personal use of UUID. The main benefits of UUID
for me have been:

   1) Different sites can generate UUID values without communicating
      according to a well known standard formula, and have an excellent
      chance of always generating unique values for a particular application
      that can be later merged together without conflict.

   2) The numbers are difficult to predict by an end user of your
      application. If exposing your row id to the world, a UUID is
      more obscure and difficult to predict than a sequence number.

The rest of the benefits are slim or non-existent. It's not more
efficient to store or access. It's not easier to process, memorize or
type. Any significance of values, such as UUIDv1 embedded a time and
mac address are of only limited value, as it relies on the generator
following the expected algorithm, and the generators having the same
time (with different allowances for error).

For example, if one truly wished to have an efficient lookup key, and
record creation time, why not use two fields? 1 x 32-bit sequence number,
and 1 x 64-bit timestamp. This gives portability and makes the purpose of
the fields clear. It gives flexibility to create the index on either/or.

For read-only data, I've taken to using the SHA1 sum of the data as
the unique id instead of UUID or SEQUENCE. Works pretty good... :-)


.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at


Reply via email to