From: Brendan McCollam <bmccollam@uchicago.edu>
To: pgsql-performance@postgresql.org
Subject: ossp-uuid: Performance considerations for different UUID approaches?
Date: Fri, 4 Dec 2015 13:34:29 -0600
Message-ID: <5661EAC5.3030401@uchicago.edu> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bmccollam@uchicago.edu
Subject: Re: ossp-uuid: Performance considerations for different UUID approaches?
In-Reply-To: <5661EAC5.3030401@uchicago.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox