How to Choose the Right Primary ID for Your Database

Introduction- Hidden cost of primary keys

Most developers choose sequential IDs or UUIDs for their database primary keys by default. But this seemingly trivial choice can become a huge architectural bottleneck later on, if not carefully matched to system requirements.

1. The UUIDv4 issue: UUIDv4 is completely random and non-sortable so it creates issue on database indexes. Every time a new row is inserted, the database has to force the random key into the middle of its B-tree index, resulting in massive index fragmentation, constant page splits and a severely degraded write performance. Since UUIDs are 16 bytes, it can also impact the storage size and performance of secondary indexes.

2. The Sequential ID Problem: Auto-incrementing IDs are fast in a single database instance. But they don’t work in a sharded or distributed database architecture. If data is horizontally partitioned across multiple nodes, we cannot rely on a local database counter to ensure global uniqueness. If we want to keep the sequence across shards, we need a central ticket server to issue keys which adds network latency and introduces a single point of failure issue.

Sequential Numbers

It is the default choice for most relational databases (PostgreSQL SERIAL, MySQL AUTO_INCREMENT).

How it works: Generates a sequence of numbers that increase by one for each new row. This feature simplifies the process of creating unique identifiers for each row in a table.

Pros:

• Very light weight and storage-efficient (4 or 8 bytes).
• Perfectly sequential resulting in zero index fragmentation (inserts are fast).
• Human-readable and easy to debug.

Cons:

• Security risks: Highly vulnerable to IDOR(Insecure Direct Object Reference) attacks and business metric leaks (enumeration) if exposed to users.
• Distributed systems: Requires a round-trip to a single primary database node which generates sequential numbers creating a bottleneck.

UUID (Universally Unique Identifier)

Industry’s first major answer to distributed ID generation.

How it works: 128-bit labels, usually represented as 36-character strings with hyphens.

Common Versions:

• v4: Completely random. Random 122 bits
• v7: Time-ordered and sortable, making it ideal for modern database primary keys. Time-ordered + 64 random bits

Pros:

• True decentralized generation (no coordination needed between nodes).
• Unpredictable (v4), making them safe for public-facing URLs and APIs.

Cons (The v4 Problem):

• Completely random distribution destroys B-tree index performance(fragmentation and index bloats) at scale due to constant page splits.
• Bloated storage footprint (16 bytes stored as UUID, or much worse if stored as a VARCHAR).

The Fix: Use UUID v7 which is time-ordered and sortable solves the database fragmentation issue while keeping native database support.

ULID (Universally Unique Lexicographically Sortable Identifier)

UUIDv7 has effectively replaced the need for ULID but ULID still holds a slight edge in a few specific edge cases like Shorter URL-Safe Strings and human readability.

Get Dhana Sekaran Chellamuthu’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

How it works: 48-bit timestamp followed by 80 bits of randomness, encoded in Base32.

Pros:

• Lexicographically sortable (solves the B-tree fragmentation problem).
• Shorter, URL-safe string representation (26 characters vs UUID’s 36).
• Millisecond precision.

Cons:

• Lacks native data types in most databases (must be stored as VARCHAR(26) or converted to UUID types for storage).

Snowflake IDs

Popularized by Twitter. They offer the speed and storage efficiency of standard integers with the distributed capabilities of a UUID.

How it works: Highly compact 64-bit integers (fitting perfectly in an 8-byte BigInt) that combine a timestamp, a machine ID, and a sequence number.

Pros:

• Extremely compact (8 bytes, just like a standard BIGINT).
• Time-ordered and highly performant for database indexes.
• Decentralized, yet guarantees no collisions.

Cons:

• High operational complexity. Requires managing “Worker IDs” across your fleet.
• Vulnerable to system clock skew (NTP synchronization issues).

While both Snowflake IDs and UUID v7 solve the core B-tree fragmentation issue by making IDs time-sortable, Snowflake IDs excel in absolute performance at massive scale because they rely on native 64-bit architecture rather than 128-bit blocks.

Comparison

Conclusion

Sequential Numbers: Use when running a single, unsharded database. They are fast, perfectly sortable, and incredibly space-efficient.

UUID v7: The modern default. It gives you decentralized generation, eliminates B-tree index fragmentation and leverages native 128-bit database types.

Snowflake IDs: Use when operating at massive scale. This 64-bit identifier delivers unparalleled CPU performance provided you can handle the operational overhead of managing worker nodes.

Additional notes:

When a database stores a UUID as a text string (using data types like CHAR(36) or VARCHAR), it allocates memory for each individual character based on the text encoding:

• Characters: 32 hexadecimal characters + 4 hyphens = 36 total characters.
• Memory: 36 characters × 1 byte per character = 36 bytes.

When a database uses a native UUID data type (like PostgreSQL’s uuid type) or a raw binary format (like MySQL’s BINARY(16)), it strips away the text formatting and stores only the raw mathematical value:

• Bits: 128 total bits.
• Memory: 128 bits / 8 bits per byte = 16 bytes.

While 20 bytes sounds small, a database index must load these values into RAM to search them quickly. Across 100 million rows, storing UUIDs as text wastes over 2 gigabytes of precious memory and disk space, which drastically slows down query performance.