🔑 Awesome Identifiers

Pick the best database primary key

SERIES (aka INT) BIGSERIES (aka BIGINT) Snowflake ID Sonyflake CUID nanoId xid ULID UUIDv4 UUIDv1
Best for Lightweight and fast PK, easy to read, ideal when you don't have a distributed system. Like SERIES but allow up to 2^64 (Quadrillion) records, still not ideal for a distributed system. Same performance of BIGSERIES with the plus of supporting a distributed system by dedicating some bits to sharding and sequencing. Inspired by Snowflake ID, longer timestamp lifetime despite the resolution of 10ms, works on more distributed machines 2^16 than snowflake 2^10. Similiar to xid but larger in terms of bits footprint in favor of a better timestamp resolution of 1ms. Completly random string, works well in a distributed system, useful for public facing identifiers since it's a completly opaque token. Not useful for sorting or pagination, not K-ordered. Thereafter not ideal as a database PK. Inspired by the Mongo Object ID, but shorter in terms of chars, 20 vs 24. XID is Sortable and works well in a distributed system. Smaller binary size than UUID or ULID could improve performance on huge databases. Timestamp resolution of 1 second. Like UUIDv4 but lexicographically sortable and without the ugly dashes. It supports generating up to 1.21e+24 unique ULIDs per millisecond. Thanks to the large number of bits of randomness it works also well in a distributed system. The most famous identifier, not sortable, completly random generated so it works well with distributed system. It's a completly opque identifier like nanoid, less compact than ULID due to the hexadecimal representation separated by the dashes. Even if encoding the timestamp within it, UUIDv1 is not lexicographically sortable. Since it uses the MAC address it allows to quickly discern which IDs are generated by a single machine.
General Specification
Example 13 120310392 1584093427933380608 cjld2cjxh0000qzrmn831i7rn V1StGXR8_Z5jdHi6B-myT 9m4e2mr0ui3e8a215n4g 01arz3ndektsv4rrffq69g5fav fa23bd22-a5ac-46c2-8a67-bda88ca3e6e4 e6a21b7c-5c2f-11ed-9b6a-0242ac120002
Number Range 0 - 4,294,967,295 0 - 18,446,744,073,709,551,615 - - - - - - - -
Numbers of bit 32 64 64 64 200 variable 96 128 128 128
GB needed to store 1 Billion Keys 4GB 8GB 8GB 8GB 25GB variable 12GB 16GB 16GB 16GB
Numbers of characters 0 - 10 0 - 20 19 25 variable 20 32 36 36
DB Specification
Performance impact on Database (eg. Storage, JOIN operations, etc...) very low low low low high variable medium high high high
Is timestamp based No No Yes Yes Yes No Yes
Sortable by creation date Yes Yes Yes Yes Yes No Yes Yes No No
Can be easly used in a distributed system No No Yes Yes Yes Yes Yes Yes Yes Yes
Support cursor-based pagination without adding a dedicated createdAt field Yes Yes Yes Yes Yes No Yes Yes No No
Needs to be configured (eg. machine ID, shard id) No No Yes Yes No No No Yes (machine ID) No No
BIT Distribution
Time 0 0 41 bit (msec res.) 39 bit (10msec res) 8 chars
(64 bit UTF8 ecoded, 41 bit real entropy, 1msec resolution)
0 32 bit
(1sec resolution)
48 bit 0 60 bit
Sequence/Counter 32 bit 64 bit 12 bit 8 bit 4 digits
(32 bit UTF8 encoded, 10-11bit of entropy)
0 24 bit
(starting random)
0 0 16 bit
(clok seq.)
Machine Id + Process Id 0 0 10 bit 16 bit 4 chars
(32 bit UTF8 encoded,
0 40 bit
(24 machine + 16 process)
0 0 48 bit
(MAC Address)
Randomnes 0 0 0 0 8 chars
(64 bit UTF8 encoded)
variable 0 80 bit 122 bit 0
Is completly Random? No No No No No Yes No No Yes No
Predictability of the ID High High Medium Medium Medium Low Medium-High Low Low Medium-High
Leaks the count of items Yes Yes Partially Partially Partially No Yes? No No No
Leaks information about the machine/process No No Yes Yes Yes No Yes No No Yes, MAC Address
Leaks the date of creation Partially
(thorugh interpolation of
known dates it could be guessed)
(thorugh interpolation of
known dates it could be guessed)
Yes Yes Yes No Yes Yes No Yes