UUID vs. ULID vs. Auto-Increment as Primary Key: Index Consequences

1. Why the primary key type directly shapes the index structure

In most relational databases, a primary key is also the clustering key, the physical sort order in which rows are stored on disk. Every newly inserted value must land exactly at the position in the B-tree matching its sort position. When a key grows monotonically, every new row lands at the right edge of the tree, which makes inserts cheap because no existing node needs to be searched and no already-full page needs to be split.

If the key is randomly distributed instead, every insert hits a practically arbitrary position in the tree. That forces regular page splits, scatters logically related rows across many physical pages, and means the write-relevant part of the index barely fits in the buffer pool anymore. This mechanism is independent of the specific database system, it follows directly from how B-tree indexes work.

2. Auto-increment: compact, sorted, but bound to a sequence

A classic auto-increment key is an integer or bigint whose value is assigned by a central sequence or an equivalent counter. Four or eight bytes per value is significantly more compact than any text or binary representation of a UUID, which directly affects the size of every foreign key column and secondary index referencing that key. The strictly monotonic order results in minimal fragmentation and very good cache locality for writes.

The downside is centralization: the next value can only be assigned by an instance managing the sequence. In a single database cluster with one primary write node, that is unproblematic. As soon as multiple independent write paths need the same key namespace, such as multiple regions, offline clients, or a later merge of two datasets, the central sequence becomes the limiting factor or a source of collisions.

-- Classic auto-increment primary key
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

3. UUID v4: globally unique without central coordination

A UUID version 4 consists of 128 bits, of which 122 bits are true randomness, the rest marks version and variant. With correct random generation the collision probability is practically negligible, even across billions of generated values. The decisive advantage over auto-increment is that every client, every service, and every offline instance can generate a valid primary key without talking to a central database first. That makes UUIDs attractive for distributed architectures, microservices, and systems with temporarily disconnected write paths.

The price is complete randomness in sort order. A UUIDv4 carries no information about its creation time at all, two consecutively generated values land at completely different positions in the index. That exact property, the one that enables distributed generation in the first place, is also the cause of the index fragmentation described in the next section.

4. The concrete fragmentation problem of random UUIDs in a B-tree

Because every new UUIDv4 value is statistically evenly distributed across the entire 128-bit space, every insert hits a random position in the index. On tables with several million rows this causes a high rate of page splits, since practically every already-full page eventually gets hit by a new row randomly placed there. The result is larger indexes with more, but more sparsely filled, pages, significantly more random I/O on writes, and a worse buffer pool hit rate, because pages relevant to current writes are scattered across the entire index tree instead of concentrated at the end.

In practice this shows up as noticeably declining insert throughput once a table with a UUIDv4 primary key reaches a size where the index no longer fits entirely in memory. Benchmarks across various relational databases regularly show several times higher I/O cost per insert for purely random UUID keys compared to sequential keys, while the difference is barely measurable on small, fully cached tables.

5. ULID and UUIDv7: sortable alternatives with a timestamp prefix

ULID and UUIDv7 solve the fragmentation problem by splitting the value into two parts: a leading timestamp with millisecond resolution and a following random component. ULID uses 48 bits of timestamp and 80 bits of randomness, UUIDv7, officially standardized in RFC 9562 since 2024, has a similar split but fits it within the existing UUID structure alongside version and variant. Because the timestamp sits in the leading position, values generated within the same millisecond are not strictly ordered, but over larger time spans the order is practically monotonically increasing.

For the B-tree this means new inserts almost always land near the right edge of the index, just like auto-increment, while every client can still generate a valid value in a decentralized way without querying a central sequence. The random component stays large enough to make collisions during parallel generation within the same millisecond practically negligible.

-- Generate UUIDv7 natively in PostgreSQL 18+
INSERT INTO orders (id, customer_id)
VALUES (uuidv7(), 42);

-- Store ULID/UUIDv7 as BINARY(16) instead of CHAR(36)
-- to further reduce index size and fragmentation
CREATE TABLE events (
    id BINARY(16) PRIMARY KEY,
    payload JSON NOT NULL
);

6. Storage footprint, readability, and collision behavior in detail

An auto-increment bigint takes eight bytes and is instantly comparable and sortable for humans, but it also reveals the approximate order and count of records, which can count as an information leak in public APIs. UUIDs and ULIDs take 16 bytes as a binary value, in the textual representation common in many applications even 36 or 26 characters respectively, which multiplies across every foreign key and secondary index referencing that key.

ULID additionally encodes its random component in Base32 without ambiguity between similarly looking characters, which makes the textual representation somewhat more compact and easier to read in logs than classic dash-separated UUID notation. UUIDv7 in turn has the advantage of being an official IETF standard and fitting seamlessly into existing UUID columns, libraries, and validations without application code having to learn a new data type.

7. Trade-offs in distributed systems: coordination versus sort order

In a distributed system with multiple independent write nodes, such as multiple regions each with their own write database, or clients that must create records offline, a central sequence is either unavailable or adds an extra network round trip per insert. UUID, ULID, and UUIDv7 solve this problem identically, because all three can be generated without a central instance. The difference lies solely in the index properties of the resulting values.

When merging two previously independent datasets later, for example after a company merger or when consolidating regional databases, globally unique keys automatically prevent collisions, whereas auto-increment keys from separate systems almost always trigger value-range conflicts requiring an expensive remapping migration. This structural advantage is fully preserved with ULID and UUIDv7, just with much better insert behavior than UUIDv4.

8. Practical implementation details: storage and conversion

Regardless of the chosen type, it pays to store UUID or ULID values as BINARY(16) instead of a 36-character string. That halves the storage footprint compared to the dash-separated textual representation and noticeably reduces the size of every referencing foreign key column too. Most database systems offer native conversion functions or a dedicated UUID data type that is already stored in binary internally.

For the application layer this means cleanly encapsulating conversion logic between the human-readable text form and the binary storage form in one place, such as a value object or an ORM type extension, instead of converting manually everywhere in the code. With ULID there is the additional benefit that libraries exist for practically every popular programming language, consistently encapsulating generation, sorting, and conversion.

9. Practical recommendation: which key type fits which scenario

For a monolith with a single primary database and no requirement for decentralized key generation, an auto-increment bigint remains the simplest and most performant choice. For systems with multiple independent write paths, offline generation, or foreseeable merge scenarios, a sortable alternative like UUIDv7 or ULID is almost always the better choice over classic UUIDv4, because it offers the same decentralized generation with far better index behavior.

Plain UUIDv4 should today only be used where an existing system or external interface specifically requires that exact standard and migrating to a sortable variant is not practical. For new schemas there is hardly any good reason left to accept UUIDv4's fragmentation when UUIDv7 offers the same decentralization without that downside.

10. Summary

11. FAQ: Primary Key Types Compared