3 ms·

> 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

by 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.

by Merad 4y ago

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.