What is the performance hit of using a string type vs a uuid type for a UUID primary key?

Question

Is there much of a speed difference for index lookups by using string for the primary key versus the actual uuid type, specifically if the string has a prefix like user-94a942de-05d3-481c-9e0c-da319eb69206 (making the lookup have to traverse 5-6 characters before getting to something unique)?

Answer 1

This is a micro-optimization and is unlikely to cause a real performance problem until you get to enormous scales. Use the key that best fits your design. That said, here's the details...

UUID is a built in PostgreSQL type. It's basically a 128 bit integer. It should perform as an index just as well as any other large integer. Postgres has no built in UUID generating function. You can install various modules to do it on the database, or you can do it on the client. Generating the UUID on the client distributes the extra work (not much extra work) away from the server.

MySQL does not have a built in UUID type. Instead there's a UUID function which can generate a UUID as a string of hex numbers. Because it's a string, UUID keys may have a performance and storage hit. It may also interfere with replication.

The string UUID will be longer; hex characters only encode 4 bits of data per byte so a hex string UUID needs 256 bits to store 128 bits of information. This means more storage and memory per column which can impact performance.

Normally this would mean comparisons are twice as long, since the key being compared is twice as long. However, UUIDs are normally unique in the first few bytes, so the whole UUID does not need to be compared to know they're different. Long story short: comparing string vs binary UUIDs shouldn't cause a noticeable performance difference in a real application... though the fact that MySQL UUIDs are UTF8 encoded might add cost.

Using UUIDs on PostgreSQL is fine, it's a built-in type. MySQL's implementation of UUID keys is pretty incomplete, I'd steer away from it. Steer away from MySQL while you're at it.