Pick the best database primary key
| SERIES (aka INT) | BIGSERIES (aka BIGINT) | Snowflake ID | Sonyflake | Randflake | 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 allows 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. | Inspired by Snowflake ID, works well in a distributed system, unpredictable, unique random ID generator, timestamp resolution of 1000ms, works on more distributed machines 2^17 than snowflake 2^10. | Similiar to xid but larger in terms of bits footprint in favor of a better timestamp resolution of 1ms. | Completely 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. Therefore 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 so 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 also works well in a distributed system. | The most famous identifier, not sortable, completely random generated so it works well with distributed system. It's a completly opaque 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 quickly discerning which IDs are generated by a single machine. |
| General Specification | |||||||||||
| Example | 13 | 120310392 | 1584093427933380608 | 3vgoe12ccb8gh | 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 | 64 | 200 | variable | 96 | 128 | 128 | 128 |
| GB needed to store 1 Billion Keys | 4GB | 8GB | 8GB | 8GB | 8GB | 25GB | variable | 12GB | 16GB | 16GB | 16GB |
| Number of characters | 0 - 10 | 0 - 20 | 19 | 13 | 25 | variable | 20 | 32 | 36 | 36 | |
| DB Specification | Suggested DB Type | INT | BIGINT | 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 | low (Stored decrypted) / medium (Stored encrypted) | high | variable | medium | high | high | high |
| Is timestamp based? | No | No | Yes | Yes | Yes | Yes | No | Yes | |||
| Is sortable by creation date? | Yes | Yes | Yes | Yes | Yes (With the Secret Key) / No (Without the Secret Key) | Yes | No | Yes | Yes | No | No |
| Can be easily used in a distributed system? | No | No | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| Supports cursor-based pagination without adding a dedicated createdAt field? | Yes | Yes | Yes | Yes | Yes (With the Secret Key) / No (Without the Secret Key) | Yes | No | Yes | Yes | No | No |
| Needs to be configured (eg. machine ID, shard ID)? | No | No | Yes | Yes | Yes | No | No | No | Yes (machine ID) | No | No |
| BIT Distribution | |||||||||||
| Time | 0 | 0 | 41 bit (msec res.) | 39 bit (10msec res) | 30 bit (1000msec 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 | 17 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 | 17 bit | 4 chars (32 bit UTF8 encoded, |
0 | 40 bit (24 machine + 16 process) |
0 | 0 | 48 bit (MAC Address) |
| Randomness | 0 | 0 | 0 | 0 | 0 | 8 chars (64 bit UTF8 encoded) |
variable | 0 | 80 bit | 122 bit | 0 |
| Security | |||||||||||
| Is completely random? | No | No | No | No | Yes | No | Yes | No | No | Yes | No |
| Predictability of the ID | High | High | Medium | Medium | Low | Medium | Low | Medium-High | Low | Low | Medium-High |
| Leaks the count of items | Yes | Yes | Partially | Partially | No | Partially | No | Yes? | No | No | No |
| Leaks information about the machine/process? | No | No | Yes | Yes | No | Yes | No | Yes | No | No | Yes, MAC Address |
| Leaks the date of creation? | Partially (through interpolation of known dates it could be guessed) |
Partially (through interpolation of known dates it could be guessed) |
Yes | Yes | No | Yes | No | Yes | Yes | No | Yes |