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

Avoid UUID Version 4 Primary Keys in Postgres

andyatkinson.com

Post reply on HN
Print view View original

201–210 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#201

Post by mexicocitinluez »

sgarland wrote: post #179
mexicocitinluez wrote: post #163

Earlier quoted context omitted.

Why would generating a PK ahead of time cause referential integrity violations? Super curious to find out.

The implication is that you need to know the PK ahead of time so that you can insert it into other tables which reference it as an FK without waiting for it to be returned, which further implies that you don’t have FK constraints, because the DB would disallow this. Tbf in Postgres, you can declare FKs to be deferrable, so their existence is checked at transaction commit, rather than at insertion time. If you don’t h…

> Tbf in Postgres, you can declare FKs to be deferrable, so their existence is checked at transaction commit, rather than at insertion time.h further implies that you don’t have FK constraints, because the DB would disallow this.

I'm using EF core which hooks up these relationships and allows me to persist them in a single transaction using MSSQL server.

> If you don’t have the DB enforcing referential integrity

I'm building an electronic medical system. I'm well aware of the benefits of referential integrity.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#202

Post by jakeydus »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

I think they address this in the article when they say that this advice is specific to monolithic applications, but I may be misremembering (I skimmed).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#203

Post by majorchord »

miroljub wrote: post #170
majorchord wrote: post #156

Earlier quoted context omitted.

> You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing? Pretty sure sorting and filtering them by date/time range in a database is the purpose.

If you need sorting and filtering by date, just add a timestamp to your table instead of misusing an Id column for that.

> just

It is easy to have strong opinions about things you are sheltered from the consequences of.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#204

Post by vbezhenar »

torginus wrote: post #61
kreetx wrote: post #11

Earlier quoted context omitted.

Using an UUIDv4 as primary key is a trade-off: you use it when you need to generate unique keys in a distributed manner. Yes, these are not datetime ordered and yes, they take 128 bits of space. If you can't live with this, then sure, you need to consider alternatives. I wonder if "Avoid UUIDv4 Primary Keys" is a rule of thumb though.

I do not understand why 128 bits is considered too big - you clearly can't have less, as on 64 bits the collision probability on real world workloads is just too high, for all but the smallest databases. Auto-incrementing keys can work, but what happens when you run out of integers? Also, distributed dbs probably make this hard, and they can't generate a key on client. There must be something in Postgres that wants t…

You won't run out of 64-bit integer. IMO, 64-bit integer (and even less for some tables that's not expected to grow much) it the best approach for internal database ID. If you want to expose ID, it might make sense to introduce second UUID for selected tables, if you want to hide internal ID.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#205

Post by jakeydus »

hippo22 wrote: post #192

The author should include benchmarks otherwise, saying that UUIDs “increase latency” is meaningless. For instance, how much longer does it take to insert a UUID vs. an integer? How much longer does scanning an index take?

The author doesn't reference any tests that they themselves ran, but they did link a cybertec article [0] with some benchmarks.

[0] https://www.cybertec-postgresql.com/en/unexpected-downsides-...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#206

Post by lukeschlather »

sgarland wrote: post #144
vintermann wrote: post #65

A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…

> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseco…

> Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates.

I am not a cryptographer, but I would want his recommendation reviewed by a cryptographer. And then I would have to implement it. UUIDs have been extensively reviewed by cryptographers, I have a variety of excellent implementations I can use, I know they solve the problem well. I know they can cause performance issues; they're a security feature that is easy to implement, and I can deal with the performance issues if and when they crop up. (Which, in my experience, it's unusual. Even at a large company, most databases I encounter do not have enough data. I will err on the side of security until it becomes a problem, which is a good problem to have.)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#207

Post by andy_ppp »

jakeydus wrote: post #202
stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

I think they address this in the article when they say that this advice is specific to monolithic applications, but I may be misremembering (I skimmed).

Are you saying a monolith cannot use a distributed database?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#208

Post by jakeydus »

reffaelwallen wrote: post #160
aynyc wrote: post #158

I've seen this type of advice a few times now. Now I'm not a database expert by any stretch of imagination, but I have yet to see UUID as primary key in any of the systems I've touched. Are there valid reasons to use UUID (assuming correctly) for primary key? I know systems have incorrectly expose primary key to the public, but assuming that's not the concern. Why use UUID over big-int?

At my company we only use UUIDs as PKs. Main reason I use it is the German Tank problem: https://en.wikipedia.org/wiki/German_tank_problem (tl;dr; prevent someone from counting how many records you have in that table)

I'm new to the security side of things; I can understand that leaking any information about the backend is no bueno, but why specifically is table size an issue?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#209

Post by spoiler »

benterix wrote: post #189
vintermann wrote: post #65

A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…

Your comment is valid but is not related to the article.

More broadly, this is the ages old surrogate vs natural key discussion, but yes the comment completely misses the point of the article. I can only assume they didn't read it in full!

Re: Avoid UUID Version 4 Primary Keys in Postgres

#210

Post by jakeydus »

andy_ppp wrote: post #207
jakeydus wrote: post #202

Earlier quoted context omitted.

I think they address this in the article when they say that this advice is specific to monolithic applications, but I may be misremembering (I skimmed).

Are you saying a monolith cannot use a distributed database?

I'm not making any claims at all, I was just adding context from my recollection of the article that appeared to be missing from the conversation.

Edit: What the article said: > The kinds of web applications I’m thinking of with this post are monolithic web apps, with Postgres as their primary OLTP database.

So you are correct that this does not disqualify distributed databases.

Post reply on HN