Will an index on a uuid column be smaller in Postgres than MySQL / InnoDB?

Question

I've got a MySQL database which uses UUID (v4) as identifier for all entities. The UUID's are stored as a CHAR(36) and ascii charset.

For one table with about 50.000.000 rows, the index on the UUID columns takes up about 5.5GB.

It is my understanding that Postgres handles UUID's better (performance wise) than MySQL / InnoDB because of the builtin uuid data type in Postgres.

Does Postgres store UUIDs in a more optimized way than MySQL, and should I expect the UUID index to take up less space than it does in MySQL / InnoDB?

Answer 1

should I expect the UUID index to take up less space than it does in MySQL / InnoDB?

Most certainly yes. It's simple math: char(36) takes up 36 bytes to store a UUID.

Postgres' uuid type is stored in 16 bytes, so storing that in an index saves 20 bytes per entry (ignoring alignment). 20 bytes times 50 million is a bit under 1 GB.

I would also assume that comparing uuid values is faster than comparing char(36) values.

---

Edit:

An index on 50 million UUIDs seems to take only 1.5 GB in Postgres:

create table the_table (id uuid);
insert into the_table
select gen_random_uuid()
from generate_series(1,50e6);

create index ix_uid on the_table(id);

select pg_size_pretty(pg_relation_size('ix_uid'))

The above yields 1504 MB using Postgres 15 (Windows 10)

So apparently there is more optimization involved than "just" the 20 bytes per row.