We have a distributed system which consists of an Identify Provider (idP) which handles user authentication and authorization. The idP associates a UUID with a user and issues JWT Access Tokens which contain the UUID as a JWT claim. We also have a MySQL database (DB) which stores user meta-data. The UUID used to identify the User by the idP is stored in the DB, this allows us to establish a connection between the idP user and their DB meta-data.

Note. The UUID extracted from the JWT Access Token is used to lookup a user in the DB, and I am unable to add any additional claims to the JWT Access Token.

My question is:

I have read numerous resources which say that UUIDs are fairly ideal for distributed systems but have a number of downsides. It seems indexing, page fragmentation and the additional storage required to store (even in BINARY(16)) are the largest issues (please correct me if I’m wrong).

A Users ID in the DB is referenced heavily by other tables. From my understanding, using an INT AUTO_INCREMENT reduces/removes much of the issues associated with using a UUID. However, I am wondering if using an INT as a Primary Key (PK) and an indexed UUID will overcome the page fragmentation issue? Although, this will require me to store both the user UUID in every referenced table (I believe).

This would mean tables which reference the user table would look like this:

CREATE TABLE user (
id_user INT NOT NULL,
uuid_user BINARY(16) NOT NULL,
PRIMARY KEY (id_user),
UNIQUE (uuid_user));
 
CREATE TABLE posts (
id_post INT NOT NULL  AUTO_INCREMENT,
id_user INT NOT NULL,
uuid_user BINARY(16) NOT NULL,
title VARCHAR(128) NOT NULL,
description VARCHAR(128) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_user) REFERENCES (user) id_user ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (uuid_user) REFERENCES (user) uuid_user ON UPDATE CASCADE ON DELETE CASCADE);

If any legend out there could help me, it would be immensely appreciated!!!