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 | Suggested DB Type | INT | BIGINT | BIGINT | BIGINT | VARCHAR(25) | VARCHAR(X) | VARCHAR(20) | VARCHAR(32)/UUID | UUID | UUID |
| 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 |
| Security | ||||||||||
| 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) |
Partially (thorugh interpolation of known dates it could be guessed) |
Yes | Yes | Yes | No | Yes | Yes | No | Yes |