6 ms·
I always use UUID PRIMARY KEY DEFAULT uuid_generate_v1mc() In Postgres. It will give you UUIDs and the larger keyspace, without being excessively random (
by minxomat 6y ago
I always use
UUID PRIMARY KEY DEFAULT uuid_generate_v1mc()
In Postgres. It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential). I do this always, even if the table has other int columns that look like friendly values that could be used as PKs. Some time in the future they will ruin your day.
- fulafel 6y agoFor others curious about this uuid type: "This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications." I guess that's not immune to problems either (i can imagine problems with both mac address and time).
- viraptor 6y agoThis is not the right function. V1mc randomises the MAC. Also it's only one of the possibilities. There are many ways if generating uuids depending on your situation.
- deleted 6y ago[deleted]
- abhishekjha 6y agoIsn't UUID discouraged for a PRIMARY KEY pertaining indexes? What would the B+Tree look like for over 2 billions UUIDS where there is no order within? Also, the cache locality problem.
- tasogare 6y agoThis is why GP wrote "It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential)". The main point for not screwing up the clustered index is the "almost sequential" part.
- dtech 6y agoThat is for random (v4) UUIDs. v1 UUIDs are mostly sequential in time on a single machine so don't have this problem, altough they do have problems that others indicated already.
- hvidgaard 6y agoIf you want to use UUID for primary keys, the argument should not be the larger key space. A 64 bit int is enough for a single server. If a 64 bit int PK is exhausted, the data used to store the keys alone would require 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds. Then you need several times that to store any kind of meaningful data If you need to use UUIDs for other reasons such as a high degree of unique keys between servers that do not coordinate, then go for it, but not for the key size.
- dx034 6y agoUUIDs can have severe performance implications. Even without massive randomness, a change in the MAC address used or non sequential numbers can slow down writes massively. As others have written, a simple 64bit integer should be enough for pretty much any case. Even Google won't exhaust those for their user database.
- viraptor 6y agov1mc does not depend on the MAC address.