UUID vs ULID: Why, Where, and When?

Choosing the correct unique?identifier is fundamental to programming and data management. Two widely used options are UUID (Universally Unique Identifier) and ULID (Universally Unique Lexicographically Sortable Identifier). This article delves into the advantages, disadvantages, and use cases, providing examples of their implementation in popular databases such as MySQL, MariaDB, PostgreSQL, SQL Server, and Oracle. Furthermore, we explore each identifier's history and generation algorithms, comprehensively understanding their functionality.

UUID (Universally Unique Identifier)

History and Algorithm:

UUIDs are standardized by the Open Software Foundation (OSF) and are defined in RFC 4122. The most commonly used version is UUIDv4, which is randomly generated. UUIDs are 128-bit numbers, typically represented as 32 hexadecimal characters, separated by hyphens.

Advantages:

1. Global Uniqueness: UUIDs ensure that identifiers are globally unique.

2. Distribution Independence: They can be generated without a central coordination point.

3. Wide Support: Most databases and programming languages natively support UUIDs.

Disadvantages:

1. Large Size: UUIDs are 128 bits, more significant than other identifiers like integers.

2. Lack of Ordering: UUIDs are not naturally sortable, which can cause issues with indexing and searching.

3. Performance Impact: The large size of UUIDs can affect database performance.

ULID (Universally Unique Lexicographically Sortable Identifier)

History and Algorithm:

ULIDs were introduced by Alizain Feerasta in 2016 to address some of the shortcomings of UUIDs. ULIDs are 128-bit values, with the first 48 bits representing the timestamp (in milliseconds since the Unix epoch) and the remaining 80 bits being random. This makes them lexicographically sortable.

Advantages:

1. Uniqueness and Ordering: ULIDs are unique and lexicographically sortable, which can significantly enhance indexing and searching efficiency, making them a potential game-changer for your systems.

2. Human Readability: ULIDs are more readable compared to UUIDs.

3. Smaller Size: ULIDs typically use less storage space than UUIDs.

Disadvantages:

1. Limited Implementation: ULIDs have less widespread support compared to UUIDs.

2. Dependency on Libraries: Generating ULIDs often requires additional libraries.

Use Cases and Recommendations

UUID

- Global Uniqueness Required: Ideal for distributed systems where global uniqueness is critical.

- Native Database Support: Best suited for environments where native support for UUIDs is essential.

ULID

- Sorting is Important: Suitable for systems that benefit from sortable identifiers.

- Human Readability: Useful in scenarios where identifiers need to be read by users.

Implementation in Databases

MySQL and MariaDB

Using UUID:

CREATE TABLE example_table (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO example_table (id, name) VALUES (UUID(), 'Example Name');        

Using ULID:

DELIMITER $$

CREATE FUNCTION generate_ulid() RETURNS CHAR(26)
BEGIN
    DECLARE current_time CHAR(10);
    DECLARE random_part CHAR(16);
    SET current_time = LPAD(CONV(UNIX_TIMESTAMP(NOW(3)) * 1000, 10, 32), 10, '0');
    SET random_part = SUBSTRING(REPLACE(UUID(), '-', ''), 1, 16);
    RETURN CONCAT(current_time, random_part);
END$$

DELIMITER ;        

PostgreSQL

Using UUID:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE example_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL
);

INSERT INTO example_table (name) VALUES ('Example Name');        

Using ULID:

CREATE OR REPLACE FUNCTION generate_ulid() RETURNS TEXT AS $$
DECLARE
    current_time TEXT;
    random_part TEXT;
BEGIN
    current_time := LPAD(TO_CHAR(EXTRACT(EPOCH FROM NOW()) * 1000, 'FM999999999999999'), 10, '0');
    random_part := SUBSTRING(REPLACE(gen_random_uuid()::TEXT, '-', ''), 1, 16);
    RETURN current_time || random_part;
END;
$$ LANGUAGE plpgsql;

-- Make sure to enable the pgcrypto extension for gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pgcrypto;        

SQL Server

Using UUID:

CREATE TABLE example_table (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    name NVARCHAR(255) NOT NULL
);

INSERT INTO example_table (name) VALUES ('Example Name');        

Using ULID:

In SQL Server, you can create a CLR function to generate ULIDs. Here is an example in C#:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ULIDGenerator
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString GenerateULID()
    {
        var timestamp = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds().ToString("X").PadLeft(10, '0');
        var randomBytes = new byte[10];
        using (var rng = new System.Security.Cryptography.RNGCryptoServiceProvider())
        {
            rng.GetBytes(randomBytes);
        }
        var randomPart = BitConverter.ToString(randomBytes).Replace("-", "").Substring(0, 16);
        return new SqlString(timestamp + randomPart);
    }
}        

Register the CLR function in SQL Server:

-- Enable CLR integration
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- Create the assembly
CREATE ASSEMBLY ULIDGenerator FROM 'C:\Path\To\ULIDGenerator.dll' WITH PERMISSION_SET = SAFE;

-- Create the function
CREATE FUNCTION dbo.GenerateULID() RETURNS NVARCHAR(26)
AS EXTERNAL NAME ULIDGenerator.[ULIDGenerator].GenerateULID;
        

Oracle

Using UUID:

CREATE TABLE example_table (
    id VARCHAR2(36) PRIMARY KEY,
    name VARCHAR2(255) NOT NULL
);

INSERT INTO example_table (id, name) VALUES (SYS_GUID(), 'Example Name');        

Using ULID:

CREATE OR REPLACE PACKAGE ulid_pkg AS
    FUNCTION generate_ulid RETURN VARCHAR2;
END ulid_pkg;
/

CREATE OR REPLACE PACKAGE BODY ulid_pkg AS
    FUNCTION generate_ulid RETURN VARCHAR2 IS
        current_time VARCHAR2(10);
        random_part VARCHAR2(16);
    BEGIN
        SELECT LPAD(TO_CHAR(SYSTIMESTAMP, 'FF3'), 10, '0') INTO current_time FROM DUAL;
        SELECT SUBSTR(TRANSLATE(DBMS_RANDOM.STRING('X', 16), '0123456789ABCDEF', '0123456789ABCDEF'), 1, 16) INTO random_part FROM DUAL;
        RETURN current_time || random_part;
    END generate_ulid;
END ulid_pkg;
/        

Conclusion

Choosing between UUID and ULID depends on your project's specific needs. If global uniqueness and native support are crucial, UUID is appropriate. If sorting and readability are priorities, ULID is a better option. Rest assured, by implementing custom functions in your database, you can easily leverage the benefits of ULIDs and enhance the performance and usability of your systems.

Important Note

The functions provided in this article for generating ULIDs are basic implementations and might not be the most optimized solutions for every use case. Depending on the requirements and constraints of your project, further optimization and testing may be necessary to ensure the best performance.