Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Unexpected downsides of UUID keys in PostgreSQL

cybertec-postgresql.com

Post reply on HN
Print view View original

191–200 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#191

Post by ramchip »

jongjong wrote: post #105
ramchip wrote: post #85

Earlier quoted context omitted.

You can use the DB for this as well, just make a table e.g. "requests" with two columns, the user and the request token. Old entries can be purged on a cron job. If you add a column to store params as well then you can also do better validation: > Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same > We design our APIs to allow our customers to explici…

You can but you add an additional database lookup step which will cost you more in performance (and latency) than just using UUIDs as the ID directly with a single table.

It's only at creation time. When you lookup the record after that you can use the ID directly, and then you don't hit the locality problems from the article.

Re: Unexpected downsides of UUID keys in PostgreSQL

#192

Post by jandrewrogers »

ceeam wrote: post #36
dewey wrote: post #32

Earlier quoted context omitted.

> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

Their uniqueness of course. > To be able to generate keys independently of the database > To move sets of related records between different databases without having to deal with renumbering everything

Unfortunately, UUIDs aren't as universally unique in practice as people think. The standard flavors are banned in quite a few organizations that care a lot about data integrity for valid reasons. Standard UUIDs work for some applications but definitely not all.

For most applications you are better off using a sensible structured key, like incrementing an integer or similar, and encrypting it if you want to obscure it. Encrypting a 128-bit value is approximately free on modern CPUs.

Re: Unexpected downsides of UUID keys in PostgreSQL

#193

Post by letitbeirie »

lolinder wrote: post #171
letitbeirie wrote: post #129

Earlier quoted context omitted.

Postgres supports UUIDs natively

ULIDs are byte-compatible with UUIDs, so the only thing Postgres's native support gives you over ULIDs is that Postgres can generate new UUIDs for you instead of having to do it in the application before insertion.

It also buys you that support in the driver libraries.

I ran into this last year - storing them as UUIDs works great, unless all of the drivers for the language you're using (Go, in my case) try to cast/validate those bytes as a UUID before you can access them.

Re: Unexpected downsides of UUID keys in PostgreSQL

#194

Post by jandrewrogers »

gregwebs wrote: post #4

UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…

Organizations that care about leaking information encrypt their identifiers as UUIDs. UUIDs conveniently have the same size as a single AES block, for which there is dedicated silicon on all modern CPUs. There is almost no overhead.

Every other issue with random UUIDs etc, which are ignored here, are solved by encrypting your identifier. Random UUIDs (i.e. UUIDv4) are banned in many places for good reasons.

Re: Unexpected downsides of UUID keys in PostgreSQL

#195

Post by 3pt14159 »

babbledabbler wrote: post #109
3pt14159 wrote: post #69

Earlier quoted context omitted.

Well, I'll give another take. If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by…

I think you raise some good questions around IDs and PII and we definitely will be tackling GDPR sooner or later. I don't quite follow on the European regulation issues raised by using as a UUID in a route and that being the PK of the record. I know you should not expose PII or any information that can be used to identify a person, however, in our case any route is behind an authed login on an SSL connection which en…

> any route is behind an authed login on an SSL connection which encrypts the path

If your application only services a single user with their own resources then you have nothing to fear. But few applications meet this definition. If, for example, you're running an invoicing application, then at some point you'll want to share some resource, say an invoice or an expense or a time sheet, with another party. If your API exposes the identifiers from one resource to another, or even a user's id when potentially adding them to a team, then these identifiers are considered PII according to European regulators.

I understand that this is frustrating, but it comes from a posture that prioritizes right-to-be-forgotten over programmer ergonomics. Imagine, for example, API crawlers that hit your /search endpoint with email=[some predetermined list of emails] and harvest user ids to match with future data.

In the end, the best thing you can do is keep join keys internal and API keys separated. There are other workarounds, but they're so much trouble that they aren't really viable alternatives. Now, whether you use UUIDs for both identifiers or UUID for external and integer ids for join keys is up to you and your performance and scaling requirements. Personally, I prefer integer keys for internal unless I really expect the database to grow to more than 200m rows before the company hits 1000 people, since int ids mean you do not need secondary indexes on things like the created_at fields, but even there, it's not such a big deal to have an extra index on every table.

> I have a principle to not go against the grain of a framework. > hacking adds complexity

Here we essentially agree, but with the right integration tests, upgrading and onboarding is a lot easier than feared. That said, do not add to the framework unless the benefit is worth it.

Re: Unexpected downsides of UUID keys in PostgreSQL

#196

Post by babbledabbler »

gregwebs wrote: post #190
babbledabbler wrote: post #172

Earlier quoted context omitted.

I'm not so much concerned with figuring out scaling in terms of volume as I expect to be able to handle millions of rows in a single DB and that would be an implementation detail and fine tuning. I'm more concerned about scaling in terms of complexity and keeping the system easy to reason about when more people, tech are involved. Lets say I have a -[1:N]- in two tables in a relational DB. This works fine at first ev…

I think switching this with zero downtime to do foreign key references with UUIDs will be easier than any of the pain you would deal with from having to do cross-DB joins. What specific issues are you worried about with the integer key? Usually the issue is dupming data into something like a staging or development environment rather than a production concern. If you attempt to dump 2 datasets into one db you will hav…

Mainly portability of data and options for the future. I'm all on one postgres instance right now and don't plan on breaking it up until necessary. If at some point I need to take a table and move it to another type of database I want that migration process to be straightforward. If I have integer keys with sequencing behavior I anticipate having to do that porting. That internal key would then become external to do the lookup and if it's an external key I want it to be UUID for security as well. Integers as IDs are guessable so I want to keep them internal.

Re: Unexpected downsides of UUID keys in PostgreSQL

#197

Post by babbledabbler »

3pt14159 wrote: post #195
babbledabbler wrote: post #109

Earlier quoted context omitted.

I think you raise some good questions around IDs and PII and we definitely will be tackling GDPR sooner or later. I don't quite follow on the European regulation issues raised by using as a UUID in a route and that being the PK of the record. I know you should not expose PII or any information that can be used to identify a person, however, in our case any route is behind an authed login on an SSL connection which en…

> any route is behind an authed login on an SSL connection which encrypts the path If your application only services a single user with their own resources then you have nothing to fear. But few applications meet this definition. If, for example, you're running an invoicing application, then at some point you'll want to share some resource, say an invoice or an expense or a time sheet, with another party. If your API…

I don't really find these considerations frustrating just a bit tricky but regardless definitely agree with GDPR and on board with keeping PII secure from the get go.

I'm still having a little trouble grokking when an ID becomes exposed or shared so I guess I'll just have to read up on this as it's certainly important.

In our system I realized user IDs are not shared nor linked to (at least not yet) so in actuality the case where there's a URL with a UUID representing a person does not occur. Content generated does not reference UUIDs for persons either. There are URLs with UUIDs representing other types of resources.

By API key I take it to mean an access key for an external reference. That's a good idea for replacing the PK integer with a PK UUID but keeping an external UUID field. That would satisfy the concern with maintaining integer sequences and migrating data.

Anyway this has been helpful so thank you for sharing your thoughts and I have some things to look go on to stay in the good graces of European regulators.

Re: Unexpected downsides of UUID keys in PostgreSQL

#198

Post by CGamesPlay »

iwwr wrote: post #22
CGamesPlay wrote: post #21

Earlier quoted context omitted.

How could temporal information be used to infer the size of the dataset for all users? Specifically, more accurately than “I now know that this database was created in 2022”.

Sequential serial numbers reveal information. See: https://en.m.wikipedia.org/wiki/German_tank_problem

Yes. How could temporal information be used to infer the size of the database?

To clarify this a bit, UUID v7 (or the existing ULID) are timestamps with random bytes at the end. You can learn when a UUID v7 was created. How do you infer the total size of the database from this information?

Re: Unexpected downsides of UUID keys in PostgreSQL

#199

Post by selcuka »

ilyt wrote: post #144
xahrepap wrote: post #141

Earlier quoted context omitted.

ULIDs have made our MySql caches breathe a sigh of relief. One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity). So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.

I wonder if just encrypting them for public usage would be enough here. Then application can convert between public and private representation at will.

Encryption is good until someone leaks the private key. Depending on the application, it may be very difficult to reassign all primary keys and foreign keys when that happens, especially if they have already been used as canonical identifiers.

You don't have such issues with randomly generated ids, unless someone obtains a full dump of your database (in which case you will have to think about bigger problems anyway).

Re: Unexpected downsides of UUID keys in PostgreSQL

#200

Post by ants_a »

seanhunter wrote: post #180

I’ll genuinely never understand why people benchmark in this way. They are doing a thing that is slow and inefficient in a fundamental way and then producing a blogpost about it being slow and inefficient in a much more trivial respect. If you care about performance, select count(uuid) from records; Makes absolutely no sense. You know the uuid is unique so you are deliberately selecting a value and then throwing it a…

I agree, though it's not the uniqueness, but not null constraint that matters here. PostgreSQL could, but doesn't right now, use the not null constraint to optimize the former to the latter.

The actual case came to my attention because an ORM insisted on generating the count(uuid) variant and I thought it peculiar that the performance difference was so large. But silly ORMs aside, the same problem will happen on any index only range scan over an index that has uuid in it. For a more realistic case, a `count(*) where somecol = 'value'` with a `(somecol, uuid)` index will hit the same problem. I thought the rather hidden single entry visibility map buffer reference cache was an interesting example where random ordering can cause performance problems.

Post reply on HN