Distributed ID Generation: UUIDv7, ULID, Snowflake & Sequences

Distributed ID Generation

Every record needs an identifier. On a single database, an auto-incrementing integer is the obvious choice. At scale it breaks down: multiple shards or regions can't share one counter without coordination, clients sometimes need IDs before writing (offline apps, idempotent creation, event sourcing), and sequential integers leak information like row counts and growth rates when exposed in URLs.

Distributed ID schemes generate unique IDs without a central bottleneck. They differ in size, sortability, randomness, index performance, and privacy. Modern defaults have converged: UUIDv7 (or ULID) for most application IDs, Snowflake-style 64-bit IDs where compactness and ordering matter at very high scale, and plain sequences where a single database suffices.

TL;DR

Auto-increment/sequences: compact and ordered, but need a central authority. Great for single databases, awkward across shards.
UUIDv4: 128-bit random, generated anywhere, but random order fragments B-tree indexes and hurts insert performance.
UUIDv7 (RFC 9562): a 48-bit millisecond timestamp plus randomness, so it's globally unique and roughly time-ordered. It's the modern default.
ULID: a similar time-ordered 128-bit ID with a compact, sortable Crockford Base32 string.
Snowflake: 64-bit = timestamp + machine ID + sequence. Compact, ordered, needs unique worker IDs.
Don't expose sequential IDs publicly if they leak business data. Time-ordered IDs reveal creation time, so consider opaque public IDs.

Quick Example

Generating UUIDv7 IDs in application code and in PostgreSQL:

A Snowflake-style generator (64-bit):

Core Concepts

Requirements to Weigh

Auto-Increment and Sequences

Database sequences are compact, ordered, and fast on one primary. To scale:

Offset sequences per shard (shard 1: 1, 11, 21…; shard 2: 2, 12, 22…). Simple, but changing the shard count is hard.
Ticket servers / range allocation: a central service hands out blocks of IDs (for example 10,000 at a time) that nodes use locally. That's low coordination, and IDs are roughly ordered.

Downsides: exposure of business metrics (order #10492 means about 10k orders), and IDs can't be generated before insert without a round trip.

UUIDv4

122 random bits: collision probability is negligible, and anyone can generate one anywhere. But random values insert at random positions in B-tree indexes, causing page splits, poor cache locality, and larger, fragmented indexes. The effect is significant at high insert rates, especially for clustered primary keys (MySQL/InnoDB, SQL Server). See database indexing.

UUIDv7 and ULID

Standardized in RFC 9562 (2024), UUIDv7 puts a Unix millisecond timestamp in the most significant bits, followed by random (or counter) bits. New IDs therefore sort roughly by creation time, so inserts append near the end of the index, like a sequence, while staying generatable anywhere without coordination. ULID predates it with the same idea and a 26-character sortable Base32 encoding.

Trade-offs: they reveal approximate creation time, and they're 128 bits (16 bytes in native uuid columns; don't store them as 36-character strings).

Snowflake IDs

Twitter's Snowflake packs a 64-bit integer with:

The result is compact (fits bigint), time-ordered, and extremely fast. Discord, Instagram (a variant generated in PostgreSQL), and many others use similar schemes. The challenges are assigning unique worker IDs (config, a coordination service, or Kubernetes StatefulSet ordinals) and clock handling.

Clock Issues

Time-based schemes depend on clocks:

Clock moving backwards (NTP adjustments, VM migration) can produce duplicates or out-of-order IDs. Snowflake generators should refuse or wait; UUIDv7 implementations often use monotonic counters within a millisecond.
Skew between machines means IDs from different nodes are only roughly ordered. Don't rely on ID order for strict causality. See distributed consensus if you need total order.

Public vs Internal IDs

Internal primary keys can be sequences or time-ordered IDs chosen for database efficiency.
Public identifiers (URLs, APIs) should avoid leaking counts or enabling enumeration: use UUIDs, or a separate random public ID. Prefixed IDs (ord_01J9Z…, Stripe-style) make IDs self-describing in logs and support tickets.
Never rely on ID unpredictability for authorization. Always check that the caller may access the object. See API security.

Best Practices

Default to UUIDv7 for New Distributed Systems

It gives coordination-free generation, good index locality, a standard format, and wide library and database support. Use native UUID column types (16 bytes).

Use 64-bit IDs When Size and Speed Matter Most

For extremely high-volume tables (events, messages) where index size dominates, Snowflake-style bigint IDs halve key size versus UUIDs, at the cost of managing worker IDs.

Generate IDs Client-Side When It Helps

Creating IDs before the write enables idempotent retries, optimistic UI, offline creation, and linking related records before persistence.

Don't Parse Business Logic From IDs

Extracting timestamps from IDs is fine for debugging and rough ordering. Store explicit created_at columns for business logic, since ID formats may change.

Common Mistakes

Random UUIDv4 Primary Keys on Write-Heavy Clustered Tables

High insert rates with random keys on InnoDB or SQL Server clustered indexes cause page splits and buffer churn, and performance degrades as tables grow. Switch to time-ordered IDs.

Storing UUIDs as Strings

VARCHAR(36) UUIDs use over twice the space of native 16-byte types, slow comparisons, and bloat every index and foreign key. Use uuid or BINARY(16).

Exposing Sequential IDs in URLs

/invoices/10492 invites enumeration attempts and reveals volume. Combined with a missing authorization check, it becomes a data breach (the classic IDOR). Use opaque IDs, and authorize every request.

FAQ

Should I use UUIDs or auto-increment integers?

For a single database with internal-only IDs, auto-increment is simple and efficient. For distributed systems, client-generated IDs, merging data across databases, or public-facing IDs, use UUIDs, preferably time-ordered UUIDv7 to keep index performance close to sequential keys.

What's the difference between UUIDv4 and UUIDv7?

UUIDv4 is almost entirely random. UUIDv7 starts with a millisecond timestamp followed by random bits, so values increase over time. Both are 128-bit and globally unique, but UUIDv7 inserts are far friendlier to B-tree indexes and sort by creation time.

Is ULID better than UUIDv7?

They offer the same benefits: 128-bit, time-ordered, coordination-free. UUIDv7 is an IETF standard with native support emerging in databases and languages. ULID has a nicer default string encoding. For new systems, UUIDv7 is usually the more interoperable choice.

Can two machines generate the same Snowflake ID?

Only if they share a worker ID, or a clock moves backwards without protection. Assign worker IDs uniquely and reliably, and make generators refuse to issue IDs when the clock regresses.