Primary Key - UUID vs Auto Increment Integer/Sequence, Which is Better?
TLDR: Choose Auto Increment Integer if it needs to be exposed externally
and readability is important, otherwise, choose UUID.
UUID
Pros
- Globally unique. e.g. No false positive for finding items using log search
- Stateless, it can be generated on the fly.
- Secure since malicious user can't guess the ID.
- Version 1 UUID stores timestamp info, could be useful sometimes.
Cons
- Not readable.
- For database like MySQL, Oracle, which uses clustered primary key, version 4 randomly generated UUID will hurt insertion performance if used as the primary key. This is because it requires reordering the rows in order to place the newly inserted row at the right position inside the clustered index. On the other hand, PostgreSQL uses heap instead of clustered primary key, thus using UUID as the PK won't impact PostgreSQL's insertion performance.
Auto Increment Integer/Sequence
Pros
- Readable. This is especially valuable if we would expose it externally. Thinking of issue id, obviously, issue-123 is much more readable than issue-b1e92c3b-a44a-4856-9fe3-925444ac4c23.
Cons
- It can't be used in the distirbuted system since it's quite likely that different hosts could produce exactly the same number.
- It can't be generated on the fly. Instead, we must consult the database to figure out the next available PK.
- Some business data can be exposed, since the latest ID could represent the total number of inventory. Attackers can also scan the integer range to explore leakage (though it shouldn't happen if ACL is implemented correctly).