Correctly Handle UUID/GUID in Database

Universally Unique Identifiers (UUID) as defined by RFC 4122, ISO/IEC 9834–8:2005, looks like below:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

“A UUID is written as a sequence of lower-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.”

UUID/GUID should be treated as a number/raw/binary, instead of a text/char/varchar!

We usually see people store UUID in char(36) or varchar(255), which waste the storage, and more importantly, slowing the performance.

1.Waste of storage using char:

In postgresql, there is type called uuid ( https://www.postgresql.org/docs/current/datatype-uuid.html), it will store uuid using only 128 bits/16 bytes.

select
    pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::text) as text_size,
    pg_column_size('a0eebc999c0b4ef8bb6d6bb9bd380a11'::uuid) as uuid_size;
 text_size | uuid_size 
-----------+-----------
        36 |        16

In mysql, refer to this article to better handle it:

https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

2.Bad performance using char

Indexing on varchar is slower than char, and char is slower than number. ( https://dba.stackexchange.com/questions/137945/indexes-integer-vs-string-performance-if-the-number-of-nodes-is-the-same)

An example showing store uuid using the built-in type improves performance by 100 times than store as char ( https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performance):

-- With text index
    QUERY PLAN
    Index Scan using tmptable_pkey on tmptable (cost=0.41..1024.34 rows=1 width=1797) (actual time=0.183..2.632 rows=1 loops=1)
      Index Cond: (primarykey = '755ad490-9a34-4c9f-8027-45fa37632b04'::text)
    Planning time: 0.121 ms
    Execution time: 2.652 ms

    -- With a uuid index 
    QUERY PLAN
    Index Scan using idx_tmptable on tmptable (cost=0.29..2.51 rows=1 width=1797) (actual time=0.012..0.013 rows=1 loops=1)
      Index Cond: (uuidkey = '755ad490-9a34-4c9f-8027-45fa37632b04'::uuid)
    Planning time: 0.109 ms
    Execution time: 0.029 ms