What are Primary Key?
- Values that uniquely identify each row in a database table (unique, non-nullable)
- Choosing a primary key format influences the system’s performance, scalability, and data integrty
What are UUIDs?
- UUID: Universally Unique Identifier
- a 128 bit value defined by the RFC 4122 standard
- Most of the relational databases suppoert UUID type such as Oracle, SQL Server, PostgreSQL, MySQL
- If a database doesn’t support UUID type, we can define it as
BINARY(16)type
- If a database doesn’t support UUID type, we can define it as
Characteristics of UUID as Primary Key
Uniqueness
- UUIDs are globally unique across tables, databases, and systems
- With this, it can be guaranteed that collision of UUIDs will not happen in distributed systems. There is no need to have “a central coordination system for managing the uniqueness of the primary keys”. This provides “the ability to share the data across distributed systems”.
- UUIDs provide additional security due to the fact that it is difficult to predict the next UUID value.
- It is “almost impossible for a malicious user to guess the ID”
- It is also possible to use UUID as part of the URL path since it does not expose information about business data
Generating the UUID
- UUID “can be generated by the application or the database system itself”
- Potential downside: “We can get the actual value of the new record only after executing the insert statement”
On the contrary, we can generate the UUID in the code by ourselves instead of telling the database to generate it on our behalf. Since the UUID is unique and isn’t sequential, we don’t have to worry about the previous values. Therefore, we can have the primary key value immediately. We don’t need to wait until the insert query is executed.
- Potential downside: “We can get the actual value of the new record only after executing the insert statement”
Memory Usage
The UUID is 128-bit long, which is double the size of a BIGINT and four times the size of the INTEGER types In relational databases, we usually use a BIGINT to store numerical identifiers, and having UUID instead may not make a big difference since it is only twice bigger. However, having a big primary key may result in performance issues, especially when it comes to select queries and indexing.
Readability
- “The UUID consists of 32 hex digits separated by four dashes, which makes it quite challenging to remember”
Sorting
- It is not possible to sort the UUID values in the natural order.
Because of this limitation, we might be forced to use another column, for instance, creating timestamp, to get ordered items. Consequently, this might increase the execution time of a query.