Unique ID generation
At scale you can't ask one database for the next number. UUIDv4 is random and decentralized but fragments indexes; Snowflake and UUIDv7 are time-ordered (timestamp + machine + sequence), keeping IDs sortable and index-friendly — at the cost of leaking time and trusting the clock.
A team sharded their orders table across eight databases and kept the schema they’d always used: an auto-incrementing primary key. The first insert against shard 3 returned id 1 — but shard 5 had already minted its own id 1 the day before. Two different orders, same global ID, and a downstream reconciliation job silently merged them. The lesson was not “auto-increment is bad” — on one node it’s perfect. It’s that the moment you have more than one writer, “the next number” stops being a thing one node can hand out, and you need an ID scheme that several machines can generate independently and still never collide.
Why not just auto-increment
A single database’s auto-increment (or a sequence) is the simplest possible ID: compact, sorted, gap-free-ish. It works beautifully on one writer because exactly one node owns the counter. Scale past one writer and it breaks in two ways. First, coordination: if every insert across a sharded fleet must ask one central sequence server for the next value, that server is now a bottleneck and a single point of failure on the write path — the very thing you sharded to avoid. Second, collisions: if each shard keeps its own local counter, two shards both emit id 1, 2, 3… and IDs are no longer globally unique. You can hack around it (give each shard a stride, or a high-bits prefix), but you’ve started reinventing a distributed ID scheme — so reach for one designed on purpose.
The goal of a distributed ID generator: any node can mint an ID locally, without coordination, and the result is globally unique. The schemes differ in what else the ID gives you — randomness, or sortability. When you pick one for a new table, ask yourself first: does this ID ever appear in a URL or API response? That single question often tells you whether you need random (public-safe) or time-ordered (index-safe).
UUIDv4: random and coordination-free
A UUIDv4 is 122 random bits (plus version/variant bits) in a 128-bit value. With that much entropy the collision probability is negligible in practice, so any process anywhere can generate one with no shared state — perfect decentralization. That’s why it’s the default for “I need a unique ID and don’t want to think about it.”
The cost shows up in the database index. Primary keys are usually backed by a B-tree, and inserts in sorted order append to the right edge — cheap, cache-friendly, few page splits. UUIDv4 keys are random, so each insert lands at a random spot in the index, scattering writes across the whole B-tree: more page splits, poor cache locality, and on a clustered index (where the table rows are physically stored in key order, as in InnoDB or SQL Server) the table itself fragments. At high insert rates this is a measurable throughput and storage penalty. Random IDs buy decentralization and pay it back in index churn.
▸Why this works
Why does a random key fragment a B-tree but a sequential one doesn’t? A B-tree keeps entries sorted. A monotonically increasing key always inserts at the rightmost leaf — the same hot page, which fills and splits cleanly, and old pages are never touched again (great for the buffer cache). A random key inserts into a random leaf each time: pages all over the tree must be read in, modified, and split, so the working set is the whole index rather than one hot page, cache hit-rate collapses, and clustered tables physically interleave unrelated rows. The fix that keeps decentralization is to make the high bits of the ID time-ordered, so inserts are mostly sequential again — which is exactly what Snowflake and UUIDv7 do.
Snowflake: timestamp + machine + sequence
Twitter’s Snowflake scheme packs a 64-bit integer ID out of three fields, most-significant first:
| 41 bits: timestamp (ms since a custom epoch) | 10 bits: machine ID | 12 bits: sequence |
└ ~69 years of milliseconds └ up to 1024 nodes └ 4096 ids/ms/nodeBecause the timestamp is in the high bits, IDs sort (roughly) by creation time — so they insert near the right edge of the index, restoring the B-tree locality auto-increment had. The machine ID makes each node’s IDs disjoint without coordination (assigned at startup from config, ZooKeeper, etc.). The sequence counter disambiguates multiple IDs minted within the same millisecond on one node — up to 4096 per ms, after which the node waits for the next millisecond. The result: 64-bit, sortable, coordination-free, ~4 million IDs/sec per node. This is the workhorse for systems that want compact, time-ordered IDs at scale.
UUIDv7: the standardized time-ordered UUID
UUIDv7 (standardized in RFC 9562, 2024) takes the same idea into the 128-bit UUID format: a 48-bit Unix-millisecond timestamp in the high bits, then random bits below. You keep the UUID’s universal acceptance (every language and database has a UUID type and generator) and its coordination-free generation, but the time-ordered prefix gives you the index locality UUIDv4 lacked. The tradeoff versus Snowflake: UUIDv7 is bigger (128 vs 64 bits — more storage, fatter indexes) and needs no machine-ID assignment, while Snowflake is compact and explicitly encodes the node. The modern default for “unique, sortable, no coordination” is UUIDv7; Snowflake wins when 64-bit compactness or an embedded machine ID matters.
The sortability-vs-randomness tradeoff
The core decision is one axis. Time-ordered IDs (Snowflake, UUIDv7) sort by creation, insert at the index’s right edge, and let you range-scan by time — but they leak information: anyone holding two IDs can infer when each was created and roughly how many were issued between them (an enumeration / competitive-intelligence risk for public IDs). Random IDs (UUIDv4) leak nothing and are unguessable — better for public-facing identifiers (password-reset tokens, public object IDs) — but fragment indexes and can’t be range-scanned. A common production split: time-ordered IDs (UUIDv7/Snowflake) for internal primary keys, and either random IDs or a separate opaque public slug for anything a client sees.
▸Common mistake
Trusting the wall clock. A Snowflake or UUIDv7 node depends on its clock moving forward. If NTP steps the clock backwards (or a VM pauses and resumes), the node can mint an ID with an earlier timestamp than one it already issued — breaking monotonicity and, in the worst case, colliding with an ID from that earlier millisecond. The standard guard: detect a backwards step, and either refuse to issue IDs until the clock catches up, or keep a monotonic in-memory counter that never goes below the last timestamp used. This is why these schemes specify “wait if the clock went backward” — clock skew is the failure mode that turns a coordination-free generator back into a source of duplicates.
An OLTP table on InnoDB (clustered primary key) ingests millions of rows/hour. The team switched the PK from auto-increment to UUIDv4 'for decentralization' and write throughput dropped while index size grew. Why?
A Snowflake generator node's clock is stepped backward 200 ms by an NTP correction. With no guard, what is the danger?
Putting the _______ in the high (most-significant) bits of a Snowflake or UUIDv7 ID is what makes the IDs sort by creation order, so inserts land near the right edge of the index instead of scattering like a random UUIDv4 key.
- 01Why does auto-increment break with more than one writer?
- 02Compare UUIDv4, Snowflake, and UUIDv7 on the axes that matter.
- 03State the sortability-vs-randomness tradeoff and the clock-skew risk.
Auto-increment is the perfect ID for one writer — compact and sorted — but fails with many writers, either as a central-sequence bottleneck/SPOF or as per-shard collisions. A distributed ID generator lets any node mint a globally-unique ID with no coordination. UUIDv4 (122 random bits) is fully decentralized and unguessable but its randomness fragments B-tree indexes (page splits, lost cache locality, clustered-table fragmentation). Snowflake (64-bit: high-bit ms timestamp + 10-bit machine ID + 12-bit sequence) and UUIDv7 (128-bit: high-bit ms timestamp + random, RFC 9562) put the timestamp in the high bits, so IDs stay sortable and insert at the index’s right edge, restoring locality — UUIDv7 for universal support, Snowflake for 64-bit compactness and an embedded node ID. The core tradeoff is sortability vs randomness: time-ordered IDs are index-friendly and range-scannable but leak time (enumeration risk), while random IDs leak nothing but churn indexes — hence time-ordered internal keys and random/opaque public IDs. And every time-ordered scheme depends on the clock moving forward: a backward step risks duplicate, out-of-order IDs unless you wait or hold a monotonic counter. Now when you see a table with a UUID primary key and mysterious write slowdowns — check whether those IDs are random (UUIDv4) or time-ordered (UUIDv7/Snowflake); that single field tells you whether the B-tree is fragmenting or not.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.