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

251–260 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#251

Post by mkleczek »

sethhochberg wrote: post #236
mkleczek wrote: post #216

Earlier quoted context omitted.

> Using a random UUID as primary key does not mean users have to memorize that UUID. In fact in most cases I don't think there's much reason for it to even be exposed to the user at all. So what is such an identifier for? Is it only for some technical purposes (like replication etc.)? Why bother with UUID at all then for internal identifiers? Sequence number should be enough.

"Internal" is a blurry boundary, though - you pick integer sequence numbers and then years on an API gets bolted on to your purely internal database and now your system is vulnerable to enumeration attacks. Does a vendor system where you reference some of your internal data count as "internal"? Is UID 1 the system user that was originally used to provision the system? Better try and attack that one specifically... th…

> "Internal" is a blurry boundary, though

Not for me :)

"Internal" means "not exposed outside the database" (that includes applications and any other external systems)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#252

Post by whateveracct »

Sometimes its nice for your PK to be uniformly distributed. As a reader, even if it hurts as a writer. For instance, you can easily shard queries and workloads.

> the impact to inserts and retrieval of individual items or ranges of values from the index.

Classic OLTP vs OLAP.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#253

Post by ezekg »

thraxil wrote: post #118
socketcluster wrote: post #18

My advice is: Avoid Blanket Statements About Any Technology. I'm tired of midwit arguments like "Tech X is N% faster than tech Y at performing operation Z. Since your system (sometimes) performs operation Z, it implies that Tech X is the only logical choice in all situations!" It's an infuriatingly silly argument because operation Z may only represent about 10% of the total CPU usage of the whole system (averaged out…

Yep. We have tables that use UUIDv4 that have 60M+ rows and don't have any performance problems with them. Would some queries be faster using something else? Probably, but again, for us it's not close to being a bottleneck. If it becomes a problem at 600M or 6B rows, we'll deal with it then. We'll probably switch to UUIDv7 at some point, but it's not a priority and we'll do some tests on our data first. Does my exper…

I have tables that have billions of rows that use UUIDv4 primary keys and I haven't encountered any issues either. I do use UUIDv7 for write-heavy tables, but even then, I got a way bigger performance boost from batching inserts than switching from UUIDv4 to UUIDv7. Issue is way overblown.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#255

Post by stickfigure »

nutjob2 wrote: post #242
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.

> You want to distribute load across the keyspace so you avoid hot shards. This is just another case of keys containing information and is not smart. The obvious solution is to have a field that drives distribution, allowing rebalancing or whatever.

This is something you should discuss with the developers at Cockroach Labs, Google Cloud, et al.

As a consumer of these databases we're stuck with them as designed, which means we have to worry about key distribution.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#256

Post by konart »

vintermann wrote: post #213
spoiler wrote: post #209

Earlier quoted context omitted.

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!

The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. A running number also carries data. Before you know it, someone's relying on the ordering or counting on there not being gaps - or counting the gaps to figure out something they shouldn't.

>Before you know it, someone's relying

Do not expose your internal IDs. As simple as that.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#257

Post by kunley »

socketcluster wrote: post #136
kunley wrote: post #123

Earlier quoted context omitted.

Ehm.. so you're saying that INSERT ... RETURNING id is not atomic from the client's pov because something terrible could happen just when client is receiving the answer inside its SQL driver?

I'm actually more thinking about the client sitting on the front-end like a single page app. Network instability could cause the response to not reach the front-end after a successful insert. This wouldn't be extremely common but would definitely be a problem for you as the database admin if you have above a certain number of users. I've seen this issue on live production systems and the root cause of duplicate recor…

OK got it. I was thinking about SQL client, not about client of a REST service. With that distinction in mind, the reasoning makes sense; thank you.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#258

Post by mzi »

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…

> Even if nothing changes, you can run into trouble. Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right?

I guess that Norway has solved it in the same or similar way as Sweden? So a person is identified by the PNR and for those systems that need to track a person over several PNR (government agencies) use PRI. And a PRI is just the first PNR assigned to a person with a 1 inserted in the middle. If that PRI is occupied, use a 2,and so on.

PRI could of course have been a UUID instead.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#259

Post by dpark »

konart wrote: post #256
vintermann wrote: post #213

Earlier quoted context omitted.

The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. A running number also carries data. Before you know it, someone's relying on the ordering or counting on there not being gaps - or counting the gaps to figure out something they shouldn't.

>Before you know it, someone's relying Do not expose your internal IDs. As simple as that.

This came up in the last two threads I read about uuidv7.

This is simply not a meaningful statement. Any ID you expose externally is also an internal ID. Any ID you do not expose is internal-only.

If you expose data in a repeatable way, you still have to choose what IDs to expose, whether that’s the primary key or a secondary key. (In some cases you can avoid exposing keys at all, but those are narrow cases.)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#260

Post by brettgriffin »

mkleczek wrote: post #89
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…

This is actually a very deep and interesting topic. Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. So, what happens next is that the real world tries to adjust and the "data-less" identifier becomes a real world artifact. The situation becomes the same but…

> Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place.

The surrogate key's purpose isn't to directly store the natural key's information, rather, it's to provide an index to it.

> The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change.

There isn't 'another' - there's just one. The surrogate key. The other pieces of information you're describing are not the means of indexing the data. They are the pieces of data you wish to retrieve.

Post reply on HN