Always use the right UUID in MariaDB
The following post was written by Stefano Petrilli, who contributed UUIDv4 and UUIDv7 implementations to MariaDB. Thank you, Stefano!
The original version of the Universal Unique IDentifiers (UUID), which is now known as UUIDv1, made his first appearance in the 1980s. The most interesting guarantee that they provide is the generation of IDs that are always unique across space and time.
To comply with this promise, it uses a combination of three elements:
- The node, which is a field that identifies the machine that generated the UUID. This field is historically set using the IEEE MAC address. The Mac address is in itself a unique identifier assigned to any physical network interface controller. These represent the first 48 bits of UUIDv1
- The Timestamp, which counts the number of 100-nanosecond intervals between the moment when the UUIDv1 is being generated and October 15, 1582 (the beginning of the Gregorian calendar).
- A clock field which is used to disambiguate UUIDv1’s which are generated on the same Timestamp or if the clock is set backwards.
Plus, it sets 6 bits to a specific value to identify that it’s a UUIDv1.
The way an UUIDv1 is composed ensures space-time uniqueness which means that if the UUIDv1 are generated according to the specification, it is virtually impossible to generate the same UUIDv1 twice. In practice this means, for instance, that when using UUIDv1 as primary keys, an arbitrary number of servers can add records to the same table without the need to coordinate to avoid collision. MariaDB makes available a function to generate this kind of UUID, which is uuid() .
While in theory the UUIDv1 implementation is sound, when it clashes with the real world complexity it ends up showing some limitations.
The most obvious is the requirement of having an IEEE MAC address. For devices that have no network interface and with the advent of virtualization and containerization, disposing of a physical MAC address cannot be given for granted. The solution proposed in the latest UUID RFC and actually used in MariaDB’s implementation of UUIDv1 is to randomly generate a MAC address. As the spatial uniqueness component of UUIDv1 is difficult to enforce in modern times, we are only left with the time uniqueness, which means that in practice a UUIDv1 collision in the wild is possible albeit the chance is so low that we can still pretend that any generated UUIDv1 is unique.
A more significant problem is posed by the leakage of two sensitive information with the UUIDv1: the MAC address of the machine that generated the UUIDv1 and the timestamp when it was generated. The MAC address opens the door to several vulnerabilities such as MAC Spoofing and MAC flooding while the timestamp, when associated with specific events or information, might reveal sensitive info about a user or a system. In addition to this, UUIDv1 is predictable to a certain extent. This predictability can also be exploited in attacks such as the Sandwich Attacks.
Mainly as the guarantee that no collision is ever going to happen cannot be hold in modern environments and for the security risks mentioned before, the UUIDv1 is considered legacy and new versions have been proposed which overcome their limitations. To receipt these changes, I opened two pull requests that started the work that ultimately made available UUIDv4 and UUIDv7 in MariaDB. The functions used to generate the two new UUID kinds are uuid_v4() and uuid_v7() .
The UUIDv4 is a solution to the security problems posed by disclosing the MAC address and the creation timestamp. As all the other versions, also this version sets 6 bits to the specific version and revision, but then randomly generates all the remaining 122 bits.
When using UUIDv4 a collision is still possible , but assuming a well seeded random number generator, the probability of generating twice the same 122 bits sequence is so low that we can just assume that a collision is never going to happen. The number of different elements that can be created is the astronomical number of 2122 unique UUIDv4. It’s difficult for human beings to understand such a big number, but, to give a perception of how big it is, if we assign a UUIDv4 to each grain of sand on earth we would not even be close to using 0.01% of the total number of UUIDv4.
The UUIDv4 represents a great improvement over UUIDv1, but, it’s not a silver bullet. The main problem that UUIDv4 shares with UUIDv1 is that the generated UUIDs are randomly distributed and not naturally sorted. In the context of databases, this can have a hit on the performances in several contexts. Generally, the data structures used internally in databases do not deal good with random insertions, which results in non-contiguous memory accesses and potentially increased disk I/O. In addition to this, some instructions internally rely on natural order, starting from a position where natural order degrades performances. This can affect the performances of Range Queries, Window Functions, and modifiers like ORDER BY and GROUP BY.
In the use cases where leaking the timestamp does not represent a vulnerability problem, like in the cases the UUIDs are only used internally and never exposed to the users, we can get natural order and therefore performances by using UUIDv7.
As it’s possible to read in the RFC, UUIDv7 can be generated in three different way. The implementation that we pursued in MariaDB is the version 3 in which: the first 60 bits of UUIDv7 are populated using the Unix Timestamp with a sub millisecond precision, 6 bits are reserved to specify the version and revision of the UUID and the remaining 62 bits are filled with random data.
In conclusion, the newly proposed UUID versions, which can be generated using the functions uuid_v4() and uuid_v7() , are superior to the legacy UUIDv1, which should be avoided due to its limitations. For use cases prioritizing performance where UUIDs are not externally exposed, UUIDv7 is the recommended choice. When security or preventing private data exposure is critical, UUIDv4 is preferable. Starting with MariaDB 11.7, both UUIDv4 and UUIDv7 are available alongside UUIDv1, enabling you to select the best version for your specific use case.