Side by side
| Dimension | UUID | Auto-increment IDs |
|---|---|---|
| Generation | UUID: generated independently by applications or databases | Auto-increment: allocated by a database sequence or counter |
| Storage | 128 bits; 16 bytes in native or binary form | Typically 32 or 64 bits; BIGINT is usually 8 bytes |
| Uniqueness scope | Designed for independent generation; collisions remain possible | Unique within a correctly managed allocator's scope |
| B-tree locality | UUIDv4 scatters inserts; UUIDv7 improves locality | Increasing keys favor right-edge inserts |
| Multiple writers | No shared ID allocator required | Needs coordinated allocation, ranges, or separate namespaces |
| Ordering | UUIDv4: none; UUIDv7: approximate generation-time order | Allocation order, not necessarily commit order |
| Public exposure | UUIDv4 is difficult to guess; UUIDv7 exposes approximate time | Predictable values can reveal approximate activity |
| Operational concerns | Validate generator quality, version, and storage format | Manage sequence state, failover, exhaustion, and imports |
The differences that matter
Independent generation changes the write path. An application can assign a UUID before inserting a row, uploading an object, or publishing an event. Auto-increment normally assigns the ID through the database, although preallocated ranges can reduce allocation coordination. UUIDs remove the shared ID-allocation dependency, not the coordination needed to store or replicate data.
Key width affects more than the primary-key column. Foreign keys and indexes also carry identifiers, and some storage engines include the primary key in every secondary index. A native UUID is 16 bytes versus 8 bytes for a typical BIGINT; storing UUIDs as 36-character text adds avoidable overhead. The total cost depends on the engine, schema, and index count.
UUID version matters for B-tree writes. UUIDv4 distributes inserts across the key space, which can increase page churn and reduce cache locality. UUIDv7 places a timestamp first and generally improves locality, but it remains wider than BIGINT. Increasing IDs can also create right-edge contention or hot range partitions, so sequential does not always mean more scalable.
Neither option defines business ordering. Auto-increment values can have gaps and can commit out of allocation order. UUIDv7 timestamps depend on generator behavior and clock accuracy, so they do not establish global causality. Use explicit timestamps, per-conversation sequences, or log offsets when the product needs a particular ordering guarantee.
Identifier choice is separate from authorization and retry safety. Unpredictable UUIDs make casual enumeration harder but do not replace access checks. Likewise, generating a fresh UUID for every retry creates distinct requests rather than deduplicating them; retries need a stable operation ID or idempotency key.
Choose UUID when
- Multiple regions or services must create identifiers without contacting one shared allocator.
- Offline clients must create objects and references before synchronizing with the server.
- Records from independent databases will be merged, and coordinating numeric namespaces would complicate operations.
- The application needs an identifier before persistence, such as linking an upload, metadata record, and asynchronous processing event.
- You want hard-to-guess public identifiers, accept the storage cost, and will still enforce authorization.
Choose Auto-increment IDs when
- A single primary database owns record creation, and independent ID generation is not a requirement.
- Large tables and many foreign-key references make compact keys valuable.
- The workload benefits from increasing B-tree keys, and measurements do not show problematic right-edge contention.
- Internal identifiers need to be simple to inspect and operate, while public APIs can use separate opaque identifiers if necessary.
A worked example
Offline photo creation across three regions
Suppose a photo service stores 100 million metadata rows and lets mobile clients create albums while offline. Clients need stable IDs immediately so photos can reference albums before either reaches the server. UUIDs fit this requirement without reserving numeric ranges per device; UUIDv7 can improve B-tree locality compared with UUIDv4. At this scale, raw ID values occupy about 1.6 GB as native UUIDs versus 0.8 GB as 64-bit integers, before row and index overhead. Foreign keys and secondary indexes can amplify the difference. If offline creation disappears and one database owns all writes, BIGINT auto-increment becomes a simpler, smaller alternative.
In the interview
- Start with the generation boundary: one database, several shards, multiple writable regions, or offline clients. Explain which components must allocate IDs without depending on another service.
- Name the actual types: UUIDv4 or UUIDv7, and BIGINT rather than an unspecified integer. Interviewers may probe collision handling, clock behavior, and numeric exhaustion.
- Connect the choice to the storage engine and partition key. Random UUIDs can spread range-partitioned writes, while hashing can distribute either identifier type; UUIDs alone do not provide a sharding strategy.
- State the ordering requirement separately. Expect follow-up questions about commit order, message order, pagination, and clock skew.
- Explain failure behavior: sequence restoration after imports or failover, duplicate-key handling, and stable IDs across retries. Allocating an ID successfully does not mean the associated write succeeded.
Common mistakes
- Treating every UUID as random, or claiming UUIDv7 provides strict global chronological order.
- Claiming auto-increment requires an application-level network call for every allocation; databases may cache sequence values, and distributed designs can allocate ranges.
- Assuming UUIDs prevent unauthorized access, guarantee retry deduplication, or make a database globally writable by themselves.
- Using an increasing ID as a gap-free counter, commit timestamp, or safe change-feed cursor without accounting for concurrent transactions.
Where it comes up
Interview questions where choosing between UUID and Auto-increment IDs comes up.
- Design a URL shortener like BitlyUnique code generation requires explaining allocation coordination and why a full UUID is usually too long to serve directly as a short code.
- Design a chat app like WhatsAppMessage IDs must support distributed creation and retry deduplication without being mistaken for a per-conversation ordering mechanism.
- Design Dropbox or Google DriveOffline clients need stable object identifiers that can be merged without colliding across independently created namespaces.
- Design Google PhotosAn identifier assigned before upload can connect object storage, metadata, and asynchronous processing while keeping retry behavior explicit.
- Design a large data migration to the cloudMerging datasets requires handling existing identifier collisions and restoring sequence state safely before new writes begin.
Questions people ask
Is UUID slower than auto-increment?
It depends on the UUID version, storage format, database engine, and workload. Random UUIDv4 keys can hurt B-tree locality, while UUIDv7 usually improves it. UUIDs are still wider than BIGINT, so compare realistic indexes, cache usage, and write latency rather than assuming a universal performance ratio.
Should I use UUIDv4 or UUIDv7 as a primary key?
UUIDv7 is often a good candidate when you want independent generation and better B-tree insertion locality. UUIDv4 is useful when random identifiers are preferred and exposing approximate creation time is undesirable. Neither provides strict global ordering, and both require a reliable generator.
Can I use auto-increment IDs in a distributed database?
Yes, but the allocation scope must be explicit. Options include a shared sequence, nonoverlapping ranges, or a composite key containing a shard identifier. These approaches add allocation and recovery rules, and they do not automatically provide global commit ordering.