Design a Unique ID Generator (Snowflake): Sortable IDs

Auto-increment IDs clash as soon as one table is split across several databases, because each database counts 1, 2, 3 on its own. Two different rows end up with the same ID. A unique ID generator fixes this by handing out IDs that never clash.

An auto-increment column is one the database fills with the next number for you. It is simple and safe while one database holds every row.

Picture a common setup. A short-story app in Lucknow keeps stories, comments, likes and follows in one PostgreSQL database, and each story's ID comes from an auto-increment column. The setup is made up, but the problem is real.

The app grows to about 30 lakh (3 million) new rows a day, and the team expects ten times that within a year. One database cannot hold and back up that many rows comfortably.

So the team splits the rows across 8 databases. This is called sharding, and each database is a shard that holds one slice of the rows. Each shard keeps its own auto-increment counter.

The next morning, a reader opens story 48213 from a shared link and sees a completely different story. Shard 2 and shard 5 had both created a story 48213.

The team needs IDs that never clash, without one machine handing out every number. The rest of this guide builds that unique ID generator and shows what each choice costs.

A good ID is unique forever, cheap to make, and small enough to store everywhere. For most apps it should also sort roughly by the time it was created.

A distributed system is one where many machines work together over a network. The app runs 12 API servers, the programs that take requests from the phone app, in front of its 8 shards. Before choosing anything, the team writes down what an ID must do.

• Unique: no two rows anywhere ever share an ID, across all shards and servers.
• Roughly sorted by time: newer stories get bigger IDs, so the feed can sort by ID alone.
• Small: it fits in 64 bits, the size of a BIGINT (a whole-number column type), so tables and indexes stay compact.
• Local and fast: each API server makes an ID in memory, without a network call.
• Numeric: the app's tables and share links already use numbers, so text IDs would mean a big migration.

A few words in that list need a plain meaning. A bit is one binary digit, a 0 or a 1. A 64-bit number uses 64 of them and takes 8 bytes.

An index is a sorted lookup structure the database keeps, so it can find a row without reading the whole table. The primary key index is sorted by the ID itself.

Roughly sorted has a name: k-sorted. It means IDs follow time order, except for items made very close together. The original Snowflake README promised order within 1 second and aimed for tens of milliseconds.

There are five common ways: one central counter, counters that skip numbers, random UUIDs, time-ordered UUIDs and Snowflake IDs. Each one gives up something different.

One central counter

The naive first idea is a single ID service that every API server calls. It hands out 1, 2, 3 in strict order. It is easy to build, but every new row now waits on a network call to one machine.

Look at the middle of this diagram. Every write has to pass through the counter box. That makes it a single point of failure: one part whose failure stops the whole system.

Counters that skip numbers

Flickr described a cheaper trick in February 2010. It ran two ticket servers, MySQL databases whose only job was to hand out the next number. It split the numbers into odds and evens. Both counted in steps of 2, one starting at 1 and one at 2.

The team could do the same with 8 shards. Shard 1 gives out 1, 9, 17, and shard 2 gives out 2, 10, 18. Nothing clashes. But adding a ninth shard means changing every step, and a busy shard's numbers race ahead of a quiet one's.

Random UUIDs

A UUID, or universally unique identifier, is a 128-bit ID that any machine can make on its own. Version 4 fills 122 of its bits with random data, so a clash is extremely unlikely. In text it is 36 characters of hex digits (0 to 9 and a to f) and hyphens.

The problem is order. RFC 9562, the current UUID standard, says versions that are not time-ordered have poor database-index locality. In plain words, each new row lands at a random spot in the index instead of at the end.

Time-ordered UUIDs

The same standard, published in May 2024, added UUID version 7, written UUIDv7. Its first 48 bits are Unix time in milliseconds, counted from 1 January 1970. The other 80 bits are 6 fixed version and variant bits plus 74 random or counter bits.

Snowflake IDs

Snowflake packs the time, a server number and a small counter into one 64-bit number. It was built for a move from MySQL to Cassandra, a database with no sequential ID feature, as its README explains.

The table puts all five side by side. Read the last column first: it is what you give up.

You split a 64-bit number into three parts: the time, the server's own number and a counter. Each server fills in all three by itself, so servers never talk to each other to make an ID.

Think of an Indian vehicle number plate, such as UP 32 AB 1234. UP is the state, 32 is the regional transport office (RTO) that issued it, AB is the series and 1234 is the running number.

Here is how the parts map. The RTO code plays the worker ID: two offices can never print the same plate, because their codes differ. The running number plays the sequence, the counter each office moves on by itself.

Where the picture breaks: a plate carries no time at all, while Snowflake puts time first. And an office uses a series for weeks, while Snowflake's counter starts again every millisecond.

Here is the layout from the original README. 41 bits hold milliseconds since a chosen start date, called the epoch. 10 bits hold the worker ID, enough for 1,024 servers. 12 bits hold the sequence, 4,096 values per server per millisecond.

That adds up to 63 bits. The top bit stays 0, which keeps the ID positive in a signed 64-bit column such as PostgreSQL's BIGINT, whose largest value is 9,223,372,036,854,775,807.

Read this diagram from left to right, the same way the bits sit in the number. Each box's note says what that part gives you.

Why put time first? Numbers compare from their highest bits, so an ID made in a later millisecond is always bigger, on clocks that agree. Sorting stories by ID then matches sorting them by creation time, to the millisecond.

Inside one millisecond, IDs from different servers sort by worker ID, not by true order. Clocks on different servers also differ slightly. That is the k-sorted part: close, but not exact.

How long a Snowflake ID lasts

41 bits of milliseconds last about 69.7 years. The app picks its own epoch, 1 January 2025, so its IDs run out on 7 September 2094. Counting from 1970 instead would run out in September 2039.

How many IDs one server can make

One worker can make 4,096 IDs per millisecond, which is 40.96 lakh (about 4.1 million) a second. Even at ten times today's traffic, the app's evening peak would be about 2,000 rows a second, so the ceiling is far away.

Our Python generator never reached that ceiling in our tests. On Python 3.14.6, it made at most about 2,400 IDs in a single millisecond.

For each ID, the server reads its clock and compares it with the last millisecond it used. Then it sets the sequence, shifts the three parts into place and joins them into one number.

Start at the top of this diagram and follow one request. The three question boxes are where the hard cases branch off. Most requests go straight through to a fresh sequence of 0.

Here is the generator in Python, using only the standard library. A thread is one line of work inside a program, and a web server runs many at once. The lock makes sure two threads never take the same sequence number.

import threading, time

EPOCH_MS = 1735689600000  # 1 Jan 2025, 00:00 UTC: the app's own epoch

class SnowflakeGenerator:
    def __init__(self, worker_id):
        if not 0 <= worker_id <= 1023:       # must fit in 10 bits
            raise ValueError("worker_id must be 0 to 1023")
        self.worker_id, self.last_ms, self.seq = worker_id, -1, 0
        self.lock = threading.Lock()

    def next_id(self):
        with self.lock:
            now = time.time_ns() // 1_000_000 - EPOCH_MS
            if now < self.last_ms:
                raise RuntimeError("clock moved backwards")
            if now == self.last_ms:
                self.seq = (self.seq + 1) & 4095  # stays inside 12 bits
                while self.seq == 0 and now <= self.last_ms:
                    now = time.time_ns() // 1_000_000 - EPOCH_MS
            else:
                self.seq = 0
            self.last_ms = now
            return (now << 22) | (self.worker_id << 12) | self.seq

The last line does the packing. The << operator shifts bits to the left, so now << 22 lifts the time above the 22 bits of worker ID and sequence. The | operator then joins the three parts.

The & 4095 keeps the sequence inside 12 bits. When it wraps to 0, this server has used all 4,096 numbers in this millisecond. The while loop then waits for the clock to tick over.

The clock check raises an error if time has gone backwards. Without it, the server could repeat a millisecond it already used and create a duplicate. The worker ID must also be unique, which a later section covers.

Because each part sits at a fixed place, you can read an ID back. The team uses this helper when a strange row turns up. EPOCH_MS is the constant from the generator above.

from datetime import datetime, timezone

def explain_id(story_id):
    ms = (story_id >> 22) + EPOCH_MS     # undo the shift, add the epoch back
    worker = (story_id >> 12) & 1023     # the middle 10 bits
    seq = story_id & 4095                # the last 12 bits
    made_at = datetime.fromtimestamp(ms / 1000, tz=timezone.utc)
    return made_at, worker, seq

To check it yourself, put both snippets in one file and print explain_id(236065495965528067). We ran it on Python 3.13 and 3.14. It needs Python 3.7 or later, because time.time_ns() arrived in 3.7.

Python prints (datetime.datetime(2026, 10, 14, 10, 0, 0, 123000, tzinfo=datetime.timezone.utc), 7, 3). That is 14 October 2026 at 10:00:00.123 UTC, worker 7, sequence 3. So that row was made at that millisecond, by worker 7, as sequence number 3.

Servers get their worker ID once, at startup, from a config file, a deployment tool or a small registry. That is the only coordination Snowflake needs, and it happens rarely instead of on every write.

The rule is strict. Two running servers must never hold the same worker ID. In our test, two generators with worker ID 4 produced the same first ID in the same millisecond, with no error.

• Fixed config: each server's worker ID is written in its config file. Fine for a few servers, but one copied file creates a silent clash.
• Numbered copies: Kubernetes, a tool that runs many copies of a program, can run them as a StatefulSet. Each copy gets a stable number, 0 to N minus 1 by default, which can become the worker ID.
• A registry with leases: the server claims a free number in a store such as etcd. The claim is a lease with a time-to-live (TTL), and it is deleted when the TTL runs out unrenewed.

The StatefulSet route has one sharp edge. Kubernetes promises at most one running copy per number, but its docs warn that force-deleting a stuck copy can create a duplicate of a still-running one.

For a Snowflake generator, that duplicate means two servers with the same worker ID. If you take worker IDs from StatefulSet numbers, treat a force delete as an ID incident, not a routine fix.

The team picks the registry with leases, because it adds and removes API servers during the evening peak. Notice that the registry and the time servers sit beside the write path, never on it.

A worker ID that moves to a new server needs one more guard. If the new server's clock is behind, it could reuse milliseconds the old holder already stamped.

In our test, a new holder 20 ms behind, starting 5 ms after the old one stopped, repeated 45 IDs. Waiting 20 ms first, our worst clock difference, repeated none.

So before making IDs, wait out your worst clock difference between servers. Or store the last millisecond in the registry, and wait until your clock passes it.

If the clock goes backwards, the generator could reuse milliseconds it already used and create duplicate IDs. So a safe generator refuses to make IDs until its clock passes the last time it used.

Clocks drift: every server's clock runs slightly fast or slow. NTP, the Network Time Protocol, keeps correcting it against time servers. The question is how it corrects.

ntpd is a long-standing NTP program, published at ntp.org. Its documentation says small offsets, under a 128 ms step threshold by default, are slewed. Slewing runs the clock a little slower or faster until it is right, so time never goes backwards.

Offsets above the threshold are stepped: the clock jumps in one go, and a jump can go backwards. ntpd's -x option raises the threshold to 600 seconds, so corrections up to that size are slewed instead.

Slewing has a cost. The same page says typical Unix kernels slew at most 0.5 ms per second, so a 600-second correction takes almost 14 days.

Your servers may run a different time program from ntpd. Check which one they use, and when it may step the clock.

It raises clock moved backwards, as our test shows on a fake clock set 3 ms behind. Without the check, it would stamp millisecond 997, which this server already used, and could repeat an ID.

Once the clock passes millisecond 1,000 again, the next call works and starts the sequence at 0.

The original Snowflake README tells you to keep clocks accurate with NTP, and better still to run NTP in a mode that never moves the clock backwards. Its generator refuses to make IDs until time passes the last ID it made.

Raising an error is the simplest safe choice. A gentler version waits out a jump of a few milliseconds and raises only for big jumps. A server that keeps failing should leave the load balancer until someone checks it.

A restart hides another trap. The generator keeps last_ms only in memory. In our test, a fresh generator with the same worker ID, started in the same millisecond, repeated all three earlier IDs.

So a server that restarts right after a backward jump can repeat old IDs. One fix is to save the last timestamp now and then, and on startup wait until the clock passes it.

One tempting fix does not work. Python's time.monotonic() never goes backwards, but its docs say its reference point is undefined. It cannot tell you the date, so it cannot fill the timestamp part on its own.

Use Snowflake when you need 64-bit numeric IDs and can manage worker IDs safely. Use UUIDv7 when you want a standard format with no worker IDs, and 128 bits is fine.

Size is the first difference. A Snowflake ID takes 8 bytes and a UUID takes 16. Every index stores the ID, and so does every foreign key, a column in another table that points to this row.

We measured what that means. We inserted 10 lakh (1 million) IDs in arrival order into three PostgreSQL 16.15 tables, each with the ID as primary key, and measured the primary key index.

A PostgreSQL index is a B-tree, a tree of 8 KB pages, and the leaf pages at the bottom hold the IDs. The table gives sizes in MiB, where 1 MiB is 1,048,576 bytes.

Two things stand out. The UUIDv7 column is twice as wide, but its index was only about 41% bigger than the BIGINT one. The random UUIDv4 index was another 24 to 27% bigger than UUIDv7.

The fill column explains the second gap. PostgreSQL's docs say B-tree pages are filled to 90% when new largest keys are added at the right edge. Pages that fill up elsewhere split, leaving fragmentation.

In our test each time-ordered ID was bigger than the last, so every insert landed at the right edge and pages stayed 90% full. Random IDs land everywhere and split pages, so ours averaged about 72%. Loading the UUIDv4 table was also clearly slower, 2 to 4 times as long as the BIGINT one in our runs on one machine.

You can check your own tables. Run SELECT pg_size_pretty(pg_relation_size('stories_pkey')); for the size. It prints a rounded value such as 21 MB, and PostgreSQL's MB is the same as a MiB. After CREATE EXTENSION pgstattuple; run SELECT avg_leaf_density FROM pgstatindex('stories_pkey'); for the fill in percent. That needs a superuser or the pg_stat_scan_tables role.

Setup is the second difference. UUIDv7 needs no worker IDs, and support is now built in. Python added uuid.uuid7() in version 3.14, and PostgreSQL 18, released on 25 September 2025, added a uuidv7() function.

Python's docs say its uuid7() uses a 42-bit counter to keep values in order within a millisecond. In our run on 3.14.6, one million values from one process came out strictly increasing.

Values from different machines in the same millisecond still sort by bits that do not reflect true order. That is the same k-sorted trade-off as Snowflake's worker IDs.

Python's uuid7() keeps going when the clock goes back

Python's uuid7() handles a backward clock differently from the generator above. It does not refuse. In the uuid.py source of CPython 3.14.6, the standard Python interpreter, a clock reading earlier than the last UUID's time is replaced by that time plus 1 ms.

Values stay unique and in order, but the time inside them can run ahead of the real clock. We set a fake clock back 50 ms and called uuid7() twice per millisecond.

One second later, the embedded time was 1,052 ms ahead of the clock. At five calls per millisecond it was 4,055 ms ahead. At one call per millisecond the gap held at 51 ms.

So any process making more than 1,000 UUIDs a second keeps drifting further ahead after a backward jump. This is undocumented as of 19 October 2026; the docs mention only the counter. Pin your Python version, and do not treat the time inside a UUIDv7 as an exact creation time.

The app already uses BIGINT columns and numeric share links. Moving to UUIDs would touch every table and every old link. The team chooses Snowflake, and notes UUIDv7 as the default for any new service that starts fresh.

You can see the rounding yourself. Open a Node.js prompt and run console.log(JSON.parse('{"id":236065495965528067}').id). It prints 236065495965528060, not the ID you sent.

Start from the requirements, show why simple counters fail, then build the 64-bit layout and defend each part. Finish with clocks and worker IDs, because that is where the design breaks.

1. 1

   Ask what the IDs need

   Uniqueness is a given. Ask about time order, size limits, numbers or text, peak IDs per second, and whether IDs may reveal creation time.

2. 2

   Reject the easy options out loud

   A central counter is a single point of failure. Random UUIDs scatter inserts across the index. One sentence each is enough.

3. 3

   Draw the bit layout

   Sign bit, 41 bits of time, 10 bits of worker ID, 12 bits of sequence. Check the maths: about 69 years, and 4,096 IDs per millisecond per worker.

4. 4

   Walk through one ID

   Read the clock, compare it with the last millisecond, bump or reset the sequence, wait if the sequence is full, then shift and join.

5. 5

   Cover the two failure modes

   For a clock that goes backwards, refuse or wait. For duplicate worker IDs, hand them out with leases and stop when a lease is lost.

6. 6

   Close with the alternatives

   Say when UUIDv7 is the better pick, and that browsers need 64-bit IDs as strings.