The UUID in database resource collection

[kitsunet]

Is there an existing issue for this topic?

• I have searched the existing issues

Description

As we use them quite extensively lets talk about UUIDs in databases, really something we should have done way earlier.

This is not (yet) a nice prosa solution text but just a collection of thoughts leading to a direction .
I'll also add some resource links below, if you want to read into it yourself and get some ideas about the considerations.

Smaller values are always better as it reduces index size and storage need. Therefore it's also important that if we use UUID we make that mandatory if at all possible, so it's not just "a string" that by default is a UUID but it is always without fail a UUID (or lets say a 16byte value).
A monotonous int column is still king in terms of performance, but we know the drawbacks.

Correctly store in the smallest possible way, that is a BINARY(16) column: UNHEX(REPLACE(UUID(), "-","")) (or respective PHP hex2bin(str_replace('-', '', $uuid))

Ensure monotonous increase of the bits, see below for UUID v7, alternatively using tricks from second percona article (hardcode first part of the UUID to a timestamp value) or use the v1 and reorder it so that the timestamp part comes first.

MySql 8 added some helper methods to convert the well known UUID string representation into binary in one go and with good performance apparently (and transform vice versa as well).
MariaDB is introducing a special UUID field type that accepts various input formats and just stores in best manner.

https://www.percona.com/blog/uuids-are-popular-but-bad-for-performance-lets-discuss/
https://www.percona.com/blog/uuid-generated-columns/ (linked in the one above)
https://uuid.ramsey.dev/en/stable/database.html
https://uuid.ramsey.dev/en/stable/rfc4122/version7.html#rfc4122-version7 (linked in the one above)

https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439 (good overview but probably nothing new if you look at the others)

Possible Solution

No response

[kitsunet]

 @kdambekalns for your entertainment. Maybe add your "senf" if you feel like it. Nothing to do here besides discuss for now.