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

121–130 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#121

Post by wongarsu »

throw0101a wrote: post #56
mort96 wrote: post #13

Earlier quoted context omitted.

I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…

> I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. See perhaps "UUIDv47 — UUIDv7-in / UUIDv4-out (SipHash‑masked timestamp)": * https://github.com/stateless-me/uuidv47 * Sept 2025: https://news.ycombinator.com/item?id=45275973

If that kind of stuff is on the able you can also use boring 64bit integer keys and encrypt those (e.g. [1]). Which in the end is just a better thought out version of what the article author did.

UUIDv47 might have a space if you need keys generated on multiple backend servers without synchronization. But it feels very niche to me.

1: https://wiki.postgresql.org/wiki/XTEA_(crypt_64_bits)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#122

Post by [deleted] »

barrkel wrote: post #113
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…

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.

[deleted]

Re: Avoid UUID Version 4 Primary Keys in Postgres

#123

Post by kunley »

socketcluster wrote: post #81
kunley wrote: post #57

Earlier quoted context omitted.

What are these certain problems, if I may ask?

A major one for me is preventing duplicate records. If the client POSTs a new object to insert it into the database; if there is a connection failure and the client does not receive a success response from the server, the client cannot know whether the record was inserted or not without making an expensive and cumbersome additional read call to check... The client cannot simply assume that the insertion did not happe…

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?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#124

Post by mort96 »

barrkel wrote: post #115
mort96 wrote: post #13

Earlier quoted context omitted.

I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…

You shouldn't generally use PKs as public identifiers, least of all UUIDs, which are pretty user hostile.

I really don't see the issue with having a UUID in a URL.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#125

Post by dboreham »

cdmckay wrote: post #19
mort96 wrote: post #13

Earlier quoted context omitted.

I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…

Out of curiosity, why is it an issue if you leak creation time?

Apart from all the other answers here: an external entity knowing the relative creation time for two different accounts, or just that the two accounts were created close in time to each other can represent a meaningful information leak.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#127

Post by conradfr »

formerly_proven wrote: post #60
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.

If all you want is to obfuscate the fact that your social media site only has 200 users and 80 posts, simply use a permutation over the autoincrement primary key. E.g. IDEA or CAST-128, then encode in base64. If someone steps on your toes because somewhere in your codebase you're using a forbidden legacy cipher, just use AES-128. (This is sort of the degenerate/tautological base case of format-preserving encryption)…

Can't you just change the starting value of your sequence?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#129

Post by bri3d »

barrkel wrote: post #113
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…

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.

> You're not going to try and extract a timestamp from a uuid.

What? The first 48 bits of an UUID7 are a UNIX timestamp.

Whether or not this is a meaningful problem or a benefit to any particular use of UUIDs requires thinking about it; in some cases it’s not to be taken lightly and in others it doesn’t matter at all.

I see what you’re getting at, that ignoring the timestamp aspect makes them “just better UUIDs,” but this ignores security implications and the temptation to partition by high bits (timestamp).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#130

Post by [deleted] »

mcny wrote: post #112
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?

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.

[deleted]
Post reply on HN