UUID vs autoincrement ID: which to use in your database
One of the most important decisions in database design is choosing the primary key. The two main options are autoincrement IDs (1, 2, 3...) and UUIDs (unique strings like 550e8400-e29b-41d4-a716-446655440000). Let's see pros and cons of each.
Autoincrement IDs: simple and compact
They're the default in MySQL, PostgreSQL, SQLite. The database automatically assigns the next number.
- Pros: compact (4 or 8 bytes), fast to index, readable, take less space.
- Cons: reveal the number of records (competitive intel!), unsafe in public URLs (e.g. /order/42 reveals you have 42 orders), hard to sync across distributed databases.
UUIDs: unique without coordination
UUIDs are 128-bit strings generated locally, without needing a central authority. Even two servers generating UUIDs simultaneously won't have collisions.
- Pros: unique without coordination, safe in public URLs, ideal for distributed systems, microservices and offline-first sync.
- Cons: larger (16 bytes or 36 characters), less efficient in indexes (random order = index fragmentation), less readable for debugging.
UUID v4 vs v7: an important difference
UUID v4 is fully random: perfect for uniqueness but terrible for B-tree indexes (each insert goes in a random index position = fragmentation).
UUID v7, recently introduced, includes a Unix timestamp in the first 48 bits. Result: UUIDs generated at close moments are also close in ordering. This solves the index fragmentation problem, making them nearly as efficient as autoincrement, while keeping the distributed uniqueness advantage.
When to use what
- Simple monolithic app, single-DB: autoincrement is fine.
- Public API where the ID is visible in URL: UUID (avoids enumeration attacks).
- Microservices / multi-DB / offline-first: UUID mandatory.
- New high-traffic systems: UUID v7 (uniqueness + good index performance).
- Legacy high-traffic systems: keep autoincrement, don't migrate just for trend.
In short
For most new projects, UUID v7 is the most balanced choice: unique, distributed, and with acceptable indexing performance. For simple and local systems, autoincrement remains an excellent, more compact and efficient choice.