4 ms·

> For example, I'm a big fan of surrogate keys and UUIDs, in database design. You would think this (using surrogate keys) would be such well worn wisdom by now

by beachy 2y ago

> For example, I'm a big fan of surrogate keys and UUIDs, in database design. You would think this (using surrogate keys) would be such well worn wisdom by now that discussions about it wouldn't be a thing. But somehow new generations of developers, weaned on nosql and on not using the god-given gifts of databases like integrity constraints, seem to love bike shedding key design and arguing vociferously that natural keys can be used as primary (and foreign) keys.

ThereIsNoWorry 2y ago
You wouldn't believe how often I have to fight for UUIDs instead of sequencing. UUIDs are great. For all practical purposes 0 possibility of collision; you can use it as an ID in a global system, it just makes so much fucking sense. But the default is still a number natural number sequence. As if it matters for 99% of all cases that stuff is "ordered" and "easily identifiable" by a natural number. But then you want to merge something or make double use and suddenly you have a huge problem that it isn't unique anymore and you need more information to identify. Guess what, an UUID does that job for you, across multiple databases and distributed systems, a UUID is still unique with 99.9999% probability. The one counter-example every 10 years can be cared for manually.
- beachy 2y ago
  As far as I know the only practical downside with UUIDs in the modern age - unless you like using keys for ordering by creation time, or you have such enormous volumes that storage is a consideration - is that they are cumbersome for humans to read and compare, e.g scanning log files. And in any case the German tank problem means that you often can't use incrementing numbers as surrogate keys if they are ever exposed to the public e.g in urls.
  - mrkeen 2y ago
    > they are cumbersome for humans to read and compare, e.g scanning log files. I think the positive still outweighs the negative here: you can search your whole company's logs for a UUID and you won't get false positives like you would with serial integers.
- akira2501 2y ago
  I've been using UUIDv7 a lot lately and I've been quite happy with the results.
prometheus76 2y ago
We were pulling the entire list of tasks (around 80,000 tasks from various projects) from Microsoft Project 2012 (MSP) and dumping it into a database every day, so that we could track progress of projects daily. MSP uses GUIDs and they are great for uniqueness, but the memory footprint they caused when reporting using Power BI became untenable after about six months. We ended up creating our own integer table tied to the GUIDs and replaced all the GUIDs with integers in our reporting database. Our memory footprint dropped by 10X and we were able to report on years worth of data, rather than months.
- CodesInChaos 2y ago
  > Our memory footprint dropped by 10X How? A UUID is only 4x the size of a 32-bit integer.
  - prometheus76 2y ago
    We had multiple columns using UUID that we needed, and the Tabular memory model goes up exponentially for every column.
marcosdumay 2y ago
I never found any kind of literature that clearly states that artificial keys are superior identifiers than natural ones. Every material about relational data I've seen either just explains them, says they can be either natural or artificial, and leaves at that, or says there's some disagreement over what is better. The unanimity exists only within field experts that apply the stuff. The theoreticians all disagree.
- CodesInChaos 2y ago
  I almost always use surrogate keys as primary keys (or a tuple of them, for join-tables). Natural keys are rarely as immutable and unique as we'd like. Even ISO country codes can be problematic, for example we currently use XK for Kosovo, but it might get an official code at some point.
- bruce511 2y ago
  It's less about literature, and more about experience. Over the years just about every natural key I've seen ends up being either not-universal (-everyone- has a phone number right? Even that new-born baby?) Or not unique (phone numbers can be shared) or mutable (like people's names changing). It's just so much easier to add a column and use a surrogate key. I prefer uuids because they are unique And immutable - properties I really appreciate in primary keys.
  - beachy 2y ago
    It's an easy challenge. Just ask someone to name a natural key that will never change. They are as rare as rocking horse shit.

by beachy 2y ago

ThereIsNoWorry 2y ago

You wouldn't believe how often I have to fight for UUIDs instead of sequencing. UUIDs are great. For all practical purposes 0 possibility of collision; you can use it as an ID in a global system, it just makes so much fucking sense. But the default is still a number natural number sequence. As if it matters for 99% of all cases that stuff is "ordered" and "easily identifiable" by a natural number. But then you want to merge something or make double use and suddenly you have a huge problem that it isn't unique anymore and you need more information to identify. Guess what, an UUID does that job for you, across multiple databases and distributed systems, a UUID is still unique with 99.9999% probability. The one counter-example every 10 years can be cared for manually.

beachy 2y ago
As far as I know the only practical downside with UUIDs in the modern age - unless you like using keys for ordering by creation time, or you have such enormous volumes that storage is a consideration - is that they are cumbersome for humans to read and compare, e.g scanning log files. And in any case the German tank problem means that you often can't use incrementing numbers as surrogate keys if they are ever exposed to the public e.g in urls.
- mrkeen 2y ago
  > they are cumbersome for humans to read and compare, e.g scanning log files. I think the positive still outweighs the negative here: you can search your whole company's logs for a UUID and you won't get false positives like you would with serial integers.
akira2501 2y ago
I've been using UUIDv7 a lot lately and I've been quite happy with the results.

prometheus76 2y ago

We were pulling the entire list of tasks (around 80,000 tasks from various projects) from Microsoft Project 2012 (MSP) and dumping it into a database every day, so that we could track progress of projects daily. MSP uses GUIDs and they are great for uniqueness, but the memory footprint they caused when reporting using Power BI became untenable after about six months. We ended up creating our own integer table tied to the GUIDs and replaced all the GUIDs with integers in our reporting database. Our memory footprint dropped by 10X and we were able to report on years worth of data, rather than months.

CodesInChaos 2y ago
> Our memory footprint dropped by 10X How? A UUID is only 4x the size of a 32-bit integer.
- prometheus76 2y ago
  We had multiple columns using UUID that we needed, and the Tabular memory model goes up exponentially for every column.

marcosdumay 2y ago

I never found any kind of literature that clearly states that artificial keys are superior identifiers than natural ones. Every material about relational data I've seen either just explains them, says they can be either natural or artificial, and leaves at that, or says there's some disagreement over what is better. The unanimity exists only within field experts that apply the stuff. The theoreticians all disagree.

CodesInChaos 2y ago
I almost always use surrogate keys as primary keys (or a tuple of them, for join-tables). Natural keys are rarely as immutable and unique as we'd like. Even ISO country codes can be problematic, for example we currently use XK for Kosovo, but it might get an official code at some point.
bruce511 2y ago
It's less about literature, and more about experience. Over the years just about every natural key I've seen ends up being either not-universal (-everyone- has a phone number right? Even that new-born baby?) Or not unique (phone numbers can be shared) or mutable (like people's names changing). It's just so much easier to add a column and use a surrogate key. I prefer uuids because they are unique And immutable - properties I really appreciate in primary keys.
- beachy 2y ago
  It's an easy challenge. Just ask someone to name a natural key that will never change. They are as rare as rocking horse shit.