Unexpected downsides of UUID keys in PostgreSQL • Hacker News Board

Re: Unexpected downsides of UUID keys in PostgreSQL

#171

Post by lolinder » Thu, Jun 22, 2023, 5:57 PM UTC

letitbeirie wrote: post #129

unshavedyak wrote: post #123

Earlier quoted context omitted.

Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing. Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)? [1]: Again, totally out of my…

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.

Re: Unexpected downsides of UUID keys in PostgreSQL

#172

Post by babbledabbler » Thu, Jun 22, 2023, 6:01 PM UTC

gregwebs wrote: post #147

babbledabbler wrote: post #114

Earlier quoted context omitted.

This was generally the reason I went with numeric ID as PK originally. It makes working with and analyzing the data as well as cross referencing relations easier. For all my tables I have a base schema that looks something like this. id: integer sequence PK uuid: uuidV4 created_at: datetime updated_at: datetime The concern I have is when I have to distribute my system when scaling. Those numeric IDs will have to be r…

Everything breaks at scale. In my experience most tables don't end up with more than a few million rows and will work fine with this. If you did want to transition a large table to be UUID only, the nice thing about this approach is that you could do it with no down time. If you are using a DB that only scales writes vertically though (most DBs, including distributed DBs) then how are you actually going to scale the…

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 even for millions of rows as you say.

At some point in the future it makes sense to have these two entities managed by different team/services/db. Let's say TRIP becomes a whole feature laden thing with fares, hotels, itinerary, dates. So I need to take this local relation and move it to different services and different DB.

If I had been using an integer PK/FK this would be a more complicated migration than if I used UUIDs.

My assumption is that we would not want to have a sequenced integer key used in a distributed system.

In other words it seems safer bet if there's a possibility of needing to move to a distributed system to use a UUID for the key from the beginning.

Re: Unexpected downsides of UUID keys in PostgreSQL

#173

Post by antifa » Thu, Jun 22, 2023, 6:09 PM UTC

kogus wrote: post #38

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?

“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.

You can tell all your tables to use the same sequence.

Re: Unexpected downsides of UUID keys in PostgreSQL

#174

Post by code-e » Thu, Jun 22, 2023, 6:12 PM UTC

unshavedyak wrote: post #135

letitbeirie wrote: post #129

Earlier quoted context omitted.

Postgres supports UUIDs natively

Is that worth the pain of dealing with randomized inserts? I guess i just don't mind creating a ULID (or i guess UUIDv7 is newly proposed and sortable) and inserting that. Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?

It does affect storage and sorting. A native UUID type uses 16 bytes. The alternative is text encoding (32 bytes for hex), or maybe a raw BYTEA. Postgres also has SortSupport for the UUID type, which basically means if the first 8 bytes only has one matching row, then the remaining 8 bytes can be skipped. Combine that with a ULID where the most first half is basically a timestamp, you'll get performance close to using a single 8byte BIGSERIAL.

You can also write a plpgsql function to generate these ULIDs in the database.

Re: Unexpected downsides of UUID keys in PostgreSQL

#175

Post by smolder » Thu, Jun 22, 2023, 6:14 PM UTC

kogus wrote: post #38

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?

“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.

We have some app generated internal IDs which have a 1 character prefix by type and then base64 sequential stuff after that. I'll admit it's nice knowing the type of an identifier by looking at it. I don't really like that they are strings, however. The original architect made some weird choices as far as deciding where and where not to optimize for performance.

Re: Unexpected downsides of UUID keys in PostgreSQL

#176

Post by qaq » Thu, Jun 22, 2023, 6:16 PM UTC

pgaddict wrote: post #158

qaq wrote: post #126

Earlier quoted context omitted.

this sounds horrible until you realise that a modern SSD will write that in about 1.5 sec.

That was just an example calculation, to illustrate the write amplification factor, of course. You can scale it up pretty arbitrarily. I mentioned only WAL for simplicity, but it also has to modify and write out the index pages themselves, and write them out eventually. And that's going to be mostly random I/O. Flash storage is good at handling that, ofc, but if things are adding up like this ... Not to mention you s…

sure but not many workloads are writing out million inserts per second.

Re: Unexpected downsides of UUID keys in PostgreSQL

#177

Post by samokhvalov » Thu, Jun 22, 2023, 6:18 PM UTC

gmhafiz wrote: post #117

rjh29 wrote: post #33

Earlier quoted context omitted.

I was going to use UUID with the time portion at the start (also known as UUIDv7) but this looks better.

One potential downside is ULID does not have an RFC, unlike UUID V7

Hopefully, it will soon have it: https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc...

Re: Unexpected downsides of UUID keys in PostgreSQL

#178

Post by SkyPuncher » Thu, Jun 22, 2023, 6:31 PM UTC

Timpy wrote: post #71

For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…

Interestingly, my experience has been the opposite. It's incredibly easy to search and filter logs with UUIDs. You can search for just the UUID without any other information.

Re: Unexpected downsides of UUID keys in PostgreSQL

#179

Post by saltcured » Thu, Jun 22, 2023, 6:36 PM UTC

pgaddict wrote: post #167

patrec wrote: post #161

Earlier quoted context omitted.

How would adding a BRIN index help in any way with reducing the discussed problems, such as write amplification?

This is a bit confusing, as it mixes two things - BRIN index and index on a timestamp. The main source of write amplification comes from updating random pages of the btree index. Imagine inserting 10 random UUID values into a large index - it's pretty likely those will go into 10 different leaf pages. And every first update of a page after a checkpoint (which typically happens every 30 minutes or so), we have to writ…

Right, you just need to avoid having the btree index on the UUID field. Similarly, don't pose queries to sort by the randomized UUID field either. This is where the often maligned hash index type could be useful, to allow lookup of individual rows by UUID without all the expense of a btree index maintenance. Use other fields for ordering that have better write locality.

What this means in practice, of course, is that you shouldn't expect to do application driven pagination with UUID keys either. You would need to expose some other boundary marker with a total order that works well with btrees. And this could bring you back to "leaking" predictable key material that you were trying to hide by adopting UUIDs...

Re: Unexpected downsides of UUID keys in PostgreSQL

#180

Post by seanhunter » Thu, Jun 22, 2023, 6:41 PM UTC

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 away just to count it.

    select count(1) from records;

Would give the same value and could be answered from the index without requiring a scan of the table.

This is true of any unique column in the table no matter what type (it doesn’t have to be a uuid). The fact that uuids are a bit slower than other types of keys when you scan the entire table unnecessarily seems beside the point.