27 ms·

Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128-bit value as opposed to a text field; or if you're

by grzm 9y ago

Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128-bit value as opposed to a text field; or if you're just speaking of applications that use a database where native UUID is not available. If the latter, feel free to ignore the rest of this :) For comparison in storage: select pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::uuid) AS uuid_bytes, pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::text) AS text_bytes; uuid_bytes | text_bytes ------------+------------ 16 | 40 (1 row) As 'craigkerstiens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. If you're using the UUID type to encode information that you may want to access subfields of, you could create function indexes to do so. Otherwise, I think indexing on the native UUID type is going to be better than a collection of narrower fields and indexes. Edit: Spell 'craigkerstiens correctly.

kbenson 9y ago
> Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128bit value as opposed to a text field I was speaking towards the idea that some people throw UUIDs into text fields, as implied by the parent;s clarification that they were not doing that. For example, in years past when that type may not have existed yet. People have been talking about "just use a uuid for the primary key" for at least around a decade now. > As 'craigkierstens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. I would assume so! Any database that implemented an actual 128 bit numeric type (no matter if it's converted to hexidecimal notation for display, ints aren't stored in decimal either) as a text field would deserve quite a bit of ridicule, in my opinion. > If you're using the UUID type to encode information that you may want to access subfields of And that answers a question I raised somewhat implicitly in my other comment, which is why use a UUID in the first place. Because you can encode specific sub type information into the parts. Thanks! Edit: Forgot some words in my paragraph that mentioned ridicule, so I added them to make sure it was obvious what and why I thought they would deserve some ridicule (which is actually a bit stronger than how I generally like to come across).

by grzm 9y ago

kbenson 9y ago

> Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128bit value as opposed to a text field I was speaking towards the idea that some people throw UUIDs into text fields, as implied by the parent;s clarification that they were not doing that. For example, in years past when that type may not have existed yet. People have been talking about "just use a uuid for the primary key" for at least around a decade now. > As 'craigkierstens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. I would assume so! Any database that implemented an actual 128 bit numeric type (no matter if it's converted to hexidecimal notation for display, ints aren't stored in decimal either) as a text field would deserve quite a bit of ridicule, in my opinion. > If you're using the UUID type to encode information that you may want to access subfields of And that answers a question I raised somewhat implicitly in my other comment, which is why use a UUID in the first place. Because you can encode specific sub type information into the parts. Thanks! Edit: Forgot some words in my paragraph that mentioned ridicule, so I added them to make sure it was obvious what and why I thought they would deserve some ridicule (which is actually a bit stronger than how I generally like to come across).