UUIDs are popular, but bad for performance (2019) • Hacker News Board

Re: UUIDs are popular, but bad for performance (2019)

#151

Post by datavirtue » Sat, Jan 08, 2022, 4:38 PM UTC

smoyer wrote: post #67

drenei wrote: post #7

Bad for performance as primary keys . But, still provide strong value as a unique identifier which is what makes them popular. I’ve used integers as primary keys, with UUIDs as alternate keys for external-to-the-data-store queries.

"Bad for performance as primary keys" -> "Bad for performance as primary keys in MySQL". This isn't an issue in PostgreSQL and perhaps the lesson here is that as you scale, you need to understand more about the internals of the DB system you've chosen. This isn't limited to RDBMS as it's pretty easy to show trade-offs in choosing a NoSQL as well.

What if you convert from MySql to postgres? Will the uuids still benefit from postgres optimizations?

Re: UUIDs are popular, but bad for performance (2019)

#152

Post by wbl » Sat, Jan 08, 2022, 4:38 PM UTC

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

Would you happen to have a link to the book?

Re: UUIDs are popular, but bad for performance (2019)

#153

Post by mhoad » Sat, Jan 08, 2022, 4:41 PM UTC

pkulak wrote: post #150

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

I use crockford 32 to _represent_ my UUIDs, but they are obviously stored as binary. Is the only problem with UUIDs that sometimes they get stored as strings? The only issue I've had with UUIDs is when they don't sort in increasing chronological order. RDBMSs don't appreciate high insert loads into random points in the index. Take care of that, however, and they're a treat.

Yeah I seem to recall he also mentioned that as an approach.

Re: UUIDs are popular, but bad for performance (2019)

#154

Post by paulryanrogers » Sat, Jan 08, 2022, 4:44 PM UTC

pkulak wrote: post #150

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

I use crockford 32 to _represent_ my UUIDs, but they are obviously stored as binary. Is the only problem with UUIDs that sometimes they get stored as strings? The only issue I've had with UUIDs is when they don't sort in increasing chronological order. RDBMSs don't appreciate high insert loads into random points in the index. Take care of that, however, and they're a treat.

ULIDs or time prefixed UUIDs are other options.

Re: UUIDs are popular, but bad for performance (2019)

#155

Post by datavirtue » Sat, Jan 08, 2022, 4:45 PM UTC

JimDabell wrote: post #95

globular-toast wrote: post #40

Earlier quoted context omitted.

Yeah. The main problem is people using them as primary keys in naïve systems like relational databases. You can't just expect a relational database to magically become a distributed system just by using UUIDs. There is a bit more work to do than that.

> You can't just expect a relational database to magically become a distributed system just by using UUIDs. Nobody thinks this, do they?

Somebody thought RabbitMQ did load balancing because it had three redundant nodes. Pretty much ruined the company. Definitely caused all of us to lose our equity.

Re: UUIDs are popular, but bad for performance (2019)

#156

Post by stickfigure » Sat, Jan 08, 2022, 4:50 PM UTC

colanderman wrote: post #128

qalmakka wrote: post #48

Isn't this easily solved by supporting 128 bit keys and using UUIDs as intended, i.e. as integers and not in their string serialization? This is as nonsensical as storing IPv4 as strings instead of 32 bit integers.

Length isn't the primary issue. Locality is. UUIDs are generally generated randomly. This results in terrible insert performance into B+tree-based indexes, and terrible (= no) lookup locality with basically any database. In a large table, successive entries ends up in separate disk pages. Even with time-based UUIDs, the time fields are ordered backward, which produces the same issue. One way to fix this (beside the m…

Firestore has exactly the opposite problem - indexed sequential values put pressure on the "last tablet" and require the same tablet to repeatedly split. This becomes the limiting factor on insert volume. Random generated keys are better, because they spread inserts across multiple tablets (ie servers).

I don't know for certain, but I suspect DynamoDB and most other databases that can trace their origins to the bigtable whitepaper have similar behavior.

Re: UUIDs are popular, but bad for performance (2019)

#157

Post by fivea » Sat, Jan 08, 2022, 4:52 PM UTC

mhoad wrote: post #133

I recently read a book by Google’s head guy on API design that was specifically about designing APIs and it had a big section on what makes a good identifier and why people reach for UUIDs and why specifically it is a problem on multiple levels. The thing that he ended up recommending however was super interesting in that I had never seen it mentioned before but it was basically to use this instead http://www.crockfo…

> (...) but it was basically to use this instead

Base32 is a representation format which provides a textual representation of numbers, not a storage format.

I mean, anyone is free to dump Base32, or even base 2, into a string and just run with that, but that would be highly inefficient.

Re: UUIDs are popular, but bad for performance (2019)

#158

Post by pachico » Sat, Jan 08, 2022, 4:53 PM UTC

This topic has been raised more than once at my work and it scared a lot of people. It's important to understand your use case before you embrace any other solution.

This will affect you only when you are frequently creating and storing new IDs, which leads to reshaping btrees.

If you have IDs and its number is under control and doesn't change a lot you're just fine.

Re: UUIDs are popular, but bad for performance (2019)

#159

Post by FabHK » Sat, Jan 08, 2022, 4:56 PM UTC

davidjytang wrote: post #147

carfacts wrote: post #139

Earlier quoted context omitted.

This format was discussed in a HN first page post just this week: > This alphabet, 0123456789ABCDEFGHJKMNPQRSTVWXYZ, is Douglas Crockford's Base32, chosen for human readability and being able to call it out over a phone if required. https://news.ycombinator.com/item?id=29794186

People in my industry in my country has specifically avoid using B and D together as they sound too similar over the phone. Also 2 and Z can be similar in writing. However it is nice to not see 0 and O, 1,I,l in the same string.

F and S sound similar over the phone, at least on POTS landlines, as they don't carry the higher frequencies (> 4 kHz) that distinguish the S from the F. Note that cat names tend to have S sounds.

POTS = Plain old telephony service is restricted to a narrow frequency range of 300–3,300 Hz, called the voiceband, which is much less than the human hearing range of 20–20,000 Hz [from https://en.wikipedia.org/wiki/Plain_old_telephone_service ]

Re: UUIDs are popular, but bad for performance (2019)

#160

Post by paulryanrogers » Sat, Jan 08, 2022, 5:00 PM UTC

jrochkind1 wrote: post #86

olliej wrote: post #28

I am very much not a database person, so forgive me if this is a dumb question. I'm reading this article and it says that UUID are compared byte by byte, and seems to be indicating they're stored as string. Is that actually the case? I would have assumed that SQL supported 128 bit ints, but this seems to imply it does not. Another question: if a column is set to char(fixed size) do the various sequel engines really n…

Not a dumb question, I think you've hit on a key oddity here. This article is about MySQL, apparently it's really the case in MySQL? It's not the case in every rdbms universally. Postgres has a uuid type that stores them how you would (rightfully) expect. I have no idea why MySQL does it this way, it does seem odd.

MySQL doesn't have a UUID type, so naive implementations use (VAR)CHAR. Those in the know use BINARY(16) and MySQL 8 now has helper functions to convert to and from hex. Apparently MariaDB will soon have a native UUID type. PostgreSQL has had them from for years.