When designing an API on Postgres or MySQL, the "auto-increment key or UUID?" question shows up sooner or later. Here's the comparison we actually used to decide at Toolbrik.
UUID v4 (random)
- Pros: reveals nothing about business volume or ordering; generated client-side with no round trip; impossible to brute-force guess (122 bits of entropy).
- Cons: scattered index pages in b-trees (lower cache density); 36 characters of text.
UUID v1 (time-based)
- Pros: approximate temporal ordering, useful for event sourcing or date-based partitioning.
- Cons: reveals the creation date and, depending on implementation, the node that generated it. Guessable if you use a fixed node.
Sequential IDs
- Pros: compact, fast indexes, easy debugging, clean URLs (
/users/42). - Cons: enumerable — anyone can scrape your resource; require the server to generate; a concurrency bug can clone them.
Practical recommendation
- Use UUID v4 as your default primary key.
- Generate UUIDs server-side if you want to hide ordering, or client-side if you want zero latency.
- For event tables, consider v1; for internal catalogs, a sequence is perfectly reasonable.
- ALWAYS store the UUID as a native
uuidtype, notvarchar, and use it as a cache key.
Normalizing UUIDs from unreliable sources
Not every system emits UUIDs in the same shape: you'll run into uppercase letters, the urn:uuid: prefix used by some standards (WSDL, ATOM), or 32-character strings with no hyphens. The UUID validator accepts all three variants, detects the version and variant (RFC 4122 vs. reserved), and normalizes it to lowercase canonical form before you store it.
Generate as many as you need with our UUID generator (v4 and v1, with or without hyphens) and validate any list with the validator before committing it.