2

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?

9
  • 1
    The UUID's are stored as a CHAR(36) and ascii charset. Why? In MySQL you may store it as BINARY(16) using BIN_TO_UUID() and UU_D_TO_BIN().
    – Akina
    Nov 2, 2022 at 9:12
  • From the other side I doubt that you'll see the difference in disk space consumption - the difference is 20 bytes only, and the amount of records per block will stay the same or as a maximum it will increase by 1 which won't effect the table body disk file size in practice.
    – Akina
    Nov 2, 2022 at 9:15
  • @Akina I'm using BINARY(16) in other databases, but for this case it's simply not practical. It would require too much work converting all primary keys of that database to BINARY(16). Also, for readability and usability when doing queries I find that it clutters things when I have to do UUID_TO_BIN and BIN_TO_UUID everywhere.
    – sbrattla
    Nov 2, 2022 at 9:46
  • 1
    @J.D. yes, I belive so. The application is based on an ORM (Hibernate), so it does not have much tailor made SQL. I suspect fewer obstacles for migrating than the massive amount of work required to rewrite queries.
    – sbrattla
    Nov 2, 2022 at 15:03
  • 1
    @sbrattla I think you're underestimating the amount of work required to migrate database systems. That's fine that your interface between the application layer and your database layer is an ORM (which makes it just as easy to change data types in your database layer then). But you're still going to have to migrate all of your Tables (and other database objects), queries, and probably re-write some of the queries (since the syntax isn't 100% the same between both database systems). And then your primary keys data types still need to be changed anyway, just as if you did it in your current DB.
    – J.D.
    Nov 2, 2022 at 15:30

1 Answer 1

4

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.

12
  • I'd use a varbinary on MySQL, which should result in a roughly similar index. Nov 2, 2022 at 8:10
  • @LaurenzAlbe for my particular case, going from char to binary would require a lot of application logic to be changed to convert back and forth to binary. The reason I'm looking at Postgres is that it has the uuid data type, and the move from MySQL to Postgres would require few application changes.
    – sbrattla
    Nov 2, 2022 at 8:27
  • 3
    @sbrattla: I just did a simple test and created an index on 50 million UUIDs - the index size is 1.5GB
    – user1822
    Nov 2, 2022 at 8:27
  • @a_horse_with_no_name thank's for doing an actual test!
    – sbrattla
    Nov 2, 2022 at 8:28
  • You does not take into account the granularity. For example, if average row size is 1 kb and block size is 16 kb then the block may contain up to 14 records. If the row size decreases by 20 bytes then the amount of records per block won't change..
    – Akina
    Nov 2, 2022 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.