9 ms·

A prime example of premature optimization. Permanent identifiers should not carry data. This is like the cardinal sin of data management. You always run into s

A prime example of premature optimization. Permanent identifiers should not carry data. This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing changes, you can run into trouble. Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that series!" Librarians in the analog age can be forgiven for cramming data into their identifiers, to save a lookup. When the lookup is in a physical card catalog, that's somewhat understandable (although you bet they could run into trouble over it too). But when you have a powerful database at your fingertips, use it! Don't make decisions you will regret just to shave off a couple of milliseconds!

oncallthrow 10mo ago
It sounds to me like you’re just arguing for premature optimization of another kind (specifically, prematurely changing your entire architecture for edge cases that probably won’t ever happen to you).
- vintermann 10mo ago
  If you have an architecture already, obviously it's hard to change and you may want to postpone it until those edge cases which probably won't ever happen to you, happen. But for new architectures, value your own grey hairs over small performance improvements.
tacone 10mo ago
Fantastic real life example. Italian PNs carry also the gender, which something you can change surgically, and you'll eventually run into the issue when operating at scale. I don't agree with the absolute statement, though. Permanent identifiers should not generally carry data. There are situations where you want to have a way to reconciliate, you have space or speed constraints, so you may accept the trade off, md5 your data and store it in a primary index as a UUID. Your index will fragment and thus you will vacuum, but life will still be good overall.
- mckirk 10mo ago
  I'm not sure whether that was intended, but 'operating at scale' actually made me laugh out loud :D
  - benterix 10mo ago
    I have to admit an unintended chuckle, too.
- cozyman 10mo ago
  how does one change their gender surgically?
  - WalterSlovotsky 10mo ago
    The preferred method would be gender affirming surgery.
    - cozyman 10mo ago
      affirming would mean the change has already taken place
      - BobaFloutist 10mo ago
        Right, because it has. The change in gender identity (or in choosing to make said identity more public )has already taken place, and the surgery seems to affirm that.
  - delichon 10mo ago
    You can't, but since gender isn't defined by anything physical, there's no need.
    - bigstrat2003 10mo ago
      That is only true if you're using an extremely idiosyncratic definition of gender. As far as 95% of English speakers are concerned, gender is defined by the body you possess.
mkleczek 10mo ago
This is actually a very deep and interesting topic. Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. So, what happens next is that the real world tries to adjust and the "data-less" identifier becomes a real world artifact. The situation becomes the same but worse (eg. you don't exist if you don't remember your social security id). In extreme cases people are tattooed with their numbers. The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change.
- vrighter 10mo ago
  You can't take into account the fact that things change when you don't know what those changes might be. You might end up needing to either rebuild a new database, have some painful migration, or support two codepaths to work with both types of keys.
  - groundzeros2015 10mo ago
    You can’t design something by trying to anticipate all future changes. things will change and break. In my personal design sense, I have found keeping away generality actually helps my code last longer (based on more concrete ideas) and easier to change when those days come.
    - dpark 10mo ago
      In my experience, virtually every time I bake concrete data into identifiers I end up regretting it. This isn’t a case of trying to predict all possible future changes. It’s a case of trying to not repeat the exact same mistake again.
      - groundzeros2015 10mo ago
        I don’t disagree with that, I’m disagreeing with this comment that we can’t make protocol or data decisions that might change.
hyperpape 10mo ago
Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.
- vintermann 10mo ago
  I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to. I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID. Isn't that clear enough?
  - mcny 10mo ago
    That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.
    - deleted 10mo ago
      [deleted]
    - vintermann 10mo ago
      You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing? I bet people will extract that date and use it, and it's hard to imagine use which wouldn't be abuse. To take the example of a PN/SSN and the usual gender bit: do you really want anyone to be able to tell that you got a new ID at that time? What could you suspect if a person born in 1987 got a new PN/SSN around 2022? Leaks like that, bypassing whatever access control you have in your database, is just one reason to use real random IDs. But it's even a pretty good one in itself.
      - mcny 10mo ago
hnfong 10mo ago
The curious thing about the article is that, it's definitely premature optimization for smaller databases, but when the database gets to the scale where these optimizations start to matter, you actually don't want to do what they suggest. Specifically, if your database is small, the performance impact is probably not very noticeable. And if your database is large (eg. to the extent primary keys can't fit within 32-bit int), then you're actually going to have to think about sharding and making the system more distributed... and that's where UUID works better than auto-incrementing ints.
- scottlamb 10mo ago
  I agree there's a scale below which this (or any) optimization matters and a scale above which you want your primary key to have locality (in terms of which shard/tablet/... is responsible for the record). But... * I think there is a wide range in the middle where your database can fit on one machine if you do it well, but it's worth optimizing to use a cheaper machine and/or extend the time until you need to switch to a distributed db. You might hit this middle range soon enough (and/or it might be a painful enough transition) that it's worth thinking about it ahead of time. * If/when you do switch to a distributed database, you don't always need to rekey everything: ** You can spread existing keys across shards via hashing on lookup or reversing bits. Some databases (e.g. DynamoDB) actually force this. ** Allocating new ids in the old way could be a big problem, but there are ways out. You might be able to switch allocation schemes entirely without clients noticing if your external keys are sufficiently opaque. If you went with UUIDv7 (which addresses some but not all of the article's points), you can just keep using it. If you want to keep using dense(-ish), (mostly-)sequential bigints, you can amortize the latency by reserving blocks at a time.
oblio 10mo ago
> Well, wrong, since although the date doesn't change. Someone should have told Julius Caesar and Gregory XIII that :-p
barrkel 10mo ago
Uuid v7 just has a bias in its generation; it isn't carrying information. You're not going to try and extract a timestamp from a uuid. Random vs time biased uuids are not a decision to shave off ms that you will regret. Most likely they will be a decision that shaves off seconds (yes, really - especially when you consider locality effects) and you'll regret nothing.
- deleted 10mo ago
  [deleted]
- bri3d 10mo ago
  > You're not going to try and extract a timestamp from a uuid. What? The first 48 bits of an UUID7 are a UNIX timestamp. Whether or not this is a meaningful problem or a benefit to any particular use of UUIDs requires thinking about it; in some cases it’s not to be taken lightly and in others it doesn’t matter at all. I see what you’re getting at, that ignoring the timestamp aspect makes them “just better UUIDs,” but this ignores security implications and the temptation to partition by high bits (timestamp).
  - nine_k 10mo ago
    Nobody forces you to use a real Unix timestamp. BTW the original Unix timestamp is 32 bits (expiring in 2038), and now everyone is switching to 64-bit time_t. What 48 bits? All you need is a guaranteed non-decreasing 48-bit number. A clock is one way to generate it, but I don't see why a UUIDv7 would become invalid if your clock is biased, runs too fast, too slow, or whatever. I would not count on the first 48 bits being a "real" timestamp.
    - bri3d 10mo ago
      > Nobody forces you to use a real Unix timestamp. Besides the UUIDv7 specification, that is? Otherwise you have some arbitrary kind of UUID. > I would not count on the first 48 bits being a "real" timestamp. I agree; this is the existential hazard under discussion which comes from encoding something that might or might not be data into an opaque identifier. I personally don't agree as dogmatically with the grandparent post that extraneous data should _not_ be incorporated into primary key identifiers, but I also disagree that "just use UUIDv7 and treat UUIDs as opaque" is a completely plausible solution either.
sgarland 10mo ago
> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseconds! A bad PK in some databases (InnoDB engine, SQL Server if clustered) can cause query times to go from sub-msec to tens of msec quite easily, especially with cloud solutions where storage isn’t node-local. I don’t just mean a UUID; a BIGINT PK on a 1:M can destroy your latency for the simple reason of needing to fetch a separate page for every record. If instead the PK is a composite of (<linked_id>, id) - e.g. (user_id, id) - where id is a monotonic integer, you’ll have WAY better data locality. Postgres suffers a different but similar problem with its visibility map lookups.
- lukeschlather 10mo ago
  > Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. I am not a cryptographer, but I would want his recommendation reviewed by a cryptographer. And then I would have to implement it. UUIDs have been extensively reviewed by cryptographers, I have a variety of excellent implementations I can use, I know they solve the problem well. I know they can cause performance issues; they're a security feature that is easy to implement, and I can deal with the performance issues if and when they crop up. (Which, in my experience, it's unusual. Even at a large company, most databases I encounter do not have enough data. I will err on the side of security until it becomes a problem, which is a good problem to have.)
  - alerighi 10mo ago
    Why they are a security feature? They are not, the article even says it. Even if UUID4 are random, nobody guarantees that they are generated with a cryptographically secure random number generator, and in fact most implementations don't! The reason why in a lot of context you use UUID is when you have a distributed system where you want your client to decide the ID that is then stored in multiple systems that not communicate. This is surely a valid scenario for random UUID. To me the rule is use UUID as a customer-facing ID for things that has to have an identity (e.g. a user, an order, etc) and expose it publicly through APIs, use integer ID as internal identifier that are used to create relations between entities, and interal IDs are always kept private. That way numeric ID that are more efficient remain inside the database and are used for joining data, UUID is used only for accessing the object from an API (for example) but then internally when joining (where you have to deal with a lot of rows) you can use the more efficient numeric ID. By the way, I think that the thing of "using UUID" came from NoSQL databases, where surely you use an UUID, but also you don't have to join data. People than transposed a best practice in one scenario to SQL, where its not really that best practice...
Traubenfuchs 10mo ago
> Norwegian PNs have your birth date Same with Austrian social security numbers, which, in somes cases, don't contain the persons birth date and in some cases don't contain any existing date at all. Yet many websites enforce a valid date and pull the persons birthdate from it...
deleted 10mo ago
[deleted]
scottlamb 10mo ago
> Permanent identifiers should not carry data. I think you're attacking a straw man. The article doesn't say "instead of UUIDv4 primary keys, use keys such as birthdays with exposed semantic meaning". On the contrary, they have a section about how to use sequence numbers internally but obfuscated keys externally. (Although I agree with dfox's and formerly_proven's comments [1, 2] that XOR method they proposed for this is terrible. Reuse of a one-time pad is probably the most basic textbook example of bad cryptography. They referred to the values as "obfuscated" so they probably know this. They should have just gone with a better method instead.) [1] https://news.ycombinator.com/item?id=46272985 https://news.ycombinator.com/item?id=46272985 [2] https://news.ycombinator.com/item?id=46273325 https://news.ycombinator.com/item?id=46273325
- naasking 10mo ago
  I don't think the objection is that it exposes semantic meaning, but that any meaningful information is contained within the key at all, eg. even a UUID that includes timestamp information about when it was generated is "bad" in a sense, as it leaks information. Unique identifiers should be opaque and inherently meaningless.
  - scottlamb 10mo ago
    Your understanding is inconsistent with the examples in vintermann's comment. Using a sequence number as an internal-only surrogate key (deliberately opaqued when sent outside the bounds of the database) is not the same as sticking gender identity, birth date, or any natural properties of a book into a broadly shared identifier.
    - naasking 10mo ago
      No it's not, they very explicitly clarify in follow-up comments that unique identifiers should not be embedded any kind of meaningful content. See: https://news.ycombinator.com/item?id=46276995 https://news.ycombinator.com/item?id=46276995 https://news.ycombinator.com/item?id=46273798 https://news.ycombinator.com/item?id=46273798
benterix 10mo ago
Your comment is valid but is not related to the article.
- deleted 10mo ago
  [deleted]
- spoiler 10mo ago
  More broadly, this is the ages old surrogate vs natural key discussion, but yes the comment completely misses the point of the article. I can only assume they didn't read it in full!
  - vintermann 10mo ago
    The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. A running number also carries data. Before you know it, someone's relying on the ordering or counting on there not being gaps - or counting the gaps to figure out something they shouldn't.
    - benterix 10mo ago
      > The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. Let's clarify things. The author argues against UUIDv4 as primary keys when compared to integers or bigints in large databases for performance reasons. The examples you give refer to the common mistake of using a non-unique attribute that can be changed for a given entity as a primary key.
    - konart 10mo ago
      >Before you know it, someone's relying Do not expose your internal IDs. As simple as that.
      - dpark 10mo ago
        This came up in the last two threads I read about uuidv7. This is simply not a meaningful statement. Any ID you expose externally is also an internal ID. Any ID you do not expose is internal-only. If you expose data in a repeatable way, you still have to choose what IDs to expose, whether that’s the primary key or a secondary key. (In some cases you can avoid exposing keys at all, but those are narrow cases.)
ralferoo 10mo ago
> Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that series!" To me, what your example really shows is the problem with incorrect default values, not a problem with encoding data into a key per se. If they'd chosen a non-date for unknown values, maybe 00 or 99 for day or month components, then the issue you described would disappear. But in case, the intention for encoding a timestamp into a UUID isn't for any implied meaning. It's both to guarantee uniqueness with a side effect that IDs are more or less monotonically increasing. Whether this is actually desirable depends on your application, but generally if the application is as a indexed key for insertion into a database, it's usually more useful for performance than a fully random ID as it avoids rewriting lots of leaf-nodes of B-trees. If you insert a load of these such keys, it forms a cluster on one side of the tree that can the rebalance with only the top levels needing to be rewritten.
- tyre 10mo ago
  And then have to enter/handle a non-date through all systems? How do you know if this non-dated person is over the age of minority? Eligible for a pension? Maybe the answer is to evenly spread the defaults over 365 days.
  - ralferoo 10mo ago
    If you don't know their birthday, you can presumably never answer that question in any case. If you only know the birth year and keyed 99 as the month for unknown, then your algorithm would determine they were of a correct age on the start of the year after that was true, which I guess would be what you want for legal compliance. If you don't even know if the birth year is correct, then the correct process depends on policy. Maybe they choose any year, maybe they choose the oldest/youngest year they might be, maybe they just encode that as 0000/9999. Again, if you don't know the birth year of someone, you would have no way of knowing their age. I'm not sure that means that the general policy of putting a birthday into their ID number is flawed. Many governments re-issue national IDs to the same person with different numbers, which is far less problematic that the many governments who choose to issue the same national ID (looking at you USA with your SSN) to multiple individuals. It doesn't seem like a massive imposition on a person who was originally issued an ID based on not knowing when their birthday to be re-issued a new ID when their birthday was ascertained. Perhaps even give them a choice of keeping the old one knowing it will cause problems, or take the new one instead and having the responsibility to tell people their number had changed. Presumably the governments that choose to embed the date into a national ID number do so because it's more useful for their purposes to do so than just assigning everyone a random number.
mzi 10mo ago
> Even if nothing changes, you can run into trouble. Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? I guess that Norway has solved it in the same or similar way as Sweden? So a person is identified by the PNR and for those systems that need to track a person over several PNR (government agencies) use PRI. And a PRI is just the first PNR assigned to a person with a 1 inserted in the middle. If that PRI is occupied, use a 2,and so on. PRI could of course have been a UUID instead.
GuB-42 10mo ago
I don't think the timestamped UUIDs are "carrying data", it is just a heuristic to improve lookup performance. If the timestamp is wrong, it will just run as slow as the non-timestamped UUID. If you take the gender example, for 99% of people, it is male/female and it won't change, and you can use that for load balancing. But if later, you found out that the gender is not the one you expect for that bucket, no big deal, it will cause a branch misprediction, but instead of happening 50% of the times when you use a random value, it will only happen 1% of the times, significant speedup with no loss in functionality.
- moralestapia 10mo ago
  >and you can use that for load balancing As long as you're not in China or India around specific years ... GP's point stands strong.
- delecti 10mo ago
  As soon as you encode imperfect data in an immutable key, you always have to check when you retrieve it. If that piece of data isn't absolutely 100% guaranteed to be perfect, then you have to query both halves of the load balanced DB anyway.
cycomanic 10mo ago
Like the other poster said, this is a problem with default values not encoding the birthday into the personnummer. I think it also is important to remember the purpose of specific numbers. For instance I would argue a PN without the birthday would be strictly worse. With the current system (I only know the Swedish one, but assume it's the same) I only have to remember a 4 digit (because the number is bdate + unique 4 digits). If we would instead use completely random numbers I would have to remember at least an 8 digit number (and likely to be future proof you'd want at least 9 digits). Sure that's fine for myself (although I suspect some people already struggle with it), but then I also have to remember the numbers for my 2 kids and my partner and things become quickly annoying. Especially, because one doesn't use the numbers often enough that it becomes easy, but still often enough that it becomes annoying to look up, especially when one doesn't always cary their phone with them.
- guhcampos 10mo ago
  It's not that bad. Brazilian CPF are 11 numbers and everyone remembers them. You just get use to it =)
asah 10mo ago
counterpoint: IRL, data values in a system like PostgreSQL are padded to word boundaries so either you're wasting bits or "carrying data."
PunchyHamster 10mo ago
The cause is more just "not having enough bits". UUID is 128 bit. You're not running out even if you use part for timestamp, the random part will be big enough. Like, it's a valid complaint.. just not for discussion at hand. Also, we do live in reality and while having entirely random one might be perfect from theory of data, in reality having it be prefixed by date have many advantages performance wise. > Permanent identifiers should not carry data. This is like the cardinal sin of data management As long as you don't use the data and have actual fields for what's also encoded in UUID, there is absolutely nothing wrong with it, provided there is enough of the random part to get around artifacts in real life data.
danudey 10mo ago
Perhaps you can clarify something for me, because I think I'm missing it. > Norwegian PNs have your birth date (in DDMMYY format) as the first six digits So presumably the format is DDMMYYXXXXX (for some arbitrary number of X's), where the XXX represents e.g. an automatically incrementing number of some kind? Which means that if it's DDMMYYXXX then you can only have 1000 people born on DDMMYY, and if it's DDMMYYXXXXX then you can have 100,000 people born on DDMMYY. So in order for there to be so many such entries in common that people are denied use of their actual birthday, then one of the following must be true: 1. The XXX counter must be extremely small, in order for it to run out as a result of people 'using up' those Jan 1 dates each year 2. The number of people born on Jan 1 or immigrating to Norway without knowledge of their birthday must be colossal If it was just DDMMXXXXX (no year) then I can see how this system would fall apart rapidly, but when you're dealing with specifically "people born on Jan 1 2014 or who immigrated to Norway and didn't know their birthday and were born on/around 2014 so that was the year chosen" I'm not sure how that becomes a sufficiently large number to cause these issues. Perhaps this only occurs in specific years where huge numbers of poorly-documented refugees are accepted? (Happy to be educated, as I must be missing something here)
maxbond 10mo ago
> Permanent identifiers should not carry data. Do you have the same criticism for serial identifiers? How about hashes? What about the version field in UUIDs?

oncallthrow 10mo ago

It sounds to me like you’re just arguing for premature optimization of another kind (specifically, prematurely changing your entire architecture for edge cases that probably won’t ever happen to you).

vintermann 10mo ago
If you have an architecture already, obviously it's hard to change and you may want to postpone it until those edge cases which probably won't ever happen to you, happen. But for new architectures, value your own grey hairs over small performance improvements.

tacone 10mo ago

Fantastic real life example. Italian PNs carry also the gender, which something you can change surgically, and you'll eventually run into the issue when operating at scale. I don't agree with the absolute statement, though. Permanent identifiers should not generally carry data. There are situations where you want to have a way to reconciliate, you have space or speed constraints, so you may accept the trade off, md5 your data and store it in a primary index as a UUID. Your index will fragment and thus you will vacuum, but life will still be good overall.

mckirk 10mo ago
I'm not sure whether that was intended, but 'operating at scale' actually made me laugh out loud :D
- benterix 10mo ago
  I have to admit an unintended chuckle, too.
cozyman 10mo ago
how does one change their gender surgically?
- WalterSlovotsky 10mo ago
  The preferred method would be gender affirming surgery.
  - cozyman 10mo ago
    affirming would mean the change has already taken place
    - BobaFloutist 10mo ago
      Right, because it has. The change in gender identity (or in choosing to make said identity more public )has already taken place, and the surgery seems to affirm that.
- delichon 10mo ago
  You can't, but since gender isn't defined by anything physical, there's no need.
  - bigstrat2003 10mo ago
    That is only true if you're using an extremely idiosyncratic definition of gender. As far as 95% of English speakers are concerned, gender is defined by the body you possess.

mkleczek 10mo ago

This is actually a very deep and interesting topic. Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. So, what happens next is that the real world tries to adjust and the "data-less" identifier becomes a real world artifact. The situation becomes the same but worse (eg. you don't exist if you don't remember your social security id). In extreme cases people are tattooed with their numbers. The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change.

vrighter 10mo ago
You can't take into account the fact that things change when you don't know what those changes might be. You might end up needing to either rebuild a new database, have some painful migration, or support two codepaths to work with both types of keys.
- groundzeros2015 10mo ago
  You can’t design something by trying to anticipate all future changes. things will change and break. In my personal design sense, I have found keeping away generality actually helps my code last longer (based on more concrete ideas) and easier to change when those days come.
  - dpark 10mo ago
    In my experience, virtually every time I bake concrete data into identifiers I end up regretting it. This isn’t a case of trying to predict all possible future changes. It’s a case of trying to not repeat the exact same mistake again.
    - groundzeros2015 10mo ago
      I don’t disagree with that, I’m disagreeing with this comment that we can’t make protocol or data decisions that might change.

hyperpape 10mo ago

Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.

vintermann 10mo ago
I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to. I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID. Isn't that clear enough?
- mcny 10mo ago
  That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.
  - deleted 10mo ago
    [deleted]
  - vintermann 10mo ago
    You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing? I bet people will extract that date and use it, and it's hard to imagine use which wouldn't be abuse. To take the example of a PN/SSN and the usual gender bit: do you really want anyone to be able to tell that you got a new ID at that time? What could you suspect if a person born in 1987 got a new PN/SSN around 2022? Leaks like that, bypassing whatever access control you have in your database, is just one reason to use real random IDs. But it's even a pretty good one in itself.
    - mcny 10mo ago

hnfong 10mo ago

The curious thing about the article is that, it's definitely premature optimization for smaller databases, but when the database gets to the scale where these optimizations start to matter, you actually don't want to do what they suggest. Specifically, if your database is small, the performance impact is probably not very noticeable. And if your database is large (eg. to the extent primary keys can't fit within 32-bit int), then you're actually going to have to think about sharding and making the system more distributed... and that's where UUID works better than auto-incrementing ints.

scottlamb 10mo ago
I agree there's a scale below which this (or any) optimization matters and a scale above which you want your primary key to have locality (in terms of which shard/tablet/... is responsible for the record). But... * I think there is a wide range in the middle where your database can fit on one machine if you do it well, but it's worth optimizing to use a cheaper machine and/or extend the time until you need to switch to a distributed db. You might hit this middle range soon enough (and/or it might be a painful enough transition) that it's worth thinking about it ahead of time. * If/when you do switch to a distributed database, you don't always need to rekey everything: ** You can spread existing keys across shards via hashing on lookup or reversing bits. Some databases (e.g. DynamoDB) actually force this. ** Allocating new ids in the old way could be a big problem, but there are ways out. You might be able to switch allocation schemes entirely without clients noticing if your external keys are sufficiently opaque. If you went with UUIDv7 (which addresses some but not all of the article's points), you can just keep using it. If you want to keep using dense(-ish), (mostly-)sequential bigints, you can amortize the latency by reserving blocks at a time.

oblio 10mo ago

> Well, wrong, since although the date doesn't change. Someone should have told Julius Caesar and Gregory XIII that :-p

barrkel 10mo ago

Uuid v7 just has a bias in its generation; it isn't carrying information. You're not going to try and extract a timestamp from a uuid. Random vs time biased uuids are not a decision to shave off ms that you will regret. Most likely they will be a decision that shaves off seconds (yes, really - especially when you consider locality effects) and you'll regret nothing.

deleted 10mo ago
[deleted]
bri3d 10mo ago
> You're not going to try and extract a timestamp from a uuid. What? The first 48 bits of an UUID7 are a UNIX timestamp. Whether or not this is a meaningful problem or a benefit to any particular use of UUIDs requires thinking about it; in some cases it’s not to be taken lightly and in others it doesn’t matter at all. I see what you’re getting at, that ignoring the timestamp aspect makes them “just better UUIDs,” but this ignores security implications and the temptation to partition by high bits (timestamp).
- nine_k 10mo ago
  Nobody forces you to use a real Unix timestamp. BTW the original Unix timestamp is 32 bits (expiring in 2038), and now everyone is switching to 64-bit time_t. What 48 bits? All you need is a guaranteed non-decreasing 48-bit number. A clock is one way to generate it, but I don't see why a UUIDv7 would become invalid if your clock is biased, runs too fast, too slow, or whatever. I would not count on the first 48 bits being a "real" timestamp.
  - bri3d 10mo ago
    > Nobody forces you to use a real Unix timestamp. Besides the UUIDv7 specification, that is? Otherwise you have some arbitrary kind of UUID. > I would not count on the first 48 bits being a "real" timestamp. I agree; this is the existential hazard under discussion which comes from encoding something that might or might not be data into an opaque identifier. I personally don't agree as dogmatically with the grandparent post that extraneous data should _not_ be incorporated into primary key identifiers, but I also disagree that "just use UUIDv7 and treat UUIDs as opaque" is a completely plausible solution either.

sgarland 10mo ago

> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseconds! A bad PK in some databases (InnoDB engine, SQL Server if clustered) can cause query times to go from sub-msec to tens of msec quite easily, especially with cloud solutions where storage isn’t node-local. I don’t just mean a UUID; a BIGINT PK on a 1:M can destroy your latency for the simple reason of needing to fetch a separate page for every record. If instead the PK is a composite of (<linked_id>, id) - e.g. (user_id, id) - where id is a monotonic integer, you’ll have WAY better data locality. Postgres suffers a different but similar problem with its visibility map lookups.

lukeschlather 10mo ago
> Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. I am not a cryptographer, but I would want his recommendation reviewed by a cryptographer. And then I would have to implement it. UUIDs have been extensively reviewed by cryptographers, I have a variety of excellent implementations I can use, I know they solve the problem well. I know they can cause performance issues; they're a security feature that is easy to implement, and I can deal with the performance issues if and when they crop up. (Which, in my experience, it's unusual. Even at a large company, most databases I encounter do not have enough data. I will err on the side of security until it becomes a problem, which is a good problem to have.)
- alerighi 10mo ago
  Why they are a security feature? They are not, the article even says it. Even if UUID4 are random, nobody guarantees that they are generated with a cryptographically secure random number generator, and in fact most implementations don't! The reason why in a lot of context you use UUID is when you have a distributed system where you want your client to decide the ID that is then stored in multiple systems that not communicate. This is surely a valid scenario for random UUID. To me the rule is use UUID as a customer-facing ID for things that has to have an identity (e.g. a user, an order, etc) and expose it publicly through APIs, use integer ID as internal identifier that are used to create relations between entities, and interal IDs are always kept private. That way numeric ID that are more efficient remain inside the database and are used for joining data, UUID is used only for accessing the object from an API (for example) but then internally when joining (where you have to deal with a lot of rows) you can use the more efficient numeric ID. By the way, I think that the thing of "using UUID" came from NoSQL databases, where surely you use an UUID, but also you don't have to join data. People than transposed a best practice in one scenario to SQL, where its not really that best practice...

Traubenfuchs 10mo ago

> Norwegian PNs have your birth date Same with Austrian social security numbers, which, in somes cases, don't contain the persons birth date and in some cases don't contain any existing date at all. Yet many websites enforce a valid date and pull the persons birthdate from it...

deleted 10mo ago

[deleted]

scottlamb 10mo ago

> Permanent identifiers should not carry data. I think you're attacking a straw man. The article doesn't say "instead of UUIDv4 primary keys, use keys such as birthdays with exposed semantic meaning". On the contrary, they have a section about how to use sequence numbers internally but obfuscated keys externally. (Although I agree with dfox's and formerly_proven's comments [1, 2] that XOR method they proposed for this is terrible. Reuse of a one-time pad is probably the most basic textbook example of bad cryptography. They referred to the values as "obfuscated" so they probably know this. They should have just gone with a better method instead.) [1] https://news.ycombinator.com/item?id=46272985 https://news.ycombinator.com/item?id=46272985 [2] https://news.ycombinator.com/item?id=46273325 https://news.ycombinator.com/item?id=46273325

naasking 10mo ago
I don't think the objection is that it exposes semantic meaning, but that any meaningful information is contained within the key at all, eg. even a UUID that includes timestamp information about when it was generated is "bad" in a sense, as it leaks information. Unique identifiers should be opaque and inherently meaningless.
- scottlamb 10mo ago
  Your understanding is inconsistent with the examples in vintermann's comment. Using a sequence number as an internal-only surrogate key (deliberately opaqued when sent outside the bounds of the database) is not the same as sticking gender identity, birth date, or any natural properties of a book into a broadly shared identifier.
  - naasking 10mo ago
    No it's not, they very explicitly clarify in follow-up comments that unique identifiers should not be embedded any kind of meaningful content. See: https://news.ycombinator.com/item?id=46276995 https://news.ycombinator.com/item?id=46276995 https://news.ycombinator.com/item?id=46273798 https://news.ycombinator.com/item?id=46273798

benterix 10mo ago

Your comment is valid but is not related to the article.

deleted 10mo ago
[deleted]
spoiler 10mo ago
More broadly, this is the ages old surrogate vs natural key discussion, but yes the comment completely misses the point of the article. I can only assume they didn't read it in full!
- vintermann 10mo ago
  The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. A running number also carries data. Before you know it, someone's relying on the ordering or counting on there not being gaps - or counting the gaps to figure out something they shouldn't.
  - benterix 10mo ago
    > The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. Let's clarify things. The author argues against UUIDv4 as primary keys when compared to integers or bigints in large databases for performance reasons. The examples you give refer to the common mistake of using a non-unique attribute that can be changed for a given entity as a primary key.
  - konart 10mo ago
    >Before you know it, someone's relying Do not expose your internal IDs. As simple as that.
    - dpark 10mo ago
      This came up in the last two threads I read about uuidv7. This is simply not a meaningful statement. Any ID you expose externally is also an internal ID. Any ID you do not expose is internal-only. If you expose data in a repeatable way, you still have to choose what IDs to expose, whether that’s the primary key or a secondary key. (In some cases you can avoid exposing keys at all, but those are narrow cases.)

ralferoo 10mo ago

> Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that series!" To me, what your example really shows is the problem with incorrect default values, not a problem with encoding data into a key per se. If they'd chosen a non-date for unknown values, maybe 00 or 99 for day or month components, then the issue you described would disappear. But in case, the intention for encoding a timestamp into a UUID isn't for any implied meaning. It's both to guarantee uniqueness with a side effect that IDs are more or less monotonically increasing. Whether this is actually desirable depends on your application, but generally if the application is as a indexed key for insertion into a database, it's usually more useful for performance than a fully random ID as it avoids rewriting lots of leaf-nodes of B-trees. If you insert a load of these such keys, it forms a cluster on one side of the tree that can the rebalance with only the top levels needing to be rewritten.

tyre 10mo ago
And then have to enter/handle a non-date through all systems? How do you know if this non-dated person is over the age of minority? Eligible for a pension? Maybe the answer is to evenly spread the defaults over 365 days.
- ralferoo 10mo ago
  If you don't know their birthday, you can presumably never answer that question in any case. If you only know the birth year and keyed 99 as the month for unknown, then your algorithm would determine they were of a correct age on the start of the year after that was true, which I guess would be what you want for legal compliance. If you don't even know if the birth year is correct, then the correct process depends on policy. Maybe they choose any year, maybe they choose the oldest/youngest year they might be, maybe they just encode that as 0000/9999. Again, if you don't know the birth year of someone, you would have no way of knowing their age. I'm not sure that means that the general policy of putting a birthday into their ID number is flawed. Many governments re-issue national IDs to the same person with different numbers, which is far less problematic that the many governments who choose to issue the same national ID (looking at you USA with your SSN) to multiple individuals. It doesn't seem like a massive imposition on a person who was originally issued an ID based on not knowing when their birthday to be re-issued a new ID when their birthday was ascertained. Perhaps even give them a choice of keeping the old one knowing it will cause problems, or take the new one instead and having the responsibility to tell people their number had changed. Presumably the governments that choose to embed the date into a national ID number do so because it's more useful for their purposes to do so than just assigning everyone a random number.

mzi 10mo ago

> Even if nothing changes, you can run into trouble. Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? I guess that Norway has solved it in the same or similar way as Sweden? So a person is identified by the PNR and for those systems that need to track a person over several PNR (government agencies) use PRI. And a PRI is just the first PNR assigned to a person with a 1 inserted in the middle. If that PRI is occupied, use a 2,and so on. PRI could of course have been a UUID instead.

GuB-42 10mo ago

I don't think the timestamped UUIDs are "carrying data", it is just a heuristic to improve lookup performance. If the timestamp is wrong, it will just run as slow as the non-timestamped UUID. If you take the gender example, for 99% of people, it is male/female and it won't change, and you can use that for load balancing. But if later, you found out that the gender is not the one you expect for that bucket, no big deal, it will cause a branch misprediction, but instead of happening 50% of the times when you use a random value, it will only happen 1% of the times, significant speedup with no loss in functionality.

moralestapia 10mo ago
>and you can use that for load balancing As long as you're not in China or India around specific years ... GP's point stands strong.
delecti 10mo ago
As soon as you encode imperfect data in an immutable key, you always have to check when you retrieve it. If that piece of data isn't absolutely 100% guaranteed to be perfect, then you have to query both halves of the load balanced DB anyway.

cycomanic 10mo ago

Like the other poster said, this is a problem with default values not encoding the birthday into the personnummer. I think it also is important to remember the purpose of specific numbers. For instance I would argue a PN without the birthday would be strictly worse. With the current system (I only know the Swedish one, but assume it's the same) I only have to remember a 4 digit (because the number is bdate + unique 4 digits). If we would instead use completely random numbers I would have to remember at least an 8 digit number (and likely to be future proof you'd want at least 9 digits). Sure that's fine for myself (although I suspect some people already struggle with it), but then I also have to remember the numbers for my 2 kids and my partner and things become quickly annoying. Especially, because one doesn't use the numbers often enough that it becomes easy, but still often enough that it becomes annoying to look up, especially when one doesn't always cary their phone with them.

guhcampos 10mo ago
It's not that bad. Brazilian CPF are 11 numbers and everyone remembers them. You just get use to it =)

asah 10mo ago

counterpoint: IRL, data values in a system like PostgreSQL are padded to word boundaries so either you're wasting bits or "carrying data."

PunchyHamster 10mo ago

The cause is more just "not having enough bits". UUID is 128 bit. You're not running out even if you use part for timestamp, the random part will be big enough. Like, it's a valid complaint.. just not for discussion at hand. Also, we do live in reality and while having entirely random one might be perfect from theory of data, in reality having it be prefixed by date have many advantages performance wise. > Permanent identifiers should not carry data. This is like the cardinal sin of data management As long as you don't use the data and have actual fields for what's also encoded in UUID, there is absolutely nothing wrong with it, provided there is enough of the random part to get around artifacts in real life data.

danudey 10mo ago

Perhaps you can clarify something for me, because I think I'm missing it. > Norwegian PNs have your birth date (in DDMMYY format) as the first six digits So presumably the format is DDMMYYXXXXX (for some arbitrary number of X's), where the XXX represents e.g. an automatically incrementing number of some kind? Which means that if it's DDMMYYXXX then you can only have 1000 people born on DDMMYY, and if it's DDMMYYXXXXX then you can have 100,000 people born on DDMMYY. So in order for there to be so many such entries in common that people are denied use of their actual birthday, then one of the following must be true: 1. The XXX counter must be extremely small, in order for it to run out as a result of people 'using up' those Jan 1 dates each year 2. The number of people born on Jan 1 or immigrating to Norway without knowledge of their birthday must be colossal If it was just DDMMXXXXX (no year) then I can see how this system would fall apart rapidly, but when you're dealing with specifically "people born on Jan 1 2014 or who immigrated to Norway and didn't know their birthday and were born on/around 2014 so that was the year chosen" I'm not sure how that becomes a sufficiently large number to cause these issues. Perhaps this only occurs in specific years where huge numbers of poorly-documented refugees are accepted? (Happy to be educated, as I must be missing something here)

maxbond 10mo ago

> Permanent identifiers should not carry data. Do you have the same criticism for serial identifiers? How about hashes? What about the version field in UUIDs?