5 ms·

If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and c

If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.

davisp 2y ago
Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work. And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the OpenSSL bug that had removed too much entropy from the RNG setup.
- kevincox 2y ago
  You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).
  - groestl 2y ago
    > UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.
    - kevincox 2y ago
      Generating IDs on the client can be very useful for offline-first systems. But you need to check for conflicts and permissions on the server (or be sure to keep the IDs secret which I wouldn't recommend).
      - groestl 2y ago
        Agreed, but in that case "upsert" is also weird, since I'd structure such a system around an immutable log datastructure.
joshuamcginnis 2y ago
> This identifier is a 128-bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database. https://www.postgresql.org/docs/current/datatype-uuid.html https://www.postgresql.org/docs/current/datatype-uuid.html
netcraft 2y ago
The point of UUIDs is that they're inherently unique. You can generate them in the database or in your application, or anywhere. You can move records between databases without worrying about a collision. Its this reason that theyre so useful and so many people use them that others like to write articles like this. You do not need to search. I have generated hundreds of millions and have never hit a duplicate. Its technically possible, but its so vanishingly rare it will be something to brag about, not worry about. If you're really worried about it, and building something that cannot tolerate an error, put a try/catch around it and detect a PK failure and try again. But it will be a waste of time.
deleted 2y ago
[deleted]
lazide 2y ago
It should be so rare that if it did happen, you likely have a serious bug somewhere. Crash dumping and investigating is the right course of action, not building in retry logic. So if you are using it as a PK value, only ever insert and if there is a duplicate, blow up loudly. Performance wise, that isn’t particularly impactful IMO.
jrochkind1 2y ago
If it's indexed in a unique index, as a primary key certainly would be, the DB is of course already checking for collision on insert, so there's no reason for application code to do it. (If it did, it would be an indexed lookup rather than a table scan). So if there's a collision you'll get an error. You can write application code to handle the error (by re-generating a new ID and re-trying). Or you can figure it's so unlikely (googling for uuidv4 says "1 in 2.71 x 1018", which is pretty huge; not sure for uuidv7 which will be somewhat more likely) that you aren't going to worry about it, and if an error happens, oh well, errors happen sometimes, depending on the domain you are in, which is probably what many apps do and do fine with it. I would guess that pg's built in uuidv4-generating implementation might re-try on colision under-the-hood, but i haven't checked to see, and it may also just count on the improbability and raise an error if it happens!

davisp 2y ago

Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work. And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the OpenSSL bug that had removed too much entropy from the RNG setup.

kevincox 2y ago
You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).
- groestl 2y ago
  > UUID generated client side and basically upserted it Read and take notes. This is crazy in untrusted environments.
  - kevincox 2y ago
    Generating IDs on the client can be very useful for offline-first systems. But you need to check for conflicts and permissions on the server (or be sure to keep the IDs secret which I wouldn't recommend).
    - groestl 2y ago
      Agreed, but in that case "upsert" is also weird, since I'd structure such a system around an immutable log datastructure.

joshuamcginnis 2y ago

> This identifier is a 128-bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database. https://www.postgresql.org/docs/current/datatype-uuid.html https://www.postgresql.org/docs/current/datatype-uuid.html

netcraft 2y ago

The point of UUIDs is that they're inherently unique. You can generate them in the database or in your application, or anywhere. You can move records between databases without worrying about a collision. Its this reason that theyre so useful and so many people use them that others like to write articles like this. You do not need to search. I have generated hundreds of millions and have never hit a duplicate. Its technically possible, but its so vanishingly rare it will be something to brag about, not worry about. If you're really worried about it, and building something that cannot tolerate an error, put a try/catch around it and detect a PK failure and try again. But it will be a waste of time.

deleted 2y ago

[deleted]

lazide 2y ago

It should be so rare that if it did happen, you likely have a serious bug somewhere. Crash dumping and investigating is the right course of action, not building in retry logic. So if you are using it as a PK value, only ever insert and if there is a duplicate, blow up loudly. Performance wise, that isn’t particularly impactful IMO.

jrochkind1 2y ago

If it's indexed in a unique index, as a primary key certainly would be, the DB is of course already checking for collision on insert, so there's no reason for application code to do it. (If it did, it would be an indexed lookup rather than a table scan). So if there's a collision you'll get an error. You can write application code to handle the error (by re-generating a new ID and re-trying). Or you can figure it's so unlikely (googling for uuidv4 says "1 in 2.71 x 1018", which is pretty huge; not sure for uuidv7 which will be somewhat more likely) that you aren't going to worry about it, and if an error happens, oh well, errors happen sometimes, depending on the domain you are in, which is probably what many apps do and do fine with it. I would guess that pg's built in uuidv4-generating implementation might re-try on colision under-the-hood, but i haven't checked to see, and it may also just count on the improbability and raise an error if it happens!