To: pgsql-performance@postgresql.org
From: Brendan McCollam <bmccollam@uchicago.edu>
Subject: ossp-uuid: Performance considerations for different UUID approaches?
Message-ID: <5661EAC5.3030401@uchicago.edu>
Date: Fri, 4 Dec 2015 13:34:29 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello,

We're in the process of designing the database for a new service, and 
some of our tables are going to be using UUID primary key columns.

We're trying to decide between:

* UUIDv1 (timestamp/MAC uuid) and

* UUIDv4 (random uuid)

And the separate but related choice between

* Generating the UUIDs client-side with the Python uuid library 
(https://docs.python.org/2/library/uuid.html) or

* Letting PostgreSQL handle uuid creation with the uuid-ossp extension 
(http://www.postgresql.org/docs/9.4/static/uuid-ossp.html)

In terms of insert and indexing/retrieval performance, is there one 
clearly superior approach? If not, could somebody speak to the 
performance tradeoffs of different approaches?

There seem to be sources online (e.g. 
https://blog.starkandwayne.com/2015/05/23/uuid-primary-keys-in-postgresql/ 
http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/) 
that claim that UUIDv4 (random) will lead to damaging keyspace 
fragmentation and using UUIDv1 will avoid this.

Is that true? If so, does that mean UUIDv1 must always be generated on 
the same machine (with same MAC address) in order to benefit from the 
better clustering of UUIDs? What about uuid_generate_v1mc() in 
uuid-ossp? Not exposing a server's real MAC address seems like a good 
security feature, but does it compromise the clustering properties of 
UUIDv1?

Thanks in advance,
Brendan McCollam


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance