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

261–270 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#261

Post by hxtk »

vintermann wrote: post #99
hyperpape wrote: post #94

Earlier quoted context omitted.

Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.

I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to. I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID. Isn't that clear enough?

When I think "premature optimization," I think of things like making a tradeoff in favor of performance without justification. It could be a sacrifice of readability by writing uglier but more optimized code that's difficult to understand, or spending time researching the optimal write pattern for a database that I could spend developing other things.

I don't think I should ignore what I already know and intentionally pessimize the first draft in the name of avoiding premature optimization.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#262

Post by bigstrat2003 »

delichon wrote: post #235
cozyman wrote: post #194

Earlier quoted context omitted.

how does one change their gender surgically?

You can't, but since gender isn't defined by anything physical, there's no need.

That is only true if you're using an extremely idiosyncratic definition of gender. As far as 95% of English speakers are concerned, gender is defined by the body you possess.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#263

Post by demurgos »

mkleczek wrote: post #251
sethhochberg wrote: post #236

Earlier quoted context omitted.

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

Internal means "not exposed outside some boundary". For most people, this boundary encompasses something larger than a single database, and this boundary can change.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#264

Post by can3p »

> For many business apps, they will never reach 2 billion unique values per table, so this will be adequate for their entire life. I’ve also recommended always using bigint/int8 in other contexts.

I'm sure every dba has a war story that starts with similar decision in the past

Re: Avoid UUID Version 4 Primary Keys in Postgres

#265

Post by dpark »

majorchord wrote: post #156
vintermann wrote: post #135

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? I bet people will extract that date and use it, and it's hard to imagine use which wouldn't be abuse. To take the example of a PN/SSN and the usual gender bit: do you really want anyone to be able to tell that you got a new ID at that time? What could you suspect if a person born…

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

That is absolutely not the purpose. The specific purpose of uuidv7 is to optimize for B-Tree characteristics, not so you can craft queries based on the IDs being sequential.

This assumption that you can query across IDs is exactly what is being cautioned against. As soon as you do that, you are talking a dependency on an implementation detail. The contract is that you get a UUID, not that you get 48 bits of timestamp. There are 8 different UUID types and even v7 has more than one variant.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#266

Post by dpark »

mixmastamyk wrote: post #200
miroljub wrote: post #170

Earlier quoted context omitted.

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

That happens, in general. The benefit comes when it’s time to look up by uuid only; the prefix is an index to its disk block location.

> the prefix is an index to its disk block location

What? This is definitely not the case and can’t be because B-tree nodes change while UUIDs do not.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#267

Post by mixmastamyk »

dpark wrote: post #266
mixmastamyk wrote: post #200

Earlier quoted context omitted.

That happens, in general. The benefit comes when it’s time to look up by uuid only; the prefix is an index to its disk block location.

> the prefix is an index to its disk block location What? This is definitely not the case and can’t be because B-tree nodes change while UUIDs do not.

I didn’t mean that literally, but no longer editable. Was supposed to have “like” etc in there.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#268

Post by GuB-42 »

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 deal, it will cause a branch misprediction, but instead of happening 50% of the times when you use a random value, it will only happen 1% of the times, significant speedup with no loss in functionality.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#269

Post by 0x457 »

bigmadshoe wrote: post #234
tmountain wrote: post #42

Earlier quoted context omitted.

Sticking with sequences and other integer types will cause problems if you need to shard later.

I’m really no expert on sharding but if you’re using increasing ints why can’t you just shard on (id % n) or something?

Because then you run into an issue when you 'n' changes. Plus, where are you increasing it on? This will require a single fault-tolerant ticker (some do that btw).

Once you encode shard number into ID, you got:

- instantly* know which shard to query

- each shard has its own ticker

* programatically, maybe visually as well depending on implementation

I had IDs that encode: entity type (IIRC 4 bit?), timestamp, shard, sequence per shard. We even had a admin page wher you can paste ID and it will decode it.

id % n is fine for cache because you can just throw whole thing away and repopulate or when 'n' never changes, but it usually does.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#270

Post by Ukv »

mkleczek wrote: post #248
Ukv wrote: post #240

Earlier quoted context omitted.

UUIDs are good for creating entries concurrently where coordinating between distributed systems may be difficult. May also be that you don't want to leak information like how many orders are being made, as could be inferred from a `/fetch_order?id=123` API with sequential IDs. Sequential primary keys are still commonly used though - it's a scenario-dependant trade-off.

If you expose the identifier outside the database, it is no longer "internal".

Given the chain was:

> > Using a random UUID as primary key does not mean users have to memorize that UUID. [...]

> So what is such an identifier for? [...] Why bother with UUID at all then for internal identifiers?

The context, that you're questioning what they're useful for if not for use by the user, suggests that "internal" means the complement. That is, IDs used by your company and software, and maybe even API calls the website makes, but not anything the user has to know.

Otherwise, if "internal" was intended to mean something stricter (only used by a single non-distributed database, not accessed by any applications using the database, and never will be in the future), then my response is just that many IDs are neither internal in this sense nor intended to be memorized/saved by the user.

Post reply on HN