6 ms·

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 parti

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.
    - Dylan16807 4y ago
      Why base36 in particular? That implies that you're worried you might lose case information but you're not worried about characters that look the same?
- selcuka 4y ago
  You may consider encoding the uuid in base58 as it is shorter, safe to use in URLs, and easier to debug as it doesn't use characters that can be confused such as 0, 0, I, and l.
  - deleted 4y ago
    [deleted]
  - inopinatus 4y ago
    I'll add a voice for base58 representation of UUIDs on the wire, it's my preferred solution as well. Not only for debugging: by being compact, unambiguous, and URL-safe, the helpdesk is also spared a cringeworthy source of PEBCAK incidents since misquoting the identifiers is simply harder. Caveat programmer, though, there is a hazard, occurring when someone is glib about the usage and relies on randomly generated fixed-length base58 values directly. It happens readily because some popular frameworks include such generation as a utility function. However, 58^22 > 2^128 > 58^21, so a 22-character base58 representation is expected for UUIDs but carelessly random 22-character base58 values may exceed the capacity of UUID's familiar hexadecimal serialization, effectively an integer overflow. We never generate base58 identifiers as a PK, for example, for this reason (they would of course not be UUIDs either). Alas, there is no conventional base encoding more compact than hexadecimal that is robust to the general problem. And I mention it because this issue was observed in the wild.
    - selcuka 4y ago
      If I'm not mistaken base64 should not cause an integer overflow (i.e. it will always be a 128-bit integer) if you limit it to 22 characters (and append two = characters to pad it when you actually decode it). It may not be as URL-safe as base58 though.
      - inopinatus 4y ago
        Yes. I think of base64 as an unconventional encoding, in that regard, since it's more than just an alphabet for powers of the base but also communicates properties of the output octet length in the serialized form. To do that it's relying on an elegant alignment of bit values to that base58 doesn't have. Perhaps there could be a means to inject the same confluence of form and function into base58, albeit I can't see it after a couple minutes of staring at the algorithm¹. And also yes: the equals sign is potentially hazardous in a query string, and the use of non-alphanumeric symbols in base64 also creates line-break and double-click/double-tap traps when passed around in an ad-hoc fashion, even in the url-safe variant. [1] https://datatracker.ietf.org/doc/html/draft-msporny-base58 https://datatracker.ietf.org/doc/html/draft-msporny-base58
Merad 4y ago
> UUIDs have a few distinct advantages: you'll never run out I'm curious what kind of applications are limited by the range of bigint values? I have no doubt that such applications exist somewhere, but most software engineers won't ever come close to encountering those limits. Even if you have a table that is consistently consuming a billion (with a B) bigint id values _every second_ (is that even feasible with current hardware and RDBMS software?) you won't run out for almost 300 years.
- Gigachad 4y ago
  For some reason a lot of things used to default to 32 bit primary keys and you absolutely did run out of those and its been a major issue for a lot of apps for the last few years. But yeah, you'll never run out of bigints SQL servers couldn't cope with the amount of data that would require.
- jandrewrogers 4y ago
  Some famous RDBMS bugs had their root cause in this kind of reasoning, because the model embeds assumptions on how those IDs will be generated and used which may not hold true in the future for reasons that are difficult to anticipate. For example, Oracle had a 48-bit ID rollover bug many years ago that by all calculations should never occur in real systems. This calculation was made under the assumption that the IDs were mostly actually used. However, many features added later necessitated generating or reserving vast numbers of IDs in bulk, a low-cost optimization, the vast majority of which were ultimately discarded. It got to the point where very large systems started running out of these IDs due to the fact that such a low percentage were used in the way the designers had anticipated. Extremely large systems do not run into the limitations of bigint because at that scale the identifiers are naturally segmented, often implicitly.
  - staticassertion 4y ago
    > Extremely large systems do not run into the limitations of bigint because at that scale the identifiers are naturally segmented, often implicitly. Agreed. That's the real key imo. You could have a 2^64 random number as a primary key if that key is also namespaced per customer - maybe in aggregate your customers generate > 2^32 events but a given customer may not. And there's other ways to limit it further. This is the approach we take, basically, although we use a counter and not a random number.
RhodesianHunter 4y ago
Something like Snowflake ids has many of these advantages while being integers.
thayne 4y ago
Also, using an incrementing id can lead to to information disclosure if they are visible in user facing APIs. For example, if I know an id for my user, document, cart, etc. Then I know all ids lower than that are probably also valid.
feoren 4y ago
> if you ever need to combine multiple data sources together in migration and recovery type scenarios This insane idea that combining data sources is a rare event in some unusual "migration and recovery" scenarios is one of the most poisonous and yet pervasive ideas in all of database design. You are always combining multiple data sources, all the time. Users submitting data from a form is a data source. Test, staging, and production deployments, with multiple of each. External APIs. Multiple clients. Eventual consistency. Replication. Microservices with distributed systems. Or even sharing any common data at all between different systems, like unit conversions, chemical data, country names, engineering constants, etc. Anyone who even considers using a single authoritative source for all entity identity either better be making a system in an underground bunker that will never talk to any other system. Otherwise they are making a serious and extremely avoidable mistake. Never use auto-incrementing IDs. It's even wrong in a monolith! Why does everyone abandon this idea of "separation of concerns" and "single responsibility principle" and "bounded contexts" and proper abstraction and limited communication between system parts and literally every design principle they've ever been taught when they go to design a database? It all just goes out the window! Why do you guys bother reading books about system design if you ignore them when you build a database? "Multiple systems communicating with each other" should apply recursively all the way from deployment and external integration down to individual functions. That means database, too. Auto-incrementing IDs are anathema to that.
- bruce511 4y ago
  While your tone might be a tad hyperbolic, I agree with your basic premise. If I could go back and tell my 30 year ago self one tip, it would be to use uuids over auto-increments. And this is back when that was expensive - in disk space and database time. Instead I'm stuck with my design, and as time has passed the real cost of auto-Inc has slowly revealed itself. What's interesting to me though is that this view is not universal. I get a lot of push-back when promoting uuids, but I can really only speak to my experience.
  - acmecorps 4y ago
    You’re not alone. I’ve been migrating my tables to use uuid instead of integers and have been using uuid whenever I have new tables, unless I have very good reason not to. Experience was my teacher.

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.
  - Dylan16807 4y ago
    Why base36 in particular? That implies that you're worried you might lose case information but you're not worried about characters that look the same?
selcuka 4y ago
You may consider encoding the uuid in base58 as it is shorter, safe to use in URLs, and easier to debug as it doesn't use characters that can be confused such as 0, 0, I, and l.
- deleted 4y ago
  [deleted]
- inopinatus 4y ago
  I'll add a voice for base58 representation of UUIDs on the wire, it's my preferred solution as well. Not only for debugging: by being compact, unambiguous, and URL-safe, the helpdesk is also spared a cringeworthy source of PEBCAK incidents since misquoting the identifiers is simply harder. Caveat programmer, though, there is a hazard, occurring when someone is glib about the usage and relies on randomly generated fixed-length base58 values directly. It happens readily because some popular frameworks include such generation as a utility function. However, 58^22 > 2^128 > 58^21, so a 22-character base58 representation is expected for UUIDs but carelessly random 22-character base58 values may exceed the capacity of UUID's familiar hexadecimal serialization, effectively an integer overflow. We never generate base58 identifiers as a PK, for example, for this reason (they would of course not be UUIDs either). Alas, there is no conventional base encoding more compact than hexadecimal that is robust to the general problem. And I mention it because this issue was observed in the wild.
  - selcuka 4y ago
    If I'm not mistaken base64 should not cause an integer overflow (i.e. it will always be a 128-bit integer) if you limit it to 22 characters (and append two = characters to pad it when you actually decode it). It may not be as URL-safe as base58 though.
    - inopinatus 4y ago
      Yes. I think of base64 as an unconventional encoding, in that regard, since it's more than just an alphabet for powers of the base but also communicates properties of the output octet length in the serialized form. To do that it's relying on an elegant alignment of bit values to that base58 doesn't have. Perhaps there could be a means to inject the same confluence of form and function into base58, albeit I can't see it after a couple minutes of staring at the algorithm¹. And also yes: the equals sign is potentially hazardous in a query string, and the use of non-alphanumeric symbols in base64 also creates line-break and double-click/double-tap traps when passed around in an ad-hoc fashion, even in the url-safe variant. [1] https://datatracker.ietf.org/doc/html/draft-msporny-base58 https://datatracker.ietf.org/doc/html/draft-msporny-base58

Merad 4y ago

> UUIDs have a few distinct advantages: you'll never run out I'm curious what kind of applications are limited by the range of bigint values? I have no doubt that such applications exist somewhere, but most software engineers won't ever come close to encountering those limits. Even if you have a table that is consistently consuming a billion (with a B) bigint id values _every second_ (is that even feasible with current hardware and RDBMS software?) you won't run out for almost 300 years.

Gigachad 4y ago
For some reason a lot of things used to default to 32 bit primary keys and you absolutely did run out of those and its been a major issue for a lot of apps for the last few years. But yeah, you'll never run out of bigints SQL servers couldn't cope with the amount of data that would require.
jandrewrogers 4y ago
Some famous RDBMS bugs had their root cause in this kind of reasoning, because the model embeds assumptions on how those IDs will be generated and used which may not hold true in the future for reasons that are difficult to anticipate. For example, Oracle had a 48-bit ID rollover bug many years ago that by all calculations should never occur in real systems. This calculation was made under the assumption that the IDs were mostly actually used. However, many features added later necessitated generating or reserving vast numbers of IDs in bulk, a low-cost optimization, the vast majority of which were ultimately discarded. It got to the point where very large systems started running out of these IDs due to the fact that such a low percentage were used in the way the designers had anticipated. Extremely large systems do not run into the limitations of bigint because at that scale the identifiers are naturally segmented, often implicitly.
- staticassertion 4y ago
  > Extremely large systems do not run into the limitations of bigint because at that scale the identifiers are naturally segmented, often implicitly. Agreed. That's the real key imo. You could have a 2^64 random number as a primary key if that key is also namespaced per customer - maybe in aggregate your customers generate > 2^32 events but a given customer may not. And there's other ways to limit it further. This is the approach we take, basically, although we use a counter and not a random number.

RhodesianHunter 4y ago

Something like Snowflake ids has many of these advantages while being integers.

thayne 4y ago

Also, using an incrementing id can lead to to information disclosure if they are visible in user facing APIs. For example, if I know an id for my user, document, cart, etc. Then I know all ids lower than that are probably also valid.

feoren 4y ago

> if you ever need to combine multiple data sources together in migration and recovery type scenarios This insane idea that combining data sources is a rare event in some unusual "migration and recovery" scenarios is one of the most poisonous and yet pervasive ideas in all of database design. You are always combining multiple data sources, all the time. Users submitting data from a form is a data source. Test, staging, and production deployments, with multiple of each. External APIs. Multiple clients. Eventual consistency. Replication. Microservices with distributed systems. Or even sharing any common data at all between different systems, like unit conversions, chemical data, country names, engineering constants, etc. Anyone who even considers using a single authoritative source for all entity identity either better be making a system in an underground bunker that will never talk to any other system. Otherwise they are making a serious and extremely avoidable mistake. Never use auto-incrementing IDs. It's even wrong in a monolith! Why does everyone abandon this idea of "separation of concerns" and "single responsibility principle" and "bounded contexts" and proper abstraction and limited communication between system parts and literally every design principle they've ever been taught when they go to design a database? It all just goes out the window! Why do you guys bother reading books about system design if you ignore them when you build a database? "Multiple systems communicating with each other" should apply recursively all the way from deployment and external integration down to individual functions. That means database, too. Auto-incrementing IDs are anathema to that.

bruce511 4y ago
While your tone might be a tad hyperbolic, I agree with your basic premise. If I could go back and tell my 30 year ago self one tip, it would be to use uuids over auto-increments. And this is back when that was expensive - in disk space and database time. Instead I'm stuck with my design, and as time has passed the real cost of auto-Inc has slowly revealed itself. What's interesting to me though is that this view is not universal. I get a lot of push-back when promoting uuids, but I can really only speak to my experience.
- acmecorps 4y ago
  You’re not alone. I’ve been migrating my tables to use uuid instead of integers and have been using uuid whenever I have new tables, unless I have very good reason not to. Experience was my teacher.