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

141–150 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#141

Post by wongarsu »

zwnow wrote: post #117
sbuttgereit wrote: post #73

Earlier quoted context omitted.

OK... but that concern seems a bit artificial.. if bigints are appropriate: use them. If the table won't get to bigint sizes: don't. I've even used smallint for some tables I knew were going to be very limited in size. But I wouldn't worry about smallint's very limited number of values for those tables that required a larger size for more records: I'd just use int or bigint for those other tables as appropriate. The…

Idk I use whatever scales best and that would be an close to infinite scaling key. The performance compromise is probably zeroed out once you have to adapt ur database to a different one supporting the current scale of the product. Thats for software that has to scale. Whole different story for stuff that doesnt have to grow obviously. I am in the UUID camp too but I dont care whether its v4 or v7.

It's not like there are dozens of options and you constantly have to switch. You just have to estimate if at maximum growth your table will have 32 thousand, 2 billion or 9 quintillion entries. And even if you go with 9 quintillion for all cases you still use half the space of a UUID

UUIDv4 are great for when you add sharding, and UUIDs in general prevent issues with mixing ids from different tables. But if you reach the kind of scale where you have 2 billion of anything UUIDs are probably not the best choice either

Re: Avoid UUID Version 4 Primary Keys in Postgres

#142

Post by mcny »

vintermann wrote: post #135
mcny wrote: post #112

Earlier quoted context omitted.

That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.

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…

> What could you suspect if a person born in 1987 got a new PN/SSN around 2022?

Thank you for spelling it for me. For the readers, It leaks information that the person is likely not a natural born citizen. The assumption doesn't have to be a hundred percent accurate, There is a way to make that assumption And possibly hold it against you.

And there are probably a million ways that a record created date could be held against you If they don't put it in writing, how will you prove They discriminated against you.

Thinking... I don't have a good answer to this. If data exists, people will extract meaning from it whether rightly or not.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#143

Post by zamadatix »

barrkel wrote: post #114
vintermann wrote: post #99

Earlier quoted context omitted.

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?

UUID v7 doesn't squeeze creation date in. If you treat it as anything other than a random sequence in your applications, you're just wrong.

"What it does" and "what I think you should do with it" should not be treated as equivalent statements.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#144

Post by sgarland »

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 milliseconds!

A bad PK in some databases (InnoDB engine, SQL Server if clustered) can cause query times to go from sub-msec to tens of msec quite easily, especially with cloud solutions where storage isn’t node-local. I don’t just mean a UUID; a BIGINT PK on a 1:M can destroy your latency for the simple reason of needing to fetch a separate page for every record. If instead the PK is a composite of (, id) - e.g. (user_id, id) - where id is a monotonic integer, you’ll have WAY better data locality.

Postgres suffers a different but similar problem with its visibility map lookups.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#145

Post by anamexis »

vintermann wrote: post #135
mcny wrote: post #112

Earlier quoted context omitted.

That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.

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…

I would argue that is one of very few situations where leaking the timestamp that the ID was created when you already have the ID is a possible concern at all.

And when working with very large datasets, there are very significant downsides to large, completely random IDs (which is of course what the OP is about).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#146

Post by sgarland »

hans_castorp wrote: post #100
dev_l1x_be wrote: post #93

Earlier quoted context omitted.

Why would you need to order by UUID? I am missing something here. Most of the time we use UUID keys for being able to create a new key without coordination and most of the time we do not want to order by primary key.

I have seen a lot of people sort by (generated) integer values to return the rows "in creation order" assuming that sorting by an integer is somehow magically faster than sorting by a proper timestamp value (which give a more robust "creation order" sorting than a generated integer value).

Assuming the integer value is the PK, it can in fact be much faster for MySQL / MariaDB due to InnoDB’s clustering index. If it can do a range scan over the PK, and that’s also the ORDER BY (with matching direction), congratulations, the rows are already ordered, no sort required. If it has to do a secondary index lookup to find the rows, this is not guaranteed.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#147

Post by reactordev »

nrhrjrjrjtntbt wrote: post #68
reactordev wrote: post #58

I fun trick I did was generate UUID-like ids. We all can identify a UUIDv4 most of the time by looking at one. "Ah, a uuid" we say to ourselves. A little over a decade ago I was working on a massive cloud platform and rather than generate string keys like the author above suggested (int -> binary -> base62 str) we opted for a more "clever" approach. The UUID is 128bits. The first 64bits are a java long. The last 64bi…

Atlassian settles for longer "ARIs" for this (e.g. https://developer.atlassian.com/cloud/guard-detect/developer... ) composed of guids which allow a scheme like the Amazon ARN to pass around.

yeah, the problem for us was the resource id. What id was it? Was it a post? an upload? a workspace? it wasn't nearly as descriptive as we needed it to be.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#148

Post by sgarland »

K0nserv wrote: post #59
dotancohen wrote: post #6

From the fine article: > Random values don’t have natural sorting like integers or lexicographic (dictionary) sorting like character strings. UUID v4s do have "byte ordering," but this has no useful meaning for how they’re accessed. Might the author mean that random values are not sequential, so ordering them is inefficient? Of course random values can be ordered - and ordering by what he calls "byte ordering" is exa…

Isn't part of this that inserting into a btree index is more performant when the keys are increasing rather than being random? A random id will cause more re-balancing operations than always inserting at the end. Increasing ids are also more cache friendly

Yes, and for Postgres, it also causes WAL bloat due to the high likelihood of full page writes.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#149

Post by gwbas1c »

I work on an application where we encrypt the integer primary key and then use the bytes to generate something that looks like a UUID.

In our case, we don't want database IDs in an API and in URLs. When IDs are sequential, it enables things like dictionary attacks and provides estimates about how many customers we have.

Encrypting a database ID makes it very obvious when someone is trying to scan, because the UUID won't decrypt. We don't even need a database round trip.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#150

Post by sgarland »

tmountain wrote: post #42
sbuttgereit wrote: post #27

Earlier quoted context omitted.

You might have missed the big H2 section in the article: "Recommendation: Stick with sequences, integers, and big integers" After that then, yes, UUIDv7 over UUIDv4. This article is a little older. PostgreSQL didn't have native support so, yeah, you needed an extension. Today, PostgreSQL 18 is released with UUIDv7 support... so the extension isn't necessary, though the extension does make the claim: "[!NOTE] As of Po…

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

There are plenty of ways to deal with that. You can shard by some other identifier (though I then question your table design), you can assign ranges to each shard, etc.
Post reply on HN