Lab · Distributed data

IDs: from auto-increment to UUIDs and Snowflake

Every row needs an ID that no other row has. Try the five main ways to make one, in the order they appeared, and measure what each costs: repeated IDs, order, trips over the network and the index.

Lab time 3.0 s

A row to save

Same ID on two rows

every ID once

IDs out of time order

smaller than the ID before

Index page fill

97%

1 page of 32 keys

Pages written

by the last 100 rows, of 1

Trips for IDs

0.0

per 100 rows: none needed

Bytes per ID

8bytes

64 bits

Numbers add up:31 saved + 0 failed + 0 in progress = 31 rows asked forThe lab checks this sum as it runs, so a counting mistake would show here.

Controls

How IDs are made

The database numbers rows 1, 2, 3 as it saves them.

Setup

Split rows over two databasesOff

Server A saves to DB 1, server B to DB 2. Starts empty tables.

Server B's clock runs behindOff

1 s behind server A. Matters for IDs that start with the time.

Rows saved12/s

Try this

Short experiments. Each one sets up the lab for one case and asks you to guess the result. Then it shows what really happens.

Auto-increment in one database
Two app servers save rows into one database. The database numbers each row 1, 2, 3 as it saves it.
Easy
Auto-increment across two databases
The rows are now split over two databases. Each database still numbers its own rows.
Medium
Blocks of 100 numbers from a ticket server
One ticket server hands out numbers. Each app server takes 100 at a time, then gives them out itself.
Medium
Random UUIDs in a growing index
Each app server makes a random 128-bit UUID (version 4) for every row. No server asks anyone.
Medium
UUID v7: time first, then random bits
Each ID starts with the time in milliseconds, then random bits. Still 128 bits, and still made with no trips.
Medium
The ticket server stops
Blocks of 100 numbers again. After 5 seconds, the ticket server stops answering.
Hard
Snowflake IDs with one clock 1 s behind
Snowflake IDs: time, machine id and a count in 64 bits. Server B's clock runs 1 s behind server A's.
Hard

The five ways side by side

What each way of making IDs gives and costs
Way	Size	Shared counter?	In time order?	Safe across databases?	Can be guessed?
Auto-increment (running)	8 bytes	Yes: the database	Yes	No: each database repeats 1, 2, 3	Yes: the next is one more
Blocks from a ticket server	8 bytes	Yes: one trip per block	Roughly	Yes	Mostly
UUID v4: random	16 bytes	No	No	Yes	No: 122 random bits
UUID v7: time first	16 bytes	No	Yes, if clocks agree	Yes	Partly: the time shows
Snowflake: 64 bits	8 bytes	Only to hand out machine ids	Yes, if clocks agree	Yes	Partly: time and machine show

The measures above come from the running model. This table states what each method gives by design.

What just happened

Nothing yet. Start traffic or run an experiment.

What this model simplifies

An index page here holds 32 keys, so splits show sooner. Real pages hold hundreds, and the effect is the same.
Only the leaf pages of the index are modelled. The pages above them are small and usually stay in memory.
A row whose server has no numbers fails at once. A real server would wait for a timeout first.
Changing how IDs are made, or splitting the rows, starts with empty tables, so each setup is measured from its start.