4 ms·

Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - T

Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But instead of worrying about this, spend your energy on a million other things first and then celebrate when UUIDs become your bottleneck.

eerikkivistik 2y ago
I was gonna say... When your system is large enough to run into this specific performance bottleneck, pop a bottle and celebrate, you are making enough money to solve that problem. While knowing this information is useful, most services fail in different domains and problems way before you reach that point. I'm not sure people really comprehend how hard you can hit a single machine before you need to distribute a workload.
paulddraper 2y ago
I haven't maintained any sizable database system without any issues, least of all performance ones. I call BS.
- tomnipotent 2y ago
  This gave me a good chuckle, and has generally been my experience. Systems grow often in unpredictable or unintuitive ways. You can pay the cost for something upfront, and the cost of maintaining it, and in the long term paid too much for something you didn't actually need. Alternatively you can wait to pay it until you're certain you need it but the work involved has become much more significant, in which it can cost more than it would have to have built and maintained it from the beginning. Compounding the issue is the build-up-front scenario costs fade with time and you don't really think about them, but build-when-you-need-it always creates a stir even if the costs are less overall than build-up-front. Either way something will go wrong no matter how many times you predict where the cards will fall.
- netcraft 2y ago
  lol, I of course don't mean that I have no issues, performance or otherwise. Only that I have never had an issue with UUIDs in postgres
  - paulddraper 2y ago
    The "issue" is that you're doubling the size of your identifiers, and increasing memory usage of indexes. And memory is the key factor of any sizable database.
jrochkind1 2y ago
Thanks, good to hear. If you are using PG, simply using it's native UUID type instead of char(36) seems like a no-opportunity-cost obvious optimization choice at least though, if you have a choice?
- netcraft 2y ago
  yes absolutely- that system was built a long time ago before the uuid type - you should def not store things in chars (and probably shouldnt use char at all, use text) was saying that even with that poor implementation we still were not having issues using uuids
sgarland 2y ago
Milliseconds matter, especially when they compound. If your DB can return a SELECT in sub-msec time (to its network boundary, obviously) instead of 10 msec, that adds up when a given page might require a dozen or more trips. Also, I have never seen devs (PMs, really – devs are the unfortunate souls slogging through tickets) suddenly care about performance-related tech debt. Why would they, when you can just click a button and double your DB’s hardware? Boom, problem solved… until it isn’t. Eventually, you run out of scaling, and since you probably don’t have a DBA/DBRE (else they’d have been screaming at you for months), it’s going to be extremely painful to solve now. The bare minimum I’m asking – as a DBRE – is to use UUIDv7 and store them in Postgres’ native UUID type. That’s all. That’s an incredibly small amount of effort to put forth.
- mewpmewp2 2y ago
  But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default. I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.
  - sgarland 2y ago
    See other reply; I don’t believe that exposing this is as big an issue as people think. But even with that, there’s also no reason to do so. Internal ID friendly to the DB and eternal, random ID that does get exposed is a common practice. Or use JWE/JWT, and never show either.
    - mewpmewp2 2y ago
      I don't know. Is it not? I frequently check out of curiosity when I'm buying something the order numbers and similar things that might of interest, if I notice integers. I imagine if you become a public company that would be very sensitive information in terms of the company is doing, and there would be strong push to check for that data, to get an advantage in the stock market. It doesn't seem right to me to expose with such ease how many sales you are doing. It's definitely not intentional to expose it.
therealdrag0 2y ago
Million records isn’t very many. But ya we have tables with billions of records and v4 UUIDs hasn’t been a blocker.
arp242 2y ago
A million rows is quite small. A string will use 36 bytes per row. bigserial will use 8 bytes per row. At 4 billion rows that's about 100G. Now imagine a row with 3 foreign keys to other tables with string UUIDs and you're wasting 300G (vs UUID type) or 400G (vs. bigserial), for no good reason. And doing things like "where id = ?" will be slower. You will be able to keep fewer rows cached in memory. Etc. It's absolutely not a bikeshed. And migrating all of this later on can be a right pain so it's worth getting it right up-frong. It's also not more effort to do things right: usually it's exactly the same effort as doing it wrong.
- groestl 2y ago
  > And migrating all of this later on can be a right pain so it's worth getting it right up-frong I've never had to move from uuids to integers. I've had to move from integers to uuids plenty of times though.
  - arp242 2y ago
    I've migrated tables to more compact formats. Inefficient storage is the sort of of thing that works fine for a lot of things, right up to the point where you're running out of disk space or memory and it's no longer fine. I'm not against UUIDs nor saying you should optimize everything, I'm just saying you should think about things, and that thinking about things really isn't that time-consuming or that much effort.
groestl 2y ago
> When UUIDs become your bottleneck. When UUIDs become your bottleneck you'll be celebrating for picking UUIDs, because now you can move to a distributed architecture and not worry about IDs.

eerikkivistik 2y ago

I was gonna say... When your system is large enough to run into this specific performance bottleneck, pop a bottle and celebrate, you are making enough money to solve that problem. While knowing this information is useful, most services fail in different domains and problems way before you reach that point. I'm not sure people really comprehend how hard you can hit a single machine before you need to distribute a workload.

paulddraper 2y ago

I haven't maintained any sizable database system without any issues, least of all performance ones. I call BS.

tomnipotent 2y ago
This gave me a good chuckle, and has generally been my experience. Systems grow often in unpredictable or unintuitive ways. You can pay the cost for something upfront, and the cost of maintaining it, and in the long term paid too much for something you didn't actually need. Alternatively you can wait to pay it until you're certain you need it but the work involved has become much more significant, in which it can cost more than it would have to have built and maintained it from the beginning. Compounding the issue is the build-up-front scenario costs fade with time and you don't really think about them, but build-when-you-need-it always creates a stir even if the costs are less overall than build-up-front. Either way something will go wrong no matter how many times you predict where the cards will fall.
netcraft 2y ago
lol, I of course don't mean that I have no issues, performance or otherwise. Only that I have never had an issue with UUIDs in postgres
- paulddraper 2y ago
  The "issue" is that you're doubling the size of your identifiers, and increasing memory usage of indexes. And memory is the key factor of any sizable database.

jrochkind1 2y ago

Thanks, good to hear. If you are using PG, simply using it's native UUID type instead of char(36) seems like a no-opportunity-cost obvious optimization choice at least though, if you have a choice?

netcraft 2y ago
yes absolutely- that system was built a long time ago before the uuid type - you should def not store things in chars (and probably shouldnt use char at all, use text) was saying that even with that poor implementation we still were not having issues using uuids

sgarland 2y ago

Milliseconds matter, especially when they compound. If your DB can return a SELECT in sub-msec time (to its network boundary, obviously) instead of 10 msec, that adds up when a given page might require a dozen or more trips. Also, I have never seen devs (PMs, really – devs are the unfortunate souls slogging through tickets) suddenly care about performance-related tech debt. Why would they, when you can just click a button and double your DB’s hardware? Boom, problem solved… until it isn’t. Eventually, you run out of scaling, and since you probably don’t have a DBA/DBRE (else they’d have been screaming at you for months), it’s going to be extremely painful to solve now. The bare minimum I’m asking – as a DBRE – is to use UUIDv7 and store them in Postgres’ native UUID type. That’s all. That’s an incredibly small amount of effort to put forth.

mewpmewp2 2y ago
But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default. I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.
- sgarland 2y ago
  See other reply; I don’t believe that exposing this is as big an issue as people think. But even with that, there’s also no reason to do so. Internal ID friendly to the DB and eternal, random ID that does get exposed is a common practice. Or use JWE/JWT, and never show either.
  - mewpmewp2 2y ago
    I don't know. Is it not? I frequently check out of curiosity when I'm buying something the order numbers and similar things that might of interest, if I notice integers. I imagine if you become a public company that would be very sensitive information in terms of the company is doing, and there would be strong push to check for that data, to get an advantage in the stock market. It doesn't seem right to me to expose with such ease how many sales you are doing. It's definitely not intentional to expose it.

therealdrag0 2y ago

Million records isn’t very many. But ya we have tables with billions of records and v4 UUIDs hasn’t been a blocker.

arp242 2y ago

A million rows is quite small. A string will use 36 bytes per row. bigserial will use 8 bytes per row. At 4 billion rows that's about 100G. Now imagine a row with 3 foreign keys to other tables with string UUIDs and you're wasting 300G (vs UUID type) or 400G (vs. bigserial), for no good reason. And doing things like "where id = ?" will be slower. You will be able to keep fewer rows cached in memory. Etc. It's absolutely not a bikeshed. And migrating all of this later on can be a right pain so it's worth getting it right up-frong. It's also not more effort to do things right: usually it's exactly the same effort as doing it wrong.

groestl 2y ago
> And migrating all of this later on can be a right pain so it's worth getting it right up-frong I've never had to move from uuids to integers. I've had to move from integers to uuids plenty of times though.
- arp242 2y ago
  I've migrated tables to more compact formats. Inefficient storage is the sort of of thing that works fine for a lot of things, right up to the point where you're running out of disk space or memory and it's no longer fine. I'm not against UUIDs nor saying you should optimize everything, I'm just saying you should think about things, and that thinking about things really isn't that time-consuming or that much effort.

groestl 2y ago

> When UUIDs become your bottleneck. When UUIDs become your bottleneck you'll be celebrating for picking UUIDs, because now you can move to a distributed architecture and not worry about IDs.