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

281–290 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#281

Post by x0x0 »

stickfigure wrote: post #233
benterix wrote: post #4

The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.

In Postgres I often like to use a single sequence for everything. It leaks some information yes but in a busy system it tends to be "obscure enough".

It's not leaking that's the concern. It's that not having the names of objects be easily enumerable is a strongly security-enhancing feature of a system.

Yes of course everyone should check and unit test that every object is owned by the user or account loading it, but demanding more sophistication from an attacker than taking "/my_things/23" and loading "/my_things/24" is a big win.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#282

Post by moralestapia »

GuB-42 wrote: post #268
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…

I don't think the timestamped UUIDs are "carrying data", it is just a heuristic to improve lookup performance. If the timestamp is wrong, it will just run as slow as the non-timestamped UUID. If you take the gender example, for 99% of people, it is male/female and it won't change, and you can use that for load balancing. But if later, you found out that the gender is not the one you expect for that bucket, no big dea…

>and you can use that for load balancing

As long as you're not in China or India around specific years ...

GP's point stands strong.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#283

Post by voidnap »

duckerude wrote: post #227
barrkel wrote: post #113

Earlier quoted context omitted.

Uuid v7 just has a bias in its generation; it isn't carrying information. You're not going to try and extract a timestamp from a uuid. Random vs time biased uuids are not a decision to shave off ms that you will regret. Most likely they will be a decision that shaves off seconds (yes, really - especially when you consider locality effects) and you'll regret nothing.

I've worked on a system where ULIDs (not UUIDv7, but similar) were used with a cursor to fetch data in chronological order and then—surprise!—one day records had to be backdated, meaning that either the IDs for those records had to be counterfeited (potentially violating invariants elsewhere) or the fetching had to be made smarter. You can choose to never make use of that property. But it's tempting.

I made a service using something like a 64 bit wide ULID but there was never a presumption that data is be inserted or updated earlier than the most recent record.

If the domain is modeling something like external events (in my case), and that external timestamp is packed into your primary key, and you support receiving events out of chronological order, then it just follows that you might insert stuff ealrier than you latest record.

You're gonna have problems "backdating" if you mix up time of insertion with when the event you model actually ocurred. Like id you treat those as the same thing when they aren't.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#284

Post by [deleted] »

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…

[deleted]

Re: Avoid UUID Version 4 Primary Keys in Postgres

#285

Post by miiiiiike »

The article is muddled, I wish he'd split it into two. One for UUID4 and another for UUID7.

I was using 64-bit snowflake pks (timestamp+sequence+random+datacenter+node) previously and made the switch to UUID7 for sortable, user-facing, pks. I'm more than fine letting the DB handle a 128-bit int vs over a 64-bit int if it means not having make sure that the latest version of my snowflake function has made it to every db or that my snowflake server never hiccups, ever.

Most of the data that's going to be keyed with a uuid7 is getting served straight out of Redis anyway.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#286

Post by kortex »

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.

As long as the key has sufficient entropy (i.e. not monotonic sequential ints), that ensures the keyspace is evenly distributed, correct? So UUID>=v4, ULID, KSUID, possibly snowflake, should be fine for the sake of even distribution of the hashes.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#287

Post by pil0u »

phendrenad2 wrote: post #273

You probably don't want integer primary keys, and you probably don't want UUID primary keys. You probably want something in-between, depending on your use case. UUID is one extreme on this spectrum, which tries to solve all of the problems, including ones you might not have.

What's in-between? I posted the article because I'm in the middle of that choice and wanted to generate discussion/contradiction.

So far, people have talked a lot about UUIDs, so I'm genuinely curious about what's in-between.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#288

Post by nine_k »

bri3d wrote: post #223
nine_k wrote: post #198

Earlier quoted context omitted.

Nobody forces you to use a real Unix timestamp. BTW the original Unix timestamp is 32 bits (expiring in 2038), and now everyone is switching to 64-bit time_t. What 48 bits? All you need is a guaranteed non-decreasing 48-bit number. A clock is one way to generate it, but I don't see why a UUIDv7 would become invalid if your clock is biased, runs too fast, too slow, or whatever. I would not count on the first 48 bits b…

> Nobody forces you to use a real Unix timestamp. Besides the UUIDv7 specification, that is? Otherwise you have some arbitrary kind of UUID. > I would not count on the first 48 bits being a "real" timestamp. I agree; this is the existential hazard under discussion which comes from encoding something that might or might not be data into an opaque identifier. I personally don't agree as dogmatically with the grandparen…

I mean, any 32-bit unsigned integer is a valid Unix timestamp up until 19 January 2038, and, by extension, any u64 is, too, for far longer time.

The only promise of Unix timestamps is that they never go back, always increase. This is a property of a sequence of UUIDs, not any particular instance. At most, one might argue that an "utterly valid" UUIDv7 should not contain a timestamp from far future. But I don't see why it can't be any time in the past, as long as the timestamp part does not decrease.

The timestamp aspect may be a part of an additional interface agreement: e.g. "we guarantee that this value is UUIDv7 with the timestamp in UTC, no more than a second off". But I assume that most sane engineers won't offer such a guarantee. The useful guarantee is the non-decreasing nature of the prefix, which allows for sorting.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#289

Post by guhcampos »

cycomanic wrote: post #271
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…

Like the other poster said, this is a problem with default values not encoding the birthday into the personnummer. I think it also is important to remember the purpose of specific numbers. For instance I would argue a PN without the birthday would be strictly worse. With the current system (I only know the Swedish one, but assume it's the same) I only have to remember a 4 digit (because the number is bdate + unique 4…

It's not that bad. Brazilian CPF are 11 numbers and everyone remembers them. You just get use to it =)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#290

Post by dpark »

konart wrote: post #280
dpark wrote: post #259

Earlier quoted context omitted.

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

You have one ID as a primary key. It is used for building relations in your database. The second ID has nothing to do with internal structure of your data. It is just another field. You can change your structure however you want (or type of your "internal" IDs) and you don't have to worry about an external consumer. They still get their artificial ID.

So what you meant is not to expose the primary key?

That’s a more reasonable statement but I still don’t agree. This feels like one of those “best practices” that people apply without thinking and create pointless complexity.

Don’t expose your primary key if there is a reason to separate your primary key from the externally-exposed key. If your primary key is the form that you want to expose, then you should just expose the primary key. e.g. If your primary key is a UUID, and you create a separate UUID just to expose publicly, you have most likely added useless complexity to your system.

Post reply on HN