9 ms·

I used to be a big proponent of using UUIDs for database PKs but I've found them inherently difficult to work with. It's much easier to remember/recognize an i

I used to be a big proponent of using UUIDs for database PKs but I've found them inherently difficult to work with. It's much easier to remember/recognize an integer based PK when troubleshooting a data problem. This isn't to say you shouldn't use UUIDs at all, but I much prefer to use an "ExternalId" column of UUID type if you don't want to expose your integer based PKs externally.

staticassertion 4y ago
I feel like `serial` has got to be pretty fast for writes and gives you some nice properties like sorted ordering based on insertion time, a smaller key, and a key that can be compressed far better than a uuid. I get the idea of a ULID/UUID7 encoding a timestamp so you get sorted order, but I wonder at what scale that beats just using serial.
- stormbrew 4y ago
  At any scale where you have to have multiple writers generating IDs or need to merge results from multiple sources, basically. Synchronizing a serial increment across hosts and across time is a pain. Obviously you can composite a timestamp to an incrementing id, but then the serial part of it is kind of useless for ordering, so you may as well use a random number and avoid needing to synchronize at all. And then you've just reinvented a time ordered uuid (but to be fair, without the endian compatibility bs mentioned elsewhere).
  - staticassertion 4y ago
    I'm wondering what that performance actually looks like though. The closest benchmark I have is a single postgres instance with a uuid pkey and a bigint column, transactionally incrementing the integer in the column. I mean I guess mathing it out, updating an atomic integer is ~2-100ns, depending on contention. If you need to coordinate the writes you have anywhere from ~250μs-10ms. We can basically throw away the increment at that point since the network is hundreds/thousands of times slower. So at 10ms, that's 100 op/s. At 250μs more like 40kops/s. That ignores the fact that your db can perform those writes concurrently and then batch the writes off to the other database, so long as it doesn't pretend that they're all committed at once. psql HOT updates would presumably be a thing here idk. If I had to guess, I'd lean towards the "40kop/s" being closer than the "100op/s" but idk! I wish we had benchmarks but I can't find anything :\
tpetry 4y ago
That‘s in my experience also the best approach. I wrote an article a few days ago about the exact thing: An integer auto incrementing PK with an UUID you use externally: https://sqlfordevs.io/uuid-prevent-enumeration-attack https://sqlfordevs.io/uuid-prevent-enumeration-attack
- tylerscott 4y ago
  Just seconding this as a sane way to use UUIDs IME. Basically the sequential integer PK is the “internal ID”. IIRC in SQLite regardless of type or existence of PK there is a private sequential integer. Super handy pattern to use.
  - ciupicri 4y ago
    Are you talking about ROWID? [1] > Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. > [...] If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately. [2] [1]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html [2]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html
- hn_throwaway_99 4y ago
  That is how I used to do (and currently still do) DB design, but honestly I think if DBs start supporting UUID v7s well that I would use that as the sole primary DB key as well as the external ID: 1. They are still sorted in increasing timestamp order (at millisecond granularity), so they should have good DB index characteristics. 2. At the same time, they contain 62 bits of randomness, would should pretty much eliminate IDOR attacks if there is a bug elsewhere that isn't doing proper access checks. Not good enough for secure tokens, but just good defense against access permission check bugs. That is, you should basically get the best of both worlds: ordered keys with enough randomness to make ID-increment attacks infeasible.
marcos100 4y ago
I can't see a case where an UUID PK is better than an INT (or BIGINT). Why would you do that?
- __s 4y ago
  If the id appears in a url, you may not want people to guess ids. The information leak exists even if you do authentication: maybe you don't want someone to be able to guess how many records there are, or how quickly records are being generated
- tylerscott 4y ago
  If for no other reason than simplified debugging I find there is value. Maybe I’m old but if you have more than one UUID involved in the debugging I’m more likely to trip up than just integers.
- lkrubner 4y ago
  There were two problems. One problem was the style that started around 2004, and was very popular with Ruby on Rails and WordPress, and then Syfmony and Django, where you expose the PK in the URL. If your integer starts with 1 and then increments, you may not get to a billion, and you'll never get to a trillion. So it became ridiculously easy to for outsiders to scan your site: http://www.example.com/1 http://www.example.com/1 http://www.example.com/2 http://www.example.com/2 http://www.example.com/3 http://www.example.com/3 ... http://www.example.com/10000000000 http://www.example.com/10000000000 That was one problem. Using UUIDs for PKs means outsiders can't simply scan your site. The other problem was that over the years, everyone ran into the problem of moving a database, or needing to combine multiple databases, in which case having PKs the start with 1 and then increment, a collision of the PKs, from different databases, is 100% guaranteed. This often happens when combining WordPress sites, for instance. If you use UUIDs as your PK, then such collisions become unlikely.
- madsbuch 4y ago
  Some applications have the need to create IDs in a distributed manner, eg. Og clients, and use that identity before the database returns it. These systems benefit from randomly generated IDs. You could potentially hist use a random number between 0 and 2^128-1 and still use ints, though I haven't seen that in action, usually pk with ints are centrally generated and consequitive.
chrismorgan 4y ago
I’ve been working on a robust scheme for encrypted sequential IDs, which is done, including library implementations in Rust, JavaScript and Python, pending just a smidgeon more writing about it and reviewing a decision on naming. You store an integer in the database, then encrypt it with a real block cipher, and stringify with Base58. I have three modes: one for 32-bit IDs, using Speck32/64 and producing 4–6 character IDs; one for 64-bit IDs, using Speck64/128 and producing 8–11 character IDs; and one hybrid, using the 32-bit mode for IDs below 2³² and the 64-bit mode above that, providing both a forwards-compatibility measure and a way of producing short IDs as long as possible. Contact me (see my profile) if you’re interested, or I’ll probably publish it in another day or two. Trouble is that I’ve been getting distracted with other related concepts, like optimally-short encoding by using encryption domains [0, 58¹), [58¹, 58²), …, [58¹⁰, 2⁶⁴) (this is format-preserving encryption; the main reputable and practical choices I’ve found are Hasty Pudding, which I’ve just about finished implementing but would like test vectors for but they’re on a dead FTP site, and NIST’s FF1 and FF3, which are patent-encumbered), and ways of avoiding undesirable patterns (curse words and such) by skipping integers from the database’s ID sequence if they encode to what you don’t want, and check characters with the Damm algorithm. If I didn’t keep getting distracted with these things, I’d have published a couple of weeks ago. (I am not aware of any open-source library embodying a scheme like what I propose—all that I’ve found have either reduced scope or badly broken encryption; https://github.com/yi-jiayu/presents https://github.com/yi-jiayu/presents encrypts soundly, but doesn’t stringify; Hashids is broken almost beyond belief and should not be considered encryption; Optimus uses an extremely weak encryption.) UUIDs are crazy overkill in any situation where you can have centralised ID allocation. Fully decentralised? Sure, 128 bits of randomness or mixed clock and randomness or similar, knock yourself out. But got a master database? Nah, you’re just generating unreasonably long values that take up unnecessary space and make for messy URLs and such.
- mappu 4y ago
  I've done something similar to obfuscate private DB IDs in a large existing application - just ensure they're all Skip32-encoded in all query parameters with an app-wide secret. It works well but you have to be very disciplined to catch every case individually. Using GUID PKs from the start just removes this entire category of problem.
cameronh90 4y ago
UUIDs have a few distinct advantages: you'll never run out, you don't need a roundtrip to find out what they are after saving them, they often make a good partitioning key and it makes things easier if you ever need to combine multiple data sources together in migration and recovery type scenarios. I also quite like how they're unique across all data sources and tables, so if you just encounter a random contextless UUID in the wild, for example in a support ticket, you can probably still find what it refers to. They are quite unwieldy though. There are a few compact representations you can use in URLs which make it a bit less ugly, but they can make your database and logs quite bloated, in particular if you've got a large number of small records.
- CharlesW 4y ago
  > There are a few compact representations you can use in URLs which make it a bit less ugly… Any thoughts on where to find best-practices guidance? I need to create an external ID scheme for several million items. hashids (hashids.org) seems interesting, but I have anxiety about choosing a solution with weaknesses that I can't identify given my current level of experience in regards to this.
  - munawwar 4y ago
    I took a shot at the math behind this at https://www.codepasta.com/databases/2020/09/10/shorter-unique-ids.html https://www.codepasta.com/databases/2020/09/10/shorter-uniqu... Using the equation listed in the article I couldn't generate a collision so far. Yet, I still check (in code) for id collision, and pick new id, just to be 100% sure.
    - Olreich 4y ago
      This article mostly just says: "If your data is small, then 56 bits is fine". Which is true. But if you are at 56 bits in your UUID, then just use an integer PK with 64 bits and you'll be fine a good deal longer. If your data might get large, an extra 64 bits to get a full 128bit UUID isn't expensive, and encoding in Base36 still yields 25 character UUIDs, which are pretty manageable: "abcdef-01234-56789-ghi-jklmno" (6-5-5-3-6, or max of a u16 for digits). I'm honestly shocked no UUID library makes it easy to export in Base36 with that grouping, it's very neat and tidy and easy to remember.
zbuf 4y ago
"Unique IDs" _can_ be super really easy to work with if they're not so baffling complicated. A random string generated using quality randomness can be adjusted to length to suit the quantity of data (negligible probability of a collision) which in most cases is very short. It's easy to increase the length as you get more data. They are visually very different for each item of data. They're evenly spread which means they hash/index well. You can tune a subset of characters if you want to decrease ambiguity eg. when exchanged by voice (no zero vs. letter O, upper/lower case etc.) And a final bonus, when working with user input only a a short prefix is needed to uniquely identify an item (in contrast, it seems like UUIDs deliberately share a common prefix) I'm very happy to concede I must be missing something here, and would be interested to know. But the above approach has served me well in a range of uses. I can see how UUIDs work, and perhaps "looks like a UUID" is a useful feature. But reading the URL above and a bit of Wikipedia doesn't give me much to go on as to _why_ any of this is happening, and why the hyphens aim to retain meaning to what is ostensibly a 'unique' number.
- jasonwatkinspdx 4y ago
  Using purely random ids in your database destroys locality. They mention this in the introduction: > Non-time-ordered UUID versions such as UUIDv4 have poor database index locality. Meaning new values created in succession are not close to each other in the index and thus require inserts to be performed at random locations. The negative performance effects of which on common structures used for this (B-tree and its variants) can be dramatic. The V7 ids work similarly to what you like, as they're just a unix timestamp and 74 bits of pseudorandom data (they present several different schemes you could use to generate this randomness, but the basic birthday bound says we'd need to be above 100 billion id's generated in a single millisecond to worry about collisons. Obviously most systems are nowhere near that territory. So using these id's gives you the practical advantages of random uinique ids, but with the performance of autoincrement ids.
  - zbuf 4y ago
    Thanks for drawing my attention to that, it's the useful answer I was looking for; my use cases haven't been bound by write performance in this manner. However, I'd still be considering carefully before making use of these UUID schemes.
deepsun 4y ago
The best strategy is to have integer primary keys for internal purposes, and some form of uuid for external (think permalinks).
kortex 4y ago
> It's much easier to remember/recognize an integer based PK when troubleshooting a data problem. How often are you actually relying on memory of an ID to troubleshoot a problem? I mean sure, if you are scanning visually, it's good to recognize the same ID over and over again, but my ability to do so caps around 4-6 characters. So I just look at the last 4 chars regardless when fast scanning. I use copy-paste for any time I need to transport IDs between contexts (that isn't just scripted, which is best). Having a copy-paste stack (Alfred, Raycast and others have this feature) is a huge game changer here.
evil-olive 4y ago
my personal favorite UUID replacement, when not concerned with external compatibility or standards-compliance: a 96-bit random value, base64-urlsafe encoded to 16 ASCII characters >>> import secrets >>> secrets.token_urlsafe(12) 'uUBpBk2eENDslHyw' with UUID, you can store it as a compact 16 bytes in a database, but it needs 36 characters if you want to embed it in a URL or a JSON payload. and there's a temptation to be "clever" and strip out the hyphens to shave off 4 bytes and create a nonstandard UUID format. by comparison these have one single canonical representation that is always a 16-character URL-safe string.
- tehlike 4y ago
  uuid is just what you said - 16bytes. you could presumably base64 that too...
  - dagss 4y ago
    This proposal was 12 bytes and the feature that it is 16 chars in base64. UUID is more than 16 chars in base64.
unixhero 4y ago
Useful to have a unique UIID too, but integers are better as PK I agree.
citrin_ru 4y ago
Being impossible to remember to me is a minor problem compare to how much RAM and HDD space wasted on UUIDs across all systems being built nowadays. In addition to being large than say 64bit integer ID they are almost not compressible. DB compression will not reduce used by UUID space in DB, logs with UUIDs are bigger and compression not a great help here either.
marcus_holmes 4y ago
I have exactly the reverse experience. I do comparisons using ```<uuid fieldname>::text like '<first few chars of the uuid>%``` when debugging in a command line. Or just copy/paste the whole thing. Yes it's marginally more annoying than integers, but only marginally. I have several times wondered why I was getting no match on a query. And then discovered that I was using a user_id on an account_id field. UUID's have saved me from shooting myself in the foot so many times.
- Aeolun 4y ago
  How do UUID’s help in that situation? You get no result at all instead of the wrong result?
  - marcus_holmes 4y ago
    yes, that. The extreme case (that I had and is the one where I was finally convinced that uuid's save me from myself) was setting admin permissions on a user. I accidentally copy/pasted their account_id instead of their user_id. If I had been using integers I would have given admin permissions to a random user and never been aware of it. But because I was using uuid's I got a nice, safe "updated 0 records" response and knew there was a problem.

staticassertion 4y ago

I feel like `serial` has got to be pretty fast for writes and gives you some nice properties like sorted ordering based on insertion time, a smaller key, and a key that can be compressed far better than a uuid. I get the idea of a ULID/UUID7 encoding a timestamp so you get sorted order, but I wonder at what scale that beats just using serial.

stormbrew 4y ago
At any scale where you have to have multiple writers generating IDs or need to merge results from multiple sources, basically. Synchronizing a serial increment across hosts and across time is a pain. Obviously you can composite a timestamp to an incrementing id, but then the serial part of it is kind of useless for ordering, so you may as well use a random number and avoid needing to synchronize at all. And then you've just reinvented a time ordered uuid (but to be fair, without the endian compatibility bs mentioned elsewhere).
- staticassertion 4y ago
  I'm wondering what that performance actually looks like though. The closest benchmark I have is a single postgres instance with a uuid pkey and a bigint column, transactionally incrementing the integer in the column. I mean I guess mathing it out, updating an atomic integer is ~2-100ns, depending on contention. If you need to coordinate the writes you have anywhere from ~250μs-10ms. We can basically throw away the increment at that point since the network is hundreds/thousands of times slower. So at 10ms, that's 100 op/s. At 250μs more like 40kops/s. That ignores the fact that your db can perform those writes concurrently and then batch the writes off to the other database, so long as it doesn't pretend that they're all committed at once. psql HOT updates would presumably be a thing here idk. If I had to guess, I'd lean towards the "40kop/s" being closer than the "100op/s" but idk! I wish we had benchmarks but I can't find anything :\

tpetry 4y ago

That‘s in my experience also the best approach. I wrote an article a few days ago about the exact thing: An integer auto incrementing PK with an UUID you use externally: https://sqlfordevs.io/uuid-prevent-enumeration-attack https://sqlfordevs.io/uuid-prevent-enumeration-attack

tylerscott 4y ago
Just seconding this as a sane way to use UUIDs IME. Basically the sequential integer PK is the “internal ID”. IIRC in SQLite regardless of type or existence of PK there is a private sequential integer. Super handy pattern to use.
- ciupicri 4y ago
  Are you talking about ROWID? [1] > Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value. > [...] If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately. [2] [1]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html [2]: https://www.sqlite.org/autoinc.html https://www.sqlite.org/autoinc.html
hn_throwaway_99 4y ago
That is how I used to do (and currently still do) DB design, but honestly I think if DBs start supporting UUID v7s well that I would use that as the sole primary DB key as well as the external ID: 1. They are still sorted in increasing timestamp order (at millisecond granularity), so they should have good DB index characteristics. 2. At the same time, they contain 62 bits of randomness, would should pretty much eliminate IDOR attacks if there is a bug elsewhere that isn't doing proper access checks. Not good enough for secure tokens, but just good defense against access permission check bugs. That is, you should basically get the best of both worlds: ordered keys with enough randomness to make ID-increment attacks infeasible.

marcos100 4y ago

I can't see a case where an UUID PK is better than an INT (or BIGINT). Why would you do that?

__s 4y ago
If the id appears in a url, you may not want people to guess ids. The information leak exists even if you do authentication: maybe you don't want someone to be able to guess how many records there are, or how quickly records are being generated
tylerscott 4y ago
If for no other reason than simplified debugging I find there is value. Maybe I’m old but if you have more than one UUID involved in the debugging I’m more likely to trip up than just integers.
lkrubner 4y ago
There were two problems. One problem was the style that started around 2004, and was very popular with Ruby on Rails and WordPress, and then Syfmony and Django, where you expose the PK in the URL. If your integer starts with 1 and then increments, you may not get to a billion, and you'll never get to a trillion. So it became ridiculously easy to for outsiders to scan your site: http://www.example.com/1 http://www.example.com/1 http://www.example.com/2 http://www.example.com/2 http://www.example.com/3 http://www.example.com/3 ... http://www.example.com/10000000000 http://www.example.com/10000000000 That was one problem. Using UUIDs for PKs means outsiders can't simply scan your site. The other problem was that over the years, everyone ran into the problem of moving a database, or needing to combine multiple databases, in which case having PKs the start with 1 and then increment, a collision of the PKs, from different databases, is 100% guaranteed. This often happens when combining WordPress sites, for instance. If you use UUIDs as your PK, then such collisions become unlikely.
madsbuch 4y ago
Some applications have the need to create IDs in a distributed manner, eg. Og clients, and use that identity before the database returns it. These systems benefit from randomly generated IDs. You could potentially hist use a random number between 0 and 2^128-1 and still use ints, though I haven't seen that in action, usually pk with ints are centrally generated and consequitive.

chrismorgan 4y ago

I’ve been working on a robust scheme for encrypted sequential IDs, which is done, including library implementations in Rust, JavaScript and Python, pending just a smidgeon more writing about it and reviewing a decision on naming. You store an integer in the database, then encrypt it with a real block cipher, and stringify with Base58. I have three modes: one for 32-bit IDs, using Speck32/64 and producing 4–6 character IDs; one for 64-bit IDs, using Speck64/128 and producing 8–11 character IDs; and one hybrid, using the 32-bit mode for IDs below 2³² and the 64-bit mode above that, providing both a forwards-compatibility measure and a way of producing short IDs as long as possible. Contact me (see my profile) if you’re interested, or I’ll probably publish it in another day or two. Trouble is that I’ve been getting distracted with other related concepts, like optimally-short encoding by using encryption domains [0, 58¹), [58¹, 58²), …, [58¹⁰, 2⁶⁴) (this is format-preserving encryption; the main reputable and practical choices I’ve found are Hasty Pudding, which I’ve just about finished implementing but would like test vectors for but they’re on a dead FTP site, and NIST’s FF1 and FF3, which are patent-encumbered), and ways of avoiding undesirable patterns (curse words and such) by skipping integers from the database’s ID sequence if they encode to what you don’t want, and check characters with the Damm algorithm. If I didn’t keep getting distracted with these things, I’d have published a couple of weeks ago. (I am not aware of any open-source library embodying a scheme like what I propose—all that I’ve found have either reduced scope or badly broken encryption; https://github.com/yi-jiayu/presents https://github.com/yi-jiayu/presents encrypts soundly, but doesn’t stringify; Hashids is broken almost beyond belief and should not be considered encryption; Optimus uses an extremely weak encryption.) UUIDs are crazy overkill in any situation where you can have centralised ID allocation. Fully decentralised? Sure, 128 bits of randomness or mixed clock and randomness or similar, knock yourself out. But got a master database? Nah, you’re just generating unreasonably long values that take up unnecessary space and make for messy URLs and such.

mappu 4y ago
I've done something similar to obfuscate private DB IDs in a large existing application - just ensure they're all Skip32-encoded in all query parameters with an app-wide secret. It works well but you have to be very disciplined to catch every case individually. Using GUID PKs from the start just removes this entire category of problem.

cameronh90 4y ago

UUIDs have a few distinct advantages: you'll never run out, you don't need a roundtrip to find out what they are after saving them, they often make a good partitioning key and it makes things easier if you ever need to combine multiple data sources together in migration and recovery type scenarios. I also quite like how they're unique across all data sources and tables, so if you just encounter a random contextless UUID in the wild, for example in a support ticket, you can probably still find what it refers to. They are quite unwieldy though. There are a few compact representations you can use in URLs which make it a bit less ugly, but they can make your database and logs quite bloated, in particular if you've got a large number of small records.

CharlesW 4y ago
> There are a few compact representations you can use in URLs which make it a bit less ugly… Any thoughts on where to find best-practices guidance? I need to create an external ID scheme for several million items. hashids (hashids.org) seems interesting, but I have anxiety about choosing a solution with weaknesses that I can't identify given my current level of experience in regards to this.
- munawwar 4y ago
  I took a shot at the math behind this at https://www.codepasta.com/databases/2020/09/10/shorter-unique-ids.html https://www.codepasta.com/databases/2020/09/10/shorter-uniqu... Using the equation listed in the article I couldn't generate a collision so far. Yet, I still check (in code) for id collision, and pick new id, just to be 100% sure.
  - Olreich 4y ago
    This article mostly just says: "If your data is small, then 56 bits is fine". Which is true. But if you are at 56 bits in your UUID, then just use an integer PK with 64 bits and you'll be fine a good deal longer. If your data might get large, an extra 64 bits to get a full 128bit UUID isn't expensive, and encoding in Base36 still yields 25 character UUIDs, which are pretty manageable: "abcdef-01234-56789-ghi-jklmno" (6-5-5-3-6, or max of a u16 for digits). I'm honestly shocked no UUID library makes it easy to export in Base36 with that grouping, it's very neat and tidy and easy to remember.

zbuf 4y ago

"Unique IDs" _can_ be super really easy to work with if they're not so baffling complicated. A random string generated using quality randomness can be adjusted to length to suit the quantity of data (negligible probability of a collision) which in most cases is very short. It's easy to increase the length as you get more data. They are visually very different for each item of data. They're evenly spread which means they hash/index well. You can tune a subset of characters if you want to decrease ambiguity eg. when exchanged by voice (no zero vs. letter O, upper/lower case etc.) And a final bonus, when working with user input only a a short prefix is needed to uniquely identify an item (in contrast, it seems like UUIDs deliberately share a common prefix) I'm very happy to concede I must be missing something here, and would be interested to know. But the above approach has served me well in a range of uses. I can see how UUIDs work, and perhaps "looks like a UUID" is a useful feature. But reading the URL above and a bit of Wikipedia doesn't give me much to go on as to _why_ any of this is happening, and why the hyphens aim to retain meaning to what is ostensibly a 'unique' number.

jasonwatkinspdx 4y ago
Using purely random ids in your database destroys locality. They mention this in the introduction: > Non-time-ordered UUID versions such as UUIDv4 have poor database index locality. Meaning new values created in succession are not close to each other in the index and thus require inserts to be performed at random locations. The negative performance effects of which on common structures used for this (B-tree and its variants) can be dramatic. The V7 ids work similarly to what you like, as they're just a unix timestamp and 74 bits of pseudorandom data (they present several different schemes you could use to generate this randomness, but the basic birthday bound says we'd need to be above 100 billion id's generated in a single millisecond to worry about collisons. Obviously most systems are nowhere near that territory. So using these id's gives you the practical advantages of random uinique ids, but with the performance of autoincrement ids.
- zbuf 4y ago
  Thanks for drawing my attention to that, it's the useful answer I was looking for; my use cases haven't been bound by write performance in this manner. However, I'd still be considering carefully before making use of these UUID schemes.

deepsun 4y ago

The best strategy is to have integer primary keys for internal purposes, and some form of uuid for external (think permalinks).

kortex 4y ago

> It's much easier to remember/recognize an integer based PK when troubleshooting a data problem. How often are you actually relying on memory of an ID to troubleshoot a problem? I mean sure, if you are scanning visually, it's good to recognize the same ID over and over again, but my ability to do so caps around 4-6 characters. So I just look at the last 4 chars regardless when fast scanning. I use copy-paste for any time I need to transport IDs between contexts (that isn't just scripted, which is best). Having a copy-paste stack (Alfred, Raycast and others have this feature) is a huge game changer here.

evil-olive 4y ago

my personal favorite UUID replacement, when not concerned with external compatibility or standards-compliance: a 96-bit random value, base64-urlsafe encoded to 16 ASCII characters >>> import secrets >>> secrets.token_urlsafe(12) 'uUBpBk2eENDslHyw' with UUID, you can store it as a compact 16 bytes in a database, but it needs 36 characters if you want to embed it in a URL or a JSON payload. and there's a temptation to be "clever" and strip out the hyphens to shave off 4 bytes and create a nonstandard UUID format. by comparison these have one single canonical representation that is always a 16-character URL-safe string.

tehlike 4y ago
uuid is just what you said - 16bytes. you could presumably base64 that too...
- dagss 4y ago
  This proposal was 12 bytes and the feature that it is 16 chars in base64. UUID is more than 16 chars in base64.

unixhero 4y ago

Useful to have a unique UIID too, but integers are better as PK I agree.

citrin_ru 4y ago

Being impossible to remember to me is a minor problem compare to how much RAM and HDD space wasted on UUIDs across all systems being built nowadays. In addition to being large than say 64bit integer ID they are almost not compressible. DB compression will not reduce used by UUID space in DB, logs with UUIDs are bigger and compression not a great help here either.

marcus_holmes 4y ago

I have exactly the reverse experience. I do comparisons using ```<uuid fieldname>::text like '<first few chars of the uuid>%``` when debugging in a command line. Or just copy/paste the whole thing. Yes it's marginally more annoying than integers, but only marginally. I have several times wondered why I was getting no match on a query. And then discovered that I was using a user_id on an account_id field. UUID's have saved me from shooting myself in the foot so many times.

Aeolun 4y ago
How do UUID’s help in that situation? You get no result at all instead of the wrong result?
- marcus_holmes 4y ago
  yes, that. The extreme case (that I had and is the one where I was finally convinced that uuid's save me from myself) was setting admin permissions on a user. I accidentally copy/pasted their account_id instead of their user_id. If I had been using integers I would have given admin permissions to a random user and never been aware of it. But because I was using uuid's I got a nice, safe "updated 0 records" response and knew there was a problem.