The Problem with Randomness

If you’re building a modern SaaS application, you likely made the correct decision to avoid auto-incrementing integers for your primary keys. Auto-increment IDs leak business intelligence (e.g., how many users signed up today) and make distributed database sharding nearly impossible.

The industry-standard solution for years has been the UUIDv4. It’s 128 bits of pure, unpredictable randomness. It guarantees uniqueness across distributed systems without coordination.

It also quietly murders your database’s write performance.

B-Trees Hate Randomness

Most relational databases (PostgreSQL, MySQL, SQL Server) organize their primary key indexes using B-Trees. B-Trees are extremely efficient for lookups because they keep data physically ordered.

When you insert a new row with a sequentially increasing ID (like an auto-increment integer or a timestamp), the database simply appends the new index entry to the right-most edge of the B-Tree. This operation is blazing fast and requires very little I/O.

However, when you use a UUIDv4, the IDs are completely random.

Row 1: f47ac10b-58cc-4372-a567-0e02b2c3d479
Row 2: 3f9121a5-3b1a-493f-8a03-75b1c5e2d694
Row 3: 88b4885a-0d65-4f47-a4c3-1f19d2b45013

When inserting a random UUID, the database must traverse the entire B-Tree to find the correct, random leaf node to insert the new entry. As your table grows into millions of rows, the index no longer fits entirely in memory (RAM).

This forces the database to perform expensive disk reads to load the correct index page, insert the UUID, and then potentially split the index page if it’s full (a process known as page splitting). This causes massive index fragmentation and write amplification. At scale, your insert throughput will fall off a cliff.

The Solution: Time-Sorted Identifiers

We still want the distributed generation benefits of UUIDs, but we need the sequential, append-only insertion characteristics of auto-incrementing integers.

The solution is to use time-sorted identifiers. Instead of 128 bits of pure randomness, these identifiers use the first ~48 bits to store a Unix timestamp, and the remaining bits for randomness to ensure collision resistance.

Enter UUIDv7 and ULID

“A good primary key should be globally unique, unguessable, and lexicographically sortable.”

Two popular standards have emerged to solve this:

1. UUIDv7: The newly ratified standard that brings time-sorting to the classic UUID format. It looks exactly like a standard UUID, meaning it is instantly compatible with native UUID column types in databases like PostgreSQL.
2. ULID (Universally Unique Lexicographically Sortable Identifier): Represented as a 26-character Base32 string (e.g., 01ARZ3NDEKTSV4RRFFQ69G5FAV). It is slightly shorter than a UUID string and URL-safe.

// Generating a ULID in TypeScript
import { ulid } from 'ulid';

const userId = ulid(); 
// Result: 01HGWV... (Always sorts chronologically!)

Designing Better Schemas

When using Sepio to design your database, we strongly recommend setting your primary key defaults to generate UUIDv7s or ULIDs at the application layer.

By making this one simple architectural change early on, you can ensure your database write performance remains consistently fast, even as your tables grow into the billions of rows. Stop relying on pure randomness, and start sorting your data by time.