4 ms·

Often UUIDs are used as keys to things... And these things are often stored in databases... And usually the database puts them into a btree internally, becaus

Often UUIDs are used as keys to things... And these things are often stored in databases... And usually the database puts them into a btree internally, because that's how tables are stored. The moment you have any kind of load on such a table, your performance goes to hell. This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends. So a common word of wisdom is to have an auto-increment primary key, and the uuid indexed! Ugh what a work around. A better way is to discover ULIDs, which are like UUIDs but with the high-bits including a timestamp. This turns inserts into, approximately, appends. Much nicer! https://github.com/ulid/spec https://github.com/ulid/spec is fairly recent, but the 'trick' has been used for years. I've build gazillion-row databases where all the data sources have, independently, used this same high-bits-are-timestamp trick. So even though I didn't see it discussed and called ULID until fairly recently, its a very old and well-known trick in some circles. Once you have ULIDs and you know you have UILDs, a lot of database type tasks become easier because the ID encodes a creation date. I've found myself adding AND id BETWEEN to queries, and using them as a partition key, and using them to DELETE old data etc, and various other admin stuff that the original creators of the table never thought about.

marcus_holmes 8y ago
thank you. That's the first decent rational explanation I've heard for why anyone would sort anything by UUID. I use UUID's for keys, and always sort my results by another field (any other field). If necessary, I add an index for that other field. I guess I'll have to change this strategy if I get to the kind of scale where the append time matters. I hope I get to that scale, it seems like a good problem to have :) I also have another metric asstonne of problems I'll need to solve at that scale. But then, premature optimisation is the devil, so I'm happy I'm not dealing with any of that until I need to :)
- tempguy9999 8y ago
  Please see my reply above where I disagree, and please watch for willvarfar's response where he's likely to point out that for his case I'm probably wrong. I've got experience with 'large' datasets (it's very relative! Mine have been tiddlers in industry terms!) and I'm happy to pass on anything that may help. Even if you don't scale up, smaller data is likely to improve response times, all else being equal. Anyway, feel free to ask.
  - marcus_holmes 8y ago
    I've tried a few different key strategies. A UUID per row per table seems to work best. Where I define "best" as "suiting my data better". I get the thing about deriving keys from data, but it always seems messy to me. I've seen too many weirdnesses in "real" data to be comfortable that there will not be any collisions (I used to play around with a database of every person and their address in the UK. There were several cases of people having the same name, the same birthdate, the same street name, but different towns. Not to mention the hundreds of cases of twins with only an middle initial to tell them apart.) UUID's might be clunky and a little slower, but at least I will never get a collision, and never cause a problem if I use one key instead of another. I'm totally happy to spend more time optimising my database in exchange for those guarantees.
    - tempguy9999 8y ago
      > Where I define "best" as "suiting my data better". Can't argue! You're in the best place to decide. > UUID's [...] but at least I will never get a collision Hmm, please do some googling for that. I've heard complaints (on the web, not in person) of this happening surprisingly easily. This may not apply for you though. Personally I only trust what I understand, or what I have written guarantees for. Anyway FYI.
- jeltz 8y ago
  Why do you use UUID's for keys? Unless you have many data sources which you need to aggregate using a 64-bit autoincremenet key is probably the best solution. 64-bits is enough for almost everything. They can be worth using if you have many data sources, but otherwise not really.
  - marcus_holmes 8y ago
    1. I have parallel processes that create records on the same table at the same time. 2. In some cases, I want to create the key in code and send it to the database, rather than fetching the key after the insert. 3. I want to know that if I mess up and send a document_id where I meant to send a user_id and vice versa, it's going to report "no match" rather than create a security breach by exposing one user's records to another user. that's the main reasons anyway
    - larkost 8y ago
      Another common reason is that on distributed databases you often don't want your data stored in the same chunk/shard as the last one. You want to spread out your writes. This of course is not good design if your access patterns involve a lot of sequential reads, so you have to be aware of what you are going to do.
  - deleted 8y ago
    [deleted]
tempguy9999 8y ago
> Often UUIDs are used as keys to things... IMO most data in DBs has a natural key. Why add a fake key? If it's to enforce uniqueness then that uniquness is spurious because it's a GUID which has no meaning to the data to which it's attached eg. you could have the same row 10000000 times but falsely uniquified with a different GUID. Only exceptions I've encountered to my 'natural key' claim are addresses, which are a sod to canonicalise (in fact, seemingly impossible, I'd welcome any pointers). Also UUIDs are bloody big keys, so fattening up tables and impairing disk/memory bandwidth. This matters if you have gazillion-row DBs, surely? I dealt with a near-billion row DB and shaving a few bytes off a row would matter to us bigly. For the sake of argument I'll assert that you should never use UUIDs as keys, but please feel free point out where I'm wrong. > This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends. This is opposite to my understanding in that an append to a table causes insertion hotspot which can be bottlenecks for insertion-heavy work. If it's inserting at random places there's no hotspot. I suppose updates scattered all over the btree could cause much more random disk activity though, do you mean that? > So a common word of wisdom is to have an auto-increment primary key, and the uuid indexed! Ugh what a work around. Oh god no! Quite agree. [stuff about ULIDs] I'd really like to understand what you're doing overall. It just sounds just wrong, though I'm sure that's down to me not understanding your use case.
- jeltz 8y ago
  Time sortable UUIDs can be useful when you have many different data sources you aggregate into one table and these data sources all need to assign IDs to the events. By having a 128-bit ID with timestmap upper and random lower bits the risk for collisions is low and by having the first bits be a time stamp you ensure that inserts are quite often done in sequential order, which is good for btrees. There are other solutions for this problem, and I do not think I would pick UUIDs or ULIDs myself, but it is a decent solution for this specific problem and does not waste much space compared to the other solutions.
- willvarfar 8y ago
  Its easy to create plausible scenarios where things have UUID IDs. For example, your classic customer database. Each customer has an ID. Its hard to find a natural key for something as simple as a customer... If you have a very narrow customer base then perhaps the company has a VAT number or something that seems natural. But get an international customer, or simply have problems getting people to enter those kind of details, gets unworkable fast. And then I suppose the 'trading name' is another natural identifier, except they can change. Hell, we've all experienced systems that have used the email address as a natural key, and all the resulting pain as those change. Just today, in fact, I had trouble with a jetbrains subscription when I used my company's new domain name to register something and it created a new account for me. Ugh. Can't work out how to link them, just because I changed the email address on the pay page instead of the account page. Jetbrains, we expect better of you! So, anyway, its normal to go with an auto-increment. But, then, the modern web site you've just built exposes that id to the customer, and you've just leaked how many customers you have! So modern best practice is to only expose UUIDs in public APIs. And I'm saying, go with ULIDs instead. Your database will thank you.
gcb0 8y ago
ulids and all other attemps to solve this on the taxonomy are either forcing a concept onto your data (what if date is irrelevant?) and gives you false assumptions on distributed systems (just because you can get IDs up to cenrtain date, does it means all the shards had a chance to sync back?)
- gcb0 8y ago
  forgot to say, if it already fits your data (i.e. you will have a createdAt column anyway) it is a great choice.

marcus_holmes 8y ago

thank you. That's the first decent rational explanation I've heard for why anyone would sort anything by UUID. I use UUID's for keys, and always sort my results by another field (any other field). If necessary, I add an index for that other field. I guess I'll have to change this strategy if I get to the kind of scale where the append time matters. I hope I get to that scale, it seems like a good problem to have :) I also have another metric asstonne of problems I'll need to solve at that scale. But then, premature optimisation is the devil, so I'm happy I'm not dealing with any of that until I need to :)

tempguy9999 8y ago
Please see my reply above where I disagree, and please watch for willvarfar's response where he's likely to point out that for his case I'm probably wrong. I've got experience with 'large' datasets (it's very relative! Mine have been tiddlers in industry terms!) and I'm happy to pass on anything that may help. Even if you don't scale up, smaller data is likely to improve response times, all else being equal. Anyway, feel free to ask.
- marcus_holmes 8y ago
  I've tried a few different key strategies. A UUID per row per table seems to work best. Where I define "best" as "suiting my data better". I get the thing about deriving keys from data, but it always seems messy to me. I've seen too many weirdnesses in "real" data to be comfortable that there will not be any collisions (I used to play around with a database of every person and their address in the UK. There were several cases of people having the same name, the same birthdate, the same street name, but different towns. Not to mention the hundreds of cases of twins with only an middle initial to tell them apart.) UUID's might be clunky and a little slower, but at least I will never get a collision, and never cause a problem if I use one key instead of another. I'm totally happy to spend more time optimising my database in exchange for those guarantees.
  - tempguy9999 8y ago
    > Where I define "best" as "suiting my data better". Can't argue! You're in the best place to decide. > UUID's [...] but at least I will never get a collision Hmm, please do some googling for that. I've heard complaints (on the web, not in person) of this happening surprisingly easily. This may not apply for you though. Personally I only trust what I understand, or what I have written guarantees for. Anyway FYI.
jeltz 8y ago
Why do you use UUID's for keys? Unless you have many data sources which you need to aggregate using a 64-bit autoincremenet key is probably the best solution. 64-bits is enough for almost everything. They can be worth using if you have many data sources, but otherwise not really.
- marcus_holmes 8y ago
  1. I have parallel processes that create records on the same table at the same time. 2. In some cases, I want to create the key in code and send it to the database, rather than fetching the key after the insert. 3. I want to know that if I mess up and send a document_id where I meant to send a user_id and vice versa, it's going to report "no match" rather than create a security breach by exposing one user's records to another user. that's the main reasons anyway
  - larkost 8y ago
    Another common reason is that on distributed databases you often don't want your data stored in the same chunk/shard as the last one. You want to spread out your writes. This of course is not good design if your access patterns involve a lot of sequential reads, so you have to be aware of what you are going to do.
- deleted 8y ago
  [deleted]

tempguy9999 8y ago

> Often UUIDs are used as keys to things... IMO most data in DBs has a natural key. Why add a fake key? If it's to enforce uniqueness then that uniquness is spurious because it's a GUID which has no meaning to the data to which it's attached eg. you could have the same row 10000000 times but falsely uniquified with a different GUID. Only exceptions I've encountered to my 'natural key' claim are addresses, which are a sod to canonicalise (in fact, seemingly impossible, I'd welcome any pointers). Also UUIDs are bloody big keys, so fattening up tables and impairing disk/memory bandwidth. This matters if you have gazillion-row DBs, surely? I dealt with a near-billion row DB and shaving a few bytes off a row would matter to us bigly. For the sake of argument I'll assert that you should never use UUIDs as keys, but please feel free point out where I'm wrong. > This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends. This is opposite to my understanding in that an append to a table causes insertion hotspot which can be bottlenecks for insertion-heavy work. If it's inserting at random places there's no hotspot. I suppose updates scattered all over the btree could cause much more random disk activity though, do you mean that? > So a common word of wisdom is to have an auto-increment primary key, and the uuid indexed! Ugh what a work around. Oh god no! Quite agree. [stuff about ULIDs] I'd really like to understand what you're doing overall. It just sounds just wrong, though I'm sure that's down to me not understanding your use case.

jeltz 8y ago
Time sortable UUIDs can be useful when you have many different data sources you aggregate into one table and these data sources all need to assign IDs to the events. By having a 128-bit ID with timestmap upper and random lower bits the risk for collisions is low and by having the first bits be a time stamp you ensure that inserts are quite often done in sequential order, which is good for btrees. There are other solutions for this problem, and I do not think I would pick UUIDs or ULIDs myself, but it is a decent solution for this specific problem and does not waste much space compared to the other solutions.
willvarfar 8y ago
Its easy to create plausible scenarios where things have UUID IDs. For example, your classic customer database. Each customer has an ID. Its hard to find a natural key for something as simple as a customer... If you have a very narrow customer base then perhaps the company has a VAT number or something that seems natural. But get an international customer, or simply have problems getting people to enter those kind of details, gets unworkable fast. And then I suppose the 'trading name' is another natural identifier, except they can change. Hell, we've all experienced systems that have used the email address as a natural key, and all the resulting pain as those change. Just today, in fact, I had trouble with a jetbrains subscription when I used my company's new domain name to register something and it created a new account for me. Ugh. Can't work out how to link them, just because I changed the email address on the pay page instead of the account page. Jetbrains, we expect better of you! So, anyway, its normal to go with an auto-increment. But, then, the modern web site you've just built exposes that id to the customer, and you've just leaked how many customers you have! So modern best practice is to only expose UUIDs in public APIs. And I'm saying, go with ULIDs instead. Your database will thank you.

gcb0 8y ago

ulids and all other attemps to solve this on the taxonomy are either forcing a concept onto your data (what if date is irrelevant?) and gives you false assumptions on distributed systems (just because you can get IDs up to cenrtain date, does it means all the shards had a chance to sync back?)

gcb0 8y ago
forgot to say, if it already fits your data (i.e. you will have a createdAt column anyway) it is a great choice.