Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Unique ID generation

Why auto-increment stops scaling, ranges handed out by one counter, random vs time-ordered IDs (UUIDv4, UUIDv7, ULID, Snowflake), leased worker ids, the clock-skew trap, and what an ID gives away.

Unique, cheap, ordered

An order ID has three jobs: be unique, be cheap to mint, and carry (or deliberately not carry) order. Every scheme in this article weights those three differently, and each also decides how far it trusts a clock. On a single database, AUTO_INCREMENT or PostgreSQL's BIGSERIAL does all three well: unique, small, sortable, easy on indexes.

It stops at the second writer, because a sequence is unique only within the database that owns it. An online shop moved its European orders to a second PostgreSQL database by copying the orders table, and both sequences carried on from the same value. The first night, the job that copies both databases into the warehouse stopped on ERROR: duplicate key value violates unique constraint "orders_pkey", with the detail Key (id)=(1048577) already exists: a pair of trainers in Lyon and a kettle in Leeds. A shard, a region or a mobile app that creates orders offline is a second writer too, and from then on the ID is a design decision that reaches your indexes, your URLs and what your IDs give away. Splitting the rows across databases is its own topic, Sharding & partitioning; the subject here is the key they share.

One counter, handed out in blocks

The smallest change keeps one authority and stops asking it for every row. A single sequence hands each API instance a range of IDs: one instance takes the next 1,000, another the 1,000 after that, and each mints from its block in memory, going back to the sequence once per thousand orders. The price is gaps, because an instance that restarts abandons the rest of its block, and disorder, because blocks run down at different speeds: a busy instance can be on its third block while a quiet one still mints from its first, so ID order only loosely follows creation order.

Minting with nobody to ask

The other way out removes the authority. A UUIDv4 is 128 bits, 122 of them random (the other six mark the version and variant), minted anywhere: in either database, in the API, on an offline phone. No coordination, no information leaked, and with a sound random source a collision is too unlikely to plan for.

Make it the orders table's primary key and a different problem appears months later. That key is a B-tree index ordered by ID. Sequential IDs always land on its rightmost page, which stays in memory; random IDs land on any page, so consecutive inserts touch pages all over the index. Once the index outgrows memory, inserts start reading pages from disk and splitting full ones, write throughput drops, and the cache fills with pages that one insert touched once. For B-trees in general, see Indexing & query optimisation.

Time first, randomness after

UUIDv7 (a standard UUID, RFC 9562) and ULID keep the 128 bits and the freedom to mint anywhere, but put a millisecond Unix timestamp in the top 48 bits and randomness after it. Later IDs sort roughly later, so new rows land near the right edge of the index again, and ORDER BY id comes close to ORDER BY created_at.

64 bits and a worker number

Some tables want half the size: a 64-bit integer fits a BIGINT column, and every index and foreign key that carries it is smaller. Snowflake, the layout Twitter published, packs ~41 bits of milliseconds since a chosen epoch (about 69 years of them), ~10 bits of worker id and ~12 bits of sequence within the millisecond, so each worker can mint 4,096 IDs per millisecond.

Within one generator, uniqueness rests on never issuing the same millisecond and sequence twice: past 4,096 it waits for the next millisecond, and a clock that steps back is the next section's problem. Across generators it rests on worker ids: no two generators may mint under the same worker id in the same millisecond. Handing those ids out is the one piece of coordination Snowflake cannot avoid, and it happens at start-up and on periodic renewals, never once per ID. At start-up each API instance takes a lease on a worker id from a coordination service, keeps renewing it, and mints locally. A lease is a promise with an expiry: a process frozen long enough to miss its renewals, by a long garbage-collection pause or a suspended VM, can lose worker 17 to a new instance and wake up still minting as 17, and checking the deadline before each mint cannot rule that out, because the pause can fall between the check and the mint. A timestamp ceiling can. Before issuing, a generator records in the coordination service a ceiling some seconds ahead of its clock and mints only below it; it raises the ceiling as it renews, with a write that fails once the lease is lost, and stops when that write fails. A new holder of 17 starts above the last recorded ceiling, so the two never mint the same millisecond. How leases expire, and how such a write is made to fail for a lost holder, is in Coordination services: small state, leases & watches.

Snowflake-style generators behind the write path

Each API instance mints locally; the coordination service hands out worker ids at start-up and renews the leases, and is never on the request path.

When the clock runs backwards

UUIDv7, ULID and Snowflake all put a wall-clock timestamp in the ID, and wall clocks go backwards: NTP step corrections, VM migrations or snapshot restores, a leap second applied as a step. A Snowflake generator whose clock steps back 5 ms re-enters milliseconds it has already used, and can mint the same (ms, worker, seq) twice. The rule: never mint below the last timestamp you issued, nor, after a restart or a handover, below the recorded ceiling; if the clock is behind that mark, either wait it out (a short gap, such as a step back of a few ms or the ceiling's margin after a restart) or refuse to mint and alarm (a larger one). What it must not do is issue the ID anyway.

Even with honest clocks, order across generators is only approximate: two IDs from different machines minted within a few milliseconds of each other are in arbitrary order. That is fine as a sort key for listing a customer's orders newest first and paging through them (Pagination: offset, keyset & cursors), and useless for deciding which of two trades happened first. A ledger or an order book that needs that answer takes its sequence from a single writer (Single-writer sequencing & matching engines), and the Stock Exchange Matching Engine Challenge is the place where that is the right design.

Watch outIDs talk. /orders/1048577 tells anyone roughly how many orders the shop has taken, tomorrow's order number tells them how many it takes a day, and the number invites a scan of /orders/1048576. A time-ordered ID reveals when the row was created, and a Snowflake ID also which worker made it. Wherever the identifier is visible to users and the count or timing is sensitive, show a random ID or a separate opaque public key. Worked design: a link shortener takes IDs from ranges and permutes each one before it becomes a public code, so the codes do not reveal their order.

Shortcuts that come back

  • Random UUIDs as the clustered primary key on a hot table. Every insert is a write to a random page; once the index outgrows memory, inserts slow down sharply. Use v7, ULID or a Snowflake integer.
  • A central ID service on the request path. One extra hop per write, and one outage takes every write down. Hand out ranges or worker ids, so the service is consulted once per thousand IDs, or at start-up and on lease renewals.
  • Sending a Snowflake ID to a browser as a JSON number. JavaScript numbers hold integers exactly only up to 2^53, and a Snowflake ID is past that a few weeks after its epoch, so the browser silently rounds it to a nearby number, which can be another order's ID. Send 64-bit IDs as strings.
  • Encoding meaning in the ID. A region code or a customer type inside the ID feels handy until the customer moves region, and the ID has to stay the same.

The scheme you have to defend

The Distributed Unique ID Generator Challenge asks for 64-bit IDs, roughly ordered by time, from many nodes in several data centers, with no coordination per request and through clock skew and restarts. That rules some of the schemes above out and leaves you a choice to make and defend on the drawing: where uniqueness comes from without a call on the write path, what hands out worker ids and what a generator does when its lease lapses, and which clock-skew rule is written on the generator node.

Practice this