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

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

#81

Post by Rafert » Sat, Jan 08, 2022, 1:49 PM UTC

Svip wrote: post #3

busymom0 wrote: post #2

Is this specific to MySQL or does it apply to Postgres too?

As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks. Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've…

The UUID type index in Postgres is optimized: https://brandur.org/sortsupport

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

#82

Post by nchi3 » Sat, Jan 08, 2022, 1:51 PM UTC

NicoJuicy wrote: post #53

nchi3 wrote: post #49

Earlier quoted context omitted.

Fair enough, though what I said remains true for an "Accepted" response.

But Accepted doesn't mean created. The feedback flow is async from the creation flow. If you get created through the feedback flow it's created.

Accepted can mean that it will eventually be created though. In the sense that it's validated and guaranteed to be created at a later point ("eventually").

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

#83

Post by mafuy » Sat, Jan 08, 2022, 1:52 PM UTC

zelphirkalt wrote: post #54

nchi3 wrote: post #44

Earlier quoted context omitted.

If you get a "Created" back, it is possible for it to be guaranteed that it'll be created eventually.

What if the object is deleted again? You could still have an inconsistent picture, no? And you could create and delete over and over again, so that a client has an inconsistent picture all the time.

That's a different design issue then - it means lack of synchronization. It is orthogonal to using uuids at a client. In fact, (properly) using uuids are a reasonable way to resolve this.

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

#84

Post by tluyben2 » Sat, Jan 08, 2022, 1:56 PM UTC

gls2ro wrote: post #23

A lot of things that we do for security or privacy are bad for performance, but I think they are still good tradeoffs.

Yep, so we need solutions for using these practices in a performant way rather than hearing they are not 'good' and then having to explain over and over again why they are there. Our datasets that use UUIDs have not had issues with performance but of course we keep looking for ways to keep using UUIDs while improving performance. It would be better to provide solutions on how to do that and spend time to get performance on par with int keys. Like someone else said; 32 bit int keys are no good anyway for many cases, so let's go to 128 bit, optimize for that and everyone is happy.

Edit: like https://news.ycombinator.com/item?id=29851653

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

#85

Post by jrochkind1 » Sat, Jan 08, 2022, 1:57 PM UTC

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.

I'm kind of shocked MySQL doesn't do this?

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

#86

Post by jrochkind1 » Sat, Jan 08, 2022, 1:59 PM UTC

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.

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

#87

Post by hashimotonomora » Sat, Jan 08, 2022, 2:02 PM UTC

damagednoob wrote: post #72

> The remaining of the UUID value comes from the MD5 of a random value and the current time at a precision of 1us. I might be misunderstanding something here but if your random seed is based on time, under high-concurrency, doesn't this risk collisions? I can't see any thread-safety guarantees in the documentation.[1] [1] https://dev.mysql.com/doc/refman/8.0/en/mathematical-functio...

If the column is UNIQUE there’s no collisions it will just fail to INSERT.

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

#88

Post by zelphirkalt » Sat, Jan 08, 2022, 2:05 PM UTC

mafuy wrote: post #83

zelphirkalt wrote: post #54

Earlier quoted context omitted.

What if the object is deleted again? You could still have an inconsistent picture, no? And you could create and delete over and over again, so that a client has an inconsistent picture all the time.

That's a different design issue then - it means lack of synchronization. It is orthogonal to using uuids at a client. In fact, (properly) using uuids are a reasonable way to resolve this.

Not arguing against using UUID. Just exploring that async concept further and the challenges in that. OK 2 separate issues : )

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

#89

Post by thomasmg » Sat, Jan 08, 2022, 2:05 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.

You are right, the internals are important. When I tested this, a long time ago [1] [2], I found that randomly distributed keys on PostgreSQL were indeed faster than e.g. on MySQL. Which surprised me! I still don't quite understand why. But, even with PostgreSQL, sequential (or nearly sequential) are much faster.

[1] https://markmail.org/thread/3jzqjy6cavxcrpbq [2] https://markmail.org/download.xqy?id=3jzqjy6cavxcrpbq&number...

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

#90

Post by aaronharnly » Sat, Jan 08, 2022, 2:05 PM UTC

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.

We do the same.