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

101–110 of 246 posts

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

#101

Post by erwincoumans »

Dachande663 wrote: post #61
busymom0 wrote: post #58

Slightly related question- does anyone know what data type Reddit uses for their post/comment id? It seems like a short alphanumeric yet unique identifier and much shorter than a uuid. Also what does twitter use for their post/comment id? Seems like some sort of big int?

Twitter created Snowflake many years ago to generate IDs. Unsure whether it’s still in use. https://blog.twitter.com/engineering/en_us/a/2010/announcing...

That sounds nice and simple, and fits in 64 bits (vs 128):

"we settled on a composition of: timestamp, worker number and sequence number. Sequence numbers are per-thread and worker numbers are chosen at startup via zookeeper"

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

#102

Post by MauranKilom »

Aardwolf wrote: post #27

> The missing 4 bits is the version number used as a prefix to the time-hi field. Why would you use 4 bits for a version number in something that's supposed to be unique? What is the benefit of following this specification despite such cost, versus creating 128 unique bits based on time / random generators / machine IDs yourself?

Because 124 random bits are still way enough to make collisions extremely unlikely (needs on the order of 2^62 UUIDs even with birthday paradox - good luck storing them all), yet they are still recognizable as being of that specific format.

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

#103

Post by marcosdumay »

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.

While the problem on the article is less of an issue in Postgres (the indexing cache locality is still there), they are still slower than the serial ones.

I don't know if you save enough problems by using them as alternative keys in Postgres for it to be faster, my guess is that just using them as primary key would be faster than a serial primary key and an UUID alternative one. Still, UUIDs are much more useful as client-facing data than as a pure database value, so I would also do that (and standardize my PK format), probably paying a performance penalty.

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

#104

Post by pkolaczk »

NavinF wrote: post #51
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…

It often doesn't matter if you use a 128bit int or a string since either way you're loading 4K/8K/16K from ssd/hdd. Database people do all sorts of silly things like storing datetimes as normalized ISO strings (2022-01-08T08:18:20) instead of using 64bit unix time. They get away with it because both the backend (durable storage) and the clients (webapps responding to a user 10ms away) are incredibly slow compared to…

You can fit many more uuids encoded as 128 bit numbers than as strings in a 4K page. Hence you'll need fewer pages to store your data and that might make a difference between fetching from cache vs fetching from disk.

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

#105

Post by erwincoumans »

Dachande663 wrote: post #61
busymom0 wrote: post #58

Slightly related question- does anyone know what data type Reddit uses for their post/comment id? It seems like a short alphanumeric yet unique identifier and much shorter than a uuid. Also what does twitter use for their post/comment id? Seems like some sort of big int?

Twitter created Snowflake many years ago to generate IDs. Unsure whether it’s still in use. https://blog.twitter.com/engineering/en_us/a/2010/announcing...

Twitter uses strings instead of ints to represent the 64 bit UID, because Javascript only supports 53bit ints instead of 64bit... https://developer.twitter.com/en/docs/twitter-ids

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

#106

Post by manigandham »

bowmessage wrote: post #33
nielsole wrote: post #30

Earlier quoted context omitted.

Ugh really? Why would they not hash the whole filename for shard assignment?

Because the file name includes the "directory" prefix, i.e. each file's name stores the `/entire/bucket/dir/tree`, which can get large.

The size of the input doesn't affect the hashing.

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

#108

Post by kyrra »

yeldarb wrote: post #10

Interestingly, for other systems you sometimes want the exact opposite: for your key space to be distributed across indexes to balance the load (vs wanting them all to hit the same “hot” index for MySQL). For example, Google’s Cloud Firestore is bottlenecked to 500 writes/second if your key is monotonically increasing (like a timestamp or these timestamp-based UUIDs) causing you to “hotspot” the index: https://cloud.…

Your other option is to hash your monotonically increasing numbers. You don't want to use something like SHA, as it does not give good distribution. You use something like murmur hash (https://en.m.wikipedia.org/wiki/MurmurHash). I've used it before for indexes at Google for values that may hotspot. See Google's impl here: https://github.com/google/zetasketch/blob/master/java/com/go...

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

#109

Post by dexterdog »

nly wrote: post #71
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.

Not to mention the string serialization is also ugly...

If it's ever being turned into a string and stored or transmitted it's always better to use a standard base64 encode to keep the string to 22 chars instead of the standard 36/38 chars.

You can also use base85 and go to 20, but you get into some funky chars there.

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

#110

Post by damagednoob »

hashimotonomora wrote: post #87
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.

Then it doesn't have the same quality as a UUID which is supposedly guaranteed to be unique across space _and_ time[1].

[1] https://datatracker.ietf.org/doc/html/rfc4122

Post reply on HN