The Complete Guide to Choosing an Identifier Format
Choosing the wrong identifier format is one of the most common and costly architectural mistakes in software development. A decision made early — often without much thought — can haunt a system for years in the form of slow queries, index fragmentation, security vulnerabilities, or painful migrations. This guide covers every major format in depth so you can make the right choice the first time.
Why Identifier Format Matters More Than You Think
In a small application with a few thousand records, the choice between UUID v4 and UUID v7 is irrelevant. But as systems scale to millions or billions of records, the identifier format becomes a critical performance variable. The primary key of a database table is indexed — and the structure of that index determines how efficiently the database can insert, update, and query records.
B-tree indexes — used by PostgreSQL, MySQL InnoDB, SQL Server, and SQLite — are optimized for sequential inserts. When new records always have a larger key than existing records, they are appended to the rightmost leaf page of the tree. Pages fill sequentially, splits are rare, and the index stays compact. This is how auto-incrementing integers work, and it is why they have historically been the default choice for primary keys.
UUID v4 breaks this pattern completely. Because v4 is fully random, each new record is inserted at a random position in the B-tree. When a page is full and a new record needs to be inserted in the middle, the database must split the page — copying half the records to a new page and updating parent pointers. At scale, this causes significant write amplification, index fragmentation, and degraded performance. UUID v7 and ULID solve this problem by embedding a timestamp prefix, restoring the sequential insert behavior while maintaining global uniqueness.
UUID v4: The Universal Default
UUID v4 is the most widely deployed identifier format in the world. Its 122 bits of cryptographic randomness make it effectively impossible to guess or collide. It is supported natively in every major programming language, database, and cloud platform. For most applications — especially those with fewer than 10 million records — UUID v4 is a perfectly reasonable choice.
The security properties of UUID v4 are particularly valuable for public-facing identifiers. Unlike sequential integers, a UUID v4 cannot be enumerated. A user who knows their own ID (550e8400-e29b-41d4-a716-446655440000) cannot derive another user's ID by incrementing a number. This prevents Insecure Direct Object Reference (IDOR) attacks at the identifier level.
The main limitation of UUID v4 is database performance at scale. For tables with more than 50 million rows where INSERT throughput is critical, the random insertion pattern causes measurable degradation. In these cases, UUID v7 is the recommended upgrade path.
UUID v7: The Modern Standard for Databases
UUID v7, ratified in RFC 9562 in May 2024, is the most significant advancement in identifier standards in nearly two decades. It combines a 48-bit Unix millisecond timestamp with 74 bits of randomness, producing an identifier that is both globally unique and lexicographically sortable by creation time.
The performance implications are substantial. In benchmarks on PostgreSQL with 100 million rows, UUID v7 achieves 2–5x better INSERT throughput compared to UUID v4, with 80–90% less index bloat. The reason is simple: because the timestamp prefix increases monotonically, new records are always appended to the end of the B-tree index. Pages fill sequentially, splits are rare, and the index remains compact and cache-friendly.
UUID v7 also makes it trivial to extract the creation time from an identifier — the first 12 hex characters encode the Unix timestamp in milliseconds. This eliminates the need for a separate created_at column for basic time queries, reducing schema complexity. For any new system using a relational database, UUID v7 should be the default choice for primary keys.
ULID: The URL-Friendly Alternative
ULID (Universally Unique Lexicographically Sortable Identifier) offers the same time-ordering benefits as UUID v7 but in a more compact, URL-friendly format. At 26 characters using Crockford Base32 encoding, ULID is 28% shorter than a UUID string and contains no hyphens — making it safe to use directly in URLs without percent-encoding.
ULID's 80 random bits (vs. UUID v7's 74) provide slightly more entropy per millisecond, though both are more than sufficient for any real-world application. The Crockford Base32 alphabet excludes visually ambiguous characters (I, L, O, U), making ULIDs easier to read, type, and speak aloud — a useful property for invite codes, support tickets, and other human-facing identifiers.
The main limitation of ULID is the lack of native database type support. While UUID has native types in PostgreSQL, MySQL, and SQL Server, ULID is typically stored as VARCHAR(26) or BINARY(16). For applications where URL ergonomics matter more than database native support, ULID is an excellent choice.
NanoID: Compact and Customizable
NanoID is the most flexible identifier format in this comparison. Its customizable alphabet and length make it adaptable to a wide range of use cases — from 6-digit numeric OTP codes to 32-character high-entropy tokens. The default configuration (21 characters, 64-character alphabet) provides ~126 bits of entropy, slightly more than UUID v4.
NanoID is particularly well-suited for short URL slugs, invite codes, and API tokens where compactness and URL safety are priorities. Its rejection-sampling algorithm ensures uniform distribution across the alphabet, avoiding the modulo bias that affects many naive ID generators.
Like UUID v4, NanoID is not time-ordered. For database primary keys at scale, UUID v7 or ULID are better choices. NanoID shines in application-layer identifiers where the ID will appear in URLs, be displayed to users, or be typed manually.
GUID: The Windows Ecosystem Standard
GUID is Microsoft's name for UUID. Structurally identical to UUID v4, GUIDs are the standard identifier format across the entire Microsoft ecosystem — .NET, SQL Server, Azure, Windows Registry, COM, and MSI installers. If you are building on the Microsoft stack, GUID is the natural choice.
SQL Server's NEWID() generates random GUIDs (equivalent to UUID v4), while NEWSEQUENTIALID() generates sequential GUIDs that reduce index fragmentation. For new SQL Server applications, consider generating UUID v7 client-side and storing it as a UNIQUEIDENTIFIER — this gives you the sequential benefits without the server-restart reset issue of NEWSEQUENTIALID().
UUID v1 and v6: The Legacy Formats
UUID v1 was the original time-based UUID, combining a 60-bit Gregorian timestamp with a MAC address node ID. While it embeds creation time, the timestamp fields are scrambled across the UUID string in a non-sortable order. The MAC address exposure is a privacy concern in modern applications, though this can be mitigated by using a random node ID.
UUID v6 fixes v1's sortability problem by reordering the timestamp fields so that the most significant bits come first. It is fully backward-compatible with v1 and is the recommended migration path for systems already using v1. For new systems, UUID v7 is preferred over v6 due to its simpler Unix epoch timestamp and broader library support.
Apache Cassandra's timeuuid type is UUID v1. If you are working with Cassandra, v1 (or v6 for sortability) is the natural choice. For all other new systems, v7 is the modern standard.
