UUIDs are popular, but bad for performance (2019)

Post by dllthomas » Sat, Jan 08, 2022, 5:31 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…

Post by jandrewrogers » Sat, Jan 08, 2022, 5:36 PM UTC

kgeist wrote: post #52

jcelerier wrote: post #42

Earlier quoted context omitted.

what's pretty large for you though ? There are fields where 100k entries is a pretty large dataset and others where "large" starts at petabyte

I didn't mention that our DB setup uses sharding, and every tenant has their own DB shard (there are tens of thousands of shards). I just checked that one of the largest tenants has 2.2 mln rows in one of the affected tables, which is usually joined with 2-4 more related tables using UUIDs (another such table is 1.1 mln rows, for example), and they're on the hot code path because it's the core of the system. Maybe wi…

Post by nchi3 » Sat, Jan 08, 2022, 5:40 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.

Post by jandrewrogers » Sat, Jan 08, 2022, 5:45 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…

Post by et-al » Sat, Jan 08, 2022, 6:03 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.