Concept · Consistency & coordination
Unique ID generation
UUIDv4, UUIDv7, Snowflake IDs and ticket servers: uniqueness, ordering and index locality.
Every record needs an ID. A single database sequence is the simplest answer until IDs must be generated on many machines, by clients, or before a row reaches the database.
| Scheme | Size | Time-ordered | Needs coordination | Notes |
|---|---|---|---|---|
Database sequence (bigserial) | 64 bits | Yes | The database | Simple; reveals volume ("order #1043"); one writer |
| UUIDv4 | 128 bits | No | No | Random; inserts scatter across B-tree indexes |
| UUIDv7 | 128 bits | Yes (ms prefix) | No | 48-bit timestamp + random bits; RFC 9562 |
| ULID | 128 bits | Yes | No | Similar to UUIDv7, base32 text form |
| Snowflake | 64 bits | Yes (ms) | A unique worker ID per process | 41 bits time, 10 bits worker, 12 bits sequence |
| Ticket server / ranges | 64 bits | Roughly | Occasionally | Each node reserves a block (say 1,000) of a central counter |
Why ordering matters for performance
B-tree indexes keep keys sorted. Random keys (UUIDv4) insert on random pages, so the whole index must stay in memory and pages split constantly. Time-ordered keys append near the right edge of the index, like a sequence, which keeps inserts cheap. For new systems, UUIDv7 is a good default: no coordination, sortable, widely supported.
Snowflake
| 1 bit 0 | 41 bits: milliseconds since a custom epoch | 10 bits: worker | 12 bits: sequence |
→ ~69 years, 1,024 workers, 4,096 IDs per millisecond per workerCompact and fast, but every running process needs a unique worker ID (leased from a coordination service, or taken from a stable ordinal), and the generator must refuse to issue IDs if the clock moves backwards.
What IDs don't promise
- Time-ordered IDs from different machines are only roughly ordered: clocks differ by milliseconds (clocks and ordering).
- Sequential IDs are guessable. For public identifiers (short links, invoice URLs), use random or scrambled IDs and still check authorisation.
See keyset pagination for using ordered IDs as cursors.