Snowflake vs UUID Visualization | IDs and Index Splits

The idea: an ID that nobody has to hand out

A single database can number its rows with an auto-increment counter. Once many machines create rows at the same time, asking one central counter for every ID is slow and a single point of failure. So each machine makes its own IDs. Two things then matter: the IDs must never collide, and it helps a lot if they sort in creation order. This page compares three common answers: Twitter Snowflake, UUIDv4 and UUIDv7.

Three bit bars. Snowflake, 64 bits: sign 1, timestamp 41, machine id 10, sequence 12. UUIDv7, 128 bits: unix_ts_ms 48, version 4, rand_a 12, variant 2, rand_b 62. UUIDv4, 128 bits: random 48, version 4, random 12, variant 2, random 62. — Snowflake and UUIDv7 put the time first, so they sort by time; UUIDv4 is random from the first bit.

On the canvas, two machines, M1 and M2, take turns making primary keys for one table. The top right panel shows the bits of the latest ID, the log lists the IDs in the order they were made, and the bottom right panel is the table's primary key index: a small B+ tree whose leaf pages hold 4 keys. Each key is drawn with its creation number, so you can see where it landed. Time runs in milliseconds; each request comes 1 ms after the one before.

Snowflake: 64 bits, 4 fields

A Snowflake ID is a 64-bit integer, so it fits a BIGINT column:

1 sign bit, always 0, so the number stays positive.
41-bit timestamp: milliseconds since a custom epoch. Twitter's is 2010-11-04 01:42:54.657 UTC (twepoch = 1288834974657). 41 bits of milliseconds last about 69 years.
10-bit machine id: up to 1,024 generators. Twitter split it into 5 bits of datacenter and 5 of worker. Each machine must get a different one (from config or a coordinator such as ZooKeeper).
12-bit sequence: counts IDs made by this machine in the same millisecond.

id = (timestamp - twepoch) << 22 | machineId << 12 | sequence

No two machines share a machine id, and one machine never reuses a (millisecond, sequence) pair, so IDs never collide, and no network call is needed. Demo 1 makes one ID on each machine; the two differ only in the time and machine fields.

4,096 per millisecond

12 bits of sequence give 4,096 IDs per millisecond per machine, about 4 million a second. When a request arrives in the same millisecond as the last one, the sequence goes up by 1. If it wraps from 4095 back to 0, the next ID would repeat the first one of this millisecond, so the generator spins until the clock reaches the next millisecond (tilNextMillis) and starts again at sequence 0. In Demo 2, M1 gets 4,097 requests in one millisecond: the last one waits 1 ms.

When the clock goes backwards

Uniqueness depends on the clock only moving forward. Real clocks do jump back: NTP corrects a fast clock, a VM is restored, an operator sets the time. A generator remembers the last millisecond it used (lastTimestamp). If its clock reads earlier than that, it could make an ID it has already made. It has two choices:

Refuse: throw an error, so the request fails. Twitter's code says Clock moved backwards. Refusing to generate id for 3 milliseconds.
Wait: sleep until the clock passes lastTimestamp, then go on. Fine for a few milliseconds; for a big jump, refusing (and alerting) is safer.

In Demo 3, M2's clock is stepped back 5 ms. Its next request is refused (3 ms behind), then, with the policy set to wait, the following one waits 3 ms and succeeds. Running NTP in slew mode, which slows the clock instead of stepping it, avoids most of this.

UUIDv4 and UUIDv7

A UUID is 128 bits, written as 32 hex digits in groups of 8-4-4-4-12. Four bits give the version and two bits the variant (10), as defined in RFC 9562.

UUIDv4 fills the other 122 bits with random numbers. It needs no clock and no machine id, and two v4 IDs practically never collide. But it carries no time, so its order is random.

UUIDv7 puts the Unix time in milliseconds into the first 48 bits and fills the remaining 74 with random bits (an implementation may use some of them as a counter). It needs no machine id either, and because the time comes first, sorting v7 IDs sorts them by creation time. In Demo 4 the v7 table sorts into creation order, 7 of 7 pairs; the v4 table does not.

Primary keys and the B+ tree

Most databases keep a table's primary key in a B+ tree (in InnoDB the table is that tree). Its leaf pages hold the keys in order. Where a new key lands depends only on its value:

A time-ordered key (Snowflake, v7, auto-increment) is larger than every key so far, so it goes at the end of the rightmost leaf. When that page is full, the database starts a new page on the right and leaves the old one full (InnoDB and PostgreSQL both treat this case specially).
A random key (v4) lands in any leaf. A full leaf splits in half, and both halves stay part empty until more random keys arrive.

Two B+ trees after 12 inserts. UUIDv7: three full leaf pages p1 p2 p3 holding keys 1 to 12 in order, 2 splits, 16 page writes. UUIDv4: five leaf pages with keys in mixed order, 4 splits, 20 page writes, leaves 60 percent full. — Time-ordered keys fill one page after another; random keys split pages all over the index.

Demo 5 inserts 12 keys of each kind. The scoreboard counts page splits, pages touched (page writes: 1 per insert, 3 for an insert that splits a page) and the leaf fill. On a real table with millions of rows the difference is bigger than these numbers show: the rightmost leaf stays in memory, while random keys need almost every leaf in the buffer pool, so inserts turn into random disk reads, and the half-empty pages make the index larger. Secondary indexes in InnoDB also store the primary key, so a 16-byte UUID costs more than an 8-byte BIGINT in every index.

Side by side

	Snowflake	UUIDv7	UUIDv4
Size	64 bits (`BIGINT`)	128 bits	128 bits
Sorts by time	yes (ms, then machine)	yes (ms)	no
Needs a machine id	yes, unique per generator	no	no
Needs a clock	yes, and it must not go back	yes (order only)	no
Uniqueness from	machine id + sequence	74 random bits	122 random bits
Limit	4,096 per ms per machine	none in practice	none in practice
B+ tree inserts	append, few splits	append, few splits	random, many splits
Leaks	creation time, machine	creation time	nothing

See also B+ Trees for how leaf pages split, B+ Tree as a Database Index for the primary key index of a table, and Partitioning vs Sharding for why a distributed table needs IDs that every shard can make on its own.

What the page leaves out

How machine ids are handed out and reclaimed (config, ZooKeeper, a lease table), and variants such as Sonyflake (10 ms ticks, 16-bit machine id) or Instagram's IDs made inside PostgreSQL.
Clock skew between machines: two machines' IDs are only roughly in time order (k-sorted). Here both clocks agree until you step one back.
UUIDv7's optional counter in rand_a for strict order inside one millisecond, and other versions (v1, v6, v8), ULID and KSUID.
Real page sizes (16 KB in InnoDB, 8 KB in PostgreSQL), fill factors, upper tree levels and page merges.
Insert contention on the one hot rightmost page under heavy concurrent load.