4 ms·

> 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 sp

> 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.
- tempguy9999 8y ago
  I'm going to clarify something; I said most data has a natural key then when presented with this and other examples I seemed to have backed away from that. I hope this makes sense... In the real world of atoms etc. there's no natural abstraction, including unique IDs (keys). These are all creations of ours. In my line(s) of work, someone's invented and allocated a key for most items by the time it's reached me - people have SSN/NI numbers, planes will have some identifier, ships have IMOs, machine parts have unique part numbers (when combined with their suppliers' ID), geographical areas have postcodes/zipcodes, and all that. So for me, someone's done that work already; a 'natural key' is available and I can rely on it. It's always been there, except for a few things such as addresses. For you, willvarfar, customer numbers have to be created. So I made a wide assertion without thinking it through based just on my experience.
heavenlyblue 8y ago
>> I dealt with a near-billion row DB and shaving a few bytes off a row would matter to us bigly. A billion rows require a gigabyte per each extra byte in a row. That's nothing.
- willvarfar 8y ago
  I have 200 billion row mysql dbs and have measured the impact of the UUID and ULID keys its forced to deal with. In my high-insert-rate scenarios the extra bytes of these long keys equates to losing about 2000 inserts/sec, just in all the network overhead, compared to if we'd had a 64-bit auto-inc. And then, for storage, I'm using tokudb (when you have tables this big there really isn't any other viable alternative, although myrocks is newer and I haven't evaluated it). Tokudb compresses things really well, and the result is in the tens of terabytes compressed. And the problem with UUIDs and the random part of ULIDs? They are, by definition, uncompressible! Again a lot of that storage footprint is because of the damn things.
tempguy9999 8y ago
Replying to all. @jeltz: > Time sortable UUIDs... Then perhaps store the time as a separate field? Makes them indexable and trivially extracted. > you ensure that inserts are quite often done in sequential order, which is good for btrees. I've just explained why that may not be the case under high insertion rates. This from https://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/ https://www.sqlpassion.at/archive/2014/04/15/an-ever-increas... "With the best practice of an ever-increasing Clustered Key value you have a single hotspot at the end of your Clustered Key. The smaller your records are, the more contention you are introducing here. How can you solve that problem? Easy: spread your INSERT statements across the whole B-Tree structure of the Clustered Index." @willvarfar: > Its hard to find a natural key for something as simple as a customer... OK, completely agree, and you go further - I can't dispute any of that. Only thing I'd say is perhaps don't use a UUID. A 4 byte int will give you plenty of space and use 1/4 of the memory of a GUID. But I may just be splitting hairs. Point accepted! @heavenlyblue: > A billion rows require a gigabyte per each extra byte in a row. That's nothing. I can do the maths, thanks. But if you need real-time responses out a single DB machine serving many users, some heavy, then you have to cache 98% to 100% inside the RAM. Each gig of table read may be an extra 1-5 seconds, depending on disks & setup. Contention increases as more users pile in. Contention increases horribly if queries are greatly slowed by needing to read off the disk. I was fighting for that space to get, and keep, responsiveness. "buy more machines" is a good response but only after you've optimised to the point of minimal returns. We were a small company, we had to make it work.
magicalhippo 8y ago
> IMO most data in DBs has a natural key. Why add a fake key? In our application users generate declarations. On the declarations users need to enter invoice numbers (with amount and date). So a natural key would be a compound key consisting of declaration id and invoice number (which has to be unique per declaration anyway). Declarations also contain goods items, and they need to be linked to an invoice. No problem there, just make a foreign key pointing to the invoice on the declaration. However, users sometime type the wrong thing. They finish up a declaration and while double checking notice that they typed the wrong invoice number. So they want to change the invoice number. With the natural key you then need to update all the goods items linked to it as well. Much easier to just have a "fake" key (autoinc or otherwise) and use that to link the goods items and invoices. Similar issues crop up all over our application, so unless there's a really good reason we avoid natural keys. That said, we don't have a single UUID key. We do not have fully disconnected clients, so we use sequence generators for "non-leaf" keys such as declaration id's.
- tempguy9999 8y ago
  Thanks for a fine example of the discrepancy between my world (the data being canonical, normalised and all-round platonically ideal) and the real world ("but I never meant to do thaaaaat!"). Yup, point taken.

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.

tempguy9999 8y ago
I'm going to clarify something; I said most data has a natural key then when presented with this and other examples I seemed to have backed away from that. I hope this makes sense... In the real world of atoms etc. there's no natural abstraction, including unique IDs (keys). These are all creations of ours. In my line(s) of work, someone's invented and allocated a key for most items by the time it's reached me - people have SSN/NI numbers, planes will have some identifier, ships have IMOs, machine parts have unique part numbers (when combined with their suppliers' ID), geographical areas have postcodes/zipcodes, and all that. So for me, someone's done that work already; a 'natural key' is available and I can rely on it. It's always been there, except for a few things such as addresses. For you, willvarfar, customer numbers have to be created. So I made a wide assertion without thinking it through based just on my experience.

heavenlyblue 8y ago

>> I dealt with a near-billion row DB and shaving a few bytes off a row would matter to us bigly. A billion rows require a gigabyte per each extra byte in a row. That's nothing.

willvarfar 8y ago
I have 200 billion row mysql dbs and have measured the impact of the UUID and ULID keys its forced to deal with. In my high-insert-rate scenarios the extra bytes of these long keys equates to losing about 2000 inserts/sec, just in all the network overhead, compared to if we'd had a 64-bit auto-inc. And then, for storage, I'm using tokudb (when you have tables this big there really isn't any other viable alternative, although myrocks is newer and I haven't evaluated it). Tokudb compresses things really well, and the result is in the tens of terabytes compressed. And the problem with UUIDs and the random part of ULIDs? They are, by definition, uncompressible! Again a lot of that storage footprint is because of the damn things.

tempguy9999 8y ago

Replying to all. @jeltz: > Time sortable UUIDs... Then perhaps store the time as a separate field? Makes them indexable and trivially extracted. > you ensure that inserts are quite often done in sequential order, which is good for btrees. I've just explained why that may not be the case under high insertion rates. This from https://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/ https://www.sqlpassion.at/archive/2014/04/15/an-ever-increas... "With the best practice of an ever-increasing Clustered Key value you have a single hotspot at the end of your Clustered Key. The smaller your records are, the more contention you are introducing here. How can you solve that problem? Easy: spread your INSERT statements across the whole B-Tree structure of the Clustered Index." @willvarfar: > Its hard to find a natural key for something as simple as a customer... OK, completely agree, and you go further - I can't dispute any of that. Only thing I'd say is perhaps don't use a UUID. A 4 byte int will give you plenty of space and use 1/4 of the memory of a GUID. But I may just be splitting hairs. Point accepted! @heavenlyblue: > A billion rows require a gigabyte per each extra byte in a row. That's nothing. I can do the maths, thanks. But if you need real-time responses out a single DB machine serving many users, some heavy, then you have to cache 98% to 100% inside the RAM. Each gig of table read may be an extra 1-5 seconds, depending on disks & setup. Contention increases as more users pile in. Contention increases horribly if queries are greatly slowed by needing to read off the disk. I was fighting for that space to get, and keep, responsiveness. "buy more machines" is a good response but only after you've optimised to the point of minimal returns. We were a small company, we had to make it work.

magicalhippo 8y ago

> IMO most data in DBs has a natural key. Why add a fake key? In our application users generate declarations. On the declarations users need to enter invoice numbers (with amount and date). So a natural key would be a compound key consisting of declaration id and invoice number (which has to be unique per declaration anyway). Declarations also contain goods items, and they need to be linked to an invoice. No problem there, just make a foreign key pointing to the invoice on the declaration. However, users sometime type the wrong thing. They finish up a declaration and while double checking notice that they typed the wrong invoice number. So they want to change the invoice number. With the natural key you then need to update all the goods items linked to it as well. Much easier to just have a "fake" key (autoinc or otherwise) and use that to link the goods items and invoices. Similar issues crop up all over our application, so unless there's a really good reason we avoid natural keys. That said, we don't have a single UUID key. We do not have fully disconnected clients, so we use sequence generators for "non-leaf" keys such as declaration id's.

tempguy9999 8y ago
Thanks for a fine example of the discrepancy between my world (the data being canonical, normalised and all-round platonically ideal) and the real world ("but I never meant to do thaaaaat!"). Yup, point taken.