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).