News for hackers. Stuff that matters.
Re:PostgreSQL and UUID as Primary Key by netcraft on Friday July 05, 2024 @05:10PM (#40886045)
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.
Re:PostgreSQL and UUID as Primary Key by eerikkivistik on Friday July 05, 2024 @05:18PM (#40886097)
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.

Re:PostgreSQL and UUID as Primary Key by paulddraper on Friday July 05, 2024 @05:43PM (#40886296)
I haven't maintained any sizable database system without any issues, least of all performance ones.

I call BS.

Re:PostgreSQL and UUID as Primary Key by netcraft on Friday July 05, 2024 @07:20PM (#40886937)
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
Re:PostgreSQL and UUID as Primary Key by paulddraper on Friday July 05, 2024 @09:09PM (#40887456)
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.

Re:PostgreSQL and UUID as Primary Key by tomnipotent on Friday July 05, 2024 @06:24PM (#40886595)
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.

Re:PostgreSQL and UUID as Primary Key by jrochkind1 on Friday July 05, 2024 @09:30PM (#40887545)
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?

Re:PostgreSQL and UUID as Primary Key by netcraft on Saturday July 06, 2024 @12:41PM (#40891514)
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

Re:PostgreSQL and UUID as Primary Key by sgarland on Friday July 05, 2024 @10:05PM (#40887703)
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.

Re:PostgreSQL and UUID as Primary Key by mewpmewp2 on Saturday July 06, 2024 @12:29AM (#40888171)
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.

Re:PostgreSQL and UUID as Primary Key by sgarland on Saturday July 06, 2024 @09:13AM (#40890264)
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.
Re:PostgreSQL and UUID as Primary Key by mewpmewp2 on Saturday July 06, 2024 @04:12PM (#40892894)
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.

Re:PostgreSQL and UUID as Primary Key by lazide on Saturday July 06, 2024 @09:54PM (#40894699)
Auto increment ID’s also leak information.

Random UUID’s shouldn’t, but will impact performance, which in most cases will have more concrete impact on the business.

There is no such thing as a free lunch.

Re:PostgreSQL and UUID as Primary Key by therealdrag0 on Friday July 05, 2024 @11:02PM (#40887894)
Million records isn’t very many. But ya we have tables with billions of records and v4 UUIDs hasn’t been a blocker.
Re:PostgreSQL and UUID as Primary Key by arp242 on Saturday July 06, 2024 @12:20AM (#40888143)
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.

Re:PostgreSQL and UUID as Primary Key by groestl on Saturday July 06, 2024 @10:15AM (#40890607)
> 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.

Re:PostgreSQL and UUID as Primary Key by arp242 on Saturday July 06, 2024 @04:47PM (#40893142)
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.

Re:PostgreSQL and UUID as Primary Key by groestl on Saturday July 06, 2024 @10:13AM (#40890596)
> 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.