UUIDs: When Auto-Increment Isn't Enough
Why unique identifiers matter and when to use them instead of sequential IDs.
Your database uses auto-incrementing IDs. 1, 2, 3, 4... Simple.
Then you merge two databases. Or expose IDs in URLs. Or someone figures out they can guess other users' data by incrementing the ID in the URL.
Sequential IDs have problems. UUIDs solve some of them.
What's a UUID?
Universally Unique Identifier. A 128-bit number, typically displayed as 32 hex characters with dashes:
550e8400-e29b-41d4-a716-446655440000
The math behind UUID generation makes collisions astronomically unlikely. You can generate UUIDs on different machines, at different times, without coordination, and they won't collide.
Why Not Just Use Auto-Increment?
Security. Sequential IDs leak information. If your user ID is 15847, attackers know there are at least 15846 other users. They can enumerate resources by trying sequential IDs.
Distributed systems. Multiple servers generating IDs need coordination to avoid duplicates. UUIDs need no coordination.
Merging data. Combining databases with sequential IDs is a nightmare. UUIDs merge cleanly.
Offline creation. Mobile apps can create records offline with UUIDs. No need to wait for a server-assigned ID.
UUID Versions
Not all UUIDs are created the same way.
Version 1: Based on timestamp and MAC address. Unique, but reveals when and where it was created.
Version 4: Random. Most common. No information leakage.
Version 7: Timestamp-based but sortable. Newer databases benefit from this.
For most uses, version 4 (random) is the right choice.
The Trade-offs
UUIDs aren't free.
Size. 128 bits vs 32 bits for an integer. Larger indexes, more storage.
Readability. user/15 is easier to remember than user/550e8400-e29b-41d4-a716-446655440000.
Performance. Random UUIDs cause index fragmentation in some databases. UUID v7 addresses this.
Debugging. Searching logs for a UUID is tedious compared to simple numbers.
When to Use What
Use auto-increment when:
- Internal-only, never exposed to users
- Simple applications, single database
- Human readability matters
Use UUIDs when:
- IDs appear in URLs or APIs
- Multiple systems create records
- Security through obscurity helps
- Merging data is possible
Practical Tips
Don't use UUIDs as primary keys in MySQL. Use them as a secondary unique column instead. MySQL's clustered indexes perform poorly with random UUIDs.
Consider UUID v7. If your database supports it, timestamp-sortable UUIDs give you the best of both worlds.
Remove dashes for URLs. 550e8400e29b41d4a716446655440000 is still a valid UUID and shorter in URLs.
UUIDs solve real problems that sequential IDs can't. But they're not always the right choice. Pick based on your actual requirements, not because UUIDs sound more professional.