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

UUIDs are popular, but bad for performance (2019)

percona.com

Post reply on HN
Print view View original

161–170 of 246 posts

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

#161

Post by bhawks »

stickfigure wrote: post #156
colanderman wrote: post #128

Earlier quoted context omitted.

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 bigta…

Indeed, traditional rdbms are generally optimized for patterns with high locality. High locality patterns with NoSQL and NewSQL style dbs often end up causing one node in the system to receive disproportionately high load that defeats attempts to spread operations across the cluster.

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

#162

Post by ei8ths »

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.

this is what i do, any public facing id - url, or js/html that would show an id would be a uuid and then the backend queries are done with primary keys / integers.

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

#163

Post by Zigurd »

Only if you are creating persistent objects in multiple places that may not be in communication with one another, and that at some point need to be distinguished from each other, do you need UUIDs.

Even in mobile apps, which seem like the most common use case for needing them: Unless your app creates these objects while not connected, you don't need UUIDs.

If your backend database is where objects get created, you don't need UUIDs.

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

#164

Post by jetzzz »

What's the purpose of using non-random data such as time and MAC address in UUID? It increases probability of collision compared to purely random bits and if you need time or MAC it seems better to store them as separate fields - easier to perform selects, groupings, etc.

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

#165

Post by CaptainNegative »

globular-toast wrote: post #79
jitl wrote: post #74

Earlier quoted context omitted.

Calling relational databases (HN’s preferred storage system) naive probably sounds like trolling to most people. There are also plenty of distributed relational databases. People downvote comments that sounds like trolling or flamebait. I use UUIDs but I don’t know why they would magically make my Postgres a distributed system. I like them because the client can generate them offline.

What? Really? Naïve in this context means a general purpose solution that doesn't "know" about your use case. Have people never heard of a naïve algorithm or solution? Distributed relational databases aren't naïve in this context. MySQL is.

> Have people never heard of a naïve algorithm or solution?

I have a fairly recent PhD in algorithms and I haven't heard naïve used this way. When I hear naïve, it usually just means "does the immediately obvious thing".

For what you're trying to say, the term I'm familiar with is "oblivious", e.g. "oblivious routing" or "oblivious local search", occasionally with modifiers such as "cache-oblivious".

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

#166

Post by [deleted] »

joshstrange wrote: post #132

I'm sure I take bigger penalty hits for less, sorry but UUID's "click" in my head and they prevent a whole slew of foot-guns. It would be one thing if auto-inc was the same as UUID but there are really annoying things with auto-inc (not knowing the id until after insert being top of mind) and also you can generate UUID's client-side/offline if needed. Yes, I know some people argue for auto-inc as primary and still us…

[deleted]

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

#167

Post by staticassertion »

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.

The linked article discusses the issues - the storage size when encoded as char, as 16byte values, as well as the impact on read/write locality.

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

#168

Post by MAGZine »

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…

I can't say I've had much issue with this in postgres, but even still, this is easily worked around by having a pkey/cluster key of a monotomically increasing int--which is useful for other things such as data migrations, as you can pick defined start and end targets without worrying about new things being inserted in between.

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

#170

Post by staticassertion »

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…

It's going to depend on the database. This article is primarily focused on InnoDB and goes over the read/write locality issues with a uuid.

That isn't always going to matter (or can be a very good thing). For a KV store like dynamodb a uuid is a great key because it'll distribute nicely across your shards.

It also depends a lot on your query patterns. If you have a uuid primary key but you're partitioning by some other value you may end up significantly reducing the number of pages you have to look through.

Post reply on HN