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

31–40 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#31

Post by kreetx »

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?

E.g, if your service users have timestamp as part of the key and this data is visible to other users, you would know when that account was created. This could be an issue.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#32

Post by lwhi »

robertlagrant wrote: post #21
cdmckay wrote: post #19

Earlier quoted context omitted.

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

Depends on the data. If you use a primary key in data about a person that shouldn't include their age (e.g. to remove age-based discrimination) then you are leaking an imperfect proxy to their age.

So the UUID could be used as an imperfect indicator of a records created time?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#34

Post by alerighi »

dimitrisnl wrote: post #14

Noob question, but why no use ints for PK, and UUIDs for a public_id field?

If you put an index on the UUID field (because you have an API where you can retrieve objects with UUID) you have kind of the same problem, at least in Postgres where a primary key index or a secondary index are more or less the same (to the point is perfectly valid in pgsql to not have any primary key defined for the table, because storage on disk is done trough an internal ID and the indexes, being primary or not, just reference to the rowId in memory). Plus the waste of space of having 2 indexes for the same table.

Of course this is not always the case that is bad, for example if you have a lot of relations you can have only one table where you have the UUID field (and thus expensive index), and then the relations could use the more efficient int key for relations (for example you have an user entity with both int and uuid keys, and user attribute references the user with the int key, of course at the expense of a join if you need to retrieve one user attribute when retrieving the user is not needed).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#36

Post by mort96 »

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?

Well you're leaking user data. I'm sure you can imagine situations where "the defendant created an account on this site on this date" could come up. And the user could have created that account not knowing that the creation date is public, because it's not listed anywhere in the publicly viewable part of the profile other than the UUID in the URL.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#37

Post by bardsore »

grim_io wrote: post #29
dimitrisnl wrote: post #14

Noob question, but why no use ints for PK, and UUIDs for a public_id field?

The article mentions microservices, which can increase the likelihood of collisions in sequential incremental keys. One more reason to stay away from microservices, if possible.

Always try to avoid having two services using the same DB. Only way I'd ever consider sharing a DB is if only one service will ever modify it and all others only read.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#38

Post by benterix »

lwhi wrote: post #32
robertlagrant wrote: post #21

Earlier quoted context omitted.

Depends on the data. If you use a primary key in data about a person that shouldn't include their age (e.g. to remove age-based discrimination) then you are leaking an imperfect proxy to their age.

So the UUID could be used as an imperfect indicator of a records created time?

UUIDv7 but not UUIDv4.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#39

Post by nbadg »

mort96 wrote: post #13
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.

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'm also not a huge fan of leaking server-side information; I suspect UUIDv7 could still be used in statistical analysis of the keyspace (in a similar fashion to the german tank problem for integer IDs). Also, leaking data about user activity times (from your other comment) is a *really* good point that I hadn't considered.

I've read people suggest using a UUIDv7 as the primary key and a UUIDv4 as a user-visible one as a remedy.

My first thought when reading the suggestion was, "well but you'll still need an index on the v4 IDs, so what does this actually get you?" But the answer is that it makes joins less expensive; you only require the index once, when constructing the query from the user-supplied data, and everything else operates with the better-for-performance v7 IDs.

To be clear, in a practical sense, this is a bit of a micro-optimization; as far as I understand it, this really only helps you by improving the data locality of temporally-related items. So, for example, if you had an "order items" table, containing rows of a bunch of items in an order, it would speed up retrieval times because you wouldn't need to do as many index traversals to access all of the items in a particular order. But on, say, a users table (where you're unlikely to be querying for two different users who happen to have been created at approximately the same time), it's not going to help you much. Of course the exact same critique is applicable to integer IDs in those situations.

Although, come to think of it, another advantage of a user-visible v4 with v7 Pk is that you could use a different index type on the v4 ID. Specifically, I would think that a hash index for the user-visible v4 might be a halfway-decent way to go.

I'm still not sure either way if I like the idea, but it's certainly not the craziest thing I've ever heard.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#40

Post by lwhi »

Lucasoato wrote: post #15

Hi, a question for you folks. What if I don’t like to embed timestamp in uuid as v7 do? This could expose to timing attacks in specific scenarios. Also is it necessary to show uuid at all to customers of an API? Or could it be a valid pattern to hide all the querying complexity behind named identifiers, even if it could cost a bit in terms of joining and indexing? The context is the classic B2B SaaS, but feel free to…

Wouldn't you need to expose UUID if you want to make use of optimistic locking?
Post reply on HN