Unique ID Generation

Back to Study

Unique ID Generation

Every user, order, message and post needs an ID that no other record will ever have. On one database this is trivial: the database counts 1, 2, 3. Once a system writes from many machines at once, and especially once its data is split across several databases, handing out unique IDs becomes a design problem of its own. This topic covers the main approaches and when to use each.

What a good ID needs

The requirements usually include:

  • Unique, across every machine, forever.
  • Fast to generate, often thousands per second per machine, without slowing every write.
  • Available: if ID generation stops, every write stops with it.
  • Compact: it appears in every index, URL and foreign key, so smaller is cheaper.
  • Often sortable by time, so that "newest first" can be answered by sorting IDs.

No single approach is best at all of these; each trades some for others.

The obvious approach: let the database count

An auto-increment column (or a sequence) gives 1, 2, 3 and so on. It is simple, compact and naturally sorted by creation order. The problems appear at scale:

  • One machine decides. Every insert in the system depends on that one database to issue the next number.
  • It does not survive sharding. With data split across several databases, each one counting from 1 produces duplicates. Giving each database a different step (one issues odd numbers, the other even) works until you need to add a third.
  • It reveals information. Sequential IDs in URLs tell anyone how many orders or users you have and let them guess other records' IDs.

The alternatives

Auto-increment, random UUIDs, a ticket server and Snowflake compared

Random UUIDs

Each machine generates a 128-bit random value (a version 4 UUID) with no coordination at all. The chance of two being equal is so small it is treated as zero. This is simple and highly available, and it is the default in many applications, including Systema's own backend. The costs: 128 bits is twice the size of a 64-bit number, the values are not sortable by time, and random values are inefficient for database indexes (see Unique IDs and B-Tree Indexes).

A ticket server

One dedicated service hands out numbers, often in blocks: a server asks for the next 1,000 IDs and assigns them locally until it needs more. IDs stay small and roughly sorted, and the ticket service is called rarely. But it is still one critical service; it needs a standby, and blocks handed to a server that crashes are simply skipped.

Snowflake: time, machine and counter in 64 bits

Twitter designed Snowflake because, as its README puts it, "There is no sequential id generation facility in Cassandra, nor should there be.". It packs three parts into one 64-bit number:

Snowflake's 64-bit layout: 41 bits of timestamp, 10 bits of machine ID, 12 bits of sequence

From Twitter's published design:

  • time, 41 bits: "millisecond precision w/ a custom epoch gives us 69 years"
  • machine ID, 10 bits: "gives us up to 1024 machines"
  • sequence, 12 bits: "rolls over every 4096 per machine", with protection against reusing a number within the same millisecond

Because the timestamp comes first, IDs created later are larger, so they sort by time. Because each machine has its own ID, machines never need to talk to each other. Twitter's stated requirements included "minimum 10k ids per second per process", IDs "under 64bits", and that "machines generating ids should not have to coordinate with each other".

Snowflake has two operational needs: every generator must be given a unique machine ID, and clocks must not jump backwards, since a clock moving back could repeat timestamps.

Time-ordered UUIDs (version 7)

RFC 9562, the current UUID standard, adds UUIDv7: a UUID that begins with a Unix timestamp in milliseconds followed by random bits. It keeps the "generate anywhere" convenience of random UUIDs while being sortable by time, and the RFC states that systems not tied to older versions "SHOULD use UUIDv7". It is 128 bits, like any UUID.

Choosing

  • One database, modest scale: auto-increment is fine. Do not over-engineer.
  • Many writers, IDs exposed publicly: random UUIDs, or UUIDv7 if the IDs are also used for sorting or as a primary key.
  • Very high volume, 64-bit IDs that sort by time: Snowflake or a similar scheme.
  • Small, sortable IDs from a central service: a ticket server, with a standby.

How to use this in an interview

When a design stores data on more than one machine, say how IDs are created. State the requirements first (unique, sortable or not, size, volume), then pick the approach that meets them and name its weakness: machine ID assignment and clocks for Snowflake, size and index locality for random UUIDs, availability for a ticket server.

Implementing it in your project

  • PostgreSQL: gen_random_uuid() creates random version 4 UUIDs. PostgreSQL 18 adds uuidv7(), a time-ordered UUID, which is the better default for new primary keys: id uuid PRIMARY KEY DEFAULT uuidv7(). On older versions, generate UUIDv7 in the application with a library.
  • Java: UUID.randomUUID() gives a version 4 (random) UUID, which is what Systema uses today. For time-ordered UUIDs, use a UUID library that supports version 7.
  • A Snowflake-style generator is a short class: read the machine ID from configuration, combine the current milliseconds, the machine ID and a per-millisecond counter with bit shifts, and guard the counter with a lock. Refuse to generate IDs if the clock moves backwards.
  • Keep internal and public IDs separate if needed: a compact time-ordered key inside the database, and a random public ID in URLs, so record counts are not exposed.

Sources and credibility

Everything above is written from scratch; the quotes come from the sources below, attributed where used.

Primary (highest confidence)

Method

  • The comparison of approaches follows the unique ID generator chapter of Alex Xu's System Design Interview, Vol. 1. The explanations are our own.

On copyright: the diagrams on this page were drawn for this article, and the explanations are original. Quoted phrases are short and attributed.