3 ms·

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 s

by olliej 5y ago

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 not optimise to do multi word comparisons? (e.g. 8byte at a time, then 4, 2, 1, as size requires)

NavinF 5y ago
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 CPUs. That aside, Postgres stores UUIDs as a 128bit int, not as a string, so it almost certainly uses multi-word comparisons via memcmp: https://stackoverflow.com/a/29882952/703382 https://stackoverflow.com/a/29882952/703382
- piaste 5y ago
  > 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 That's super weird. I can't think of a major RDBS that doesn't have a native date/time data type, unless you count SQLite.
- pkolaczk 5y ago
  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.
jrochkind1 5y ago
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.
- paulryanrogers 5y ago
  MySQL doesn't have a UUID type, so naive implementations use (VAR)CHAR. Those in the know use BINARY(16) and MySQL 8 now has helper functions to convert to and from hex. Apparently MariaDB will soon have a native UUID type. PostgreSQL has had them from for years.
jandrewrogers 5y ago
At least in the case of Postgres, I know from reading the source before that it stores UUIDs as 128-bit values and does optimized binary comparator operations on those values. It can export and import UUIDs as text strings but does not treat them as text internally. I'm not sure why any competently engineered database engine would do otherwise.

by olliej 5y ago

NavinF 5y ago

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 CPUs. That aside, Postgres stores UUIDs as a 128bit int, not as a string, so it almost certainly uses multi-word comparisons via memcmp: https://stackoverflow.com/a/29882952/703382 https://stackoverflow.com/a/29882952/703382

piaste 5y ago
> 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 That's super weird. I can't think of a major RDBS that doesn't have a native date/time data type, unless you count SQLite.
pkolaczk 5y ago
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.

jrochkind1 5y ago

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.

paulryanrogers 5y ago
MySQL doesn't have a UUID type, so naive implementations use (VAR)CHAR. Those in the know use BINARY(16) and MySQL 8 now has helper functions to convert to and from hex. Apparently MariaDB will soon have a native UUID type. PostgreSQL has had them from for years.

jandrewrogers 5y ago

At least in the case of Postgres, I know from reading the source before that it stores UUIDs as 128-bit values and does optimized binary comparator operations on those values. It can export and import UUIDs as text strings but does not treat them as text internally. I'm not sure why any competently engineered database engine would do otherwise.