ULID Generator Tool

ULID: The Engineer's Guide to Sortable Identifiers

If you've ever had to troubleshoot a production database where write performance plummeted as the dataset grew, you've likely encountered the "index fragmentation" problem. For years, we've been forced to choose between auto-incrementing integers (which are a nightmare for distributed systems) and UUIDv4 (which are a nightmare for B-Tree indexes).

ULIDs (Universally Unique Lexicographically Sortable Identifiers) were designed to end this trade-off. This guide is for the developers, DBAs, and system architects who need global uniqueness without sacrificing database throughput.

Who uses it?

ULIDs are primarily used by Backend Engineers, Distributed Systems Architects, and Database Administrators (DBAs).

• Backend Engineers use them when they need to generate IDs on the application server rather than waiting for a database sequence. This allows for a decoupled architecture where the app doesn't have to perform a round-trip to the DB just to get a primary key.
• System Architects implement them in event-sourcing patterns or distributed logs. Because ULIDs are time-ordered, the ID itself acts as a chronological marker, making it easier to replay events in the correct sequence.
• DBAs advocate for ULIDs because they prevent "page splits" in clustered indexes. Instead of inserting random values into the middle of a B-Tree, ULIDs are always appended to the end, keeping the physical storage contiguous.
• SDETs and Testers use them to generate unique, time-stamped test data. This makes debugging distributed traces much easier because the IDs naturally sort by the time they were generated.

How is it used?

In a professional implementation, the ULID is generated at the application layer before the record ever hits the persistence layer.

The technical workflow looks like this:

1. Generation: The library captures the current Unix timestamp in milliseconds (48 bits) and appends a cryptographically secure random string (80 bits).
2. Encoding: To make it human-readable, it's encoded using Crockford's Base32. This turns the 128-bit binary into a 26-character string (e.g., 01AN4Z0C6P5XG3C8K8P7N6M4S2). This encoding is a huge win for developers because it excludes ambiguous characters like I, L, O, and U.
3. Storage: While the string representation is great for logs, the professional move is to store them as BINARY(16) or the native UUID type in the database to minimize storage overhead and maximize index speed.

Is it suitable for databases?

Short answer: Yes, it is specifically engineered for this.

The core benefit of the ULID is its monotonicity. In a relational database, the primary key is usually the clustered index. When you use a random ID (like UUIDv4), the database is forced to insert rows into random pages on the disk. ULIDs, however, are inserted sequentially.

Here is how it maps across the major engines:

• PostgreSQL: Excellent. Use the UUID data type. Since ULIDs are 128-bit, they fit perfectly into the native UUID slot, and the sortability keeps the B-Tree lean.
• MySQL: Highly recommended. Use BINARY(16). Using a ULID as a primary key in InnoDB prevents the massive page fragmentation that kills performance in high-write environments.
• SQL Server: Very suitable. Use UNIQUEIDENTIFIER. It behaves similarly to NEWSEQUENTIALID() but can be generated on the app server rather than inside the DB engine.
• Oracle: Use RAW(16). It provides the same clustering benefits as other sequential keys.
• SQLite: Use BLOB. The sequential nature ensures that inserts remain fast even as the database grows to millions of rows.
• Amazon RDS / Azure SQL / Google Cloud SQL: Since these are managed versions of the engines above, the same physics apply. In cloud environments, where you pay for I/O and storage throughput, reducing index fragmentation directly reduces your monthly bill and your latency.

When was it created, why, and where is it defined?

ULIDs were created to fill a gap in the UUID specifications. While the original UUID standards provided uniqueness, they didn't provide a consistent, sortable format that worked well with database indexes without leaking sensitive hardware information (like the MAC address in UUIDv1).

The ULID is defined as an open specification. The goal was to create a 128-bit identifier that is globally unique, case-insensitive, and—most importantly—lexicographically sortable. By defining a strict split between a 48-bit timestamp and an 80-bit random component, it ensures that IDs generated a millisecond apart are numerically adjacent.

Why was it created?

The primary driver was the "Index Fragmentation" problem.

In any B-Tree based index, inserting random values forces the database to move existing data to make room for new entries (page splits). This results in:

1. Bloated Indexes: Pages are left partially empty, increasing the overall storage footprint.
2. Disk Thrashing: The database performs random I/O instead of sequential writes.
3. Write Bottlenecks: As the table grows, the cost of these page splits increases, and insert performance drops off a cliff.

ULIDs were created to give us the "best of both worlds": the distributed generation and collision resistance of a UUID, and the write performance of an auto-incrementing integer.

Exactly how many bits are random, and why not all 128?

A ULID consists of exactly 128 bits.

• 48 bits are dedicated to the timestamp (milliseconds since the Unix epoch).
• 80 bits are used for randomness.

Why not all 128? If all 128 bits were random, you would have a UUIDv4. As we've discussed, total randomness destroys database locality. By dedicating the first 48 bits to the timestamp, we ensure that new IDs are always greater than previous ones.

Is 80 bits of randomness enough? Absolutely. Even if you are generating thousands of IDs per millisecond across a hundred different servers, the probability of a collision is statistically zero for almost any practical application. You get the sortability of a sequence without the collision risks of smaller integer IDs.

Where is it appropriate (or not) to use a UUIDv4?

With the availability of ULIDs and UUIDv7, you should be very intentional about when you choose UUIDv4.

Appropriate use of UUIDv4:

• Security-Sensitive Tokens: If you don't want a user to be able to derive the creation time of a record from its ID, use v4. ULIDs leak the exact millisecond a record was created.
• Non-Indexed Fields: If the ID is stored in a field that is never used as a primary key or sorted (e.g., a metadata tag), v4 is perfectly fine.
• Pure Anonymity: When the chronological order of generation must be completely hidden for privacy or security reasons.

Inappropriate use of UUIDv4:

• Clustered Primary Keys: Never use v4 as a clustered index in a high-volume SQL database. It is a textbook performance anti-pattern.
• Time-Series Data: If you find yourself adding a separate created_at column and indexing it just to sort your records, you should have used a ULID.

ULID vs UUIDv7

This is the most common question for developers today. Both are 128-bit, both use a timestamp, and both are sortable.

The main difference is Standardization vs. Representation.

• UUIDv7 is an official RFC standard. It uses the traditional hexadecimal representation (36 characters with hyphens). If your project requires strict adherence to RFC standards or you are using a language with native UUID support, v7 is the way to go.
• ULID uses Crockford's Base32 encoding. This makes it shorter (26 characters) and more human-friendly. It is not an RFC standard, but it is a widely accepted specification.

If you need the "Standard" version, go with UUIDv7. If you want a "URL-friendly" version that is easier for humans to read and copy, go with ULID.

Is it suitable for URLs?

Yes, it is ideal.

Because ULIDs use Base32 encoding, they are:

1. Compact: 26 characters is significantly shorter than the 36 characters of a standard UUID.
2. URL-Safe: No special characters that require percent-encoding.
3. Case-Insensitive: This prevents the classic bug where ID-Abc and id-abc are treated as different records.
4. Clean: They look a lot more professional in a browser address bar than a long string of hex.

Is it sortable?

Yes. This is the "S" in ULID. They are lexicographically sortable.

This means that if you sort them as strings (using the Base32 representation) or as binary bytes, they will be in the exact order they were created. This allows you to perform range queries extremely efficiently. You can find all records created between two specific times by simply querying the ID range, eliminating the need for a separate timestamp index.

Comparison to other UUID versions and identifiers

The landscape of identifiers is a trade-off between entropy, sortability, and standard compliance.

• UUIDv1: Time-based but leaks the MAC address. It's sortable, but the security risk is too high for modern web apps.
• UUIDv3 & v5: Name-based/Hashed. These are deterministic. They aren't for primary keys; they are for creating stable aliases.
• UUIDv6: An attempt to make v1 sortable. It's better, but ULID's encoding is more practical.
• UUIDv7: The new RFC standard. Very similar to ULID.
• NanoID: A random string. Great for URLs, but like UUIDv4, it causes massive database fragmentation if used as a primary key.

Comparison Table

Final Engineering Takeaway

If you are building a distributed system and need a primary key that doesn't require a central coordinator but also doesn't kill your database performance, ULID (or UUIDv7) is the only correct choice.

Stop using UUIDv4 for primary keys in relational databases. The cost of index fragmentation is simply too high, and the benefits of random IDs are negligible compared to the performance gains of a time-sorted identifier. Use ULIDs for your keys, store them as binary, and enjoy your write throughput.

What is the ULID Text Layout?

What is the ULID Binary Bit Layout?