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