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

61–70 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#61

Post by torginus »

kreetx wrote: post #11
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…

Using an UUIDv4 as primary key is a trade-off: you use it when you need to generate unique keys in a distributed manner. Yes, these are not datetime ordered and yes, they take 128 bits of space. If you can't live with this, then sure, you need to consider alternatives. I wonder if "Avoid UUIDv4 Primary Keys" is a rule of thumb though.

I do not understand why 128 bits is considered too big - you clearly can't have less, as on 64 bits the collision probability on real world workloads is just too high, for all but the smallest databases.

Auto-incrementing keys can work, but what happens when you run out of integers? Also, distributed dbs probably make this hard, and they can't generate a key on client.

There must be something in Postgres that wants to store the records in PK order, which while could be an okay default, I'm pretty sure you can this behavior, as this isn't great for write-heavy workloads.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#62

Post by K0nserv »

An additional thing I learned when I worked on a ulid alternative over the weekend[0] is: Postgres's internal Datum type is at most 64 bits which means every uuid requires heap allocation[1] (at least until we get 128 bit machines).

0: https://bsky.app/profile/hugotunius.se/post/3m7wvfokrus2g

1: https://github.com/postgres/postgres/blob/master/src/backend...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#63

Post by raxxorraxor »

> Do not assume that UUIDs are hard to guess; they should not be used as security capabilities

The issue is that is true for more or less all capability URLs. I wouldn't recommend UUIDs per se here, probably better to just use a random number. I have seen UUIDs for this in practice though and these systems weren't compromised because of that.

I hate the tendency that password recovery flows for example leave the URL valid for 5 minutes. Of course these URLs need to have a limited life time, but mail isn't a real time communication medium. There is very little security benefit from reducing it from 30 minutes to 5 minutes for example. You are not getting "securer" this way.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#64

Post by bruce511 »

Sayrus wrote: post #30
bux93 wrote: post #25

Long article about why not to use UUIDv4 as Primary Keys, but.. Who is doing so? And why are they doing that? How would you solve their requirements? Just throwing out "you can use UUIDv7" doesn't help with, e.g., the size they take up. Aren't people using (big)ints are primary keys, and using UUIDs as logical keys for import/export, solving portability across different machines?

UUIDs are usually the go-to solution to enumeration problems. The space is large enough that an attacker cannot guess how many X you have (invoices, users, accounts, organizations, ...). When people replace the ints by UUIDv4, they keep them as primary keys.

I'd add that it's also used when data is created in multiple places.

Consider say weather hardware. 5 stations all feeding into a central database. They're all creating rows and uploading them. Using sequential integers for that is unnecessarily complex (if even possible.)

Given the amount of data created on phones and tablets, this affects more situations than first assumed.

It's also very helpful in export / edit / update situations. If I export a subset of the data (let's say to Excel), the user can edit all the other columns and I can safely import the result. With integer they might change the ID field (which would be bad). With uuid they can change it, but I can ignore that row (or the whole file) because what they changed it to will be invalid.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#65

Post by vintermann »

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 changes, you can run into trouble. Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that series!"

Librarians in the analog age can be forgiven for cramming data into their identifiers, to save a lookup. When the lookup is in a physical card catalog, that's somewhat understandable (although you bet they could run into trouble over it too). But when you have a powerful database at your fingertips, use it! Don't make decisions you will regret just to shave off a couple of milliseconds!

Re: Avoid UUID Version 4 Primary Keys in Postgres

#66

Post by kijin »

zelphirkalt wrote: post #44
kijin wrote: post #12

Earlier quoted context omitted.

The space requirement and index fragmentation issue is nearly the same no matter what kind of relational database you use. Math is math. Just the other day I delivered significant performance gains to a client by converting ~150 million UUIDv4 PKs to good old BIGINT. They were using a fairly recent version of MariaDB.

If they can live with making keys only in one place, then sure, this can work. If however they need something that is very highly likely unique, across machines, without the need to sync, then using a big integer is no good. if they can live with MariaDB, OK, but I wouldn't choose that in the first place these days. Likely Postgres will also perform better in most scenarios.

Yeah, they had relatively simple requirements so BIGINT was a quick optimization. MariaDB can guarantee uniqueness of auto-incrementing integers across a cluster of several servers, but that's about the limit.

Had the requirements been different, UUIDv7 would have worked well, too, because fragmentation is the biggest problem here.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#67

Post by torginus »

alerighi wrote: post #34
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,…

You can create hash indexes in Postgres, so the secondary index uuid seems workable:

https://www.postgresql.org/docs/current/hash-index.html

Re: Avoid UUID Version 4 Primary Keys in Postgres

#68

Post by nrhrjrjrjtntbt »

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.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#70

Post by nrhrjrjrjtntbt »

bruce511 wrote: post #64
Sayrus wrote: post #30

Earlier quoted context omitted.

UUIDs are usually the go-to solution to enumeration problems. The space is large enough that an attacker cannot guess how many X you have (invoices, users, accounts, organizations, ...). When people replace the ints by UUIDv4, they keep them as primary keys.

I'd add that it's also used when data is created in multiple places. Consider say weather hardware. 5 stations all feeding into a central database. They're all creating rows and uploading them. Using sequential integers for that is unnecessarily complex (if even possible.) Given the amount of data created on phones and tablets, this affects more situations than first assumed. It's also very helpful in export / edit /…

Yes and the DB might be columnular or a distributed KV, sidestepping the index problem.
Post reply on HN