The Snowflake Problem: Unique IDs at Scale, Explained Like You're New
Blueprints of Scale — Core Concepts #4
Every row in every database needs a name: a primary key, a unique ID. It sounds like the most boring problem in system design, and it's one of the few decisions that is truly forever. Once millions of rows, URLs, API responses, and other people's bookmarks reference your ID format, changing it is a migration of everything. And the moment you outgrow one database, the moment the sharding post's split happens, the humble auto-increment quietly breaks, and you're in the snowflake problem: how do a thousand machines, with no time to talk to each other, mint IDs that are unique, roughly ordered, and fast enough never to be the bottleneck?
Here's what's covered: why auto-increment dies at sharding, and what a database sequence actually promises; UUIDs and the arithmetic of randomness, plus the map of UUID versions nobody remembers; why random IDs punish your database's index, with the B-tree mechanics that explain it; Snowflake's anatomy (time, machine, sequence), the variants Discord, Instagram, and Sony run, and the worker-ID assignment problem the diagrams skip; the lies clocks tell and the menu of policies for handling them; Flickr's ticket servers as they actually worked, and the range-allocation pattern that grew out of them; the modern middle ground (ULID, UUIDv7) and which databases generate it natively now; the failure modes: exhaustion, the 32-bit cliff, hotspots, predictability, and the ID that comes back after a failover; and the principal-level framework: the ID as a contract, internal versus external IDs, natural versus surrogate keys, multi-region generation, URL-safe formats, and the migration nobody wants to do.
If you've never thought about what an ID even needs to do, start at Section 1; the first two sections assume nothing, and every term is defined where it appears. Sections 3 through 8 are the realities every backend engineer meets: index fragmentation, Snowflake, clock skew, ticket servers, ULIDs, and the ways each breaks. Section 9 is the judgment. The cheat sheet is at the end under IDs, distilled, and every diagram is described in the text around it, so nothing is lost on a screen reader.
Section 1 — The humble auto-increment (and the day it breaks)
In this section: we start where every database starts, id SERIAL PRIMARY KEY, look at what a sequence actually guarantees (less than you think), and see exactly why the simplest ID scheme on earth stops working the moment you shard.
On a single database, ID generation is a solved problem. AUTO_INCREMENT in MySQL, SERIAL or an identity column backed by a SEQUENCE in Postgres: each new row gets the next integer, 1, 2, 3, 4. It's perfect in every way that matters. Tiny (4 or 8 bytes), naturally ordered (row 1000 was created before row 2000), index-friendly (new rows append to the end of the index, no fragmentation), and readable in a debugger. If you never outgrow one machine, stop reading here. This is the right answer.
Two things about sequences are worth knowing even on one machine, because both surprise people later. First, a sequence is not gapless. A rolled-back transaction keeps the number it drew; Postgres logs sequence values to disk 32 at a time ahead of use, so a crash can skip a few dozen; and if you ever rely on "no gaps" for an invoice number or an audit trail, you'll need a real gapless counter with a lock, which is a different, slower thing. Second, the counter has to survive a restart and a failover (the promotion of a standby copy when the main database dies). MySQL before version 8.0 recomputed the auto-increment counter from the largest existing value on restart, which meant that if you deleted the newest row and restarted, the next insert got the same ID again, and it's the reason 8.0 started persisting the counter in the redo log (its crash-recovery log). The replication post (#5) has the failover version of this problem, and Section 8 comes back to it.
Now shard the table across 16 machines, the way the sharding post (#3) taught you. Each shard has its own counter. Shard A inserts user 1, 2, 3. Shard B inserts user 1, 2, 3. Sixteen shards, sixteen user #1s. The IDs are unique per shard and duplicated globally, which is to say not unique at all. The first time you try to merge data, build a global secondary index (an index that spans every shard), or move a row between shards, the collision detonates.
Picture the team that shards its users table over a weekend, the heroic kind from the sharding post, and on Monday finds the analytics pipeline joining user activity to the wrong users. Sixteen user #48219s, each on a different shard, each with different activity. The pipeline had been correct for years on one database. Sharding didn't break the pipeline so much as the assumption it was built on, that an ID names one row. The auto-increment was never really an ID scheme. It was a single-machine privilege.
The patches people try first, and why they're patches. Auto-increment with a different offset per shard (shard A mints 1, 17, 33; shard B mints 2, 18, 34; MySQL's auto_increment_increment and auto_increment_offset settings exist for exactly this) works until you add a 17th shard and the arithmetic breaks. A central counter service works until it's the bottleneck and the single point of failure (Section 6 does this properly). The real lesson: at scale, ID generation must work with zero coordination between machines at the moment of minting. Every scheme from here on is a different answer to "how do we agree without talking?"
Section 2 — UUIDs: randomness as a strategy
If machines can't coordinate, let them not coordinate. This is the UUID answer: why 122 random bits make collisions a non-event, what the other UUID versions are for, where UUIDs shine, and what they cost, in bytes and in more than bytes.
Each machine picks its IDs at random from a space so vast that two machines will never pick the same one. That's the UUID (universally unique identifier), version 4: 128 bits, of which 122 are random (the other 6 encode the version and a "variant" marker, so that any tool can tell what kind of UUID it's holding). The space has 2¹²² ≈ 5 × 10³⁶ possible values. The collision arithmetic is the birthday paradox, the same effect that makes two people in a room of 23 likelier than not to share a birthday: you reach a 50% chance of a single collision after about the square root of the space, roughly 2.7 × 10¹⁸ IDs. Generate one billion UUIDs per second, every second, and you'd get there in about 86 years. Your database will not live 86 years. Your company will not generate a billion IDs a second. Randomness at this scale is uniqueness: no coordination, no central anything, every machine independent.
One caveat that has bitten real systems: the arithmetic assumes the random bits are actually random. A UUID library seeded from a weak source, or two virtual machines cloned from the same image with the same random state, can collide in an afternoon. Use the operating system's cryptographic random source (every serious UUID library does) and the birthday math holds.
Version 4 is the one everyone means, but the version number is a map worth keeping, because you'll meet the others in the wild:
- v1 (the 1980s design, standardized later): a timestamp plus the machine's MAC address (its network card's hardware address). Time-ordered in principle, but the timestamp is stored with its low bits first, so it doesn't sort, and it leaks the hardware address. Mostly historical.
- v3 and v5: name-based. Hash a namespace and a name (MD5 for v3, SHA-1 for v5) and you get the same UUID every time, which is Section 9's deterministic-ID idea in standard form.
- v4: random. The default for two decades.
- v6: v1 with the timestamp bits reordered so it sorts. A compatibility bridge.
- v7: a millisecond timestamp followed by random bits, sortable and decentralized. Section 7's subject, and the modern default.
- v8: "do what you like," a reserved version for custom layouts so vendors stop inventing incompatible ones.
The properties that follow from v4: UUIDs are opaque (you can't read anything from one, which is a security feature: nobody can guess your user count or walk rows 1 through 100,000), decentralized (a phone in airplane mode can mint IDs that will never collide when it syncs later), and standard (every language generates them, every database stores them, usually in a native 16-byte type).
Picture the offline-first field app: survey workers in areas with no connectivity, creating hundreds of records a day on tablets and syncing when they find signal. With auto-increment, syncing would be a collision nightmare, every tablet's record #1. With UUIDs, sync is a dumb merge: insert everything, zero conflicts, ever. When the generators can't talk, randomness is the only agreement they need.
The costs, stated plainly. Sixteen bytes is twice a bigint and four times an int, and the bloat multiplies: in MySQL's InnoDB every secondary index stores a copy of the primary key alongside its own entry, so a billion-row table with five secondary indexes carries the 8 extra bytes six times over, about 48 GB of pure ID. They're unreadable in a debugger (550e8400-e29b... tells you nothing), and there's a classic mistake that makes them worse: storing the 36-character text form in a VARCHAR instead of the 16-byte binary form, which doubles the size again and slows every comparison. And the big one, which gets its own section next: they're random, so they're unordered, and your database's index cares about order, deeply.
When to use: when generators are decentralized or offline, when opacity is a feature (user-facing IDs), when you need zero coordination and can pay the storage and index costs. UUIDs are the default answer to "IDs without coordination," and Section 7 is the version of that answer you should probably use.
Section 3 — The ordering problem: why random IDs hurt your index
Random IDs have a hidden price, and your index pays it. Page splits, fragmentation, write amplification, and the two database designs that pay it differently. This is the cost of Section 2 that motivates everything clever that follows.
Your database's primary key lives in a B-tree: rows sorted by key, packed into fixed-size pages (16 KB in InnoDB, 8 KB in Postgres), with a tree of index pages above them that lets any key be found in a few hops. With auto-increment IDs, every insert goes to the end. The rightmost page fills up, a new page is allocated (InnoDB even has a special fast path for splits at the right edge), and life is simple. The tree stays dense, sequential scans fly, and the write pattern is a calm append into a page that's already in memory.
Now insert UUIDs. Each new ID is random, so each insert lands at a random position in the tree. The target page is probably full, so the database splits it: half the rows stay, half move to a new page, parent pointers update. The next insert hits another random full page. And another. The tree becomes Swiss cheese, half-empty pages scattered across the disk, every insert doing the write work of several page writes (that's write amplification, more bytes written to disk than the row is worth), and the buffer pool (the database's in-memory cache of pages) thrashing because the working set is the entire tree instead of its tail.
Where the pain lands depends on how the database stores the table, and this is worth knowing before you blame the ID. InnoDB is clustered: the table is the primary-key B-tree, rows physically sorted by ID, so random primary keys fragment the whole table, and every secondary index (which points at rows by primary key) gets fatter too. Postgres stores rows in an unordered heap and keeps the primary key as a separate index, so the heap is fine and only the index fragments; but Postgres has its own amplifier, because the first time any page is modified after a checkpoint it writes the whole page to the write-ahead log (a "full-page write"), and random inserts touch far more distinct pages per second than sequential ones do. Different mechanism, same verdict.
Picture the migration that teaches it. A team moves its primary keys from auto-increment to UUIDv4, for good reasons (opacity, decentralization), and watches write throughput fall off a cliff over three weeks. Not immediately: at first the tree fits in RAM and splits are cheap. Then it outgrows memory, and every insert becomes random disk I/O plus split amplification. Their p99 insert latency (the time 99% of inserts come in under) goes from 2 ms to 80 ms. The fix is not to go back but to move to Section 7's time-ordered IDs, which append almost like auto-increment while keeping the decentralization. The database has nothing against randomness, but its index prefers the future to arrive in order.
The numbers to internalize: pages filled by random inserts settle at roughly half to two-thirds full instead of nearly full, so the same data makes an index about 1.5 to 2 times larger than sequential inserts would, and the write amplification from splits can multiply the I/O per insert several-fold once the tree exceeds RAM. If your write path is hot, ID order isn't cosmetic; it's performance.
When this matters: high-write tables with indexes bigger than RAM, and any table in a clustered store. If your table is small or write-light, UUIDv4's fragmentation is a footnote. If you're inserting millions of rows a day, it's the whole story, and you want Section 4 or Section 7.
Section 4 — Snowflake: time, machine, sequence
In this section: we dissect Twitter's famous answer, the Snowflake ID. Sixty-four bits of timestamp, machine, and sequence give you ordered, unique, coordination-free IDs; "roughly ordered" is the precise description; and the machine-ID bits hide the one coordination problem the scheme doesn't remove.
In 2010, Twitter had the problem at scale: tens of millions of tweets a day (about 50 million early in the year, 65 million by summer), generated across many machines, needing IDs that were unique and roughly time-ordered, because the API let clients ask for "everything since this ID" and the database they planned to move to, Cassandra (a distributed store with no central counter), had no sequence to lean on. Their answer, Snowflake, packs everything into 64 bits:
- 1 bit unused, kept at zero so the number is always positive in a signed 64-bit integer.
- 41 bits: timestamp in milliseconds since a custom epoch, about 69 years of runway.
- 10 bits: machine ID, 1,024 generators, assigned before the generator starts.
- 12 bits: sequence number, reset each millisecond, 4,096 IDs per generator per millisecond.
Read it left to right: IDs sort by time first, then machine, then sequence. Two IDs generated on the same machine are strictly ordered; IDs from different machines in the same millisecond interleave, but stay within that millisecond's band. Twitter's README calls this k-sorted: every ID is within a bounded distance of its true time position, with a promise of one second and a target of tens of milliseconds. That's what "ordered" ever meant in practice, and it's good enough for timelines, pagination, and time-range scans.
The throughput arithmetic: 4,096 IDs per millisecond per generator is about 4 million IDs per second per generator, and generation is pure local arithmetic, no network, no lock. (Twitter's stated requirement was a modest 10,000 per second per process with a 2 ms response; the format has more than two orders of magnitude of headroom.) The IDs are 8 bytes and mostly appending, which is exactly what Section 3's B-tree wanted.
The catches, which are real. First, the machine ID must be unique per generator, and that's the coordination Snowflake doesn't eliminate, it just moves it out of the hot path. The original Snowflake was a service, a Thrift server written in Scala, and workers claimed their IDs through ZooKeeper at startup. Since then, every organization has re-solved the problem its own way: a config file or an environment variable (fine until someone copies a config), the pod's ordinal in a Kubernetes StatefulSet, a row inserted into a database table to claim a number (Baidu's UidGenerator, which spends a fresh row on every restart), a persistent node in ZooKeeper (a coordination service) cached on local disk (Meituan's Leaf), or the low bits of the host's private IP address (Sony's Sonyflake). The trap is exhaustion: 10 bits is 1,024 workers for all time, none of those schemes reclaims a number on its own, and an autoscaled fleet that mints a fresh worker ID per container burns through 1,024 in a week. Add a lease and a reclaim step, and alert when the pool runs low. Second, the IDs are predictable: if you see tweet ID X, you know roughly when it was created, and anyone collecting a few IDs an hour can estimate your posting rate (Section 8). Third, the whole scheme trusts the clock, which is Section 5, because clocks lie.
Two pieces of Twitter history explain why 64 bits was chosen with such care, and both are worth knowing because they will happen to you at a smaller scale. In June 2009, the "Twitpocalypse": tweet IDs passed 2,147,483,647, the largest value a signed 32-bit integer can hold, and third-party clients that had stored IDs in 32-bit fields broke. Then in late 2010, as Snowflake's 64-bit IDs arrived, a second cliff: JavaScript can't represent integers above 2⁵³ exactly, so any web client parsing the JSON API would silently round the new IDs, and Twitter shipped an id_str field carrying every ID as a string a few weeks ahead of the switch. An ID format is a platform primitive. Chosen well, an entire company builds on it; chosen without thinking about every consumer, it becomes the thing everyone works around.
The format is a template, and the variants show how flexible it is: Discord uses 42 bits of milliseconds since the start of 2015, then 5 bits of worker and 5 bits of process, then a 12-bit increment. Instagram generates IDs inside Postgres itself, with a function per logical shard: 41 bits of time, 13 bits of shard ID, and 10 bits from the shard's own sequence, so the ID carries its shard's address, which the sharding post (#3) used. Sony's Sonyflake trades throughput for runway: 39 bits of time in 10-millisecond units (174 years), 8 bits of sequence, 16 bits of machine ID taken from the private IP. Re-cut the bits to fit your fleet size, your lifetime, and your burst rate; the shape stays the same.
When to use: when you need high-throughput, roughly ordered, compact IDs from many machines, and you can assign worker IDs reliably and trust your clocks (with Section 5's safeguards). It's the workhorse of the industry.
Section 5 — Clock trouble: the lies time tells
Snowflake's foundational gamble: that machines agree on what time it is. Clock skew, NTP steps, the leap second (and its scheduled retirement), then the defenses, the menu of policies for a clock that runs backward, and the hybrid clock that makes the whole problem go away.
Every time-based ID scheme assumes the clock moves forward, uniformly, on every machine. Real clocks do not. Clock skew: two machines' clocks disagree by milliseconds, or, after a bad sync, by seconds, so machine A's "now" is behind machine B's, and A's IDs sort before B's older IDs. Annoying, usually harmless, and the reason Snowflake promises k-sorted rather than sorted. NTP steps: NTP (the protocol that keeps clocks in sync) normally nudges a drifted clock gently, but when the drift is large it steps the clock, and a step can go backward. A Snowflake generator that just minted IDs at timestamp T, then sees the clock read T − 5000 ms, will happily mint duplicate (timestamp, machine, sequence) triples. That's the exact failure mode, and it's silent until the primary-key constraint explodes. Leap seconds: the occasional 61st second in a minute, inserted to keep atomic time aligned with the Earth's rotation, handled differently by different systems (some step, some smear it across the day) and the cause of a whole genre of 2012-era outages. Two updates on that front: no leap second has been inserted since the end of 2016, and in 2022 the world's metrology bodies voted to stop inserting them altogether by 2035. The code that handles them is still in every operating system, so smear-versus-step still matters, but it's a shrinking problem rather than a growing one.
The defenses, in order of practicality:
- Never trust, always guard. The generator remembers the last timestamp it used. If the clock reads earlier than that, it refuses to generate, or sleeps until the clock catches up. This is what Twitter's original code did ("snowflake will refuse to generate ids until a time that is after the last time we generated an id"), and it trades brief unavailability for guaranteed uniqueness, the right trade every time.
- Choose the policy for the size of the jump. A backward jump of a few milliseconds: wait it out. A jump of a few seconds: some generators keep minting using the last timestamp and burn through the sequence bits (borrowing from the future, which keeps the IDs unique and only slightly misordered). A jump of minutes: something is deeply wrong, and the right move is to fail loudly rather than mint IDs in a time warp. Write the thresholds down; don't let the library's default decide.
- Monotonic clocks where it matters. Every operating system offers a clock that can't step backward (it counts time since boot, not wall-clock time). Use it for the sequence logic and the "did time move forward" check, even if the wall clock provides the timestamp bits.
- Monitor the skew. Alert on NTP offset the way you'd alert on disk space (the observability post, #9). Clock health is ID health.
- Configure NTP to slew, not step. Most NTP daemons can be told never to step the clock backward and to correct large offsets by running slightly slow instead. The Snowflake README pointed at this option in 2010, and it's still the cheapest fix on the list.
And one defense that dissolves the problem instead of guarding it: the hybrid logical clock (HLC, 2014). An HLC timestamp is the wall-clock time plus a logical counter that increments whenever the wall clock fails to move forward, and it's carried along on every message, so a node that receives a timestamp ahead of its own clock adopts it. Time never goes backward from the ID's point of view, causality is preserved across machines, and the wall-clock part stays close enough to real time to be useful. CockroachDB runs on HLCs; if you're building a distributed ID generator from scratch today, it's the clock to build on.
One I keep coming back to, quieter than the leap-second stories and more instructive: a VM running Snowflake-style IDs whose clock drifted 30 seconds slow after a host migration. Its IDs sorted a month's worth of "new" rows into the past. Nothing broke, because the IDs were unique, but every "latest first" query silently misordered a slice of data for a week before anyone noticed. Time-based IDs don't just need the clock to be right. They need wrongness to be loud.
When to use these defenses: always, with any time-based scheme. The guard ("never go backward") is five lines of code, and it's the difference between a scheme that's production-ready and one that's a demo.
Section 6 — The ticket server: centralization done right
Back to coordination, but done cleverly. Flickr's ticket servers proved that "centralized" doesn't have to mean "bottleneck," and the range-allocation pattern that grew out of the same idea is the one the URL shortener's key generation service was built on.
Section 1 dismissed the central counter as a bottleneck. Flickr's 2010 write-up is the counter-argument, and it's worth describing as it actually worked, because the version that circulates is subtly wrong. Flickr ran two tiny MySQL servers, both live, each holding a table with a single row. To get an ID, an application server ran one statement against either server: a REPLACE INTO on that one row, which bumps the auto-increment counter, followed by SELECT LAST_INSERT_ID(). One round trip, one ID. The two servers were configured so that one handed out only odd numbers and the other only even (MySQL's auto_increment_increment = 2 with offsets 1 and 2), so both could serve at once, neither needed to know about the other, and losing one meant losing half the numbers, not the service. IDs were unique across every Flickr shard, roughly ordered, and plain 64-bit integers, on infrastructure its engineers called "the dumbest possible thing that will work." Sometimes the best distributed system is a centralized one with the work removed.
Flickr paid a network round trip per ID, which was fine at photo-upload rates. The natural extension when it isn't fine is to hand out ranges instead of single IDs: an app server asks the allocator for a block ("you own 8,421,000 to 8,421,999"), then mints from that block locally, no network, until it runs out. The central database does one tiny transaction per thousand IDs instead of per ID. This is an old idea with several names: Hibernate's hi/lo generator did it for object-relational mappers in the early 2000s; Meituan's Leaf calls it segment mode and keeps two segments buffered so the refill never blocks the hot path; and the URL shortener's Step 6 gave it a job title, the key generation service, which allocates ranges centrally and mints at the edge.
The properties of the range version: IDs are compact integers, globally unique, and roughly sequential (ranges interleave across servers, so 8,421,003 may be minted after 8,422,001), and the hot path never touches the network. "Gapless" carries an asterisk: a server that dies mid-block takes the rest of its thousand with it, so the numbers have holes, which is fine for keys and not fine for anything an accountant reads. And the block size is a real knob. Bigger blocks mean fewer round trips and bigger holes on a crash; smaller blocks mean the opposite; and a block should be sized so that a server refills every few seconds, not every few milliseconds and not once a day.
The costs, plainly: it's still a single logical allocator, so if the allocator is down and every server has exhausted its block, minting stops everywhere (mitigated by making the allocator's table tiny, replicated, and boring, and by refilling blocks before they run out). IDs are predictable, sequential integers, fine for internal rows and terrible for anything user-facing where enumeration matters (Section 8). And there's operational surface: one more critical service to run, monitor, and fail over, and the replication post (#5) covers what happens to a counter when its database fails over.
When to use: when you want compact, roughly sequential integer IDs across shards, your ID rate fits "blocks per second" (thousands of allocations a second is plenty when each covers a thousand IDs), and you can tolerate the allocator as a critical-but-simple dependency. It's the least fashionable option here and often the most pragmatic.
Section 7 — The modern middle: ULID and UUIDv7
The best of both worlds. The decentralization of UUIDs with the order-friendliness of Snowflake: how time-ordered randomness fixed Section 3's fragmentation problem, what the bit layout really is, and which databases will generate it for you now.
Remember the two complaints: UUIDv4 is unordered (Section 3's index pain), and Snowflake trusts clocks and needs machine IDs (Sections 4 and 5). The modern answer combines them: a timestamp in the high bits, then random bits. ULID (universally unique lexicographically sortable identifier, 2016): 128 bits, a 48-bit millisecond timestamp followed by 80 random bits, written as 26 characters of Crockford's Base32 (no ambiguous letters, case-insensitive), and lexicographically sortable: sort the strings and you've sorted by time. UUIDv7 (standardized in RFC 9562, May 2024): the same idea inside the official UUID format, so it works everywhere UUIDs work. Its exact layout, since the diagrams tend to round it: 48 bits of Unix milliseconds, 4 bits of version, 12 bits of random (or a sub-millisecond counter, if you want ordering within the millisecond), 2 bits of variant, and 62 more random bits, which is 74 random bits in total. A third sibling worth knowing is KSUID (Segment, 2017): 32 bits of seconds plus 128 random bits, the same family with different packing, 20 bytes that print as 27 characters of Base62.
Why this fixes Section 3: inserts arrive in roughly increasing order, the B-tree sees appends again, page splits collapse, and the index stays dense. You keep v4's zero-coordination generation (any machine, any time, no machine IDs to assign, unlike Snowflake) and lose the worst of its fragmentation. The randomness within each millisecond still scatters slightly, but "slightly scattered appends" and "uniformly random" are different universes for a B-tree. Two honest caveats. The IDs now leak their creation time, like Snowflake's, which is usually fine and occasionally not (Section 8). And the k-sorted skew from Section 5 applies here too, since the timestamp is whatever the minting machine's clock said.
Picture the team on UUIDv4 primaries, living Section 3's fragmentation with p99 inserts climbing, that migrates to UUIDv7. Same 128-bit columns, same code paths, a different generator. Write throughput recovers most of the way to sequential-insert performance, and the migration is a library swap, not a schema change. The cheapest performance fix is sometimes a better random number.
Details worth knowing. Within a single millisecond, the recommended generators keep the random bits monotonic, incrementing rather than re-rolling, so IDs minted in a burst stay ordered and unique without coordination (RFC 9562 describes several ways to do it, including that 12-bit sub-millisecond counter). And the databases have caught up: Postgres 18 (2025) generates them natively with uuidv7(), MariaDB 11.7 has UUID_v7(), and MySQL still doesn't ship one as of mid-2026, so on MySQL you generate in the application or install a component. Whichever database, store them in the native 16-byte type, never as text.
When to use: this is the default I'd recommend to most teams starting today. Decentralized like v4, index-friendly like Snowflake, standardized, no machine-ID assignment, no central service. Reach for Snowflake when you need 64-bit compactness or extreme throughput; reach for ticket servers or ranges when you need compact sequential integers.
Section 8 — Failure modes: the ways IDs break
In this section: we tour the wreckage. Exhaustion and the 32-bit cliff, sequence overflow, hotspots (with the precision the sharding post demands), predictability and what it leaks, and the ID that comes back from the dead after a failover. Each scheme has a characteristic way of dying, and knowing yours in advance is the job.
Exhaustion. Every fixed-width scheme has a last day. Snowflake's 41-bit timestamp runs about 69 years from its custom epoch; pick the epoch wrong, or live long enough, and the IDs wrap. The 12-bit sequence allows 4,096 IDs per millisecond per generator; a viral millisecond can exceed that, and the correct behavior is to wait for the next millisecond (backpressure, which the resilience post, #2, explains), never to overflow into the machine bits. And the most common exhaustion of all has nothing to do with clever schemes: it's the 32-bit integer column. A plain INT primary key tops out at 2,147,483,647, which sounds unreachable until an events table gets there, and then inserts fail. Basecamp went read-only for almost five hours in November 2018 when a column hit that limit. The cure is a migration to BIGINT, and on a big table it's a project, because changing the column type rewrites the table and its indexes; the practical route is to add a new bigint column, backfill it in batches, swap the primary key in a short lock, and fix every foreign key and every application type that assumed 32 bits along the way. Size your ID space for the business's lifetime, then double it. Sixty-four bits is the minimum respectable width; 128 is comfortable; 32 is a cliff with a date on it.
Hotspotting on time-ordered IDs. Here's the sharding post reaching into this one, and it needs one word of precision that the folklore usually drops. If your IDs are time-ordered and your data is range-partitioned by ID (shard A holds the lowest IDs, shard D the highest), every new write lands on the shard holding "now," the newest shard takes 100% of writes, and the others idle. You fixed index fragmentation (Section 3) and recreated the hotspot from the sharding post's Section 4. If instead the ID is hash-partitioned, the hash scatters the time-ordered IDs evenly and there's no write hotspot at all; what you lose is locality, since "the last hour's rows" are now spread across every shard, and a time-range scan is a fan-out, a query that has to ask every shard. So the tension is between time-ordered IDs and range sharding on the ID, not between time-ordered IDs and sharding in general. The mitigations: shard by something else (user_id, not the ID), hash the ID for placement while keeping the timestamp inside it for ordering, prefix range keys with a small salt so writes spread across a few ranges, or accept the hotspot and over-provision. Time-ordered IDs and range sharding are in tension. Design them together, not separately.
Predictability, and what an ID confesses. Picture the company whose public API uses sequential order IDs. A competitor's analyst plots order numbers against timestamps from confirmation emails and publishes their growth curve, accurate to within a few percent, a quarter before earnings. No breach, no hack, just IDs that confessed. That trick is older than software and has a name from a different century: the German tank problem. In the Second World War, Allied statisticians estimated German tank production from the serial numbers on captured tanks, closely enough to shape planning. Eighty years later the same arithmetic works on your order IDs, your user count, and your growth curve, and time-ordered IDs add a second confession, the creation timestamp of every object, which is how a "private" document's ID can tell a stranger when it was written. Two more consequences deserve their own names. Sequential IDs make enumeration trivial: a script can walk /orders/1000 through /orders/9999, and the rate limiting post (#8) is where you slow that down. And the deeper problem is the insecure direct object reference: an endpoint that trusts the ID as proof of ownership. Opacity makes IDs hard to guess; it is not authorization, and the security post (#12) is emphatic that every object access checks the caller's permission regardless of how unguessable the ID is. (The URL shortener fought the enumeration battle in Step 11 with a keyed permutation of the counter, since plain XOR and alphabet shuffles preserve the sequence.)
The ID that comes back. One more failure that belongs to the replication post (#5) but starts here. A database fails over to a replica that is a few transactions behind. The old primary had handed out IDs 1,000,001 through 1,000,040 and acknowledged them; the replica's counter says 1,000,020. The new primary now re-issues 21 through 40 to new rows, and if any of the originals were already sent to a client, cached, or written to another system, you have two objects with one name. Postgres logs sequence values ahead of use (to save a disk write per number, with the useful side effect that a restarted primary never re-issues one), which makes this rare; MySQL's persisted counter helps too; a Snowflake-style generator with a fresh worker ID is immune by construction. Whatever the scheme, the test to run before you trust it: fail the database over on a Tuesday and check that the next ID is bigger than every ID a client has ever seen.
One line for the design review, covering all of the above:
Your ID format is a public statement. Write it deliberately.
Section 9 — Going deep: the principal-level toolkit
In this section: the decision framework. We'll treat the ID as a contract, split it in two, let content name itself, settle the natural-versus-surrogate argument, handle the sharding and multi-region interplay, make IDs safe to put in a URL or read over the phone, and talk about the migration nobody wants to do.
The ID is a contract. Before picking a scheme, write down what the ID promises, because every consumer will depend on it. Is it sortable (can I ORDER BY id and mean "by time")? Is it opaque (can I expose it in a URL)? Is it compact (does it fit in 64 bits for that legacy system, and in JavaScript's 53 for that web client)? Is it unpredictable? Is it ever reused? Does it leak anything: a timestamp, a shard number, a fleet size? Every property you don't specify will be assumed by someone, and the assumption will be load-bearing by year three. Put the contract in writing. Version the format (a v1_ prefix, or a reserved bit, is cheap insurance), and reserve bits: Pinterest's ID layout kept two spare bits and its engineers later wrote that reserved bits are "worth their weight in gold."
The two-ID pattern: one for the machine, one for the public. Everything so far treated "the ID" as one thing. The production answer is often two. An internal ID: a compact bigint from a sequence or a Snowflake, used in joins and foreign keys, never leaves the building. An external ID: opaque, prefixed, random-based (ord_9f3k...), used in URLs, APIs, and anything a customer can see. The internal ID stays fast and ordered where the database feels it; the external one stays opaque and unguessable where the world touches it; the mapping lives in one indexed column. It looks like overhead until the first time you need to change the external format and discover the internal one doesn't care.
Deterministic IDs: let the content name itself. One more scheme that doesn't fit the spectrum: derive the ID from the payload, a hash of the content. Git commits, content-addressed storage, and a lot of dedup pipelines work this way, and UUID versions 3 and 5 are the standardized form. The superpower is free idempotency (the idempotency post, #6): the same content always yields the same ID, so retries and duplicate deliveries collapse into no-ops. The price is that the ID leaks nothing except sameness: anyone who can guess the content can confirm it exists, so salt the hash with a secret or a namespace when that matters. And mind the width: a 64-bit hash reaches a 50% chance of collision after only about five billion items, which is reachable, so content hashes should be 128 bits at minimum and are usually 256.
Natural, surrogate, or composite. A natural key is a real-world identifier used as the primary key: an email address, a national ID number, an ISBN. It's tempting because it's meaningful, and it's almost always a mistake, because real-world identifiers change (people change emails), get reused, and turn out not to be unique (two products, one barcode). A surrogate key is a meaningless ID the system assigns, which is everything else in this post, and it's the default for a reason. The refinement the sharding post (#3) already argued for is the composite key: a surrogate ID prefixed by its owner, (tenant_id, order_id), so a row carries its own routing information and the shard key is never missing from a lookup. Keep the natural identifier as a uniquely indexed column; don't make it the name.
The decision framework, as a flowchart you'd actually use in a design review:
In prose: if your generators can coordinate cheaply and you want compact sequential integers, ticket servers, ranges, or a single database sequence are underrated. If they can't coordinate and order matters, UUIDv7 or ULID is the modern default, with Snowflake when 64 bits or extreme throughput demands it. If order doesn't matter at all, UUIDv4 and move on. There is no best ID. There is only the ID whose trade-offs match your constraints.
Opacity versus order is the fundamental tension. Time-ordered IDs are debuggable, sortable, and index-friendly, and they leak time (and sometimes rates). Random IDs are opaque and coordination-free, and they fragment indexes and tell you nothing in a debugger. Every scheme in this post is a point on this spectrum; the principal move is choosing your point deliberately rather than inheriting it from a tutorial, and the two-ID pattern is how you take two points at once.
Design the ID and the shard key together. The sharding post's Section 4 chose the shard key; this post chooses the ID, and they're often the same column. If the ID is time-ordered and the data is range-sharded on it, you get Section 8's hotspot. If the shard key is user_id and the ID is a UUID, placement is even but "latest rows across all users" fans out. Instagram's answer is the elegant one: put the shard number inside the ID, so any row can be routed from its own identity with no lookup. The ID strategy and the sharding strategy are one decision. Make it in one design review.
Multi-region. When generators run in several regions, the machine bits become an address. Reserve a few of them for the region (Discord's worker and process bits are the same idea one level down), allocate worker IDs per region so two regions can never hand out the same one, and accept that the timestamp bits will disagree across regions by the clock skew between them, which is why "roughly ordered" was always the promise. Range allocation works across regions too, as long as each region's allocator hands out ranges from a region-specific band, and a hash-based ID needs nothing at all, which is one more argument for UUIDv7 when the fleet is global.
Make it safe to put in a URL, a log, or a phone call. An ID's representation is a separate decision from its bits. Base62 (the URL shortener's Step 4; digits plus both cases of letters) packs more into each character than Base32 and is case-sensitive, which is fine in a URL and a disaster read aloud. Crockford's Base32 (ULID's choice) drops the letters that look like digits (I, L, O) and U (to avoid accidental obscenities), is case-insensitive, and is what you want for anything a human might type. And for IDs humans transcribe, a check digit (the Luhn algorithm on credit-card numbers, the final digit of an ISBN) catches most transposed pairs and misread characters before they reach the database. Prefixes belong here too: Stripe's cus_, ch_, and pi_ style, which Paul Asjes's write-up on their object IDs explains, buys type safety across APIs (you can't pass an order ID where a customer ID goes), readability in logs, and a place to hang a version, all for one underscore. It's the cheapest principal-level upgrade in this entire post.
Migration: the surgery. Changing ID formats on a live system is a year-long project: dual-write new IDs alongside old, backfill, migrate every foreign key, every API consumer, every bookmark and webhook payload, then cut over and keep the old IDs resolving forever, because the internet never forgets a URL. The teams I've watched do this successfully treat the old format as a permanent alias, not a deleted thing, and they do the smaller version of the surgery, the 32-bit-to-64-bit widening from Section 8, before the cliff rather than during the outage. Which is why Section 1's real lesson was never about sharding but about this: choose the ID as if it's forever, because it nearly is.
IDs, distilled
For the skimmers and the revisitors: everything above, on one page.
Key numbers:
| UUIDv4 collision bound | 122 random bits; 50% chance of one collision after ~2.7 × 10¹⁸ IDs, which is ~86 years at a billion per second |
| UUIDv7 layout | 48-bit ms timestamp, 4-bit version, 12 random (or counter) bits, 2-bit variant, 62 random bits: 74 random bits total |
| ULID / KSUID | 48-bit ms + 80 random (26 chars of Crockford Base32) / 32-bit seconds + 128 random (27 chars of Base62) |
| Snowflake layout | 1 + 41 (ms, ~69 years) + 10 (1,024 workers) + 12 (4,096 per ms) = 64 bits; ~4M IDs/sec per generator |
| Snowflake's promise | k-sorted: within 1 s of true time position promised, tens of ms targeted |
| Variants | Discord 42/5/5/12; Instagram 41/13/10 inside Postgres; Sonyflake 39 (10 ms units, 174 yr)/8/16 |
| Twitter's two cliffs | 2³¹ − 1 = 2,147,483,647 (June 2009); 2⁵³ in JavaScript (late 2010; id_str shipped ahead of it) |
| 32-bit INT primary key | Ends at 2,147,483,647; Basecamp went read-only for five hours on it in November 2018 |
| Ticket server, Flickr style | One REPLACE INTO + LAST_INSERT_ID() per ID; two servers, odd and even |
| Range allocation | One transaction per block (e.g. 1,000 IDs); holes on crash; refill before empty |
| Random-insert index bloat | Pages ~50–67% full; index ~1.5–2× larger than sequential once the tree exceeds RAM |
| UUID storage tax | 16 bytes vs 8; InnoDB repeats the PK in every secondary index; 1B rows × 6 copies ≈ 48 GB extra |
| 64-bit hash birthday bound | ~5 billion items to a 50% collision; don't use 64-bit hashes as IDs at scale |
| Native UUIDv7 | Postgres 18 uuidv7(); MariaDB 11.7 UUID_v7(); MySQL none as of mid-2026 |
| Leap seconds | None inserted since end of 2016; to be discontinued by 2035 |
Every trade-off, in one table:
| Decision | Chose | Over | Why |
|---|---|---|---|
| Single database | Auto-increment / sequence | Anything fancy | Tiny, ordered, index-friendly; perfect until you shard (and never gapless) |
| Sharded generators | Zero coordination at mint time | Shared counter per ID | Per-ID coordination is a bottleneck and a single point of failure |
| No coordination, no order needed | UUIDv4 | Snowflake | No machine IDs, no clocks; pay in size and index fragmentation |
| No coordination, order needed | UUIDv7 / ULID | UUIDv4 | Time-ordered prefix restores append-friendly indexes; native in Postgres 18 |
| Order + 64-bit compact + huge throughput | Snowflake | UUIDv7 | 8 bytes, ~4M IDs/sec per generator; pay in worker-ID assignment and clock trust |
| Worker IDs | Assigned by something durable (ZooKeeper, a DB row, a pod ordinal), with a lease and a reclaim step | Hand-edited config | 1,024 is forever unless IDs come back; copied configs collide |
| Compact sequential integers across shards | Ticket servers or range allocation | Per-ID central counter | Flickr: two live servers, odd/even; ranges: one transaction per thousand |
| Clock goes backward | Refuse or wait (small), borrow the last timestamp (medium), fail loudly (large) | Mint through it | A backward clock reuses timestamps → duplicate IDs; unavailability beats duplication |
| Clock source | Monotonic clock for the checks; HLC if building from scratch | Wall clock everywhere | Wall clocks step; monotonic ones don't; HLCs never go backward by construction |
| Storage | Native 16-byte / 8-byte types | UUIDs as 36-char text | Text doubles the size and slows every comparison |
| Public IDs | Opaque, prefixed | Sequential | Sequential IDs leak counts, rates, growth curves; time-ordered ones leak timestamps |
| Authorization | Check ownership on every access | Trust an unguessable ID | Opacity is not authorization (IDOR) |
| Internal vs external IDs | Two IDs (bigint inside, opaque outside) | One ID for both | Change the public format without touching the joins |
| Dedup-friendly IDs | Content hash, ≥128 bits, salted when secrecy matters | Random | Same payload → same ID; retries collapse into no-ops |
| Key kind | Surrogate, composed with its owner | Natural keys | Emails change, barcodes repeat; (tenant_id, id) routes itself |
| Shard key vs ID | Design together; carry the shard in the ID if you can | Choose separately | Time-ordered ID + range sharding on it = all writes hit the "now" shard |
| Multi-region | Region bits in the worker ID; per-region ranges | One global pool | Two regions must never mint the same worker ID |
| Representation | Base62 in URLs; Crockford Base32 + check digit for humans | Whatever the library prints | Ambiguous letters and transposed digits are support tickets |
| Width | 64 minimum, 128 comfortable | 32-bit INT | 2,147,483,647 is a cliff with a date on it; widen before, not during |
| Format longevity | Contract + version prefix + reserved bits | "We'll migrate later" | ID migration is a year-long, dual-write, keep-old-aliases-forever project |
| Debuggability | Prefixed IDs (ord_...) |
Bare integers/UUIDs | Type safety across APIs, readable logs, format versioning for one underscore |
Three ideas to take with you:
- An ID is a contract, chosen once. Sortability, opacity, width, unpredictability, what it leaks: every property you don't specify becomes someone's load-bearing assumption. Write the contract down, version the format, reserve bits, and choose as if it's forever.
- Randomness buys independence; time buys order; you pay for both somewhere. UUIDs pay in index fragmentation, Snowflake pays in clock trust and worker assignment, ticket servers pay in a central dependency. There is no free ID, only the price you prefer.
- The ID strategy and the sharding strategy are one decision. The shard key chapter and this chapter are the same design review. Time-ordered IDs plus range sharding on the ID is a hotspot; opaque IDs plus hash sharding is even but unsortable; a shard number inside the ID routes itself. Decide them together.
Further reading
- Twitter Engineering, Announcing Snowflake (2010) and the archived Snowflake README. The 41/10/12 anatomy, the k-sorted promise, and the clock guard, in the original words.
- Flickr, Ticket servers: distributed unique primary keys on the cheap (2010). One row,
REPLACE INTO, two servers on odd and even; behind Section 6. - RFC 9562, Universally Unique IDentifiers (2024). The standard for every UUID version including v7, with the monotonic-counter techniques from Section 7.
- ULID specification and KSUID. The other members of Section 7's family.
- Instagram Engineering, Sharding & IDs at Instagram (2011). IDs generated inside Postgres with the shard number built in; behind Sections 4 and 9.
- Discord, How Discord Stores Billions of Messages (2017) and the Discord API reference on snowflakes. The 42/5/5/12 layout in production.
- Sony, Sonyflake. A Snowflake re-cut for a longer lifetime and a bigger fleet.
- Kulkarni et al., Logical Physical Clocks and Consistent Snapshots in Globally Distributed Databases (2014). The hybrid logical clock from Section 5.
- Paul Asjes, Designing APIs for humans: Object IDs (Stripe, 2022). Why the prefixes are worth the underscore.
- Falsehoods programmers believe about time. The catalog of clock lies; background for Section 5.
Where you'll meet this
This post is the missing half of the sharding post's (#3) Section 4: the shard key chapter chose which column decides placement, this one chose what fills that column, and Section 8 showed what happens when the two choices fight. It was hiding in the URL shortener too: the short code is a unique-ID problem (Step 4's alphabet, Step 6's range-allocating key generation service, Step 11's keyed permutation against enumeration), compact, opaque, and coordination-free, exactly the properties this post trades off. The replication post (#5) picks up the counter that fails over, the idempotency post (#6) is what deterministic IDs buy you, and the security (#12) and rate limiting (#8) posts are where guessable IDs become someone else's problem. Next up in Core Concepts is keeping copies of your data that actually agree (#5): replication, failover, RPO and RTO, and the consistency models that decide what "the truth" means when there are several copies of it.
Keep exploring: the Core Concepts series
Every post in the series stands alone. Read them in any order.
- From Laptop to Billions of Clicks: Designing a URL Shortener in 11 Steps — the anchor: one design, every concept under load.
- #1 The 100:1 Superpower: Caching — the fastest request is the one you never make.
- #2 The Blast Radius: Surviving the Day Your Dependencies Fail — staying up when everything you depend on goes down.
- #3 Divide and Conquer: Sharding and Partitioning — splitting one database into many without losing your mind.
- #4 The Snowflake Problem: Unique IDs at Scale — naming things when millions are born every second. (this post)
- #5 Copies of the Truth: Replication — keeping copies of your data that actually agree.
- #6 Do No Harm Twice: Idempotency — making "just retry it" safe.
- #7 The Copy at the Doorstep: CDNs and Edge Computing — serving from next door instead of across the ocean.
- #8 The Bouncer's Math: Rate Limiting — saying no politely, at scale.
- #9 What Broke at 3 AM: Observability, p99, and Useful Alerts — knowing what's wrong before your users tell you.
- #10 Do It Later, On Purpose: Async Processing and Queues — the work the user doesn't have to wait for.
- #11 The Traffic Cop: Load Balancing — the box in every diagram nobody explains.
- #12 Assume They're Already Knocking: Security and Abuse at Scale — designing for the users who are designing against you.
- #13 Do the Math First: Estimation for System Design — the two minutes of arithmetic that choose the architecture.
- #14 The Whiteboard Playbook: Taking On Any System Design Challenge — the whole series in forty-five minutes.
Blueprints of Scale — Core Concepts #4. If this helped, the best thanks is a share with someone who's learning.