Using uuid type for PostgreSQL?

[archiloque]

PostgreSQL has an UUID type https://www.postgresql.org/docs/current/datatype-uuid.html that is stored as binary but exposed as a string, which means a more efficient storage for data and index with no change in the queries.

Since Teddy use UUID for its identifiers, it would save some storage and makes things more efficient (indexes would use less memory for example).

I understand it's not a big change and it would be a PostgreSQL-specific feature it's bothering me so I wanted to ask.

It seems the com.sismics.util.jpa.DialectUtil facilities could help to implement it in a clean way, like using a uuid type in the SQL files and replace it with varchar(36) on all database except PostgreSQL.

What's your opinion on it? If you think it's a good idea I can try do a PR.

[jendib]

Wouldn't this require a long migration script for users having large dataset?

[archiloque]

@jendib Yes you're right, it depends of the size you expect, on my local machine on a SSD with 3.5 millions records, changing the type of a column took 20 seconds. It means updating all the columns of the database would require a few minutes, including dropping and recreating the related foreign keys.

For our own usage use case I think the tradeoff would be acceptable but I understand that it may not be a good idea for the project.