4 ms·

A UUID primary key has to live in every index in the table and every foreign key that references it. We have a table with a UUID as its primary and it alone co

A UUID primary key has to live in every index in the table and every foreign key that references it. We have a table with a UUID as its primary and it alone consumes 22GB, then a index reference it, so now another 22GB... That one primary key uses over 100GB of storage, a developer recently went to add another table that reference it and we had to decide whether we were okay taking another hit. If we used your example of using a char type (we use blob) it would be double the size... It doesn't sound like much, but in a database with all primary keys being UUIDs you are going to inflate the size of your DB quickly, or have to forego using foreign keys. I imagine if we used only UUIDs we would have to double or triple our database infrastructure. Additionally, we would be forced to introduce partitioning sooner. Now we have a tech debt ticket to add an auto increment to that table so we can reclaim disk / memory.

bogomipz 9y ago
>"We have a table with a UUID as its primary and it alone consumes 22GB, then a index reference it, so now another 22GB... That one primary key uses over 100GB of storage," I'm not following your math here. Can you elaborate? I see 22 and 22 and the 100.
- JshWright 9y ago
  I assume they have more than one reference to that table...
gruez 9y ago
>I imagine if we used only UUIDs we would have to double or triple our database infrastructure I think you're greatly exaggerating how big UUIDs are compared to ints. The upper bound is 4x, and that's if your table is keys only, with no other data. That falls down to 2x if you're using bigints. In a typical user table with a bunch of other fields, like a username + password hash, the storage used by the keys is quickly dwarfed by the storage used for "other data".
dalore 9y ago
I would argue that's a reasonable hit for the gains. I would never add a tech debit ticket to change just to reclaim disk / memory, that's an anti pattern. Memory and disk space is cheap. Also UUIDs are just 128bit integers internally. My example clearly points out that most modern databases have a native uuid type (and if not use use a 128 bit integer). Using a char type to store UUIDs is very wasteful and no wonder your indexes are so huge. The tech debt ticket you really have is to convert type of uuid column from char/blob to native. Note also that if you need to expose your data, you can either expose the auto incrementing primary key (which leaks data, in that other people can work out growth rates of various models, like how England worked out how many German tanks were being built based on a auto incrementing serial number), or you create a another column (as someone else suggested) which is a uuid. Which you would need to index anyway. If index size is an issue then you can just integers which are randomly generated. You can calculate your collision chance based on how large your integer is and how often you create. If you happen to pick 128bit integers, congratulations, you're using UUIDs.
- bearjaws 9y ago
  >Using a char type to store UUIDs is very wasteful and no wonder your indexes are so huge. That was a hypothetical, its 22GiB using BLOB storage. >Note also that if you need to expose your data, you can either expose the auto incrementing primary key (which leaks data, in that other people can work out growth rates of various models, like how England worked out how many German tanks were being built based on a auto incrementing serial number), or you create a another column (as someone else suggested) which is a uuid. Which you would need to index anyway. I state that we use the auto-increment inside of the application, everything exposed by the API is a UUID. While we lose 32 bits of space per row, just one foreign key or index saves enough space to justify it. Best of both worlds. >Memory and disk space is cheap. Not when you need to move from multiple r4.8XL to r4.16XLs (think multi-az, multi region). It would work out to around double the price, we can hire a developer for the cost of hosting that infrastructure. Even when your business has the money, you don't want to be the guy telling them to drop another $24k a month on hosting costs because we didn't think one year ahead. This of course ignores all the issues that come with replication and partitioning, multi-master etc.

bogomipz 9y ago

>"We have a table with a UUID as its primary and it alone consumes 22GB, then a index reference it, so now another 22GB... That one primary key uses over 100GB of storage," I'm not following your math here. Can you elaborate? I see 22 and 22 and the 100.

JshWright 9y ago
I assume they have more than one reference to that table...

gruez 9y ago

>I imagine if we used only UUIDs we would have to double or triple our database infrastructure I think you're greatly exaggerating how big UUIDs are compared to ints. The upper bound is 4x, and that's if your table is keys only, with no other data. That falls down to 2x if you're using bigints. In a typical user table with a bunch of other fields, like a username + password hash, the storage used by the keys is quickly dwarfed by the storage used for "other data".

dalore 9y ago

I would argue that's a reasonable hit for the gains. I would never add a tech debit ticket to change just to reclaim disk / memory, that's an anti pattern. Memory and disk space is cheap. Also UUIDs are just 128bit integers internally. My example clearly points out that most modern databases have a native uuid type (and if not use use a 128 bit integer). Using a char type to store UUIDs is very wasteful and no wonder your indexes are so huge. The tech debt ticket you really have is to convert type of uuid column from char/blob to native. Note also that if you need to expose your data, you can either expose the auto incrementing primary key (which leaks data, in that other people can work out growth rates of various models, like how England worked out how many German tanks were being built based on a auto incrementing serial number), or you create a another column (as someone else suggested) which is a uuid. Which you would need to index anyway. If index size is an issue then you can just integers which are randomly generated. You can calculate your collision chance based on how large your integer is and how often you create. If you happen to pick 128bit integers, congratulations, you're using UUIDs.

bearjaws 9y ago
>Using a char type to store UUIDs is very wasteful and no wonder your indexes are so huge. That was a hypothetical, its 22GiB using BLOB storage. >Note also that if you need to expose your data, you can either expose the auto incrementing primary key (which leaks data, in that other people can work out growth rates of various models, like how England worked out how many German tanks were being built based on a auto incrementing serial number), or you create a another column (as someone else suggested) which is a uuid. Which you would need to index anyway. I state that we use the auto-increment inside of the application, everything exposed by the API is a UUID. While we lose 32 bits of space per row, just one foreign key or index saves enough space to justify it. Best of both worlds. >Memory and disk space is cheap. Not when you need to move from multiple r4.8XL to r4.16XLs (think multi-az, multi region). It would work out to around double the price, we can hire a developer for the cost of hosting that infrastructure. Even when your business has the money, you don't want to be the guy telling them to drop another $24k a month on hosting costs because we didn't think one year ahead. This of course ignores all the issues that come with replication and partitioning, multi-master etc.