UUIDv7 vs ULID vs Snowflake IDs: Picking a Primary Key Strategy

Pick a primary key strategy and you’re really deciding between three things you can’t have all at once: a short, comparison-cheap ID; an ID any node can generate without asking anyone else; and an ID that doesn’t turn your busiest index into a random-write machine. Auto-increment gets the first two and fails the third. Random UUIDs get the second and fail the first and third. The real answer, for most schemas built today, is UUIDv7 or one of its close relatives, and it’s worth understanding exactly what problem each option solves before you bake a choice into a schema that’s expensive to change later.

Why auto-increment stops being enough

SERIAL or AUTO_INCREMENT is the default for a reason: it’s compact (4 or 8 bytes), strictly ordered, and cheap for the database to index and compare. It also has three problems that only show up once an application grows past a single writable database.

It leaks information. A sequential order ID tells anyone who sees two of them roughly how many orders exist between them, which is a real disclosure risk for anything user-facing.

It needs a single source of truth. Generating the next value requires the database to hand it out, which means every insert has to round-trip through whichever node owns the sequence. That’s fine on one Postgres instance. It stops being fine the moment you want to generate an ID client-side, offline, or across shards without a coordination step.

It doesn’t shard cleanly. Two independent databases both counting from 1 will collide the moment you try to merge their data, which is exactly the situation you’re in after a shard split, a multi-region write setup, or a merger of two previously separate systems.

Random UUIDv4 fixes the coordination problem and creates a new one

UUIDv4 (the fully random variant most people mean when they just say “UUID”) solves the coordination problem completely. Any node, anywhere, can generate a 128-bit value with a collision probability low enough to ignore. No round trip, no shared sequence, no leaked count.

The cost is index locality, and it’s a real cost, not a theoretical one. A B-tree index performs best when inserts land at or near the rightmost edge of the tree, extending the structure the same way appending to a sorted array is cheaper than inserting into the middle of one. A sequential integer does this by construction. A fully random UUID does the opposite: each new value lands at an effectively random position across the entire key space, forcing the index to split pages and scatter writes across leaves that used to be cold. On a small table this is invisible. On a write-heavy table with tens of millions of rows, it shows up as index bloat, worse buffer cache hit rates, and inserts that get measurably slower as the table grows, specifically because of key randomness, not row count.

UUIDv7: sortable, standardized, no coordination

UUIDv7 keeps the property that made UUIDv4 attractive, any node generates one independently, and fixes the index locality problem by putting time first.

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                         |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms          |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

RFC 9562, standardized in 2024, puts a 48-bit Unix millisecond timestamp in the high-order bits, followed by a 4-bit version field, 12 bits of randomness, a 2-bit variant field, and 62 more random bits. Because the timestamp occupies the most significant bits, sorting UUIDv7 values as plain byte strings sorts them in creation order. Two IDs generated a millisecond apart land next to each other in the index instead of at opposite ends of the key space.

-- Postgres 18+: native uuidv7()
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

# Client-side generation, no round trip needed
import uuid6  # or your language's UUIDv7 implementation

order_id = uuid6.uuid7()

This is why UUIDv7 is the reasonable default for new schemas in 2026: it’s a real, standardized format, every language and database already understands the underlying UUID type, and it restores the append-mostly insert pattern that made sequential integers fast without giving up independent generation.

ULID: the same idea, a friendlier string

ULID predates UUIDv7 as an ecosystem convention (it isn’t an IETF standard) and solves the identical problem with a different encoding: a 48-bit millisecond timestamp plus 80 bits of randomness, rendered as a 26-character Crockford Base32 string, 01ARZ3NDEKTSV4RRFFQ69G5FAV, rather than the hyphenated hex of a UUID.

import { ulid } from "ulid";

const orderId = ulid(); // "01ARZ3NDEKTSV4RRFFQ69G5FAV"

The practical differences from UUIDv7 are small. ULID’s string form is shorter and case-insensitive-friendly, which some teams prefer for URLs and logs. UUIDv7 stores as a native UUID column type everywhere that type already exists, which usually means less custom tooling. Neither one is wrong; if your stack has first-class UUID support already, UUIDv7 is less friction. If you want a compact, readable string and don’t mind a type your ORM might not recognize natively, ULID is a fine choice.

Snowflake IDs: smaller, but you’re back to coordination

Twitter’s Snowflake design, and the near-identical variants Discord and Instagram built afterward, takes a different tradeoff: pack a millisecond timestamp, a machine or worker ID, and a per-millisecond sequence number into a single 64-bit integer instead of 128 bits.

| 1 bit unused | 41 bits timestamp | 10 bits machine ID | 12 bits sequence |

Half the storage of a UUID, cheaper to compare, and still time-ordered and roughly sortable. The cost is that the coordination problem UUIDv7 eliminated comes back in a narrower form: something has to hand out unique, non-colliding machine or worker IDs to every ID-generating process, which is a real operational dependency, not a free lunch. That tradeoff is worth paying at Twitter or Discord scale, where index size and comparison cost genuinely matter across billions of rows. Most applications, including most that feel “high scale” internally, never get there, which is the practical reason UUIDv7 has become the more common default rather than Snowflake-style generation.

Choosing one

Strategy	Size	Sortable	Coordination needed	Best fit
Auto-increment	4-8 bytes	Yes	Central sequence	Single-writer, simple schemas
UUIDv4 (random)	16 bytes	No	None	Legacy compatibility only
UUIDv7	16 bytes	Yes	None	Default choice for new schemas
ULID	16 bytes (26-char string)	Yes	None	Same as UUIDv7, prefer compact strings
Snowflake	8 bytes	Yes	Machine/worker IDs	Very high write volume, size-sensitive

For most new tables, UUIDv7 is the boring, correct answer: independently generated, sorts by creation time, indexes like a sequential key, and needs nothing beyond a library your stack probably already has. It pairs naturally with the same database indexing fundamentals that make sequential keys fast, and it removes the one property, random insert order, that made UUIDv4 a bad default in the first place. Reach for Snowflake only once you can point at the specific bottleneck, index size or comparison cost at real scale, that 64 bits actually fixes for you.