Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL and UUID as Primary Key

maciejwalkowiak.com

Post reply on HN
Print view View original

261–270 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#261

Post by wibblewobble125 »

sgarland wrote: post #248
matharmin wrote: post #214

Earlier quoted context omitted.

Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.

If your app isn’t working with billions of rows, you really don’t need to be worrying about distributed anything. Even then, I’d be suspicious. I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and tran…

Sometimes distribution is not for performance but tenant isolation for regulatory or general isolation purposes. I work in such an industry.

Re: PostgreSQL and UUID as Primary Key

#262

Post by groestl »

branko_d wrote: post #226

I'm surprised author didn't mention foreign keys: since primary key is often referenced by foreign keys, then if you have a "fat" PK all your FKs will also be "fat". This can be solved by using the UUID as an alternate key and the regular integer as a primary key in the "top" table, and then referencing that integer from all the "child" tables. Obviously, this has ramifications for data write perf, but may well be wo…

If the UUID is used for external representation, now you need to join a row to get it.

Re: PostgreSQL and UUID as Primary Key

#263

Post by sgarland »

riku_iki wrote: post #169
arp242 wrote: post #154

Earlier quoted context omitted.

With multiple tables and the same IDs being references more than once, this kind of stuff can really add up. For example I have a table that has about a billion rows and uses bigserial, but that table references about 6 other much smaller tables that use serial. I'm saving 48 bytes per row, or 90GB in total. It's a fairly significant save, and that's just on this one table: I have a bunch of tables like this. If I ha…

> I'm saving 48 bytes per row you saving 24 bytes per row: downsizing 6 columns from 8 bytes to 4, which is fraction of your table size. If your system is sensitive to such change, you likely should optimize something else. > Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems. I think you cherry picked some fiction…

> If your system is sensitive to such change, you likely should optimize something else.

This isn’t even optimization, it’s just understanding your tools and needs. It’s akin to profiling your app under load, seeing that at worst it needs 1 GiB of RAM allocated, and then giving it 8 EiB just in case.

By all means, if you can reasonably predict that a given table will near or pass 2^31 rows in the near future, just set it to BIGINT now. But as has been pointed out, most apps are never, ever going to have close to 2 billion users, or for that matter 2 billion anything. The only thing I can think of that might reasonably run up against that would be invoices for large scales, or object metadata for something like Notion.

Re: PostgreSQL and UUID as Primary Key

#264

Post by groestl »

netcraft wrote: post #17

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 i…

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

Re: PostgreSQL and UUID as Primary Key

#265

Post by hu3 »

blits wrote: post #208

You might also be interested in TypeID, which is extension of UUIDv7 and also sortable and unlike UUIDv7 more human readable. It is inspired by Stripe IDs and looks like that: user_2x4y6z8a0b1c2d3e4f5g6h7j8k I've recently built a postgres extension for it that allows to use it like UUID: https://github.com/blitss/typeid-postgres

love the underscore instead of hyphen. easy to double click for selecting text.

Re: PostgreSQL and UUID as Primary Key

#266

Post by groestl »

arp242 wrote: post #158
netcraft wrote: post #17

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 i…

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 absolut…

> 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

#267

Post by sgarland »

arp242 wrote: post #172
riku_iki wrote: post #169

Earlier quoted context omitted.

> I'm saving 48 bytes per row you saving 24 bytes per row: downsizing 6 columns from 8 bytes to 4, which is fraction of your table size. If your system is sensitive to such change, you likely should optimize something else. > Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems. I think you cherry picked some fiction…

> cherry picked some fictional example What an incredibly rude and dismissive accusation. Here's my table: https://github.com/arp242/goatcounter/blob/master/db/schema.... – number of IDs is actually 7, not 6. I can give a lot more details and context on all of that and why it works the way it works and the savings are certainly not insignificant and theoretical, but save me real money in server costs every month. But…

I’d just like to congratulate you on perhaps the nicest “fuck off” I’ve ever read.

Unrelated, I quite enjoyed reading your blog posts. Cheers!

Re: PostgreSQL and UUID as Primary Key

#268

Post by groestl »

kevincox wrote: post #106
davisp wrote: post #50

Earlier quoted context omitted.

Worrying about UUID collisions is like worrying about being hit in the head by a meteor. Sure, its technically possible, but it happens so rarely that worrying about a collision as a performance concern is just a misunderstanding on how UUIDs work. And, it’s so random that if you ever do see a collision you should immediately start looking for a compromised system or bug. This is basically how GitHub discovered the O…

You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).

> UUID generated client side and basically upserted it

Read and take notes. This is crazy in untrusted environments.

Re: PostgreSQL and UUID as Primary Key

#269

Post by x3al »

kroolik wrote: post #229
groestl wrote: post #228

Earlier quoted context omitted.

Ha? Please elaborate.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

Strictly monotonic fields are quite expensive and the bigserial PK alone won't give you that.

Re: PostgreSQL and UUID as Primary Key

#270

Post by sgarland »

wibblewobble125 wrote: post #261
sgarland wrote: post #248

Earlier quoted context omitted.

If your app isn’t working with billions of rows, you really don’t need to be worrying about distributed anything. Even then, I’d be suspicious. I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and tran…

Sometimes distribution is not for performance but tenant isolation for regulatory or general isolation purposes. I work in such an industry.

Fair point. You can still use monotonic IDs with these, via either interleaving chunks to each DB, or with a central server that allocates them – the latter approach is how Slack handles it, for example.
Post reply on HN