4 ms·

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 result

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

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]